I want to create a procedure that uses cursor as an input in Oracle. I tried something like this:
cursor cur is select * from table t1;
and then also:
create or replace procedure (I want to input cursor cur here)
is
begin
end;/
How can I do that?
Here's an example which shows one way to do it.
Procedure that accepts cursor as a parameter, loops through it and displays it contents:
SQL> create or replace procedure p_test (par_cursor in sys_refcursor)
2 is
3 l_ename emp.ename%type;
4 l_sal emp.sal%type;
5 begin
6 loop
7 fetch par_cursor into l_ename, l_sal;
8 exit when par_cursor%notfound;
9 dbms_output.put_line(l_ename ||' - '|| l_sal);
10 end loop;
11 end;
12 /
Procedure created.
How to use it?
SQL> set serveroutput on
SQL> declare
2 l_rc sys_refcursor;
3 begin
4 open l_rc for
5 select ename, sal
6 from emp
7 where deptno = 10;
8 p_test (l_rc);
9 end;
10 /
CLARK - 2450
KING - 5000
MILLER - 1300
PL/SQL procedure successfully completed.
SQL>