java设置游标输出参数_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> list = new ArrayList>();

//遍历结果集

while (rs.next()) {

Map map = new HashMap();

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");  }