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
