Oracle 金额转大写

在Oracle中，可以使用以下函数将金额转换为大写：

CREATE OR REPLACE FUNCTION num_to_chinese(num IN NUMBER)
   RETURN VARCHAR2
IS
   str_num     VARCHAR2 (50) DEFAULT TO_CHAR (num);
   len         NUMBER := LENGTH (str_num);
   units       CONSTANT VARCHAR2 (20) :=
                  '零壹贰叁肆伍陆柒捌玖';
   tens        CONSTANT VARCHAR2 (20) :=
                  '零拾佰仟';
   big_units   CONSTANT VARCHAR2 (20) :=
                  '零万亿兆京垓';
   result      VARCHAR2 (100) := '';
   cur_unit    VARCHAR2 (1);
   prev_unit   VARCHAR2 (1);
   i           NUMBER;
   digit       NUMBER;
   prev_digit  NUMBER;
BEGIN
   IF num = 0
   THEN
      RETURN '零';
   END IF;

   FOR i IN 1 .. len
   LOOP
      digit := TO_NUMBER (SUBSTR (str_num, i, 1));

      IF digit = 0
      THEN
         IF MOD (i - 1, 4) = 0
         THEN
            cur_unit := SUBSTR (big_units, i / 4 + 1, 1);

            IF cur_unit <> prev_unit
            THEN
               result := result || cur_unit;
               prev_unit := cur_unit;
            END IF;
         END IF;

         IF MOD (i - 1, 4) <> 0
            AND prev_digit <> 0
         THEN
            result := result || '零';
         END IF;
      ELSE
         cur_unit := SUBSTR (tens, MOD (i - 1, 4) + 1, 1);

         IF cur_unit <> prev_unit
         THEN
            result := result || cur_unit;
            prev_unit := cur_unit;
         END IF;

         result := result || SUBSTR (units, digit + 1, 1);

         IF MOD (i - 1, 4) = 0
         THEN
            cur_unit := SUBSTR (big_units, i / 4 + 1, 1);

            IF cur_unit <> prev_unit
            THEN
               result := result || cur_unit;
               prev_unit := cur_unit;
            END IF;
         END IF;
      END IF;

      prev_digit := digit;
   END LOOP;

   RETURN result;
END;

然后，您可以使用以下SQL语句将金额转换为大写：

SELECT num_to_chinese(123456789.12) FROM dual;

输出：

壹亿贰仟叁佰肆拾伍万陆仟柒佰捌拾玖元壹角贰分