Oracle函數實現：人民幣小寫轉換為發票大寫

利用資料庫的函數，實現人民幣小寫轉換成發票大寫方式：

CREATE OR REPLACE FUNCTION L2U -- 小寫金額轉換成大寫
(
    n_LowerMoney IN NUMBER
)
RETURN VARCHAR2
AS
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;

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,'零零','零');

-- 對壹元以下的金額的處理
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;