MySQL Generate serial number

Basic Rules Table Rul_sequence:

Data in the Rules table:

Secondary stored procedures:

CREATEDefiner=' Root ' @ '%`PROCEDURE' Proc_getseqence ' (inch' Seqcode 'VARCHAR( -), out ' Returnnum 'VARCHAR( +), out ' Messagecode 'VARCHAR( -)) COMMENT'Get serial number'BEGINDeclareSeqnownumstrVARCHAR( -);DeclareSeqnownumINT;DeclareNowyearCHAR(4);DeclareNowmonthCHAR(2);DeclareNowdayCHAR(2);DeclareNowlengthINT;DeclareDataFormatVARCHAR( -);DeclareInivalueINT;DeclareResettypeVARCHAR(Ten);DeclareLastdateCHAR(8);DeclareWorkflowstrVARCHAR( -);DeclareDatanowCHAR(8);DeclareIINT; /*Initialize Variables*/     SETMessagecode='999'; SETReturnnum= '0'; SETNowlength=0; SETSeqnownum=0; SETDatanow=Date_format (now (),'%y%m%d'); SETNowyear=SUBSTRING(Datanow,1,4); SETNowmonth=SUBSTRING(Datanow,5,2); SETNowday=SUBSTRING(Datanow,7,2); SetI=1; SelectValue_length,now_seqvalue,date_max,data_format,reset_type,init_value intoNowlength,seqnownum,lastdate,dataformat,resettype,inivalue fromRul_sequencewhereSeq_code=Seqcode; UpdateRul_sequenceSetIs_running='2' whereSeq_code=Seqcode andIs_running='1';If(Resettype=2  andDatanow<>Lastdate andInivalue>0)         OR(Resettype=3  andConcat (Nowyear,nowmonth)<>SUBSTR (Lastdate,1,6) andInivalue>0)        OR(Resettype=4  andNowyear<>SUBSTR (Lastdate,1,4) andInivalue>0) Then           SetSeqnownum=Inivalue;End if; SetI=Nowlength;--I represent the total length of the serial number at this timeSetWorkflowstr='<'; whileNowlength>0 DoSetWorkflowstr=Concat (WORKFLOWSTR,'X'); SetNowlength=Nowlength-1; End  while; SetWorkflowstr=Concat (WORKFLOWSTR,'>'); /*********** code format end*******/    SetSeqnownumstr=Concat (Seqnownum,"'); SetNowlength=I-length (SEQNOWNUMSTR); /*********** complement 0 operation start*******/                 whileNowlength>0 DoSetSeqnownumstr=Concat'0', SEQNOWNUMSTR); SetNowlength=Nowlength-1; End  while; /*********** complement 0 operation end*******/      SetReturnnum=REPLACE(DataFormat,'<YYYY>', nowyear);--Replace the rule <YYYY> with the corresponding year    SetReturnnum=REPLACE(Returnnum,'<MM>', Nowmonth);--Replace the <MM> in the rule with the corresponding month    SetReturnnum=REPLACE(Returnnum,'<DD>', Nowday);--Replace the <DD> in the rule with the corresponding day    SetReturnnum=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=Now ()WHEREIs_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*******/--set Messagecode =concat (' No this number sequence ', messagecode);END

MySQL Generate serial number