Oracle主键自动生成_表and存储过程

-- Create table
create table T_EB_SYS_DN_SEQUENCE_CONFIG
(
  sequence_id       VARCHAR2(36) default sys_guid() not null,
  sequence_name     VARCHAR2(50) not null,
  sequence_desc     VARCHAR2(200),
  sequence_len      INTEGER default 4 not null,
  reset_type        INTEGER default 2 not null,
  separator         VARCHAR2(10) default ‘-‘,
  prefix            VARCHAR2(50),
  date_format       VARCHAR2(50) default ‘YYYYMM‘,
  current_value     INTEGER default 0 not null,
  initial_value     INTEGER default 1 not null,
  step              INTEGER default 1 not null,
  creator           VARCHAR2(50) not null,
  created_date      TIMESTAMP(6) default systimestamp not null,
  modifier          VARCHAR2(50) not null,
  last_updated_date TIMESTAMP(6) default systimestamp not null,
  is_enable         VARCHAR2(2) default ‘1‘ not null,
  sdp_user_id       VARCHAR2(36) default nvl(SYS_CONTEXT(‘SDP_CONTEXT‘,‘userid‘),‘88888‘) not null,
  sdp_org_id        VARCHAR2(36) default nvl(SYS_CONTEXT(‘SDP_CONTEXT‘,‘orgid‘),‘2‘) not null,
  update_control_id VARCHAR2(36) default sys_guid() not null
)
tablespace EB_DATA_TBS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64
    next 1
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table 
comment on table T_EB_SYS_DN_SEQUENCE_CONFIG
  is ‘T_SEQUENCE_CONFIG‘;
-- Add comments to the columns 
comment on column T_EB_SYS_DN_SEQUENCE_CONFIG.sequence_id
  is ‘流水号ID‘;
comment on column T_EB_SYS_DN_SEQUENCE_CONFIG.sequence_name
  is ‘流水号名称 流水号名称必须唯一‘;
comment on column T_EB_SYS_DN_SEQUENCE_CONFIG.sequence_desc
  is ‘流水号描述 对流水号的说明‘;
comment on column T_EB_SYS_DN_SEQUENCE_CONFIG.sequence_len
  is ‘流水号长度 如4表示0001这种,6表示000001这种‘;
comment on column T_EB_SYS_DN_SEQUENCE_CONFIG.reset_type
  is ‘重置类型 0不重置,1按年,2按月,3按日‘;
comment on column T_EB_SYS_DN_SEQUENCE_CONFIG.separator
  is ‘分隔符 分隔流水号各部分的连接符,如-,则生成的流水号为 前缀-日期-流水号形式,如果为空,则各部分没有连接符号‘;
comment on column T_EB_SYS_DN_SEQUENCE_CONFIG.prefix
  is ‘前缀‘;
comment on column T_EB_SYS_DN_SEQUENCE_CONFIG.date_format
  is ‘日期格式 日期格式格式可以是yyyy yy mm dd hh ii ss的组合,如yyyymm,yy-mm,yyyymmdd,yyyymmdd-hh等,如果按年重置,则日期格式中必须有年,如果按月重置,则日期格式中必须有月.如果不重置,则日期格式不限定,甚至可以为空‘;
comment on column T_EB_SYS_DN_SEQUENCE_CONFIG.current_value
  is ‘当前流水号 流水号当前值,如234表示当前的流水号生成到了234,下一个为234+STEP(步长)‘;
comment on column T_EB_SYS_DN_SEQUENCE_CONFIG.initial_value
  is ‘初始值 初始流水号是多少,默认是0‘;
comment on column T_EB_SYS_DN_SEQUENCE_CONFIG.step
  is ‘步长 每个流水号生成间隔是多少,如2,表示每个生成0001,0003这种流水号‘;
comment on column T_EB_SYS_DN_SEQUENCE_CONFIG.creator
  is ‘创建人‘;
comment on column T_EB_SYS_DN_SEQUENCE_CONFIG.created_date
  is ‘创建时间‘;
comment on column T_EB_SYS_DN_SEQUENCE_CONFIG.modifier
  is ‘最后更新人员‘;
comment on column T_EB_SYS_DN_SEQUENCE_CONFIG.last_updated_date
  is ‘最后更新时间‘;
comment on column T_EB_SYS_DN_SEQUENCE_CONFIG.is_enable
  is ‘是否可用‘;
comment on column T_EB_SYS_DN_SEQUENCE_CONFIG.sdp_user_id
  is ‘SDP用户ID‘;
comment on column T_EB_SYS_DN_SEQUENCE_CONFIG.sdp_org_id
  is ‘SDP组织ID‘;
comment on column T_EB_SYS_DN_SEQUENCE_CONFIG.update_control_id
  is ‘并发控制字段‘;
-- Create/Recreate primary, unique and foreign key constraints 
alter table T_EB_SYS_DN_SEQUENCE_CONFIG
  add constraint PK_RDP_SYS_SEQUENCE_CONFIG primary key (SEQUENCE_ID)
  using index 
  tablespace EB_IDX_TBS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate check constraints 
alter table T_EB_SYS_DN_SEQUENCE_CONFIG
  add constraint CK_SEQUENCE_DATE_FORMAT
  check (RESET_TYPE=0
 OR (RESET_TYPE=1 AND INSTR(UPPER(DATE_FORMAT),‘Y‘,1,1)>0)
 OR (RESET_TYPE=2 AND INSTR(UPPER(DATE_FORMAT),‘Y‘,1,1)>0 AND INSTR(UPPER(DATE_FORMAT),‘M‘,1,1)>0)
 OR (RESET_TYPE=3 AND INSTR(UPPER(DATE_FORMAT),‘Y‘,1,1)>0 AND INSTR(UPPER(DATE_FORMAT),‘M‘,1,1)>0 AND INSTR(UPPER(DATE_FORMAT),‘D‘,1,1)>0));
alter table T_EB_SYS_DN_SEQUENCE_CONFIG
  add constraint CK_SEQUENCE_RESET_TYPE
  check (RESET_TYPE IN (0,1,2,3));
-- Grant/Revoke object privileges 
grant select, insert, update, delete on T_EB_SYS_DN_SEQUENCE_CONFIG to BOM;

CREATE OR REPLACE PROCEDURE P_EB_GET_SEQUENCE(PARA_SEQUENCE_NAME   VARCHAR2,
                                           PARA_DYNAMIC_CONTENT VARCHAR2,
                                           PARA_SEQUENCE        OUT VARCHAR2) IS
  V_SEQUENCE_LEN      INTEGER;
  V_RESET_TYPE        INTEGER;
  V_SEPARATOR          VARCHAR2(10);
  V_PREFIX            VARCHAR2(50);
  V_DATE_FORMAT        VARCHAR2(50);
  V_CURRENT_VALUE      INTEGER;
  V_INITIAL_VALUE      INTEGER;
  V_STEP              INTEGER;
  V_LAST_UPDATED_DATE TIMESTAMP;
  V_YYYY              VARCHAR2(4);
  V_MM                VARCHAR2(2);
  V_DD                VARCHAR2(2);
  V_HH                VARCHAR2(2);
  V_II                VARCHAR2(2);
  V_SS                VARCHAR2(2);
  V_DATESTRFULL       VARCHAR2(50);
  V_DATESTR           VARCHAR2(50);

BEGIN
  --设置事务隔离级别为序列化,防止并发产生相同的流水号
  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

  SELECT SEQUENCE_LEN,
         RESET_TYPE,
         SEPARATOR,
         PREFIX,
         UPPER(DATE_FORMAT),
         CURRENT_VALUE,
         INITIAL_VALUE,
         STEP,
         LAST_UPDATED_DATE
    INTO V_SEQUENCE_LEN,
         V_RESET_TYPE,
         V_SEPARATOR,
         V_PREFIX,
         V_DATE_FORMAT,
         V_CURRENT_VALUE,
         V_INITIAL_VALUE,
         V_STEP,
         V_LAST_UPDATED_DATE
    FROM T_EB_SYS_DN_SEQUENCE_CONFIG
   WHERE SEQUENCE_NAME = PARA_SEQUENCE_NAME;

  --日期生成 日期全字符串 YYYY-MM-DD HH:MM:SS
  V_DATESTRFULL := TO_CHAR(SYSDATE, ‘YYYY-MM-DD HH24:MI:SS‘);
  V_YYYY        := SUBSTR(V_DATESTRFULL, 1, 4);
  V_MM          := SUBSTR(V_DATESTRFULL, 6, 2);
  V_DD          := SUBSTR(V_DATESTRFULL, 9, 2);
  V_HH          := SUBSTR(V_DATESTRFULL, 12, 2);
  V_II          := SUBSTR(V_DATESTRFULL, 15, 2);
  V_SS          := SUBSTR(V_DATESTRFULL, 18, 2);

  IF V_DATE_FORMAT IS NOT NULL THEN
    BEGIN
      V_DATESTR := REPLACE(V_DATE_FORMAT, ‘YYYY‘, V_YYYY);
      V_DATESTR := REPLACE(V_DATESTR, ‘YY‘, SUBSTR(V_YYYY, -2));
      V_DATESTR := REPLACE(V_DATESTR, ‘MM‘, V_MM);
      V_DATESTR := REPLACE(V_DATESTR, ‘M‘, SUBSTR(V_MM, -1));
      V_DATESTR := REPLACE(V_DATESTR, ‘DD‘, V_DD);
      V_DATESTR := REPLACE(V_DATESTR, ‘D‘, SUBSTR(V_DD, -1));
      V_DATESTR := REPLACE(V_DATESTR, ‘HH‘, V_HH);
      V_DATESTR := REPLACE(V_DATESTR, ‘H‘, SUBSTR(V_HH, -1));
      V_DATESTR := REPLACE(V_DATESTR, ‘II‘, V_II);
      V_DATESTR := REPLACE(V_DATESTR, ‘I‘, SUBSTR(V_II, -1));
      V_DATESTR := REPLACE(V_DATESTR, ‘SS‘, V_SS);
      V_DATESTR := REPLACE(V_DATESTR, ‘S‘, SUBSTR(V_SS, -1));
    END;
  END IF;

  IF V_RESET_TYPE <> 0 THEN
    BEGIN

        IF (V_RESET_TYPE = 1 AND TO_CHAR(SYSDATE,‘YYYY‘)>TO_CHAR(V_LAST_UPDATED_DATE,‘YYYY‘))
          OR (V_RESET_TYPE = 2 AND TO_CHAR(SYSDATE,‘YYYYMM‘)>TO_CHAR(V_LAST_UPDATED_DATE,‘YYYYMM‘))
          OR (V_RESET_TYPE = 3 AND TO_CHAR(SYSDATE,‘YYYYMMDD‘)>TO_CHAR(V_LAST_UPDATED_DATE,‘YYYYMMDD‘)) THEN
             V_CURRENT_VALUE := V_INITIAL_VALUE;
        ELSE
             V_CURRENT_VALUE := V_CURRENT_VALUE + V_STEP;
        END IF;
    END;
  ELSE
    BEGIN
        V_CURRENT_VALUE := V_CURRENT_VALUE + V_STEP;
    END;
  END IF;
  PARA_SEQUENCE :=   CASE WHEN V_PREFIX IS NOT NULL THEN V_PREFIX || V_SEPARATOR END
                  || CASE WHEN PARA_DYNAMIC_CONTENT IS NOT NULL THEN PARA_DYNAMIC_CONTENT || V_SEPARATOR END
                  || CASE WHEN V_DATESTR IS NOT NULL THEN V_DATESTR || V_SEPARATOR END
                  || LPAD(TO_CHAR(V_CURRENT_VALUE), V_SEQUENCE_LEN, ‘0‘);


  UPDATE T_EB_SYS_DN_SEQUENCE_CONFIG
     SET CURRENT_VALUE = V_CURRENT_VALUE,LAST_UPDATED_DATE = SYSDATE
   WHERE SEQUENCE_NAME = PARA_SEQUENCE_NAME;

  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
END;