How to return multiple rows from the stored procedure? (Oracle PL/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

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