delimiter $$ create definer=`root`@`localhost` function `uf_get_tmaxcode`( v_itype int ) returns varchar(10) charset utf8 begin /* 业务描述：根据编码类别产生编码 输入参数： v_itype:编码类别，定义建tmaxcode的itype字段的定义 调用示例： select uf_get_tmaxcode(1); 创建日期： 创建人： */ declare icode int; if v_itype in (1,3) then #非订单类 if exists (select dtlast from tmaxcode where itype=v_itype limit 0,1) then select ifnull(imax,0)+1 into icode from tmaxcode where itype=v_itype; else set icode=1; end if; select ifnull(imax,0)+1 into icode from tmaxcode where itype=v_itype; else #订单类 if exists (select imax from tmaxcode where itype=v_itype and datediff(dtlast,current_date)=0 limit 0,1) then #上次生成时间是当天 select ifnull(imax,0)+1 into icode from tmaxcode where itype=v_itype; else set icode=1; end if; end if; if exists (select imax from tmaxcode where itype=v_itype limit 0,1) then update tmaxcode set imax=icode, dtlast=current_date where itype=v_itype; else insert into tmaxcode (itype,imax,dtlast) select v_itype,icode,current_date; end if; if v_itype in (1,3) then #非订单类编码 return concat('',icode); else #订单类编码 if icode between 1 and 9 then return concat('0000',icode); elseif icode between 10 and 99 then return concat('000',icode); elseif icode between 100 and 999 then return concat('00',icode); elseif icode between 1000 and 9999 then return concat('0',icode); else//开源代码phprm.com return concat('',icode); end if; end if; end $$ delimiter ;
文章地址:
转载随意^^请带上本文地址!