How to return multiple rows from the stored procedure? (Oracle PL/SQL)
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?
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
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.
You may use Oracle pipelined functions
Basically, when you would like a PLSQL (or java or c) routine to be the «source» of data -- instead of a table -- you would use a pipelined function.
Simple Example - Generating Some Random Data
How could you create N unique random numbers depending on the input argument?
create type array
as table of number;
create function gen_numbers(n in number default null)
return array
PIPELINED
as
begin
for i in 1 .. nvl(n,999999999)
loop
pipe row(i);
end loop;
return;
end;
Suppose we needed three rows for something. We can now do that in one of two ways:
select * from TABLE(gen_numbers(3));
COLUMN_VALUE
1
2
3
or
select * from TABLE(gen_numbers)
where rownum <= 3;
COLUMN_VALUE
1
2
3
pipelied Functions1 pipelied Functions2