MySql生成流水号

基础规则表Rul_Sequence:

 

规则表中的数据：

 

辅助存储过程：

 

CREATE DEFINER=`root`@`%` PROCEDURE `proc_getseqence`(IN `SeqCode` VARCHAR(60), OUT `ReturnNum` VARCHAR(40), OUT `MessageCode` VARCHAR(800))
    COMMENT '获取序列号'
BEGIN
declare SeqNowNumStr VARCHAR(20);
declare SeqNowNum INT;
declare Nowyear CHAR(4);
declare Nowmonth CHAR(2);
declare Nowday CHAR(2);
declare NowLength INT;
declare DataFormat VARCHAR(50);
declare IniValue INT;
declare ResetType VARCHAR(10);
declare LastDate    CHAR(8);
declare WorkFLowStr VARCHAR(20);
declare DataNow CHAR(8);
declare i INT;

    /* 初始化变量 */
     SET MessageCode='999';
     SET ReturnNum = '0';
     SET NowLength=0;
     SET SeqNowNum =0;
     SET DataNow=DATE_FORMAT(NOW(),'%Y%m%d');
     SET Nowyear=SUBSTRING(DataNow,1,4);
     SET Nowmonth =SUBSTRING(DataNow,5,2);
     SET Nowday =SUBSTRING(DataNow,7,2);
     Set i=1;
         
         Select   value_length,now_seqvalue,date_max,data_format,reset_Type,init_value 
           into NowLength,SeqNowNum,LastDate,DataFormat,ResetType,IniValue
             From RUL_Sequence where seq_code=SeqCode;
 
      Update RUL_Sequence Set Is_Running='2' where Seq_Code=SeqCode and is_running='1';

If (ResetType=2 and DataNow<>LastDate  AND IniValue>0) 
        OR (ResetType=3 and concat(nowyear,nowmonth)<>substr(LastDate,1,6) AND IniValue>0)
        OR (ResetType=4 and nowyear<>substr(LastDate,1,4) AND IniValue>0 ) then
           set SeqNowNum=IniValue;
 end if; 

set i=NowLength; -- i 此时表示流水号的总长度
set WorkFLowStr='<';
while NowLength>0 do     
         set WorkFLowStr=concat(WorkFLowStr,'X');
        set NowLength=NowLength-1;
     end while;  
    set WorkFLowStr=concat(WorkFLowStr,'>');
     /***********拼流水号格式 End*******/
    set SeqNowNumStr=concat(SeqNowNum,'');
    set NowLength=i-length(SeqNowNumStr); 
     /***********补零操作 Start*******/           
     WHILE NowLength>0 do
         set SeqNowNumStr=concat('0',SeqNowNumStr);
         set NowLength=NowLength-1;
     end while;
     /***********补零操作 End*******/  

    set ReturnNum=REPLACE(DataFormat,'<YYYY>',nowyear);            -- 把规则中<YYYY>替换成相应年
    set ReturnNum=REPLACE( ReturnNum,'<MM>',nowmonth);                -- 把规则中<MM>替换成相应月
    set ReturnNum=REPLACE( ReturnNum,'<DD>',nowday);                -- 把规则中<DD>替换成相应日
    set ReturnNum=REPLACE( ReturnNum,WorkFLowStr,SeqNowNumStr);-- 把规则中的形如<XXX>的替换成相应流水号，
     
     /***********更新当前流水值为最大流水号、上一个流水号生成时间和运行标记(运行标记置为"1"(没有运行) ) Start*******/

     UPDATE RUL_Sequence SET now_seqvalue=SeqNowNum+1,date_max=DataNow,is_running='1', edit_time=now()
     WHERE is_running='2' AND  seq_code=SeqCode;
     commit;
     /***********更新当前流水值为最大流水号、上一个流水号生成时间和运行标记(运行标记置为"1"(没有运行) ) End*******/

-- set MessageCode =concat('无此编号规则',MessageCode);
END