Oracle REF CURSOR

With the REF_CURSOR you can return a recordset/cursor from a stored procedure.

There are 2 basic types: Strong ref cursor and weak ref cursor
For the strong ref cursor the returning columns with datatype and length need to be known at compile time.
For the weak ref cursor the structure does not need to be known at compile time.

The STRONG_REF_CURSOR and until Oracle 9i also the weak-type need to be declared in a package structure lik this:

create or replace
package REFCURSOR_PKG as
      TYPE WEAK8i_REF_CURSOR   IS REF CURSOR;
      TYPE STRONG_REF_CURSOR IS REF CURSOR RETURN EMP%ROWTYPE;
end REFCURSOR_PKG;

The pl/sql procedure that returns a ref-cursor looks like this:

/** until Oracle 9 */
create or replace
procedure test( p_deptno IN number,
                      p_cursor  OUT REFCURSOR_PKG.WEAK8i_REF_CURSOR)
is
begin
     open p_cursor FOR
     select *
     from   emp
     where  deptno = p_deptno;
end test;

Since Oracle 9i you can use SYS_REFCURSOR as the type for the returning REF_CURSOR.

/** From Oracle 9 */
create or replace
procedure test( p_deptno IN number,
                      p_cursor OUT SYS_REFCURSOR)
is
begin
      open p_cursor FOR
      select *
      from   emp
      where  deptno = p_deptno;
end test;

/* Strong type */
create or replace
procedure test( p_deptno  IN number,
                      p_cursor  OUT REFCURSOR_PKG.STRONG REF_CURSOR)
is
begin
      open p_cursor FOR
      select *
      from   emp
      where  deptno = p_deptno;
end test;

Selecting the ref_cursor from JDBC
To get the cursor from Java you can use the following JDBC-code:

public void method() throws SQLException{
  Connection conn = getConnection();
  CallableStatement cstmt = null;
  ResultSet rs = null;
  int deptno = 10;
  Object temp;
  try{
      cstmt = conn.prepareCall("begin  test(?,?); end;");
      cstmt.setInt(1, deptno);
      cstmt.registerOutParameter(2, OracleTypes.CURSOR);
      cstmt.execute();
      rs = (ResultSet) cstmt.getObject(2);
      ResultSetMetaData rsm = rs.getMetaData();
      int columnCount = rsm.getColumnCount();
      while (rs.next()){
         for (int j=0;j< columnCount;j++){
            temp = rs.getObject(j+1);
         }
      }
  } finally {
      if (!rs==null){
        rs.close();
      }
      if (!stmt==null){
        stmt.close();
      }
      if (!conn==null){
        conn.close();
      } 
  }
}
Calling ref-cursor from pl/sql
create or replace procedure test_call is
  c_cursor REFCURSOR_PKG.STRONG_REF_CURSOR;
  r_emp    c_emp%rowtype;
begin
  test(10,c_cursor);
  loop
    fetch c_cursor into r_emp;
    exit when c_cursor%notfound;
    dbms_output.put_line(r_emp.name);
  end loop;
  close c_cursor;
end test_call;

출처 : http://www.oradev.com/ref_cursor.jsp