Как вернуть SELECT запрос из функции?

Question

Нужно создать функцию, которая позволит вернуть тот же результат, что и запрос SELECT, и которая могла бы содержать PL/SQL код.

Пробую нечто действительно простое:

CREATE OR REPLACE FUNCTION foo
  RETURN SYS_REFCURSOR
IS
  l_rc SYS_REFCURSOR;
BEGIN
  OPEN l_rc FOR 
    SELECT * FROM tab;
  RETURN l_rc;
END;
/

Но когда вызываю эту функцию запросом:

SELECT foo FROM dual;

Получаю все результаты из таблицы в одной ячейке, а не каждый столбец отдельно:

{<ID=1,NAME=name 1>,<ID=2,NAME=name 2>,<ID=3,NAME=name 3>,}

Есть ли способ вывести результат функции в табличной форме?

_{Свободный перевод вопроса How can I return a SELECT query with an oracle function? от участника @Natty}

Answer 1

Функция возвращяет открытый курсор. Как результат его выполнения выводится, полностью зависит от клиентского ПО или IDE, где эта функция вызывается.

Например, Курсор, полученый через преременную подстановки, так же выводится в виде таблицы:

create table tab (id, name) as
    select rownum, 'name '||rownum from dual connect by level<=3 
/
create or replace function TabById (id int) return sys_refcursor is
    rc sys_refcursor;
begin
    open rc for 
        select * from tab where id=TabById.id;
    return rc;
end;
/

var rc refcursor
exec :rc := TabById (1);
print rc

        ID NAME                                         
---------- ---------------------------------------------
         1 name 1

В более новых версиях SQL*Plus, SQL Developer курсор, полученый как столбец в SQL запросe, выводится так же в виде таблицы:

SQL> select TabById (1) from dual;

TABBYID(1)
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

        ID NAME
---------- ---------------------------------------------
         1 name 1

Answer 2

Для задачи как в вопросе -- вывести результат определённого запроса, и иметь возможность его предварительно обработать, хорошо подойдёт такое решение: поместить определение курсора в спецификацию пакета, и на его основе добавить в пакет конвейерную функцию.

Воспроизводимый пример:

create or replace package pack as
    cursor cur (id int) is
        select * 
        from tab where id=cur.id;
    type resty is table of tab%rowtype;  
    function TabById (id int) return resty pipelined;
end;
/
create or replace package body pack as
    function TabById (id int) return resty pipelined is
        r tab%rowtype;
    begin
        open cur (id);
        loop fetch cur into r;
            exit when cur%notfound;
            r.name := '*'||r.name;
            pipe row (r);
        end loop;
        return;
    end;
end;
/

Запрос и вывод результата:

select * from pack.TabById (1);

        ID NAME                                         
---------- ---------------------------------------------
         1 *name 1

БЛОГ НА HUSL

Как вернуть SELECT запрос из функции?

Ответы (2 шт):