Функция на поочерёдный вывод строк в ORACLE

Question

Есть функция, которая выводит строку (только одну) согласно введённым параметрам. Но сейчас появилась необходимость выводить не одну строку, а сразу несколько. На сколько я знаю, функция не может сохранять в таблицу (указанную в функции в return) несколько строк сразу.

Возможно, кто-то уже сталкивался с таким вопросом и сможет направить меня к решению.

Честно говоря, у меня даже нет идей, как такое можно сделать. Возможно, использовать в функции цикл, но не совсем представляю, как его вписать в функцию.

Пробовал сделать, как объяснено по этой ссылке , но в итоге получил ошибки:

PL/SQL: ORA-00942: таблица или представление пользователя не существует
Compile error at line 8, column 37

PL/SQL: SQL Statement ignored
Compile error at line 8, column 8

Вот сама функция:

CREATE OR REPLACE function test
  (tname_p in varchar2, schema_p in varchar2, tab_p in varchar2, field_p in varchar2, doc_id_p in integer)
  return sys_refcursor
as  
  res sys_refcursor;
begin
  open res
   for select field_p from schema_p.tab_p where doc_id=doc_id_p;
  return res; 
end;

Не понимаю, в чём ошибка.

Answer 1

CREATE OR REPLACE function test
  (tname_p in varchar2, schema_p in varchar2, tab_p in varchar2, field_p in varchar2, doc_id_p in integer)
  return sys_refcursor
as  
  vRes sys_refcursor;
  vQuery VARCHAR2(32767);
begin
  vQuery := 'select ' || field_p || ' from ' || schema_p || '.' || tab_p || ' where doc_id=' || TO_CHAR(doc_id_p);
  open vRes for vQuery;
  return vRes; 
end;

Answer 2

функция не может сохранять в таблицу (указанную в функции в return) несколько строк сразу.

Функция может возвращать (не сохранять, а возвращать) таблицу, точнее сказать, коллекцию. Такие функции называются - табличные.

Воспроизводимый пример (на db<>fidlle):

create or replace type stringTable is table of varchar2 (32)
/
create or replace function getColumn (
    schema varchar2, tabname varchar2, colname varchar2
        ) return stringtable as
    ret stringtable;
begin
    execute immediate '
        select '||colname||' 
        from '  ||schema ||'.'||tabname bulk collect into ret;
    return ret;
end;
/

Результат функции можно получить простым запросом:

create table t (id, memo, created) as
    select rownum, 'row '||rownum, date'2021-08-01'+(rownum) 
    from dual connect by level<=3;    

select * from table (getColumn (user, 't', 'memo'));

Result Sequence
--------------------------------
row 1
row 2
row 3

Или получить результат в переменную:

declare
    ret stringtable;
begin   
    ret := getColumn (user, 't', 'memo');
    for i in 1..ret.count loop
        dbms_output.put_line ('row('||i||')='||ret(i));
    end loop;
end;
/
row(1)=row 1
row(2)=row 2
row(3)=row 3

БЛОГ НА HUSL

Функция на поочерёдный вывод строк в ORACLE

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