用Oracle 写一个金额转大写的函数

好的，请见下面的 Oracle 函数：

CREATE FUNCTION num_to_text (p_number IN NUMBER)
   RETURN VARCHAR2
IS
   l_number   NUMBER := TRUNC (p_number);
   l_leftover NUMBER := ROUND (100 * (p_number - l_number));
   l_text     VARCHAR2 (4000);
BEGIN
   IF l_number > 999999999999999THEN
      RAISE_APPLICATION_ERROR (-20001, 'Number too large');
   END IF;

   IF l_number = 0THEN
      l_text := 'ZERO';
   ELSE
      l_text := num_to_text (TRUNC (l_number / 1000)) || ' THOUSAND ';
      l_number := MOD (l_number, 1000);

      l_text := l_text || num_to_text (TRUNC (l_number / 100)) || ' HUNDRED ';
      l_number := MOD (l_number, 100);

      IF l_number > 0THEN
         l_text := l_text || num_to_text_under_100 (l_number);
      END IF;
   END IF;

   IF l_leftover > 0THEN
      l_text := l_text || 'AND ' || num_to_text_under_100 (l_leftover) || ' CENTS';
   END IF;

   RETURN TRIM (l_text);
END num_to_text;

FUNCTION num_to_text_under_100 (p_number NUMBER)
   RETURN VARCHAR2
IS
   l_text VARCHAR2 (200);
BEGIN
   IF p_number = 0THEN
      l_text := '';
   ELSIF p_number < 10THEN
      l_text :=
            DECODE (
               p_number,
               1, 'ONE',
               2, 'TWO',
               3, 'THREE',
               4, 'FOUR',
               5, 'FIVE',
               6, 'SIX',
               7, 'SEVEN',
               8, 'EIGHT',
               9, 'NINE');
   ELSIF p_number < 20THEN
      l_text :=
            DECODE (
               p_number,
               10, 'TEN',
               11, 'ELEVEN',
               12, 'TWELVE',
               13, 'THIRTEEN',
               14, 'FOURTEEN',
               15, 'FIFTEEN',
               16, 'SIXTEEN',
               17, 'SEVENTEEN',
               18, 'EIGHTEEN',
               19, 'NINETEEN');
   ELSE
      l_text :=
         DECODE (
            TRUNC (p_number / 10),
            2, 'TWENTY',
            3, 'THIRTY',
            4, 'FORTY',
            5, 'FIFTY',
            6, 'SIXTY',
            7, 'SEVENTY',
            8, 'EIGHTY',
            9, 'NINETY') || ' ';

      l_text := l_text || num_to_text_under_100 (MOD (p_number, 10));
   END IF;

   RETURN l_text;
END num_to_text_under_100;

这个函数可以将一个金额转换为大写形式。例如，如果你运行：

SELECT num_to_text (123456.78) FROM DUAL;

将会得到：

ONE HUNDRED TWENTY-THREE THOUSAND FOUR HUNDRED FIFTY-SIX AND 78/100 CENTS

希望这能对你有所帮助！