1.创建函数
CREATE OR REPLACE FUNCTION LTOU -- 小写金额转换成大写(n_LowerMoney IN NUMBER)RETURN VARCHAR2ASv_LowerStr VARCHAR2(200); -- 小写金额v_UpperPart VARCHAR2(200);v_UpperStr VARCHAR2(200); -- 大写金额BEGINv_LowerStr := LTRIM(RTRIM(TO_CHAR(ROUND(n_LowerMoney,2),'9999999999990.99')));IF SUBSTR(v_LowerStr,1,1) = '#' THENRETURN '转换金额超过计算范围(计算范围为:计算范围为: 0 - 9,999,999,999,990.99)';END IF;FOR I IN 1 .. LENGTH(v_LowerStr) LOOPSELECT 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) = '整' THENv_UpperStr := '零元整';END IF;RETURN v_UpperStr;EXCEPTION WHEN OTHERS THENRETURN '发生错误: '||SQLCODE||'--'||SQLERRM;END LTOU;
2.调用函数
SELECT LTOU(745.25) FROM dual;
查询结果：柒佰肆拾伍元贰角伍分
