在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;
/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7416120/viewspace-995599/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7416120/viewspace-995599/