mysql MySqlDump数据库备份代码 |
时间:2015-01-23 来源:西部数据 作者:西部数据 |
|
- DELIMITER $$
-
- CREATE DEFINER=`root`@`localhost` FUNCTION `UF_Get_tMaxCode`(
- V_iType INT
- ) RETURNS varchar(10) CHARSET utf8
- BEGIN
-
-
-
-
-
-
-
-
-
-
-
-
-
- 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
- RETURN CONCAT('',iCode);
- END IF;
- END IF;
- END
- $$
-
- DELIMITER ;
|
|
|
|