小写数字转财务大写

转载的知识点，感谢原创作者，我这里备忘记录下。

 

CREATE OR REPLACE FUNCTION MONEY2CHINESE(MONEY IN NUMBER) RETURN VARCHAR2 IS
  STRYUAN       VARCHAR2(150);
  STRYUANFEN    VARCHAR2(152);
  NUMLENYUAN    NUMBER;
  NUMLENYUANFEN NUMBER;
  STRRSTYUAN    VARCHAR2(600);
  STRRSTFEN     VARCHAR2(200);
  STRRST        VARCHAR2(800);
  TYPE TYPETABMAPPING IS TABLE OF VARCHAR2(2) INDEX BY BINARY_INTEGER;
  TABNUMMAPPING  TYPETABMAPPING;
  TABUNITMAPPING TYPETABMAPPING;
  NUMUNITINDEX   NUMBER;
  I              NUMBER;
  J              NUMBER;
  CHARCURRENTNUM CHAR(1);
BEGIN
  IF MONEY IS NULL THEN
    RETURN NULL;
  END IF;
  STRYUAN := TO_CHAR(FLOOR(MONEY));
  IF STRYUAN = '0' THEN
    NUMLENYUAN := 0;
    STRYUANFEN := LPAD(TO_CHAR(FLOOR(MONEY * 100)), 2, '0');
  ELSE
    NUMLENYUAN := LENGTH(STRYUAN);
    STRYUANFEN := TO_CHAR(FLOOR(MONEY * 100));
  END IF;
  IF STRYUANFEN = '0' THEN
    NUMLENYUANFEN := 0;
  ELSE
    NUMLENYUANFEN := LENGTH(STRYUANFEN);
  END IF;
  IF NUMLENYUAN = 0 OR NUMLENYUANFEN = 0 THEN
    STRRST := '零圆整';
    RETURN STRRST;
  END IF;
  TABNUMMAPPING(0) := '零';
  TABNUMMAPPING(1) := '壹';
  TABNUMMAPPING(2) := '贰';
  TABNUMMAPPING(3) := '叁';
  TABNUMMAPPING(4) := '肆';
  TABNUMMAPPING(5) := '伍';
  TABNUMMAPPING(6) := '陆';
  TABNUMMAPPING(7) := '柒';
  TABNUMMAPPING(8) := '捌';
  TABNUMMAPPING(9) := '玖';
  TABUNITMAPPING(-2) := '分';
  TABUNITMAPPING(-1) := '角';
  TABUNITMAPPING(1) := '';
  TABUNITMAPPING(2) := '拾';
  TABUNITMAPPING(3) := '佰';
  TABUNITMAPPING(4) := '仟';
  TABUNITMAPPING(5) := '万';
  TABUNITMAPPING(6) := '拾';
  TABUNITMAPPING(7) := '佰';
  TABUNITMAPPING(8) := '仟';
  TABUNITMAPPING(9) := '亿';
  FOR I IN 1 .. NUMLENYUAN LOOP
    J            := NUMLENYUAN - I + 1;
    NUMUNITINDEX := MOD(I, 8);
    IF NUMUNITINDEX = 0 THEN
      NUMUNITINDEX := 8;
    END IF;
    IF NUMUNITINDEX = 1 AND I > 1 THEN
      STRRSTYUAN := TABUNITMAPPING(9) || STRRSTYUAN;
    END IF;
    CHARCURRENTNUM := SUBSTR(STRYUAN, J, 1);
    IF CHARCURRENTNUM <> 0 THEN
      STRRSTYUAN := TABNUMMAPPING(CHARCURRENTNUM) ||
                    TABUNITMAPPING(NUMUNITINDEX) || STRRSTYUAN;
    ELSE
      IF (I = 1 OR I = 5) THEN
        IF SUBSTR(STRYUAN, J - 3, 4) <> '0000' THEN
          STRRSTYUAN := TABUNITMAPPING(NUMUNITINDEX) || STRRSTYUAN;
        END IF;
      ELSE
        IF SUBSTR(STRYUAN, J + 1, 1) <> '0' THEN
          STRRSTYUAN := TABNUMMAPPING(CHARCURRENTNUM) || STRRSTYUAN;
        END IF;
      END IF;
    END IF;
  END LOOP;
  FOR I IN -2 .. -1 LOOP
    J              := NUMLENYUAN - I;
    CHARCURRENTNUM := SUBSTR(STRYUANFEN, J, 1);
    IF CHARCURRENTNUM <> '0' THEN
      STRRSTFEN := TABNUMMAPPING(CHARCURRENTNUM) || TABUNITMAPPING(I) ||
                   STRRSTFEN;
    END IF;
  END LOOP;
  IF STRRSTYUAN IS NOT NULL THEN
    STRRSTYUAN := STRRSTYUAN || '圆';
  END IF;
  IF STRRSTFEN IS NULL THEN
    STRRSTYUAN := STRRSTYUAN || '整';
  ELSIF LENGTH(STRRSTFEN) = 2 AND SUBSTR(STRRSTFEN, 2) = '角' THEN
    STRRSTFEN := STRRSTFEN || '整';
  END IF;
  STRRST := STRRSTYUAN || STRRSTFEN;
  --STRRST := REPLACE(STRRST, '亿零', '亿');
  --STRRST := REPLACE(STRRST, '万零', '万');
  RETURN STRRST;
END MONEY2CHINESE;