Oracle store procedure and Function返回结果集。

Oracle下可以用refer cursor来返回一个结果集给调用者：

 

1. Oracle store procedure返回结果集：

 

create or replace procedure sp_test(v_result out sys_refcursor) is
begin
 open v_result for
    select 'Bryan', 100, 20, 80, 200, 180
    from dual;
end;

2. Oracle Function返回一个结果集：

 

create or replace function fn_test return sys_refcursor
as
TYPE GenericCurTyp IS REF CURSOR;
v_result GenericCurTyp;
begin
 open v_result for
    select 'Bryan', 100, 20, 80, 200, 180
    from dual;
 return v_result;
end;

3.  Java中如何得到Oracle返回的结果集：

 

   String call=  "{ call ? := fn_test()}";  

   ResultSet rs = null;   
   CallableStatement stmt = null;
   
   stmt = conn.prepareCall();
   stmt.registerOutParameter(1, OracleTypes.CURSOR );
   stmt.execute();
   rs = (ResultSet)stmt.getObject(1);