Oracle PRIMARY KEY Auto-generate _ table and stored procedure

--Create tablecreate table T_eb_sys_dn_sequence_config (sequence_id VARCHAR2 () default sys_guid () not NULL, SEQ Uence_name VARCHAR2 () not NULL, Sequence_desc VARCHAR2 ($), Sequence_len INTEGER default 4 NOT NULL, RE  Set_type INTEGER default 2 NOT NULL, separator VARCHAR2 (Ten) Default '-', prefix VARCHAR2 (50), Date_format VARCHAR2 () Default ' Yyyymm ', current_value INTEGER default 0 NOT NULL, Initial_value intege R default 1 is not NULL, step INTEGER default 1 is not NULL, creator VARCHAR2 () is not NULL, CREATED_DA Te TIMESTAMP (6) default systimestamp NOT NULL, modifier VARCHAR2 (.) not NULL, Last_updated_date TIMESTAMP (6) Default systimestamp NOT NULL, is_enable VARCHAR2 (2) Default ' 1 ' is not NULL, sdp_user_id VARCHAR2 (+) de Fault NVL (Sys_context (' Sdp_context ', ' userid '), ' 88888 ') not NULL, sdp_org_id VARCHAR2 (*) Default NVL (Sys_context (' Sdp_context ', ' OrgID '), ' 2') not NULL, update_control_id VARCHAR2 ($) default sys_guid () NOT null) tablespace Eb_data_tbs pctfree 1 m Axtrans 255 Storage (initial 1 minextents 1 Maxextents Unlimited);--Add comments to the table Co Mment on table t_eb_sys_dn_sequence_config is ' t_sequence_config ';--Add comments to the columns comment on column t_eb_s YS_DN_SEQUENCE_CONFIG.SEQUENCE_ID is ' serial id '; Comment on column t_eb_sys_dn_sequence_config.sequence_name is ' Serial number name Serial number name must be unique '; Comment on column t_eb_sys_dn_sequence_config.sequence_desc is ' Serial number description of the serial number '; Comment on column t_eb_sys_dn _sequence_config.sequence_len is ' serial number 4 means 0001 this, 6 means 000001 such '; Comment on column t_eb_sys_dn_sequence_config.reset_ Type is ' reset types 0 do not reset, 1 by year, 2 by month, 3 by day '; Comment on column t_eb_sys_dn_sequence_config.separator is ' delimiter separates the connectors of each part of the serial number, such as-the generated serial number As a prefix-date-serial number form, if empty, the parts do not have a connection symbol '; Comment on column t_eb_sys_dn_sequence_config.prefix is ' prefix '; Comment on column T_eb_sys_ Dn_sequence_config.date_format is ' date latticeFormat date format can be yyyy yy mm DD HH II SS combination, such as YYYYMM,YY-MM,YYYYMMDD,YYYYMMDD-HH, if reset by year, the date format must be years, if the month reset, the date format must have months. If you do not reset, The date format is not qualified, even can be empty '; Comment on column t_eb_sys_dn_sequence_config.current_value is ' current serial number current value, such as 234 indicates that the current serial number is generated to 234, The next is 234+step (step) '; Comment on column t_eb_sys_dn_sequence_config.initial_value is ' initial value of what is the initial serial number, default is 0 '; Comment on column T_eb_sys_dn_sequence_config.step is ' step length each serial number generation interval is how much, such as 2, indicating each generated 0001,0003 this serial number '; Comment on column t_eb_sys_dn_sequence _config.creator is ' creator '; Comment on column t_eb_sys_dn_sequence_config.created_date are ' creation time '; Comment on column T_eb_sys _dn_sequence_config.modifier is ' last updated staff '; Comment on column T_eb_sys_dn_sequence_config.last_updated_date was ' last updated '; Comment on column t_eb_sys_dn_sequence_config.is_enable is ' available '; Comment on column t_eb_sys_dn_sequence_config.sdp_ USER_ID is ' SDP user ID '; Comment on column t_eb_sys_dn_sequence_config.sdp_org_id is ' SDP organization ID '; Comment on column t_eb_sys_dn _sequence_config.update_control_id is ' concurrency control field ';--CrEate/recreate Primary, unique and FOREIGN KEY constraints ALTER TABLE T_EB_SYS_DN_SEQUENCE_CONFIG add constraint pk_rdp_s Ys_sequence_config primary KEY (sequence_id) using index tablespace Eb_idx_tbs pctfree, Initrans 2 Maxtrans 255 s Torage (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 ', ' >0 ') OR (reset_type=2 and INSTR (UPPER (date_format), ' Y ', and ") >0 and INSTR (UPPER (date_ FORMAT), ' m ', ' >0 ') OR (reset_type=3 and INSTR (UPPER (date_format), ' Y ', "St") >0 and INSTR (UPPER (date_format), ' m ', >0 and INSTR (UPPER (date_format), ' D ', () >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, delet E on t_eb_sys_dn_sequence_config to BOM;

  Calling stored procedures:

CREATE OR REPLACE PROCEDURE p_eb_get_sequence (para_sequence_name VARCHAR2, PAR A_dynamic_content VARCHAR2, para_sequence out VARCHAR2) is V_sequence_le  N 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-Sets the transaction isolation level to serialize, preventing concurrency from generating the same serial number set TRANSACTION isolation levels SERIALIZABLE; SELECT Sequence_len, Reset_type, SEPARATOR, PREFIX, UPPER (date_format), Current_va LUE, 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;  --Date Generation date full string 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 isn't 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_CU        Rrent_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 was not NULL and then V_prefix | | V_separator END | | Case when Para_dynamic_content was not NULL and then Para_dynamic_content | | V_separator END | | Case when V_datestr was not NULL and 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_NA  ME = Para_sequence_name; COMMIT; EXCEPTION when OTHERS and then ROLLBACK; END;

Oracle PRIMARY KEY Auto-generate _ table and stored procedure