Oracle PL SQL将金额数字转换成大写

原文地址：
http://blog.sina.com.cn/s/blog_6a3922be0101a72t.html




CREATE OR REPLACE FUNCTION SMALITOBLG(
SMALLMONEY VARCHAR2
)
RETURN VARCHAR2 IS
BIGWRITE VARCHAR2(54); -- 用于负担会答谢的钱数
BIGNUM VARCHAR(2);    -- 用于存放每一个阿拉伯数字对应的汉子
RMB VARCHAR(2);       -- 用于存放人民币单位
MONEYPLACE NUMBER;    -- 用于确定人民币的精度，最多只能精确到分
DOTPLACE NUMBER;      -- 确定小数点的位置
MONEYNUM NUMBER;      -- 人民币的位置
MYEXCEPTION EXCEPTION; -- 自定义异常
BEGIN
 
   DOTPLACE := INSTR(SMALLMONEY,'.');
 
   IF(LENGTH(SMALLMONEY) > 14) OR ((LENGTH(SMALLMONEY) > 12) AND (DOTPLACE = 0)) THEN
      RAISE MYEXCEPTION;
   END IF;
 
   IF DOTPLACE = 0 THEN
     MONEYPLACE := 0;
   ELSE
     MONEYPLACE := DOTPLACE - LENGTH(SMALLMONEY);
   END IF;
 
   FOR MONEYNUM IN REVERSE 1..LENGTH(SMALLMONEY) LOOP
   
    IF MONEYNUM <> DOTPLACE THEN
      
      CASE SUBSTR(SMALLMONEY,MONEYNUM,1)
         WHEN '1' THEN BIGNUM := '壹';
         WHEN '2' THEN BIGNUM := '贰';
         WHEN '3' THEN BIGNUM := '叁';
         WHEN '4' THEN BIGNUM := '肆';
         WHEN '5' THEN BIGNUM := '伍';
         WHEN '6' THEN BIGNUM := '陆';
         WHEN '7' THEN BIGNUM := '柒';
         WHEN '8' THEN BIGNUM := '捌';
         WHEN '9' THEN BIGNUM := '玖';
         WHEN '0' THEN BIGNUM := '零';
      END CASE;
      CASE MONEYPLACE
         WHEN '-2' THEN RMB := '分';
         WHEN '-1' THEN RMB := '角';
         WHEN '0' THEN RMB := '元';
         WHEN '1' THEN RMB := '拾';
         WHEN '2' THEN RMB := '佰';
         WHEN '3' THEN RMB := '仟';
         WHEN '4' THEN RMB := '萬';
         WHEN '5' THEN RMB := '拾';
         WHEN '6' THEN RMB := '佰';
         WHEN '7' THEN RMB := '仟';
         WHEN '8' THEN RMB := '亿';
         WHEN '9' THEN RMB := '拾';
         WHEN '10' THEN RMB := '佰';
         WHEN '11' THEN BIGNUM := '仟';
      END CASE;
      MONEYPLACE := MONEYPLACE + 1;
      IF BIGWRITE IS NULL THEN
        BIGWRITE := BIGNUM || RMB;
      ELSE
        BIGWRITE := BIGNUM || RMB || BIGWRITE;
      END IF;
    END IF;
   END LOOP;
   RETURN BIGWRITE;
   EXCEPTION -- 异常处理部分
   WHEN MYEXCEPTION THEN
     DBMS_OUTPUT.PUT_LINE('该函数只能转换长度不大于14位或整数位不大于12位的钱数！');
   WHEN OTHERS THEN
     DBMS_OUTPUT.PUT_LINE('不是有效的钱数！');
END SMALITOBLG;