oracle游标作为out参数,oracle 存储过程 带游标作为OUT参数输出

包中带过程 要自己定义一个type [cur_name] is ref cursor游标，返回的时候就直接 procedure AAA(变量名 out [cur_name])如此申明OUT变量

存储过程 用系统默认的 sys_refcursor 游标类型 定义变量就OK了

Sql代码

--PL/SQL Code (包中带过程) 过程带游标的OUT参数，返回游标(ref cursor)

create or replace package my_pack as

type my_ref_cursor is ref cursor;

procedure getMyCursor(val out my_ref_cursor);

end my_pack;

create or replace package body my_pack as

procedure getMyCursor(val out my_ref_cursor)

is

begin

open val for select * from student;

end;

end my_pack;java代码:

Class.forName("oracle.jdbc.driver.OracleDriver");

Connection conn = DriverManager.getConnection ("jdbc:oracle:thin:@XX.XX.XX.XXX:XXXX:oracle9i","XXX_temp","XXX_temp");

DriverManager.registerDriver (new oracle.jdbc.OracleDriver());

CallableStatement cs = conn.prepareCall("{ call my_pack.getMyCursor(?) }");

cs.registerOutParameter(1,OracleTypes.CURSOR);

cs.execute();

ResultSet rs = ((OracleCallableStatement)cs).getCursor(1);

while(rs.next())

{

System.out.println(rs.getString(1)+" "+rs.getString(2)+" "+rs.getString(3)+" "+ rs.getDate(4)+" "+rs.getString(5));

}

sql代码:

--PL/SQL Code(存储过程) 带游标的OUT参数，返回游标(ref cursor)

create or replace procedure retCursor(ret_cursor out sys_refcursor)is

ret_cursor_value sys_refcursor;

begin

open ret_cursor_value for select * from student;

ret_cursor:=ret_cursor_value;

end retCursor;

java代码：

Class.forName("oracle.jdbc.driver.OracleDriver");

Connection conn = Connection conn = DriverManager.getConnection ("jdbc:oracle:thin:@XX.XX.XX.XXX:XXXX:oracle9i","XXX_temp","XXX_temp");

DriverManager.registerDriver (new oracle.jdbc.OracleDriver());

CallableStatement cs = conn.prepareCall("{ call retCursor(?) }");

cs.registerOutParameter(1,OracleTypes.CURSOR);

cs.execute();

ResultSet rs = ((OracleCallableStatement)cs).getCursor(1);

while(rs.next())

{

System.out.println(rs.getString(1)+" "+rs.getString(2)+" "+rs.getString(3)+" "+ rs.getDate(4)+" "+rs.getString(5));

}

下面是个每个学生求平均值的存储过程。遇到的问题是带参数游标中的变量名字不要和表中的一样，否则会出问题

sql代码：

create or replace procedure AAA

as

--查询学生表的ID

cursor s_sno is select s.sno from student s;

--通过学生ID查询平均成绩

cursor sc_avg(s_no varchar2) is select avg(sc.degree) from score sc where sc.sno=s_no;

s_sno_j student.sno%type; --变量ID

sc_avg_i score.degree%type; --变量平局成绩

begin

open s_sno;--打开查询ID的游标

loop

fetch s_sno into s_sno_j;

exit when s_sno%notfound;

open sc_avg(s_sno_j); --打开查询平均成绩的游标，参数为学生ID

loop

fetch sc_avg into sc_avg_i;

exit when sc_avg%notfound;

dbms_output.put_line(sc_avg_i);

end loop;

close sc_avg;

end loop;

close s_sno;

end AAA;