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

1、编写oracle函数

 CREATE OR REPLACE Function MoneyToChinese(Money In Number) Return Varchar2 Is

   strYuan       Varchar2(150);

   strYuanFen    Varchar2(152);

   numLenYuan    Number;

   numLenYuanFen Number;

   strRstYuan    Varchar2(600);

   strRstFen     Varchar2(200);

   strRst        Varchar2(800);

   Type typeTabMapping Is Table Of Varchar2(2) Index By Binary_Integer;

   tabNumMapping  typeTabMapping;

   tabUnitMapping typeTabMapping;

   numUnitIndex   Number;

   i              Number;

   j              Number;

   charCurrentNum Char(1);

 Begin

   If Money Is Null Then

     Return Null;

   End If;

   strYuan := TO_CHAR(FLOOR(Money));

   If strYuan = '' Then

     numLenYuan := 0;

     strYuanFen := lpad(TO_CHAR(FLOOR(Money * 100)), 2, '');

   Else

     numLenYuan := length(strYuan);

     strYuanFen := TO_CHAR(FLOOR(Money * 100));

   End If;

   If strYuanFen = '' Then

     numLenYuanFen := 0;

   Else

     numLenYuanFen := length(strYuanFen);

   End If;

   If numLenYuan = 0 Or numLenYuanFen = 0 Then

     strRst := '零元整';

     Return strRst;

   End If;

   tabNumMapping(0) := '零';

   tabNumMapping(1) := '壹';

   tabNumMapping(2) := '贰';

   tabNumMapping(3) := '叁';

   tabNumMapping(4) := '肆';

   tabNumMapping(5) := '伍';

  tabNumMapping(6) := '陆';

   tabNumMapping(7) := '柒';

   tabNumMapping(8) := '捌';

   tabNumMapping(9) := '玖';

   tabUnitMapping(-2) := '分';

   tabUnitMapping(-1) := '角';

   tabUnitMapping(1) := '';

   tabUnitMapping(2) := '拾';

   tabUnitMapping(3) := '佰';

   tabUnitMapping(4) := '仟';

   tabUnitMapping(5) := '万';

   tabUnitMapping(6) := '拾';

   tabUnitMapping(7) := '佰';

   tabUnitMapping(8) := '仟';

   tabUnitMapping(9) := '亿';

   For i In 1 .. numLenYuan Loop

     j            := numLenYuan - i + 1;

     numUnitIndex := Mod(i, 8);

     If numUnitIndex = 0 Then

       numUnitIndex := 8;

     End If;

     If numUnitIndex = 1 And i > 1 Then

       strRstYuan := tabUnitMapping(9) || strRstYuan;

     End If;

     charCurrentNum := substr(strYuan, j, 1);

     If charCurrentNum <> 0 Then

       strRstYuan := tabNumMapping(charCurrentNum) ||

                     tabUnitMapping(numUnitIndex) || strRstYuan;

     Else

       If (i = 1 Or i = 5) Then

         If substr(strYuan, j - 3, 4) <> '' Then

           strRstYuan := tabUnitMapping(numUnitIndex) || strRstYuan;

         End If;

       Else

         If substr(strYuan, j + 1, 1) <> '' Then

           strRstYuan := tabNumMapping(charCurrentNum) || strRstYuan;

         End If;

       End If;

     End If;

   End Loop;

   For i In -2 .. -1 Loop

     j              := numLenYuan - i;

     charCurrentNum := substr(strYuanFen, j, 1);

     If charCurrentNum <> '' Then

       strRstFen := tabNumMapping(charCurrentNum) || tabUnitMapping(i) ||

                    strRstFen;

     End If;

   End Loop;

   If strRstYuan Is Not Null Then

     strRstYuan := strRstYuan || '元';

   End If;

   If strRstFen Is Null Then

     strRstYuan := strRstYuan || '整';

   Elsif length(strRstFen) = 2 And substr(strRstFen, 2) = '角' Then

     strRstFen := strRstFen || '整';

   End If;

   strRst := strRstYuan || strRstFen;

   --strRst := Replace(strRst, '亿零', '亿');

   --strRst := Replace(strRst, '万零', '万');

   Return strRst;

 End MoneyToChinese;

注：如需测试该函数，请复制到Oracle数据库中，右击函数名“MoneyToChinese”，选择“test” 进行测试，输入你想要的金额。

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

2、在irport的Database里面写查询语句调用MoneyToChinese函数

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

小结：

(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

SOD

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