Создание табличной (pipelined) функции с помощью динамического SQL запроса в ORACLE

Иногда возникает необходимость вернуть результат некоторой функции с на основе запроса сформированного динамически, во время выполнения функции. 
Продемонстрируем как это сделать.
Подготовим тестовый запрос - пусть это будет запрос из таблицы all_objects

select o.OBJECT_NAME, o.OBJECT_TYPE from all_objects o
 where upper(object_name) like upper('t%') and rownum < 35

Определим типы возвращаемых данных

type rowAllObj is record
  (
    OBJECT_NAME  all_objects.OBJECT_NAME%type,
    OBJECT_TYPE all_objects.OBJECT_TYPE%type
   );

Так же табличный тип

type tblAllObj is table of rowAllObj;

Упакуем все это в PL SQL пакет 

create or replace package test_ref_pipl is
  -- спецификация пакета
  -- Author  : orasource.ru
  -- Purpose : тестирование динамический SQL и piplined функция
  type rowAllObj is record
  (
    OBJECT_NAME  all_objects.OBJECT_NAME%type,
    OBJECT_TYPE all_objects.OBJECT_TYPE%type
   );
  type tblAllObj is table of rowAllObj;
  -- Public function and procedure declarations
 function GetAllObj(p_likename varchar2 := 'A%', p_rown number := 30)
    return tblAllObj pipelined;-- ы

end test_ref_pipl;
/
create or replace package body test_ref_pipl is
  -- тело пакета 
  -- наименование и тип обьекта из таблицы all_object
  function GetAllObj(p_likename varchar2 := 'A%', p_rown number := 30)
    return tblAllObj  pipelined is c   sys_refcursor; buf tblAllObj;
  begin
    open c for 'select OBJECT_NAME, OBJECT_TYPE from all_objects
    where upper(object_name) like upper(:0) and rownum < :1'
      using IN p_likename, p_rown ;
    loop
      fetch c bulk collect
        into buf limit 100;
      for i in 1 .. buf.Count loop
        pipe row(buf(i));
      end loop;
      exit when c%NotFound;
    end loop;
    close c;
    return;
  end;
end test_ref_pipl;
/

Вызовем нашу табличную функцию, на основе динамического SQL запроса

select * from TABLE(test_ref_pipl.GetAllObj('T%',30))

Итак, мы возвращаем данные в ref_cursor , далее наши данные передаются как результат табличной функции , порциями по 100 записей
Так же, следует обратить внимание на передачу параметров в динамический запрос, и использование bulk collect

• < Назад
• Вперёд >