Wie werden mehrere Zeilen von der gespeicherten Prozedur zurückgegeben? (Oracle PL / SQL)

So erstellen Sie eine Funktion, die eine Ergebnismenge zurückgibt, die wie eine Tabelle abgefragt werden kann:

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

Ich denke, Sie möchten einen REFCURSOR zurückgeben:

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

pdate: Wenn Sie dies von SQL aus aufrufen müssen, verwenden Sie eine Tabellenfunktion wie die von @Tony Andrews vorgeschlagene.

Sie können Oracle-Pipeline-Funktionen verwenden

Wenn Sie möchten, dass eine PLSQL oder Java oder c) -Routine - anstelle einer Tabelle - die "Datenquelle" ist, verwenden Sie grundsätzlich eine Pipeline-Funktion.

Einfaches Beispiel - Generieren von zufälligen Daten
Wie können Sie N eindeutige Zufallszahlen abhängig vom Eingabeargument erstellen?

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;

Angenommen, wir brauchen drei Zeilen für etwas. Wir können das jetzt auf zwei Arten tun:

select * from TABLE(gen_numbers(3));

COLUMN_VALUE

       1
       2
       3

oder

select * from TABLE(gen_numbers)
 where rownum <= 3;

COLUMN_VALUE

       1
       2
       3

--- (pipelied Functions1pipelied Functions2

Wenn Sie es in einfachem SQL verwenden möchten, würde ich die Speicherprozedur eine Tabelle oder temporäre Tabelle mit den resultierenden Zeilen füllen lassen (oder den @ Tony Andrews-Ansatz wählen).
Wenn Sie die @ Thilo-Lösung verwenden möchten, müssen Sie den Cursor mit PL/SQL schleifen. Hier ein Beispiel: (Ich habe eine Prozedur anstelle einer Funktion verwendet, wie @Thilo)

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;

create procedure <procedure_name>(p_cur out sys_refcursor) as begin open p_cur for select * from <table_name> end;