222

 CREATE OR REPLACE PROCEDURE P_MONEY2CHINESE
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);
  ERR_NULL       EXCEPTION ;
  CURSOR MYCUR IS (SELECT id, num  FROM t_a  );
  MONEY          NUMBER(11,2) ;
  V_ID           NUMBER ;
 
BEGIN
  OPEN MYCUR;
  IF(MYCUR%ISOPEN) THEN
  DBMS_OUTPUT.put_line('OPEN') ;
  ELSE
  DBMS_OUTPUT.put_line('NOT OPEN') ;
  END IF ;
  IF(MYCUR%FOUND) THEN
  DBMS_OUTPUT.put_line('FOUND') ;
  ELSE
  DBMS_OUTPUT.put_line('NOT FOUND') ;
  END IF ;
  WHILE MYCUR%FOUND LOOP
  FETCH MYCUR INTO V_ID,MONEY ;
  DBMS_OUTPUT.put_line(MONEY) ;
  IF MONEY IS NULL THEN
    RAISE ERR_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;*/
 DBMS_OUTPUT.PUT_LINE('RESULT IS : '||STRRST) ;
 INSERT INTO t_b VALUES(V_ID,STRRST) ;
 COMMIT ;
 END LOOP ;
 CLOSE MYCUR ;
EXCEPTION
  WHEN ERR_NULL THEN
    DBMS_OUTPUT.put_line('不能出现空值！') ;
    ROLLBACK ;
  WHEN OTHERS THEN
    dbms_output.put_line(SQLCODE||SQLERRM) ;
    ROLLBACK ;
END P_MONEY2CHINESE;