How to return multiple rows from the stored procedure? (Oracle PL/SQL)

Question

I want to create a stored procedure with one argument which will return different sets of records depending on the argument. What is the way to do this? Can I call it from plain SQL?

Answer 1

I think you want to return a REFCURSOR:

create function test_cursor 
            return sys_refcursor
            is
                    c_result sys_refcursor;
            begin
                    open c_result for
                    select * from dual;
                    return c_result;
            end;

Update: If you need to call this from SQL, use a table function like @Tony Andrews suggested.

Answer 2

Probably with the use of table functions. Have a look at this documentation Table Functions and Cursor Expressions

Answer 3

Here is how to build a function that returns a result set that can be queried as if it were a table:

SQL> create type emp_obj is object (empno number, ename varchar2(10));
  2  /

Type created.

SQL> create type emp_tab is table of emp_obj;
  2  /

Type created.

SQL> create or replace function all_emps return emp_tab
  2  is
  3     l_emp_tab emp_tab := emp_tab();
  4     n integer := 0;
  5  begin
  6     for r in (select empno, ename from emp)
  7     loop
  8        l_emp_tab.extend;
  9        n := n + 1;
 10       l_emp_tab(n) := emp_obj(r.empno, r.ename);
 11     end loop;
 12     return l_emp_tab;
 13  end;
 14  /

Function created.

SQL> select * from table (all_emps);

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7902 FORD
      7934 MILLER

Answer 4

If you want to use it in plain SQL, I would let the store procedure fill a table or temp table with the resulting rows (or go for @Tony Andrews approach).
If you want to use @Thilo's solution, you have to loop the cursor using PL/SQL. Here an example: (I used a procedure instead of a function, like @Thilo did)

create or replace procedure myprocedure(retval in out sys_refcursor) is
begin
  open retval for
    select TABLE_NAME from user_tables;
end myprocedure;

 declare 
   myrefcur sys_refcursor;
   tablename user_tables.TABLE_NAME%type;
 begin
   myprocedure(myrefcur);
   loop
     fetch myrefcur into tablename;
     exit when myrefcur%notfound
     dbms_output.put_line(tablename);
   end loop;
   close myrefcur;
 end;

Answer 5

this code is missing a semi-colon, look below:

create or replace procedure myprocedure(retval in out sys_refcursor) is begin open retval for select TABLE_NAME from user_tables; end myprocedure;

declare
myrefcur sys_refcursor; tablename user_tables.TABLE_NAME%type; begin myprocedure(myrefcur); loop fetch myrefcur into tablename; exit when myrefcur%notfound; --<<<< HERE dbms_output.put_line(tablename); end loop; close myrefcur; end;