1、编写oracle函数
1 CREATE OR REPLACE Function MoneyToChinese(Money In Number) Return Varchar2 Is 2 strYuan Varchar2(150); 3 strYuanFen Varchar2(152); 4 numLenYuan Number; 5 numLenYuanFen Number; 6 strRstYuan Varchar2(600); 7 strRstFen Varchar2(200); 8 strRst Varchar2(800); 9 Type typeTabMapping Is Table Of Varchar2(2) Index By Binary_Integer; 10 tabNumMapping typeTabMapping; 11 tabUnitMapping typeTabMapping; 12 numUnitIndex Number; 13 i Number; 14 j Number; 15 charCurrentNum Char(1); 16 Begin 17 If Money Is Null Then 18 Return Null; 19 End If; 20 strYuan := TO_CHAR(FLOOR(Money)); 21 If strYuan = '0' Then 22 numLenYuan := 0; 23 strYuanFen := lpad(TO_CHAR(FLOOR(Money * 100)), 2, '0'); 24 Else 25 numLenYuan := length(strYuan); 26 strYuanFen := TO_CHAR(FLOOR(Money * 100)); 27 End If; 28 If strYuanFen = '0' Then 29 numLenYuanFen := 0; 30 Else 31 numLenYuanFen := length(strYuanFen); 32 End If; 33 If numLenYuan = 0 Or numLenYuanFen = 0 Then 34 strRst := '零元整'; 35 Return strRst; 36 End If; 37 tabNumMapping(0) := '零'; 38 tabNumMapping(1) := '壹'; 39 tabNumMapping(2) := '贰'; 40 tabNumMapping(3) := '叁'; 41 tabNumMapping(4) := '肆'; 42 tabNumMapping(5) := '伍'; 43 tabNumMapping(6) := '陆'; 44 tabNumMapping(7) := '柒'; 45 tabNumMapping(8) := '捌'; 46 tabNumMapping(9) := '玖'; 47 tabUnitMapping(-2) := '分'; 48 tabUnitMapping(-1) := '角'; 49 tabUnitMapping(1) := ''; 50 tabUnitMapping(2) := '拾'; 51 tabUnitMapping(3) := '佰'; 52 tabUnitMapping(4) := '仟'; 53 tabUnitMapping(5) := '万'; 54 tabUnitMapping(6) := '拾'; 55 tabUnitMapping(7) := '佰'; 56 tabUnitMapping(8) := '仟'; 57 tabUnitMapping(9) := '亿'; 58 For i In 1 .. numLenYuan Loop 59 j := numLenYuan - i + 1; 60 numUnitIndex := Mod(i, 8); 61 If numUnitIndex = 0 Then 62 numUnitIndex := 8; 63 End If; 64 If numUnitIndex = 1 And i > 1 Then 65 strRstYuan := tabUnitMapping(9) || strRstYuan; 66 End If; 67 charCurrentNum := substr(strYuan, j, 1); 68 If charCurrentNum <> 0 Then 69 strRstYuan := tabNumMapping(charCurrentNum) || 70 tabUnitMapping(numUnitIndex) || strRstYuan; 71 Else 72 If (i = 1 Or i = 5) Then 73 If substr(strYuan, j - 3, 4) <> '0000' Then 74 strRstYuan := tabUnitMapping(numUnitIndex) || strRstYuan; 75 End If; 76 Else 77 If substr(strYuan, j + 1, 1) <> '0' Then 78 strRstYuan := tabNumMapping(charCurrentNum) || strRstYuan; 79 End If; 80 End If; 81 End If; 82 End Loop; 83 For i In -2 .. -1 Loop 84 j := numLenYuan - i; 85 charCurrentNum := substr(strYuanFen, j, 1); 86 If charCurrentNum <> '0' Then 87 strRstFen := tabNumMapping(charCurrentNum) || tabUnitMapping(i) || 88 strRstFen; 89 End If; 90 End Loop; 91 If strRstYuan Is Not Null Then 92 strRstYuan := strRstYuan || '元'; 93 End If; 94 If strRstFen Is Null Then 95 strRstYuan := strRstYuan || '整'; 96 Elsif length(strRstFen) = 2 And substr(strRstFen, 2) = '角' Then 97 strRstFen := strRstFen || '整'; 98 End If; 99 strRst := strRstYuan || strRstFen; 100 --strRst := Replace(strRst, '亿零', '亿'); 101 --strRst := Replace(strRst, '万零', '万'); 102 Return strRst; 103 End MoneyToChinese;
注：如需测试该函数，请复制到Oracle数据库中，右击函数名“MoneyToChinese”，选择“test” 进行测试，输入你想要的金额。
2、在irport的Database里面写查询语句调用MoneyToChinese函数
小结：
(SELECT moneytochinese((select sum(sod.ACTUAL_UNIT_PRICE * sod.ACTUAL_QUANTITY) from SALE_ORDER_DETAIL sod where sod.sale_order_no = oci.sell_order_no)) FROM dual) as majuscule_price
其中moneytochinese是函数名。sum(sod.ACTUAL_UNIT_PRICE * sod.ACTUAL_QUANTITY) 是总金额，加工数量*单价的和。
|
原创作者：DSHORE 作者主页：http://www.cnblogs.com/dshore123/ 原文出自：http://www.cnblogs.com/dshore123/p/8033624.html 版权声明：欢迎转载，转载务必说明出处。（如果本文对您有帮助，可以点击一下右下角的 推荐，或评论，谢谢！） |