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

User · Answer

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

User · Answer

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

User · Answer

create procedure  lt procedure name gt  p cur out sys refcursor  as begin open p cur for select   from  lt table name gt  end

User · Answer

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

User · Answer

Here is how to build a function that returns a result set that can be queried as if it were a table   SQL gt  create type emp obj is object  empno number  ename varchar2 10      2     Type created   SQL gt  create type emp tab is table of emp obj    2     Type created   SQL gt  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 gt  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

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

Examples related to sql

Examples related to oracle

Examples related to plsql