mysql MySqlDump database backup code

mysql tutorial MySqlDump database tutorial backup code

DELIMITER $$

CREATE DEFINER = `root` @` localhost` FUNCTION` UF_Get_tMaxCode` (
V_iType INT
) RETURNS varchar (10) CHARSET utf8
BEGIN
/ *
Business Description: Generate a code based on the encoding category

Input parameters:
V_iType: encoding category, define the definition of iType field tMaxCode

Example call:
SELECT UF_Get_tMaxCode (1);

Created on:
founder:

* /
DECLARE iCode INT;

IF V_iType IN (1,3)

THEN
# Non-order category
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
# Order category
IF EXISTS (SELECT iMax FROM tMaxCode WHERE iType = V_iType AND DATEDIFF (dtLast, CURRENT_DATE) = 0 LIMIT 0,1)
THEN
# Last generation time is the same day
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
# Non-order type code
RETURN CONCAT ('', iCode);
ELSE
# Order class code
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;

?>