Oracle implements custom Sequence number generation method _oracle

Actual work, will inevitably encounter serial number generation problem, the following is a simple serial 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 is not null,
 initcycle  CHAR (1) is not NULL,
 cur_sernum VARCHAR2 is not null,
 ZERO_FLG  VARCHAR (2) NOT NULL,
 Sequencestyle VARCHAR2 (m),
 memo   VARCHAR2
);
--ADD comments to the columns 
comment in column S_AUTOCODE.PK1 is ' primary key ';
Comment on column s_autocode.atype is ' serial number type ';
Comment on column s_autocode.owner is ' serial number owner ';
Comment on column s_autocode.initcycle is ' serial number increment ';
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 ' remarks ';
--Create/recreate Indexes 
Create index pk_s_autocode on S_autocode (Atype, OWNER);

(2) Initialize the configuration table, for example:

Copy Code code as follows:

Insert into S_autocode (PK1, Atype, OWNER, Initcycle, Cur_sernum, ZERO_FLG, Sequencestyle, MEMO)
VALUES (' 0a772aedfbed4feea46442003ce1c6a6 ', ' zdbcontcn ', ' 012805 ', ' 1 ', ' 200000 ', ' 7 ', ' $YEAR $ $orgapp$ ', "" Chinese number of the pledge contract ");

(3) Custom serial number generation function:

Create a function: Sf_sys_gen_autocode

CREATE OR REPLACE FUNCTION sf_sys_gen_autocode (I_atype in VARCHAR2,/* Sequence category * * I_owner in VARCHAR2/* sequence owner/*) retur N VARCHAR2 is/************************************************************************************************** /* PROCEDURE Name:sf_sys_gen_autocode */////////* Developed BY:WANGXF * * DESCRIPTI                    On: Mainly used to generate custom serial number * * * Developed date:2016-10-08 * * CHECKED by: */////* LOAD method:f1-delete INSERT *//*******************************************************      /O_autocode VARCHAR2 (100); /* Output Serial number * * 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_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 days, such as 20161008*/V_date_date_all CHAR (14); /* Full year sequence, such as 20161008155732*//* Supported parameter sequence: $YEAR $--> Year $YEAR _month$--> year + month, excluding man $DATE $--> year + month A + date, excluding the man $DATE _all$--> full date, does not contain a man $ORGAPP $--> owner $SER $--> The current serial number/--resolves the problem that the query transaction cannot perform DML PRAGM
a autonomous_transaction; BEGIN--Serial number configuration for query review criteria 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 the current date SELECT to_char (sysdate, ' yyyy '), To_char (sysdate, ' yyyymm '), To_char (sysdate, ' yyyyMMdd '), To_char (SY
  
  Sdate, ' Yyyymmddhh24miss ') into the 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 back the current serial number to make sure that each time you increment UPDATE s_autocode T SET t.cur_sernum=v_seq_num WHERE t.atype=i_atype and T.owner=i_owner; --Front patch 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, return the error value when No_data_found THEN ROLLBACK;
   Dbms_output.put_line (' There no config as you need ... ');
Return ' ERROR ';


 End Sf_sys_gen_autocode;

(4) Testing:

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

Copy Code code as follows:

SELECT sf_sys_gen_autocode (' ZDBCONTCN ', ' 012805 ') from DUAL;

(5) Results

2016 012805 quality word No. 0200001

The above is the entire content of this article, I hope to help you learn, but also hope that we support the cloud habitat community.