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

在网上找了很久的 存储过程返回OUT参数的游标 例子，都不理想，最后还是自己写个。以备后用。

包中带过程 要自己定义一个type [cur_name] is ref cursor游标，返回的时候就直接 procedure AAA(变量名 out [cur_name])如此申明OUT变量
存储过程 用系统默认的 sys_refcursor 游标类型 定义变量就OK了

			--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;
	

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

		
			--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;

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

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

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;

评论

1 楼 20055294 2010-06-11  

最近我正在学习存储过程，谢了