JAVA调用存储过程输出参数为游标

CREATE OR REPLACE PACKAGE PR_EXPORT AS

--定义数据类型
type my_ref_cursor is ref cursor;  
  PROCEDURE P_PR_(ret_cursor out sys_refcursor);
END PR_EXPORT;

CREATE OR REPLACE PACKAGE BODY PR_EXPORT AS
  PROCEDURE P_PR_(ret_cursor out sys_refcursor) IS
  ret_cursor_value  sys_refcursor;
--执行代码块开始
  BEGIN  

--此处给游标赋值，最后给java程序调用的时候会转换成相应的ResultSet结果集
   open ret_cursor_value  for  SELECT tab1.name,tab1.handleDate,tab1.totalManey,tab1.totalLine,tab2.notHandleTotalDate,tab2.prNotHandletotalManey,tab2.prNotHandletotalLine FROM (select *
  from (select eu.cn name,
               decode(s.handleDate, null, '0', s.handleDate) handleDate,
               decode(s.totalManey, null, 0, s.totalManey) totalManey,
               decode(s.totalLine, null, 0, s.totalLine) totalLine
          from (select cn name,
                       w.handleDate,
                       round(sum(w.totalManey) / 10000, 2) totalManey,
                       count(w.handleDate) totalLine
                  from ((select h.applypurreqid,
                                to_char(h.createdate, 'YYYY-mm-dd') handleDate,
                                a.quantity * a.unitprice totalManey,
                                u.cn
                           from msm_pr_assign_history    h,
                                msm_applypurchaserequest a,
                                ecp_user_mapping         u
                          where h.purchaser_id = u.userid
                            and h.applypurreqid = a.applypurreqid
                            and h.purchaser_id not in
                                (375777, 375752, 252430, 375782, 233768, 252460,
                                 375757, 375772, 252405, 392682)
                            and u.erp_type = 1) union all
                        (select distinct wa.applypurreqid,
                                         to_char(mc.createdate, 'YYYY-mm-dd') handleDate,
                                         a.quantity * a.unitprice totalManey,
                                         u.cn
                           from msm_workdocheader          wh,
                                msm_commission             mc,
                                ecp_user_mapping           u,
                                msm_workdocapplypurreqline wa,
                                msm_applypurchaserequest   a
                          where wh.workdocheaderid = mc.workdocheaderid
                            and wh.workdocheaderid = wa.workdocheaderid
                            and wa.applypurreqid = a.applypurreqid
                            and wh.purchaserid = u.userid
                            and mc.commission_userid in
                                (375777, 375752, 252430, 375782, 233768, 252460,
                                 375757, 375772, 252405, 392682)
                            and u.erp_type = 1)) w
                 where w.handleDate like '2012-12-09'
                 group by w.handleDate, w.cn) s,
               users_usergroups ug,
               usergroup g,
               user_ u,
               ecp_user_mapping eu
         where g.usergroupid = ug.usergroupid
           and ug.userid = u.userid
           and u.userid = eu.userid
           and eu.erp_type = 1
           and g.name like '%采购员%'
           and eu.cn = s.name(+))
 order by handleDate desc, totalManey desc) tab1

 LEFT JOIN
 (select s.name,
       decode(s.notHandleTotalDate, null, '0', s.notHandleTotalDate) notHandleTotalDate,
       decode(s.prNotHandletotalManey, null, 0, s.prNotHandletotalManey) prNotHandletotalManey,
       decode(s.prNotHandletotalLine, null, 0, s.prNotHandletotalLine) prNotHandletotalLine
  from (select cn name,
               decode(a.notHandleTotalDate, 1, '30天以下', 2, '30-60天', '60天以上') notHandleTotalDate,
               round(sum(a.totalManey) / 10000, 2) prNotHandletotalManey,
               count(*) prNotHandletotalLine
          from (select r.misprnum,
                       r.linenum,
                       r.materialnum,
                       r.materialdesc,
                       decode(round((sysdate - r.assigndate) / 30, 0),
                              0,
                              1,
                              1,
                              1,
                              2,
                              2,
                              3) notHandleTotalDate,
                       (r.unitprice * r.quantity) totalManey,
                       u.cn cn
                  from msm_applypurchaserequest r, ecp_user_mapping u
                 where r.purchaserid = u.userid
                   and u.erp_type = 1
                   and r.purchaserid not in
                       (375777, 375752, 252430, 375782, 233768, 252460, 375757,
                        375772, 252405, 392682)
                   and r.misprnum like '2%'
                   and (r.materialnum like '2%' or r.materialnum like '71%' or
                       r.materialnum like '72%' or r.materialnum like '73%' or
                       r.materialnum like '74%' or r.materialnum like '75%' or
                       r.materialnum like '76%' or r.materialnum like '77%' or
                       r.materialnum like '78%' or
                       r.materialnum like '3201%')
                   and r.status1 in (2)) a
         group by a.notHandleTotalDate, a.cn) s,
       users_usergroups ug,
       usergroup g,
       user_ u,
       ecp_user_mapping eu
 where g.usergroupid = ug.usergroupid
   and ug.userid = u.userid
   and u.userid = eu.userid
   and eu.erp_type = 1
   and g.name like '%采购员%'
   and eu.cn = s.name(+)
 order by notHandleTotalDate desc, prNotHandletotalManey desc
) tab2 ON tab1.name=tab2.name ;

ret_cursor:=ret_cursor_value;  

--赋值结束

END P_PR_;
END PR_EXPORT;

//此处做的是用jxtl.jar做成的一个excel报表，不过只能导出office03以下的格式，如果想导出03以上的建议用poi.jar

public static void export(String inPath, String outPath,String uri,String userName,String password)
   throws Exception {

  Class.forName("oracle.jdbc.driver.OracleDriver");
  Connection conn = DriverManager.getConnection(uri,userName, password);
 // System.out.println(sql);

//导出后写出到硬盘的文件，如"D:\\pr2.xls";
 OutputStream os = new FileOutputStream(outPath);

//读取excel模板文件
 Workbook wrb = Workbook.getWorkbook(new File(inPath));
 WritableWorkbook wwb = Workbook.createWorkbook(os, wrb);
 //得到模板文件中的sheet页，这里得到的是第一个sheet页
 WritableSheet ws = wwb.getSheet(0);
 //此处调用存储过程
 CallableStatement cs = conn.prepareCall("{call PR_EXPORT.P_PR_(?)}");
 //设置存储过程的输出参数为游标 类型
 cs.registerOutParameter(1, OracleTypes.CURSOR);
 cs.execute();
 //将游标转换成记录结果集的形式
 ResultSet rs = (ResultSet) cs.getObject(1);
 List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
 //遍历结果集
 while (rs.next()) {
  Map<String, Object> map = new HashMap<String, Object>();
  map.put("name", rs.getString(1));
  map.put("handleDate", rs.getString(2));
  map.put("totalManey", rs.getString(3));
  map.put("totalLine", rs.getString(4));
  map.put("notHandleTotalDate", rs.getString(5));
  map.put("prNotHandletotalManey", rs.getString(6));
  map.put("prNotHandletotalLine", rs.getString(7));
  list.add(map);
 
 }
 //System.out.println(list.size());
 for (int i = 0; i < list.size(); i++) {
 //从sheet页面的第四行开始填写
  addCellToSheet(ws, 0, i + 3, i + 1 + "", null, null);
  addCellToSheet(ws, 1, i + 3, list.get(i).get("name") + "", null,
    null);
  if (list.get(i).get("notHandleTotalDate") == null
    || "30天以下".equals(list.get(i).get("notHandleTotalDate")
      + "")) {
   if ((list.get(i).get("prNotHandletotalLine") + "")
     .equals("null")) {
    addCellToSheet(ws, 2, i + 3, "", null, null);
   } else {
    addCellToSheet(ws, 2, i + 3,
      list.get(i).get("prNotHandletotalLine") + "", null,
      null);
   }
   if ((list.get(i).get("prNotHandletotalManey") + "")
     .equals("null")) {
    addCellToSheet(ws, 5, i + 3, "", null, null);
   } else {
    addCellToSheet(ws, 5, i + 3,
      list.get(i).get("prNotHandletotalManey") + "",
      null, null);
   }

  } else if ((list.get(i).get("notHandleTotalDate") + "")
    .equals("30-60天")) {
   addCellToSheet(ws, 6, i + 3,
     list.get(i).get("prNotHandletotalManey") + "", null,
     null);
   addCellToSheet(ws, 3, i + 3,
     list.get(i).get("prNotHandletotalLine") + "", null,
     null);
  } else {
   addCellToSheet(ws, 7, i + 3,
     list.get(i).get("prNotHandletotalManey") + "", null,
     null);
   addCellToSheet(ws, 4, i + 3,
     list.get(i).get("prNotHandletotalLine") + "", null,
     null);
  }
  addCellToSheet(ws, 8, i + 3, list.get(i).get("totalLine") + "",
    null, null);
  addCellToSheet(ws, 9, i + 3, list.get(i).get("totalManey") + "",
    null, null);
 }
 wwb.write();
 wwb.close();
 }
 
 //往sheet页面中的每个格子里填数据
 public static  Label addCellToSheet(WritableSheet wsheet, int col, int row,
   String content, WritableCellFormat wcFM, WritableCellFeatures wcFT)
   throws Exception {
  Label newCell = null;
  if (wcFM != null) {
   newCell = new Label(col, row, content, wcFM);
  } else {
   newCell = new Label(col, row, content);
  }
  if (wcFT != null) {
   newCell.setCellFeatures(wcFT);
  }
  wsheet.addCell(newCell);
  return newCell;
 }

//主函数调用
 public static void main(String[] args) throws Exception {
  export("D:\\pr.xls", "D:\\pr2.xls", "jdbc:oracle:thin:@10.4.42.210:12701:spmsit", "spmrep", "spmrep");
 }