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;