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

在Oracle中將小寫人民幣轉換成大寫

相關文章