Я хочу создать хранимую процедуру с одним аргументом, который будет возвращать разные наборы записей в зависимости от аргумента. Каков способ сделать это? Могу ли я вызвать его из простого SQL?
Как вернуть несколько строк из хранимой процедуры? (Oracle PL/SQL)
Ответ 1
Вот как построить функцию, которая возвращает набор результатов, который можно запросить, как если бы это была таблица:
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
Ответ 2
Я думаю, вы хотите вернуть 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;
Обновить. Если вам нужно вызвать это из SQL, используйте функцию таблицы, например, предложенную @Tony Andrews.
Ответ 3
Вы можете использовать конгруэнтные функции Oracle
В принципе, если вы хотите, чтобы подпрограмма PLSQL (или java или c) была "источником", данных - вместо таблицы - вы должны использовать конвейерную функцию.
Простой пример - создание некоторых случайных данных
Как вы могли создать N уникальных случайных чисел в зависимости от входного аргумента?
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;
Предположим, нам понадобилось три строки для чего-то. Теперь мы можем сделать это одним из двух способов:
select * from TABLE(gen_numbers(3));
COLUMN_VALUE
1
2
3
или
select * from TABLE(gen_numbers)
where rownum <= 3;
COLUMN_VALUE
1
2
3
Ответ 4
Если вы хотите использовать его в простом SQL, я бы позволил процедуре хранилища заполнить таблицу или таблицу temp результирующими строками (или перейти на подход @Tony Andrews).
Если вы хотите использовать решение @Thilo, вам нужно зациклировать курсор, используя PL/SQL.
Вот пример: (Я использовал процедуру вместо функции, например @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;
Ответ 5
create procedure <procedure_name>(p_cur out sys_refcursor) as begin open p_cur for select * from <table_name> end;