Oracle数据库将数字金额转换为大写汉字

Oracle：

--------------------------------------------------------

--  数字金额转大写

--------------------------------------------------------

 

create or replace FUNCTION MoneyToChinese(P_NUM IN NUMBER DEFAULT NULL)

  RETURN NVARCHAR2 IS

  RESULT      NVARCHAR2(100); --返回字符串

  NUM_ROUND   NVARCHAR2(100) := TO_CHAR(ABS(ROUND(P_NUM, 2))); --转换数字为小数点后2位的字符(正数)

  NUM_LEFT    NVARCHAR2(100); --小数点左边的数字

  NUM_RIGHT   NVARCHAR2(2); --小数点右边的数字

  STR1        NCHAR(10) := '零壹贰叁肆伍陆柒捌玖'; --数字大写

  STR2        NCHAR(16) := '元拾佰仟万拾佰仟亿拾佰仟万拾佰仟'; --数字位数(从低至高)

  NUM_PRE     NUMBER(1) := 1; --前一位上的数字

  NUM_CURRENT NUMBER(1); --当前位上的数字

  NUM_COUNT   NUMBER := 0; --当前数字位数

  NUM1        NUMBER;

 

BEGIN

  IF P_NUM IS NULL THEN

    RETURN NULL;

  END IF; --转换数字为null时返回null

 

  SELECT TO_CHAR(NVL(SUBSTR(TO_CHAR(NUM_ROUND),

                            1,

                            DECODE(INSTR(TO_CHAR(NUM_ROUND), '.'),

                                   0,

                                   LENGTH(NUM_ROUND),

                                   INSTR(TO_CHAR(NUM_ROUND), '.') - 1)),

                     0))

    INTO NUM_LEFT

    FROM DUAL; --取得小数点左边的数字

  SELECT SUBSTR(TO_CHAR(NUM_ROUND),

                DECODE(INSTR(TO_CHAR(NUM_ROUND), '.'),

                       0,

                       LENGTH(NUM_ROUND) + 1,

                       INSTR(TO_CHAR(NUM_ROUND), '.') + 1),

                2)

    INTO NUM_RIGHT

    FROM DUAL; --取得小数点右边的数字

  SELECT CASE

           WHEN LENGTH(NUM_LEFT) >= 8 THEN

            TO_NUMBER(SUBSTR(TO_CHAR(NUM_LEFT), -8, 4))

           ELSE

            TO_NUMBER(SUBSTR(TO_CHAR(NUM_LEFT),

                             -LENGTH(NUM_LEFT),

                             LENGTH(NUM_LEFT) - 4))

         END

    INTO NUM1

    FROM DUAL; ---取得千、百、十、万位上的数字

  IF LENGTH(NUM_LEFT) > 16 THEN

    RETURN '**********';

  END IF; --数字整数部分超过16位时

 

  --采用从低至高的算法，先处理小数点右边的数字

  IF LENGTH(NUM_RIGHT) = 2 THEN

    IF TO_NUMBER(SUBSTR(NUM_RIGHT, 1, 1)) = 0 THEN

      RESULT := '零' ||

                SUBSTR(STR1, TO_NUMBER(SUBSTR(NUM_RIGHT, 2, 1)) + 1, 1) || '分';

    ELSE

      RESULT := SUBSTR(STR1, TO_NUMBER(SUBSTR(NUM_RIGHT, 1, 1)) + 1, 1) || '角' ||

                SUBSTR(STR1, TO_NUMBER(SUBSTR(NUM_RIGHT, 2, 1)) + 1, 1) || '分';

    END IF;

  ELSIF LENGTH(NUM_RIGHT) = 1 THEN

    RESULT := SUBSTR(STR1, TO_NUMBER(SUBSTR(NUM_RIGHT, 1, 1)) + 1, 1) || '角整';

  ELSE

    RESULT := '整';

  END IF;

  --再处理小数点左边的数字

  FOR I IN REVERSE 1 .. LENGTH(NUM_LEFT) LOOP

    --(从低至高)

    NUM_COUNT   := NUM_COUNT + 1; --当前数字位数

    NUM_CURRENT := TO_NUMBER(SUBSTR(NUM_LEFT, I, 1)); --当前位上的数字

    IF NUM_CURRENT > 0 THEN

      --当前位上数字不为0按正常处理

      RESULT := SUBSTR(STR1, NUM_CURRENT + 1, 1) ||

                SUBSTR(STR2, NUM_COUNT, 1) || RESULT;

    ELSE

      --当前位上数字为0时

      IF NUM_COUNT = 5 THEN

        IF MOD(NUM_COUNT - 1, 4) = 0 AND NUM1 <> 0 THEN

          RESULT  := SUBSTR(STR2, NUM_COUNT, 1) || RESULT;

          NUM_PRE := 0; --元、万,亿前不准加零 --当前位是元、万或亿时

        END IF;

      ELSE

        IF MOD(NUM_COUNT - 1, 4) = 0 THEN

          RESULT  := SUBSTR(STR2, NUM_COUNT, 1) || RESULT;

          NUM_PRE := 0; --元、万,亿前不准加零

        END IF;

      END IF;

      IF NUM_PRE > 0 OR LENGTH(NUM_LEFT) = 1 THEN

        --上一位数字不为0或只有个位时

        RESULT := SUBSTR(STR1, NUM_CURRENT + 1, 1) || RESULT;

      END IF;

    END IF;

    NUM_PRE := NUM_CURRENT;

  END LOOP;

 

  IF P_NUM < 0 THEN

    --转换数字是负数时

    RESULT := '负' || RESULT;

  END IF;

 

  RETURN RESULT;

 

EXCEPTION

  WHEN OTHERS THEN

    RAISE_APPLICATION_ERROR(-20001, '数字转换大写出现错误！' || SQLERRM);

END;

 

 

将上面函数代码在数据库中执行，直接用函数即可完成转换。