转换大小写金额的过程

CREATE OR REPLACE FUNCTION money_to_chinese (money IN VARCHAR2)  
   RETURN VARCHAR2  
IS  
   c_money    VARCHAR2 (12);  
   m_string   VARCHAR2 (60) := '分角圆拾佰仟万拾佰仟亿';  
   n_string   VARCHAR2 (40) := '壹贰叁肆伍陆柒捌玖';  
   b_string   VARCHAR2 (80);  
   n          CHAR;  
   len        NUMBER (3);  
   i          NUMBER (3);  
   tmp        NUMBER (12);  
   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, '999999999999')));  
   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 = 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 = 6  
            THEN  
               b_string := b_string || '万';  
            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;
/

使用方法

SELECT money_to_chinese(ssje) as dx FROM ywdjb WHERE djh='02x1995052119950140'