SYS_REFCURSOR

1、使用SYS_REFCURSOR
SQL> Declare
  2    Cur Sys_Refcursor;
  3    R   scott.dept%Rowtype;
  4  Begin
  5    Open Cur For Select * From scott.dept;
  6    Loop
  7      Fetch Cur Into R;
  8      Exit When Cur%Notfound;
  9      dbms_output.put_line('DEPTNO:'||R.deptno||' DNAME:'||R.DNAME||' LOC:'||R.LOC);
 10    End Loop;
 11  End;
 12  /
DEPTNO:10 DNAME:ACCOUNTING LOC:NEW YORK
DEPTNO:20 DNAME:RESEARCH LOC:DALLAS
DEPTNO:30 DNAME:SALES LOC:CHICAGO
DEPTNO:40 DNAME:OPERATIONS LOC:BOSTON
PL/SQL procedure successfully completed


2、使用自定义游标
SQL> Declare
  2    Type T_Cur Is Ref Cursor Return scott.dept%Rowtype;
  3    Cur T_Cur;
  4    R   Cur%Rowtype;
  5  Begin
  6    Open Cur For Select * From scott.dept;
  7    Loop
  8      Fetch Cur Into R;
  9      Exit When Cur%Notfound;
 10      dbms_output.put_line('DEPTNO:'||R.deptno||' DNAME:'||R.DNAME||' LOC:'||R.LOC);
 11    End Loop;
 12  End;
 13  /
DEPTNO:10 DNAME:ACCOUNTING LOC:NEW YORK
DEPTNO:20 DNAME:RESEARCH LOC:DALLAS
DEPTNO:30 DNAME:SALES LOC:CHICAGO
DEPTNO:40 DNAME:OPERATIONS LOC:BOSTON
PL/SQL procedure successfully completed


3、使用SYS_REFCURSOR作为输出游标
SQL> Create Or Replace Procedure Proc1(Cur_Out Out Sys_Refcursor)
  2  Is
  3  Begin
  4    Open Cur_Out For Select * From Dept;
  5  End;
  6  /
Procedure created
SQL> Declare
  2    Cur Sys_Refcursor;
  3    R   dept%Rowtype;
  4  Begin
  5    Proc1(Cur);
  6    Loop
  7      Fetch Cur Into R;
  8      Exit When Cur%Notfound;
  9      dbms_output.put_line('DEPTNO:'||R.deptno||' DNAME:'||R.DNAME||' LOC:'||R.LOC);
 10    End Loop;
 11  End;
 12  /
DEPTNO:10 DNAME:ACCOUNTING LOC:NEW YORK
DEPTNO:20 DNAME:RESEARCH LOC:DALLAS
DEPTNO:30 DNAME:SALES LOC:CHICAGO
DEPTNO:40 DNAME:OPERATIONS LOC:BOSTON
PL/SQL procedure successfully completed


4、使用自定义游标作为输出游标
SQL> Create Or Replace Package pck2
  2  Is
  3    Type t_csr Is Ref Cursor ;
  4  End;
  5  /
Package created
SQL> Create Or Replace Procedure Proc2(Cur_Out Out pck2.t_csr)
  2  Is
  3  
  4  Begin
  5    Open Cur_Out For Select * From Dept;
  6  End;
  7  /
Procedure created
SQL> Declare
  2    Cur pck2.t_csr;
  3    R   dept%Rowtype;
  4  Begin
  5    Proc2(Cur);
  6    Loop
  7      Fetch Cur Into R;
  8      Exit When Cur%Notfound;
  9      dbms_output.put_line('DEPTNO:'||R.deptno||' DNAME:'||R.DNAME||' LOC:'||R.LOC);
 10    End Loop;
 11  End;


 12  /
DEPTNO:10 DNAME:ACCOUNTING LOC:NEW YORK
DEPTNO:20 DNAME:RESEARCH LOC:DALLAS
DEPTNO:30 DNAME:SALES LOC:CHICAGO
DEPTNO:40 DNAME:OPERATIONS LOC:BOSTON
PL/SQL procedure successfully completed