数据库数字金额转中文大写函数–Oracle

今天同事使用到自定义函数MONEY_TO_CHINESE，感觉挺实用，记录一下，待用

CREATE OR REPLACE FUNCTION MONEY_TO_CHINESE(money in VARCHAR2)
   return varchar2 is
     c_money   VARCHAR2(30);
     m_string VARCHAR2(60) := '分角元拾佰仟万拾佰仟亿拾佰仟万';
     n_string VARCHAR2(40) := '壹贰叁肆伍陆柒捌玖';
     b_string VARCHAR2(80);
     n         CHAR;
     len       NUMBER(3);
     i         NUMBER(3);
     tmp       NUMBER(20);
     is_zero   BOOLEAN;
     z_count   NUMBER(3);
     l_money   NUMBER;
     l_sign    VARCHAR2(10);

   BEGIN
     l_money := abs(money);
     IF money < 0 THEN
       l_sign := '负' ;
     ELSE
       l_sign := '';
     END IF;
     tmp      := round(l_money, 2) * 100;
     c_money := rtrim(ltrim(to_char(tmp, '999999999999999')));
     len      := length(c_money);
     is_zero := TRUE;
     z_count := 0;
     i        := 0;
     
    WHILE i < len LOOP
       i := i + 1;
       n := substr(c_money, i, 1);
       IF n = '0' THEN
         IF   len - i =10 OR len - i = 6 OR len - i = 2 OR len = i THEN
           IF is_zero THEN
             b_string := substr(b_string, 1, length(b_string) - 1);
             is_zero   := FALSE;
           END IF;
           IF (len - i =10  ) THEN
             b_string := b_string || '亿';
           
           END IF;
          
           IF (len - i = 6) THEN
              IF z_count=3 THEN
                 b_string := b_string;
              ELSE
                 b_string := b_string|| '万';
              END IF;
           END IF;
    
           IF (len - i = 2   )THEN
             b_string := b_string || '元';
           END IF;
           
           IF len = i THEN
             b_string := b_string || '整';
           END IF;
           z_count := 0;
           
         ELSE 
           IF z_count = 0 THEN
             b_string := b_string || '零';
             is_zero   := true;
           END IF;
           z_count := z_count + 1;
           
    
         END IF;
     
       ELSE  
         b_string := b_string || substr(n_string, to_number(n), 1) ||
                     substr(m_string, len - i + 1, 1);
         z_count   := 0;
         is_zero   := FALSE;
       END IF;
     END LOOP;

     b_string := l_sign || b_string ;
     RETURN b_string;
exception
   --异常处理
    WHEN OTHERS THEN
       RETURN(SQLERRM);
END;