mysql 四舍六入函数

CREATE FUNCTION ZRoundSub(str VARCHAR(50) ,i INT,flag INT)
 RETURNS varchar(20)
BEGIN
	IF flag THEN -- 入
		CASE i 
			WHEN 0 THEN SET str = CAST(CAST(SUBSTR(str,1,LOCATE('.' ,str) - 1 + i) AS DECIMAL(38,0)) + 1.0 / POWER(10 ,i) AS DECIMAL(38,0));
			WHEN 1 THEN SET str = CAST(CAST(SUBSTR(str,1,LOCATE('.' ,str) + i) AS DECIMAL(38,1)) + 1.0 / POWER(10,i) AS DECIMAL(38,1));
			WHEN 2 THEN SET str = CAST(CAST(SUBSTR(str,1,LOCATE('.' ,str) + i) AS DECIMAL(38,2)) + 1.0 / POWER(10,i) AS DECIMAL(38,2));
			WHEN 3 THEN SET str = CAST(CAST(SUBSTR(str,1,LOCATE('.' ,str) + i) AS DECIMAL(38,3)) + 1.0 / POWER(10,i) AS DECIMAL(38,3));
			WHEN 4 THEN SET str = CAST(CAST(SUBSTR(str,1,LOCATE('.' ,str) + i) AS DECIMAL(38,4)) + 1.0 / POWER(10,i) AS DECIMAL(38,4));
			WHEN 5 THEN SET str = CAST(CAST(SUBSTR(str,1,LOCATE('.' ,str) + i) AS DECIMAL(38,5)) + 1.0 / POWER(10,i) AS DECIMAL(38,5));
			WHEN 6 THEN SET str = CAST(CAST(SUBSTR(str,1,LOCATE('.' ,str) + i) AS DECIMAL(38,6)) + 1.0 / POWER(10,i) AS DECIMAL(38,6));
			WHEN 7 THEN SET str = CAST(CAST(SUBSTR(str,1,LOCATE('.' ,str) + i) AS DECIMAL(38,7)) + 1.0 / POWER(10,i) AS DECIMAL(38,7));
			WHEN 8 THEN SET str = CAST(CAST(SUBSTR(str,1,LOCATE('.' ,str) + i) AS DECIMAL(38,8)) + 1.0 / POWER(10,i) AS DECIMAL(38,8));
			WHEN 9 THEN SET str = CAST(CAST(SUBSTR(str,1,LOCATE('.' ,str) + i) AS DECIMAL(38,9)) + 1.0 / POWER(10,i) AS DECIMAL(38,9));
		END CASE;
	ELSE	-- 舍
		IF i = 0 THEN
			SET str = SUBSTR(str,1,LOCATE('.' ,str) - 1 + i);
		ELSE
			SET str = SUBSTR(str,1,LOCATE('.' ,str) + i);
		END IF;
	END IF;
	
	RETURN str;
END;




CREATE FUNCTION ZRound(num DECIMAL(38,14) ,i INT)
 RETURNS varchar(20)
BEGIN
	/*原数据转换成字符类型*/
	DECLARE str VARCHAR (100);
	/*保留小数位之后的数字  --如：1.5401 保留一位时 str2='401' */
	DECLARE str2 VARCHAR (100);
	/*保留的最后一位小数    --如：1.54   保留一位时 str3='5' */
	DECLARE str3 VARCHAR (2);

	SET str = CAST(num AS CHAR);

	/*若为整数，则返回原值*/
	IF LOCATE('.' ,str) = 0 THEN 
		RETURN str;
	END IF;

	/*保留小数位之后的数字*/
	SET str2 = REVERSE(
		SUBSTR(REVERSE(str), 1, LOCATE('.', REVERSE(str)) - 1 - i )
	);

	/*保留的最后一位小数 ,需注意若保留小数位数为0时，小数点的截取问题*/
	IF i = 0 THEN
		SET str3 = SUBSTR(str, LOCATE('.' ,str) - 1, 1);
	ELSE
		SET str3 = SUBSTR(str, LOCATE('.' ,str) + i, 1);
	END IF;

	/*符合五成双的条件*/
	IF SUBSTR(str2, 1, 1) != 0 AND SUBSTR(str2, 1, 1) % 5 = 0 THEN
		/*判断5后面是否还存在不为0的数字*/
		IF LENGTH(str2) >= 2 AND SUBSTR(str2, 2, LENGTH(str2) - 1) > 0 THEN
			/*若存在，则进一（注意小数点的截取问题）*/
			SET str = ZRoundSub(str,i,TRUE);
		/*不存在，另判断需保留小数最后一位的奇偶数，奇数进一，偶数舍五*/
		ELSE
			/*偶数舍五 ,需注意小数点的截取问题*/
			IF str3 % 2 = 0 THEN
				SET str = ZRoundSub(str,i,FALSE);
			/*奇数进一 ,需注意小数点的截取问题*/
			ELSE
				SET str = ZRoundSub(str,i,TRUE);
			END IF;
		END IF;
	/*四舍六入*/
	ELSE
		SET str = CAST(Round(str ,i) AS CHAR);
	END IF;
	
	RETURN str;
END;


调用函数

SELECT ZRound(1,1);

SELECT ZRound(0.999,2)