代码编织梦想

前言

项目团队开发中,有时候需要制定系统配置表,但新增数据时,为了保证数据的唯一性,除了使用唯一键外,还可以先查询是否存在,再判断是否进行数据新增。

本次介绍一种新的编写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 判断是否存在
版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/qq_38322527/article/details/130819830

duplicate 复制过程(10g)完结-爱代码爱编程

3.4执行复制过程 sqlplus / as sysdba startup nomount create spfile from pfile shutdown immediate startup nomount rman auxiliary /  target  sys/oracle@dup01   (连接辅助及目标实例) duplicate  t

mysql,oracle使用区别-爱代码爱编程

1.1concat,concat_ws,group_concat         配置方式                 临时:SET SESSION group_concat_max_len = 10240;                 永久:配置文件my.ini增加group_concat_max_len=10240        

《oracle高级数据库》复习--客观题-爱代码爱编程

文章目录 Oracle基础,数据库、数据表基本操作与管理约束、查询、视图PL/SQL编程、存储过程、触发器、用户与权限拓展 数据库命令总结 Oracle基础,数据库、数据表基本操作