oracle显示人民币,在Oracle中将小写人民币转换成大写

CREATE OR REPLACE FUNCTION uf_getbig (nsmallmoney NUMBER)

RETURN VARCHAR2

/*

NAME: UF_GETBIG

PURPOSE:

Author: I

CreateDate: 2007-12-23

描述: 用于返回大写的人民币,目前只支持到百万

*/

IS

sbigmoney VARCHAR2(50);

rcmoney pk_var.rc;

[@more@]BEGIN

OPEN rcmoney FOR

SELECT TRANSLATE

( CASE

WHEN MOD (FLOOR (nsmallmoney / 1000000), 10) > 0

THEN MOD (FLOOR (nsmallmoney / 1000000), 10) || 'c'

END

|| CASE

WHEN MOD (FLOOR (nsmallmoney / 100000), 10) > 0

THEN MOD (FLOOR (nsmallmoney / 100000), 10) || 'd'

END

|| CASE

WHEN MOD (FLOOR (nsmallmoney / 10000), 10) > 0

THEN MOD (FLOOR (nsmallmoney / 10000), 10) || 'a'

END

|| CASE

WHEN MOD (FLOOR (nsmallmoney / 1000), 10) > 0

THEN MOD (FLOOR (nsmallmoney / 1000), 10) || 'b'

END

|| CASE

WHEN MOD (FLOOR (nsmallmoney / 100), 10) > 0

THEN MOD (FLOOR (nsmallmoney / 100), 10) || 'c'

END

|| CASE

WHEN MOD (FLOOR (nsmallmoney / 10), 10) > 0

THEN MOD (FLOOR (nsmallmoney / 10), 10) || 'd'

END

|| CASE

WHEN MOD (FLOOR (nsmallmoney), 10) > 0

THEN MOD (FLOOR (nsmallmoney), 10) || '.'

END

|| CASE

WHEN MOD (FLOOR (nsmallmoney / 0.1), 10) > 0

THEN MOD (FLOOR (nsmallmoney / 0.1), 10) || 'e'

END

|| CASE

WHEN MOD (FLOOR (nsmallmoney / 0.01), 10) > 0

THEN MOD (FLOOR (nsmallmoney / 0.01), 10) || 'f'

END,

'1234567890.abcdef', -- 这个串到时会用下面的替换

'壹贰叁肆伍陆柒捌久零圆万千佰十角分'

)

FROM DUAL;

FETCH rcmoney

INTO sbigmoney;

CLOSE rcmoney;

RETURN sbigmoney;

EXCEPTION

WHEN NO_DATA_FOUND

THEN

NULL;

END uf_getbig;

/