oracle——使用exists进行动态数据填充-爱代码爱编程
前言
项目团队开发中,有时候需要制定系统配置表,但新增数据时,为了保证数据的唯一性,除了使用唯一键
外,还可以先查询是否存在,再判断是否进行数据新增。
本次介绍一种新的编写SQL方式,也能实现同样的功能。
表的创建
如下,使用flyway
创建了一张新的表,sql如下所示:
declare
cnt integer ;
begin
SELECT COUNT(0)
INTO cnt
FROM USER_ALL_TABLES
where table_name = upper('IMES_INDICATOR_NAME_MODEL_PARAM');
IF cnt = 0 THEN
EXECUTE IMMEDIATE 'CREATE TABLE IMES_INDICATOR_NAME_MODEL_PARAM(
ID INTEGER NOT NULL,
SYSTEM_CODE VARCHAR2(64) NOT NULL,
SYSTEM_NAME VARCHAR2(100) NOT NULL,
MODEL_CODE VARCHAR2(100) NOT NULL,
MODEL_NAME VARCHAR2(100) NOT NULL,
PARAM_CODE VARCHAR2(100) NOT NULL,
PARAM_NAME VARCHAR2(100) NOT NULL,
PRIMARY KEY (ID)
)';
EXECUTE IMMEDIATE 'COMMENT ON TABLE IMES_INDICATOR_NAME_MODEL_PARAM IS ''指标取数计算规则下拉选项配置表''';
EXECUTE IMMEDIATE 'COMMENT ON COLUMN IMES_INDICATOR_NAME_MODEL_PARAM.ID IS ''主键ID''';
EXECUTE IMMEDIATE 'COMMENT ON COLUMN IMES_INDICATOR_NAME_MODEL_PARAM.SYSTEM_CODE IS ''系统名称编号''';
EXECUTE IMMEDIATE 'COMMENT ON COLUMN IMES_INDICATOR_NAME_MODEL_PARAM.SYSTEM_NAME IS ''系统名称''';
EXECUTE IMMEDIATE 'COMMENT ON COLUMN IMES_INDICATOR_NAME_MODEL_PARAM.MODEL_CODE IS ''模块编号''';
EXECUTE IMMEDIATE 'COMMENT ON COLUMN IMES_INDICATOR_NAME_MODEL_PARAM.MODEL_NAME IS ''模块名称''';
EXECUTE IMMEDIATE 'COMMENT ON COLUMN IMES_INDICATOR_NAME_MODEL_PARAM.PARAM_CODE IS ''字段编号''';
EXECUTE IMMEDIATE 'COMMENT ON COLUMN IMES_INDICATOR_NAME_MODEL_PARAM.PARAM_NAME IS ''字段名称''';
END IF;
END;
/
-- 添加自增序列
declare
cnt integer;
begin
select count(0)
into cnt
from user_sequences a
where a.sequence_name = upper('IMES_INDICATOR_NAME_MODEL_PARAM_SEQ');
if cnt = 0 then
execute immediate 'create sequence IMES_INDICATOR_NAME_MODEL_PARAM_SEQ
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20';
end if;
end;
/
动态数据填充
既然需要使用exists
语法实现没有才新增
的功能,只需要sql像下面这样编写即可。
insert into IMES_INDICATOR_NAME_MODEL_PARAM(ID,SYSTEM_CODE,SYSTEM_NAME,MODEL_CODE,MODEL_NAME,PARAM_CODE,PARAM_NAME)
select IMES_INDICATOR_NAME_MODEL_PARAM_SEQ.NEXTVAL,'001','信贷系统','001001','信贷合同','001001001','贷款余额' from dual
where not exists (select 1 from IMES_INDICATOR_NAME_MODEL_PARAM where SYSTEM_CODE = '001' and MODEL_CODE = '001001' and PARAM_CODE = '001001001');
从sql结构上可以看出分为三步:
- insert into xxx 向制定表添加指定数据
- select xxx from dual 查询虚拟表,伪造表数据格式
- where not exists 判断是否存在