Oracle return table из функции

Question

Столкнулся в изучении Oracle вот с какой задачей. Мне, нужно, чтобы функция возвратила значения как таблица, но не могу понять как это правильно сделать. В PostgreSQL - это было бы так:

create or replace function fn_list(vname varchar) 
  returns table(id integer, name text, status text) 
as $$
begin
  return query
    SELECT id,name,status 
    from usuario 
    WHERE name= vname;
end;
$$ language plpgsql;

Можете подсказать как правильно это делать в Oracle или какие обходные пути существуют (желательно для версии 12с).

Answer 1

Создаем обьектный тип (Object type):

create or replace type record_address as object
( 
    Street       varchar2(80),
    City         varchar2(80),
    State        char(2),
    Zip          varchar2(10) 
);

Создаем табличный тип (Nested Table Type):

create or replace type table_address as table of record_address;

Теперь на основе этого можно сделать скалярную функцию, возращающую таблицу:

create or replace function funcListAddress(vState varchar) return table_address 
as
   _result_address table_address;
begin
   select street, city, state, zip 
      bulk collect into _result_address
      from address
      where state = vState;
   return _result_address;
end;

Работа с коллекциями это уже совсем другая история!)

Думаю ничего не забыл!)

Answer 2

Всё сильно зависит от того, в каком контексте функция будет использоваться.

В PL/SQL контексте в качестве табличных значений можно возвращать, или ссылку на курсор, или коллекцию определённую локально. Отличие, первая вернёт ссыллку на данные до того, как они выбраны из SQL контекста, вторая - после.

Минимальный и воспроизводимый пример обоих функций:

declare
    type rectype is record (id int, name varchar2 (8), status varchar2 (8));
    type curtype is ref cursor return rectype;
    type arrtype is table of rectype;
    arr arrtype;
    
    function func (name varchar2) return curtype is
        ret curtype; 
    begin 
        open ret for 
            select * 
            from t1
            where name = func.name;
        return ret;
    end;
 
    function func (cur curtype) return arrtype is
        ret arrtype; 
    begin 
        fetch cur bulk collect into ret;
        close cur;
        return ret;
    end;     
begin 
    arr := func (func ('myname'));
    for i in 1..arr.count loop
        dbms_output.put_line (
            'id/name/status='||arr(i).id||'/'||arr(i).name||'/'||arr(i).status); 
        end loop;     
end;
/
id/name/status=1/myname/ready
id/name/status=2/myname/opened
id/name/status=3/myname/closed

Данные для воспроизведения примера:

create table t1 (id int, name varchar2 (8), status varchar2 (8));
insert all
    into t1 values (1, 'myname', 'ready')
    into t1 values (2, 'myname', 'opened')
    into t1 values (3, 'myname', 'closed')
select * from dual;

Answer 3

В SQL контексте в качестве табличных значений можно возвращать вложенную таблицу, но она должна быть известна SQL движку. Для лучшей согласованности PL/SQL и SQL контекстов принято использовать т.н. "трубные" функции. Это некий симбиоз открытого курсора и коллекции, то есть, в PL/SQL функция ещё не завершиась, а в SQL запросе данные уже доступны и идёт выборка.

Минимальный и воспроизводимый пример такой функции:

create or replace type rectype is object (id int, name varchar2 (8), status varchar2 (8))
/
create or replace type arrtype is table of rectype 
/
create or replace function func (name varchar2) return arrtype pipelined is
    arr arrtype;
begin
    select rectype (id, name, status) bulk collect into arr 
    from t1
    where name = func.name;
    for i in 1..arr.count loop
        pipe row (rectype (arr(i).id, arr(i).name, 'deleted')); 
    end loop;     
    return;
end;
/

Вызывать её надо так, и она даст следующий результат:

select * from func ('myname'); 

        ID NAME     STATUS  
---------- -------- --------
         1 myname   deleted 
         2 myname   deleted 
         3 myname   deleted

БЛОГ НА HUSL

Oracle return table из функции

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