Oracle generates serial numbers

• Serial Number rules table Rul_sequence:

Data in the Rules table:

Generate serial number-assisted stored procedures:

CREATE OR REPLACE PROCEDUREProc_getseqence (Seqcodeinch  varchar2, Returnnum outVarchar2, Messagecode outvarchar2)--exception messages, etc. isSeqnownumstrVARCHAR2( -); Seqnownumint;--Current Value Year CHAR(4);--year YYYYMonth CHAR(2);--month MM Day CHAR(2)    ;--Day DDNowlengthint;--length of serial numberDataFormatVARCHAR2( -);--Serial Number RulesInivalueint;--return to zero valueResettypeVARCHAR2(Ten);--Zeroing MethodLastdateCHAR(8);--Date Maximum ValueWorkflowstrVARCHAR2( -);--Date value when the serial number was last calledDatanowCHAR(8);--Current DateIint;--Convert variables, action reference code contextbegin    /*Initialize Variables*/Messagecode:='888';--Successful ExecutionReturnnum:= '0'; Nowlength:=0; Seqnownum:=0; Datanow:=To_char (Sysdate,'YYYYMMDD');--get 20130704 of the time format      Year:=SUBSTR (Datanow,1,4); Month:=SUBSTR (Datanow,5,2);  Day:=SUBSTR (Datanow,7,2); I:=1 ; SelectValue_length,now_seqvalue,date_max,data_format,reset_type,init_value intoNowlength,seqnownum,lastdate,dataformat,resettype,inivalue fromRul_sequencewhereSeq_code=Seqcode;<<Wait>> UpdateRul_sequenceSetIs_running='2' whereSeq_code=Seqcode andIs_running='1'; ifSql%RowCount=0  Then   /*********** If there are concurrent runs, wait up to 1 seconds, and then continue running *******/Dbms_lock.sleep (1);--Grant execute on Dbms_lock to DHLink requires authorization   Gotowait;End if;Commit;If(Resettype=2  andDatanow<>Lastdate andInivalue>0)        OR(Resettype=3  and  Year||Month<>SUBSTR (Lastdate,1,6) andInivalue>0)        OR(Resettype=4  and  Year<>SUBSTR (Lastdate,1,4) andInivalue>0) ThenSeqnownum:=Inivalue;End if; I:=Nowlength;--I represent the total length of the serial number at this timeWORKFLOWSTR:='<'; whileNowlength>0Loop workflowstr:=Workflowstr||'X'; Nowlength:=Nowlength-1; EndLoop; WORKFLOWSTR:=Workflowstr||'>' ; /*********** code format end*******/Seqnownumstr:=To_char (Seqnownum); Nowlength:=I-length (SEQNOWNUMSTR); /*********** complement 0 operation start*******/      whileNowlength>0Loop seqnownumstr:='0'||Seqnownumstr; Nowlength:=Nowlength-1; EndLoop; /*********** complement 0 operation end*******/Returnnum:=REPLACE(DataFormat,'<YYYY>', Year);--Replace the rule <YYYY> with the corresponding yearReturnnum:=REPLACE(Returnnum,'<MM>',Month);--Replace the <MM> in the rule with the corresponding monthReturnnum:=REPLACE(Returnnum,'<DD>', Day);--Replace the <DD> in the rule with the corresponding dayReturnnum:=REPLACE(RETURNNUM,WORKFLOWSTR,SEQNOWNUMSTR);--replace the shape in the rule with the corresponding serial number, such as <XXX>     /*********** Updates the current pipelining value to the maximum serial number, the last serial number generation time, and the run tag (run tag is set to "1" (not running)) start*******/     UPDATERul_sequenceSETNow_seqvalue=Seqnownum+1, Date_max=Datanow,is_running='1', Edit_time=sysdateWHEREIs_running='2'  andSeq_code=Seqcode; Commit; /*********** Updates the current pipelining value to the maximum serial number, the last serial number generation time, and the run tag (run tag is set to "1" (not running)) end*******/Exception whenOthers Thenrollback; Messagecode:='no this number sequence'||Messagecode;Endproc_getseqence;--SELECT * from Rul_sequence

Oracle generates serial numbers