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

Sql代码
1.--PL/SQL Code (包中带过程) 过程带游标的OUT参数，返回游标(ref cursor)  
2. 
3.create or replace package my_pack as 
4. type my_ref_cursor is ref cursor;  
5. procedure getMyCursor(val out my_ref_cursor);   
6.end my_pack;  
7. 
8.create or replace package body my_pack as 
9. procedure getMyCursor(val out my_ref_cursor)  
10. is 
11. begin 
12.  open val for select * from student;  
13. end;  
14.end my_pack;  
   --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代码
1. 
2.Class.forName("oracle.jdbc.driver.OracleDriver");  
3.Connection conn = DriverManager.getConnection                       ("jdbc:oracle:thin:@XX.XX.XX.XXX:XXXX:oracle9i","XXX_temp","XXX_temp");   
4.DriverManager.registerDriver (new oracle.jdbc.OracleDriver());     
5.CallableStatement cs = conn.prepareCall("{ call my_pack.getMyCursor(?) }");  
6.cs.registerOutParameter(1,OracleTypes.CURSOR);  
7.cs.execute();  
8.ResultSet rs = ((OracleCallableStatement)cs).getCursor(1);  
9.while(rs.next())  
10.{  
11.    System.out.println(rs.getString(1)+"  "+rs.getString(2)+"  "+rs.getString(3)+"  "+ rs.getDate(4)+"  "+rs.getString(5));  
12.} 
  
   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代码
1.--PL/SQL Code(存储过程) 带游标的OUT参数，返回游标(ref cursor)  
2.   
3.create or replace procedure retCursor(ret_cursor out sys_refcursor)is 
4.ret_cursor_value  sys_refcursor;  
5.begin 
6.open ret_cursor_value for select * from student;  
7. ret_cursor:=ret_cursor_value;  
8.end retCursor; 
  
   --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代码
1.Class.forName("oracle.jdbc.driver.OracleDriver");  
2.Connection conn = Connection conn = DriverManager.getConnection                       ("jdbc:oracle:thin:@XX.XX.XX.XXX:XXXX:oracle9i","XXX_temp","XXX_temp");   
3. 
4.DriverManager.registerDriver (new oracle.jdbc.OracleDriver());     
5.CallableStatement cs = conn.prepareCall("{ call retCursor(?) }");  
6.cs.registerOutParameter(1,OracleTypes.CURSOR);  
7.cs.execute();  
8.ResultSet rs = ((OracleCallableStatement)cs).getCursor(1);  
9.while(rs.next())  
10.{  
11.    System.out.println(rs.getString(1)+"  "+rs.getString(2)+"  "+rs.getString(3)+"  "+ rs.getDate(4)+"  "+rs.getString(5));  
12.} 
   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代码
1.create or replace procedure AAA  
2.as   
3.--查询学生表的ID  
4.cursor s_sno is select s.sno from student s;   
5.--通过学生ID查询平均成绩  
6.cursor sc_avg(s_no varchar2) is select avg(sc.degree) from score sc where sc.sno=s_no;   
7.s_sno_j student.sno%type;   --变量ID  
8.sc_avg_i score.degree%type; --变量平局成绩  
9.begin 
10. open s_sno;--打开查询ID的游标  
11. loop  
12.   fetch s_sno into s_sno_j;  
13.   exit when s_sno%notfound;    
14.     open sc_avg(s_sno_j); --打开查询平均成绩的游标，参数为学生ID  
15.     loop  
16.     fetch sc_avg into sc_avg_i;  
17.     exit when sc_avg%notfound;  
18.     dbms_output.put_line(sc_avg_i);  
19.     end loop;  
20.     close sc_avg;  
21. end loop;  
22. close s_sno;  
23.end AAA;