irport报表，把数字金额转换成大写人民币金额

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

版权声明：欢迎转载，转载务必说明出处。（如果本文对您有帮助，可以点击一下右下角的 推荐，或评论，谢谢！）
posted @ 2017-12-13 16:35  DSHORE  阅读(1381)  评论(0)  编辑  收藏  举报