sys_refcursor can be used to pass cursors from and to a stored precedure.
The following procedure
proc_ref_cursor accepts a
sys_refcursor and loops on that cursor and prints out (
dbms_output) what it fetches:
create or replace procedure proc_ref_cursor (rc in sys_refcursor) as v_a number; v_b varchar2(10); begin loop fetch rc into v_a, v_b; exit when rc%notfound; dbms_output.put_line(v_a || ' ' || v_b); end loop; end; /
Here's a table that we will use to select from:
create table table_ref_cursor ( a number, b varchar2(10) ); insert into table_ref_cursor values(1, 'one'); insert into table_ref_cursor values(2, 'two'); insert into table_ref_cursor values(3, 'three'); insert into table_ref_cursor values(4, 'four'); commit;
Here's another stored procedure (
call_proc_ref_cursor) that opens a cursor (
select * from table_ref_cursor) and passes that cursor to
proc_ref_cursor.
create or replace procedure call_proc_ref_cursor as v_rc sys_refcursor; begin open v_rc for select * from table_ref_cursor; proc_ref_cursor(v_rc); close v_rc; end; / 转自:http://www.adp-gmbh.ch/ora/sql/sys_refcursor.html