MySQL自定义函数实现金额转大写

MySQL中自定义函数，然后在SQL语句中调用即可

CREATE  FUNCTION fun_convertm(MONEY VARCHAR(150) ) RETURNS VARCHAR(150) CHARSET utf8
    DETERMINISTIC
BEGIN
DECLARE  RESULT      VARCHAR(100); -- 返回字符串
DECLARE  NUM_ROUND   VARCHAR(100); -- 转换数字为小数点后2位的字符(正数)
DECLARE  NUM_LEFT    VARCHAR(100); -- 小数点左边的数字
DECLARE  NUM_RIGHT   VARCHAR(2); -- 小数点右边的数字
DECLARE  STR1        VARCHAR(10); -- 数字大写
DECLARE  STR2        VARCHAR(16); -- 数字位数(从低至高)
DECLARE  NUM_PRE     INT; -- 前一位上的数字
DECLARE  NUM_CURRENT INT; -- 当前位上的数字
DECLARE  NUM_COUNT   INT; -- 当前数字位数
DECLARE  NUM1        INT;
SET    MONEY=CONVERT(MONEY,DECIMAL(14,2));
SET NUM_ROUND=CONCAT(MONEY, '');
SET STR1='零壹贰叁肆伍陆柒捌玖'; 
SET STR2='圆拾佰仟万拾佰仟亿拾佰仟万拾佰仟';
SET NUM_PRE=1;
SET NUM_COUNT=0;
SET NUM_LEFT=FLOOR(MONEY);
SET NUM_RIGHT=REPLACE(NUM_ROUND,CONCAT(NUM_LEFT,'.'),'');
 
IF MONEY IS NULL THEN
SET RESULT=NULL;
END IF; -- 转换数字为null时返回null
 
IF LENGTH(NUM_LEFT)>=8 THEN SET NUM1=CAST(SUBSTR(NUM_LEFT, -8, 4) AS SIGNED);
ELSEIF LENGTH(NUM_LEFT)>4 THEN    SET NUM1=CAST(SUBSTR(NUM_LEFT, -LENGTH(NUM_LEFT), LENGTH(NUM_LEFT)-4) AS SIGNED);
ELSE SET NUM1=CAST(SUBSTR(NUM_LEFT, 1, 4) AS SIGNED);
END IF;
 
IF LENGTH(NUM_LEFT) > 16 THEN SET RESULT='**********';
END IF; -- 数字整数部分超过16位时
 
 -- 采用从低至高的算法，先处理小数点右边的数字
IF LENGTH(NUM_RIGHT) = 2 THEN
   IF CAST(SUBSTR(NUM_RIGHT, 1, 1) AS SIGNED) = 0 THEN
      SET RESULT = CONCAT('零' ,
                SUBSTR(STR1, CAST(SUBSTR(NUM_RIGHT, 2, 1) AS SIGNED) + 1, 1) , '分');
   ELSE
      SET RESULT = CONCAT(SUBSTR(STR1, CAST(SUBSTR(NUM_RIGHT, 1, 1) AS SIGNED) + 1, 1) , '角' ,
                SUBSTR(STR1, CAST(SUBSTR(NUM_RIGHT, 2, 1) AS SIGNED) + 1, 1) , '分');
   END IF;
ELSE 
   IF LENGTH(NUM_RIGHT) = 1 THEN
    SET RESULT = CONCAT(SUBSTR(STR1, CAST(SUBSTR(NUM_RIGHT, 1, 1) AS SIGNED) + 1, 1) , '角整');
   ELSE
        SET RESULT = '整';
   END IF;
END IF;
 
-- 再处理小数点左边的数字
myloop:LOOP
SET NUM_COUNT=NUM_COUNT+1; -- 当前数字位数
SET NUM_CURRENT=CAST(SUBSTR(NUM_LEFT, LENGTH(NUM_LEFT)-NUM_COUNT+1, 1) AS SIGNED);
IF  NUM_CURRENT > 0 THEN SET RESULT=CONCAT(SUBSTR(STR1, NUM_CURRENT + 1, 1) ,
                SUBSTR(STR2, NUM_COUNT, 1) , RESULT);
ELSE
    IF NUM_COUNT = 5 THEN
       IF MOD(NUM_COUNT - 1, 4) = 0 AND NUM1 <> 0 THEN SET RESULT = CONCAT(SUBSTR(STR2, NUM_COUNT, 1) , RESULT); SET NUM_PRE = 0;
       END IF;
    ELSE
       IF MOD(NUM_COUNT - 1, 4) = 0 THEN SET RESULT  = CONCAT(SUBSTR(STR2, NUM_COUNT, 1) , RESULT); SET  NUM_PRE = 0; -- 元、万,亿前不准加零
       END IF;
    END IF;
    IF NUM_PRE > 0 OR LENGTH(NUM_LEFT) = 1 THEN
        -- 上一位数字不为0或只有个位时
        SET RESULT = CONCAT(SUBSTR(STR1, NUM_CURRENT + 1, 1) , RESULT);
    END IF;
END IF;
SET NUM_PRE = NUM_CURRENT;
IF NUM_COUNT>=LENGTH(NUM_LEFT) THEN LEAVE myloop;
END IF;
END LOOP myloop; 
IF MONEY < 0 THEN
    -- 转换数字是负数时
    SET RESULT =CONCAT( '负' , RESULT);
END IF;
 SET RESULT=REPLACE(RESULT,'零零分','整');
 SET RESULT=REPLACE(RESULT,'零分','整');
 SET RESULT=REPLACE(RESULT,'元整','圆整');
RETURN RESULT;
END