作者:管怡6440_368 | 来源:互联网 | 2018-04-24 01:35
DELIMITER$$CREATEDEFINER`root`@`localhost`FUNCTION`UF_Get_...
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 ;
文章地址:
转载随意^^请带上本文地址!