Oracle implements custom serial number generation

In the actual work, will inevitably encounter serial number generation problem, the following is a simple sequence number generation function

(1) Create a custom serial number configuration table as follows:

--Custom SequencesCreate TableS_autocode (PK1VARCHAR2( +)Primary Key, AtypeVARCHAR2( -) not NULL, ownerVARCHAR2(Ten) not NULL, InitcycleCHAR(1) not NULL, Cur_sernumVARCHAR2( -) not NULL, ZERO_FLGVARCHAR(2) not NULL, SequencestyleVARCHAR2( -), MemoVARCHAR2( -));--Add Comments to the columnsComment on columnS_autocode.pk1 is 'PRIMARY Key'; Comment on columnS_autocode.atype is 'Serial Number Type'; Comment on columnS_autocode.owner is 'Serial number owner'; Comment on columnS_autocode.initcycle is 'Serial number Increment'; Comment on columnS_autocode.cur_sernum is 'Serial Number'; Comment on columnS_autocode.zero_flg is 'Serial number Length'; Comment on columnS_autocode.sequencestyle is 'Serial Number Style'; Comment on columnS_autocode.memo is 'Notes';--create/recreate IndexesCreate IndexPk_s_autocode onS_autocode (Atype, OWNER);

(2) Initialize the configuration table, for example:

Insert  intoS_autocode (PK1, Atype, OWNER, Initcycle, Cur_sernum, ZERO_FLG, Sequencestyle, MEMO)Values('0A772AEDFBED4FEEA46442003CE1C6A6','ZDBCONTCN','012805','1','200000','7','$YEAR $ $orgapp$ character number $ser$','Pledge contract Chinese number');

(3) Custom serial number generation function:

To create a function: Sf_sys_gen_autocode

CREATE OR REPLACE FUNCTIONSf_sys_gen_autocode (I_atypeinch VARCHAR2,/*sequence category*/I_ownerinch VARCHAR2   /*sequence owner*/) RETURN VARCHAR2  is        /**************************************************************************************************/        /*PROCEDURE Name:sf_sys_gen_autocode*/        /*developed BY:WANGXF*/        /*DESCRIPTION: Used primarily to generate custom serial numbers*/                           /*developed date:2016-10-08*/        /*CHECKED by:*/        /*LOAD method:f1-delete INSERT*/        /**************************************************************************************************/O_autocodeVARCHAR2( -);/*serial number of the output*/v_initcycle S_autocode. Initcycle%TYPE;/*Serial number Increment*/V_cur_sernum S_autocode. Cur_sernum%TYPE;/*Serial Number*/V_zero_flag S_autocode. ZERO_FLG%TYPE;/*Serial number Length*/V_sequencestyle S_autocode. Sequencestyle%TYPE;/*Serial Number Style*/V_seq_numVARCHAR2( -);/*serial number of this time*/V_date_yearCHAR(4);/*year, such as*/V_date_year_monthCHAR(6);/*year months, such as 201610*/v_date_dateCHAR(8);/*year Month day, such as 20161008*/V_date_date_allCHAR( -);/*complete year series, such as 20161008155732*/              /*supported parameter sequences: $YEAR $-and year $YEAR _month$-year + month, no man $DATE $--year + month + date, no With man $DATE _all$--Full date, no man $ORGAPP $--and owner $SER $--current serial number*/              --Troubleshooting query transactions that cannot execute DMLPragma autonomous_transaction;BEGIN       --serial number configuration for query review criteria       SELECTt.initcycle, T.cur_sernum, T.ZERO_FLG, T.sequencestyle intoV_initcycle,v_cur_sernum,v_zero_flag,v_sequencestyle fromS_autocode TWHERET.atype=I_atype andT.owner=I_owner; --formatting the current date       SELECTTo_char (Sysdate,'yyyy'), To_char (Sysdate,'yyyymm'), To_char (Sysdate,'YYYYMMDD'), To_char (Sysdate,'Yyyymmddhh24miss')             intoV_date_year,v_date_year_month,v_date_date,v_date_date_all fromDUAL; --Date ProcessingO_autocode:= REPLACE(V_sequencestyle,'$YEAR $', v_date_year); O_autocode:= REPLACE(O_autocode,'$YEAR _month$', V_date_year_month); O_autocode:= REPLACE(O_autocode,'$DATE $', v_date_date); O_autocode:= REPLACE(O_autocode,'$DATE _all$', V_date_date_all); --owner HandlingO_autocode:= REPLACE(O_autocode,'$ORGAPP $', I_owner); --Serial Number processingV_seq_num:=To_char (To_number (V_cur_sernum)+To_number (v_initcycle)); --write down the current serial number to make sure it increments every time       UPDATES_autocode TSETT.cur_sernum=V_seq_numWHERET.atype=I_atype andT.owner=I_owner; --the front complement of the length is not satisfied 0       IFLENGTH (V_seq_num)<to_number (V_zero_flag) ThenLOOP V_seq_num:= '0'||V_seq_num; EXIT  whenLENGTH (V_seq_num)=To_number (V_zero_flag); ENDLOOP; END IF; O_autocode:= REPLACE(O_autocode,'$SER $', V_seq_num); COMMIT; RETURNO_autocode; EXCEPTION--if there is no corresponding configuration item, the error value is returned        whenNo_data_found Then         ROLLBACK; Dbms_output.put_line ('There is no config as you need ...'); RETURN 'ERROR';ENDSf_sys_gen_autocode;

(4) Test:

Configuration item: $YEAR $ $orgapp$ $ser$

SELECT Sf_sys_gen_autocode ('zdbcontcn','012805 ' from DUAL;

(5) Results

2016 012805 quality Word No. No. 0200001

Oracle implements custom serial number generation