Force get sequence Next value/Current value (Oracle function)

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 sequence CREATE table S_autocode (  pk1           VARCHAR2 (+) primary key,  atype         VARCHAR2 () not NULL,  owner         VARCHAR2 (Ten) not NULL,  initcycle     CHAR (1) is not NULL,  cur_sernum    VARCHAR2 () is not NULL,  Zero_ FLG      VARCHAR (2) NOT NULL,  Sequencestyle VARCHAR2 (),  memo          VARCHAR2);--ADD comments to the Columns comment on column S_AUTOCODE.PK1 is  ' primary key '; Comment on column s_autocode.atype is  ' serial number type '; Comment on Colum N S_autocode.owner is  ' serial number owner '; Comment on column s_autocode.initcycle is  ' serial number incremented '; Comment on column S_ Autocode.cur_sernum is  ' serial number '; Comment on column S_AUTOCODE.ZERO_FLG is  ' serial number length '; Comment on column S_ Autocode.sequencestyle is '  serial number style '; Comment on column S_autocode.memo is  ' remark ';--Create/recreate indexes Create INDEX Pk_s_autocode on S_autocode (Atype, OWNER);

(2) Initialize the configuration table, for example:

(3) Custom serial number generation function:

Create function: Sf_sys_gen_autocode

 

CREATE OR REPLACE FUNCTION sf_sys_gen_autocode (I_atype in VARCHAR2,/* sequence category */I_owner in VARCHAR2/* sequence Column owner */) RETURN VARCHAR2 is/**************************************************************************************        /* PROCEDURE Name:sf_sys_gen_autocode */ /* Developed BY:WANGXF */* DESC Ription: Used primarily to generate custom serial numbers */* developed DA                                                                               TE:2016-10-08 */* CHECKED by:                                                              */* LOAD method:f1-delete INSERT */        /******************************************************** *************************/O_autocode VARCHAR2 (100); /* The serial number of the output */v_initcycle S_autocode.       Initcycle%type; /* Serial Number incremented */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_num VARCHAR2 (100);                         /* This serial number */V_date_year CHAR (4);                   /* year, such as 2016*/V_date_year_month CHAR (6);                         /* Year month, such as 201610*/v_date_date CHAR (8);                    /* Year Month day, such as 20161008*/V_date_date_all CHAR (14); /* Complete year series, such as 20161008155732*//* Supported parameter sequence: $YEAR $--$YEAR _month$--year + month,  No man $DATE $--year + month + date, no man $DATE _all$--Full date, no man $ORGAPP $--and owner $SER $ --Current Serial number */-resolves a problem where query transactions cannot perform DML Pragma autonomous_transaction; BEGIN--Query the condition of the reviewSerial number configuration SELECT t.initcycle, T.cur_sernum, T.ZERO_FLG, T.sequencestyle Into V_initcycle,v_cur_sernum,v_zero_flag,v_sequencestyle from S_autocode T WHERE T.atype=i_atype and T.OWNER=              I_owner; --Format Current date SELECT to_char (sysdate, ' yyyy '), To_char (sysdate, ' yyyymm '), To_char (Sysdat E, ' YyyyMMdd '), To_char (sysdate, ' Yyyymmddhh24miss ') into v_date_year,v_date_year_month,v_date_date,v              _date_date_all from DUAL;       --Date processing O_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 handling O_autocode: = REPLACE (O_autocode, ' $ORGAPP $ ', i_owner); --Serial number processing V_seq_num: = To_char (To_number (v_cur_sernum) +to_number (v_initcycle));              --Write down the current serial number to ensure that each time it is incremented UPDATE s_autocode T SET t.cur_sernum=v_seq_num WHERE t.atype=i_atype and T.owner=i_owner;              --not satisfying the length of the front fill 0 IF Length (v_seq_num) < To_number (V_zero_flag) then/* LOOP V_seq_num: = ' 0 ' | |           V_seq_num;           EXIT when LENGTH (v_seq_num) = To_number (V_zero_flag);　　　　　　END LOOP;       */V_seq_num: = Lpad (V_seq_num,to_number (v_zero_flag), ' 0 ');                 END IF;              O_autocode: = REPLACE (O_autocode, ' $SER $ ', v_seq_num);       COMMIT; RETURN O_autocode;         EXCEPTION--if there is no corresponding configuration item, the error value is returned when No_data_found and then ROLLBACK;         Dbms_output.put_line (' There is no config as you need ... '); RETURN ' ERROR '; END Sf_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

Force get sequence Next value/Current value (Oracle function)