oracle大写数字转小写,求助oracle小写金额转换大写金额的函数

申明不是本人所写的！

FUNCTION L2U -- 小写金额转换成大写

(n_LowerMoney IN NUMBER,

v_TransType  IN NUMBER DEFAULT 1 -- 1: directly translate, 0: read it in words

) RETURN VARCHAR2 is

v_LowerStr  VARCHAR2(200); -- 小写金额

v_UpperPart VARCHAR2(200);

v_UpperStr  VARCHAR2(200); -- 大写金额

BEGIN

v_LowerStr := LTRIM(RTRIM(TO_CHAR(ROUND(n_LowerMoney, 2),

'9999999999999.99')));

IF SUBSTR(v_LowerStr, 1, 1) = '#' THEN

RETURN '转换金额超过计算范围(计算范围为:计算范围为: 0 - 9,999,999,999,999.99)';

END IF;

FOR I IN 1 .. LENGTH(v_LowerStr) LOOP

SELECT DECODE(SUBSTR(v_LowerStr, LENGTH(v_LowerStr) - I + 1, 1),

'.',

'元',

'0',

'零',

'1',

'壹',

'2',

'贰',

'3',

'叁',

'4',

'肆',

'5',

'伍',

'6',

'陆',

'7',

'柒',

'8',

'捌',

'9',

'玖') || DECODE(I,

1,

'分',

2,

'角',

3,

NULL,

4,

NULL,

5,

'拾',

6,

'佰',

7,

'仟',

8,

'万',

9,

'拾',

10,

'佰',

11,

'仟',

12,

'亿',

13,

'拾',

14,

'佰',

15,

'仟',

16,

'万',

NULL)

INTO v_UpperPart

FROM DUAL;

v_UpperStr := v_UpperPart || v_UpperStr;

END LOOP;

IF v_TransType = 0 then

v_UpperStr := REPLACE(v_UpperStr, '零拾', '零');

v_UpperStr := REPLACE(v_UpperStr, '零佰', '零');

v_UpperStr := REPLACE(v_UpperStr, '零仟', '零');

v_UpperStr := REPLACE(v_UpperStr, '零零零', '零');

v_UpperStr := REPLACE(v_UpperStr, '零零', '零');

v_UpperStr := REPLACE(v_UpperStr, '零角零分', '整');

v_UpperStr := REPLACE(v_UpperStr, '零分', '整');

v_UpperStr := REPLACE(v_UpperStr, '零角', '零');

v_UpperStr := REPLACE(v_UpperStr, '零亿零万零元', '亿元');

v_UpperStr := REPLACE(v_UpperStr, '亿零万零元', '亿元');

v_UpperStr := REPLACE(v_UpperStr, '零亿零万', '亿');

v_UpperStr := REPLACE(v_UpperStr, '零万零元', '万元');

v_UpperStr := REPLACE(v_UpperStr, '万零元', '万元');

v_UpperStr := REPLACE(v_UpperStr, '零亿', '亿');

v_UpperStr := REPLACE(v_UpperStr, '零万', '万');

v_UpperStr := REPLACE(v_UpperStr, '零元', '元');

v_UpperStr := REPLACE(v_UpperStr, '零零', '零');

END IF;

-- 对壹元以下的金额的处理

v_UpperStr := LTRIM(LTRIM(LTRIM(LTRIM(v_UpperStr, '元'), '零'), '角'),

'分');

IF SUBSTR(v_UpperStr, 1, 1) = '整' THEN

v_UpperStr := '零元整';

END IF;

RETURN v_UpperStr;

EXCEPTION

WHEN OTHERS THEN

RETURN '发生错误: ' || SQLCODE || '--' || SQLERRM;

END L2U;