Passing ref cursors to procedures
sys_refcursor
can be used to pass
cursors from and to a stored precedures
In the following example, the procedure
proc_ref_cursor
accepts a
sys_refcursor
and
loops over that cursor and prints (
dbms_output
) what it fetches:
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;
And this is the definition of the procedure.
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 the stored procedure (
call_proc_ref_cursor
) that opens a cursor for a
select statement (
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;
/
Execute call_proc_ref_cursor
:
begin
call_proc_ref_cursor;
end;
/