-- 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;
