-- Create tablecreate 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 = http://www.mamicode.com/V_CURRENT_VALUE,LAST_UPDATED_DATE = SYSDATE>