Oracle 存储过程

//存储过程
create or replace procedure pro_order_check
 (ls_expressNo varchar, --快递单号
  ls_order_id varchar)   --订单号
as
 
----订单金额 o_l_payAmt
o_l_payAmt  number(8,2);
----订单金额 e_l_payAmt  excel 导入的订单金额
e_l_payAmt  number(8,2) ;
begin  
      select   sum(ORDER_ITEM.GOODS_PRICE*ORDER_ITEM.Goods_Amount) -max(ORDER_INFO.Discount_fee+ORDER_INFO.Order_Points+ORDER_INFO.order_vouchers+ORDER_INFO.other_discounts-ORDER_INFO.Delivery_Fee)   into o_l_payAmt
      from ORDER_INFO, ORDER_SHIPPING,ORDER_ITEM
      where ORDER_INFO.Order_Id in ( select  max(ORDER_DELIVERY.Order_Id) from ORDER_DELIVERY where ORDER_DELIVERY.Order_Id = ORDER_INFO.Order_Id  )
      and   ORDER_SHIPPING.Order_Delivery_Id in  ( select  max(ORDER_DELIVERY.ORDER_DELIVERY_ID) from ORDER_DELIVERY where ORDER_DELIVERY.Order_Id = ORDER_INFO.Order_Id  )
      and   ORDER_SHIPPING.Order_Shipping_No= ls_expressNo
      and   ORDER_INFO.Order_Id =ORDER_ITEM.Order_Id  ;

      select sum(to_Number(EXT_ORDER_CHECKEXCELLOAD.Checkamt)) into e_l_payAmt
      from EXT_ORDER_CHECKEXCELLOAD
      where EXT_ORDER_CHECKEXCELLOAD.Expressno = ls_expressNo;


      if o_l_payAmt = e_l_payAmt then
 
        update ORDER_INFO  set ORDER_INFO.Is_Check_Bills ='Y'
        where  to_char(ORDER_INFO.Order_Id ) in (  select   ORDER_DELIVERY.Order_Id
                                                 from   EXT_ORDER_CHECKEXCELLOAD , ORDER_SHIPPING,ORDER_DELIVERY
                                                 where EXT_ORDER_CHECKEXCELLOAD.Expressno  = ls_expressNo    
                                                 and   ORDER_SHIPPING.Order_Shipping_No= EXT_ORDER_CHECKEXCELLOAD.Expressno
                                                 and   ORDER_SHIPPING.Order_Delivery_Id = ORDER_DELIVERY.ORDER_DELIVERY_ID
                                                 and   ORDER_INFO.Order_Id = ORDER_DELIVERY.Order_Id
                                                  
        );
       --raise_application_error(-20001, ls_order_id||'-----'||sql%Rowcount);

       update EXT_ORDER_CHECKEXCELLOAD  set EXT_ORDER_CHECKEXCELLOAD.CHECKTYPE = 'Y',EXT_ORDER_CHECKEXCELLOAD.Checkdate = sysdate,EXT_ORDER_CHECKEXCELLOAD.ISCF='Y'
       where EXT_ORDER_CHECKEXCELLOAD.Expressno = ls_expressNo;


      end if;

      select     sum(ORDER_ITEM.GOODS_PRICE*ORDER_ITEM.Goods_Amount) -max(ORDER_INFO.Discount_fee+ORDER_INFO.Order_Points+ORDER_INFO.order_vouchers+ORDER_INFO.other_discounts)  into o_l_payAmt
      from ORDER_INFO,ORDER_SHIPPING,ORDER_ITEM
      where ORDER_INFO.Order_Id in ( select  max(ORDER_DELIVERY.Order_Id) from ORDER_DELIVERY where ORDER_DELIVERY.Order_Id = ORDER_INFO.Order_Id  )
      and   ORDER_SHIPPING.Order_Delivery_Id in  ( select  max(ORDER_DELIVERY.ORDER_DELIVERY_ID) from ORDER_DELIVERY where ORDER_DELIVERY.Order_Id = ORDER_INFO.Order_Id  )
      and   ORDER_INFO.Order_Id = ls_order_id
      and   ORDER_INFO.Order_Id =ORDER_ITEM.Order_Id ;

      select sum(to_Number(EXT_ORDER_CHECKEXCELLOAD.Checkamt)) into e_l_payAmt
      from EXT_ORDER_CHECKEXCELLOAD
      where EXT_ORDER_CHECKEXCELLOAD.Jy_Orderno = ls_order_id;


      if o_l_payAmt = e_l_payAmt then
 
        update ORDER_INFO  set ORDER_INFO.Is_Check_Bills ='Y'
        where  to_char(ORDER_INFO.Order_Id ) in ( select  EXT_ORDER_CHECKEXCELLOAD.JY_ORDERNO
                                                 from   EXT_ORDER_CHECKEXCELLOAD
                                                 where EXT_ORDER_CHECKEXCELLOAD.Jy_Orderno = ls_order_id
        );
         --raise_application_error(-20001, ls_order_id||'-----'||sql%Rowcount);

       update EXT_ORDER_CHECKEXCELLOAD  set EXT_ORDER_CHECKEXCELLOAD.CHECKTYPE = 'Y',EXT_ORDER_CHECKEXCELLOAD.Checkdate = sysdate,EXT_ORDER_CHECKEXCELLOAD.ISCF='Y'
       where EXT_ORDER_CHECKEXCELLOAD.Jy_Orderno = ls_order_id;


      end if;

end;

//java  代码调用]

 

 

 

    public String OrderCheck(String l_expressNo, String l_Order, String isCf) {
        Order_Cw_Check oc = new Order_Cw_Check();
        String l_expressNoArray[] = l_expressNo.split(","); // 里 快递单号
        String l_OrderArray[] = l_Order.split(","); // 里 快递单号

        for (int i = 1; i < l_expressNoArray.length; i++) {
            oc.setL_expressNo(l_expressNoArray[i]);
            oc.setL_jy_orderNo(l_OrderArray[i]);
            if (l_expressNoArray[i] == null || l_expressNoArray[i] == "") {

            } else {
                if ("No".equals(isCf)) {// 非处方药对账的过程
                    commonDao.update("OrderPhoneCheck.CheckOrder", oc);
                }
                if ("qt".equals(isCf)) {// 其他网站订单和ERP订单对账的
                    commonDao.update("OrderPhoneCheck.CheckOrderQt", oc);
                } else {// 处方药对账的过程
                    commonDao.update("OrderPhoneCheck.CheckOrderCfy", oc);
                }

            }

        }

        return "1";
    }

 http://blog.sina.com.cn/s/blog_82faefb00100tn6o.html