salida - ¿Cómo devolver varias filas desde el procedimiento almacenado?(Oracle PL/SQL)

procedimientos y funciones oracle pl/sql

Question

Quiero crear un procedimiento almacenado con un argumento que devolverá diferentes conjuntos de registros dependiendo del argumento. ¿Cuál es la manera de hacer esto? ¿Puedo llamarlo desde SQL simple?

Answer 1

Aquí se explica cómo crear una función que devuelve un conjunto de resultados que se puede consultar como si fuera una tabla:

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 2

Creo que quieres devolver un 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;

Actualización : si necesita llamar esto desde SQL, use una función de tabla como sugirió @Tony Andrews.

Answer 3

Puede usar funciones canalizadas de Oracle

Básicamente, cuando quisieras que una rutina PLSQL (o java o c) fuera la "fuente" de datos, en lugar de una tabla, usarías una función canalizada.

Ejemplo simple - Generación de algunos datos aleatorios
¿Cómo podría crear N números aleatorios únicos dependiendo del argumento de entrada?

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;

Supongamos que necesitamos tres filas para algo. Ahora podemos hacer eso de una de estas dos maneras:

select * from TABLE(gen_numbers(3));

COLUMN_VALUE

       1
       2
       3

o

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

COLUMN_VALUE

       1
       2
       3

Funciones pipepelied1 Funciones pipepelied2

Answer 4

Si desea usarlo en SQL simple, permitiría que el procedimiento de almacenamiento llene una tabla o tabla temporal con las filas resultantes (o vaya para el enfoque @Tony Andrews).
Si desea utilizar la solución de @ Thilo, debe recorrer el cursor utilizando PL / SQL. Aquí un ejemplo: (utilicé un procedimiento en lugar de una función, como lo hizo @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;

Answer 5

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