sqloracle-databasestored-proceduresdatabase-cursor

How to enter cursor as an input in procedure oracle


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?


Solution

  • 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>