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