Создание табличной (pipelined) функции с помощью динамического SQL запроса в ORACLE
Категория: Блог
Опубликовано 24.05.2017
Иногда возникает необходимость вернуть результат некоторой функции с на основе запроса сформированного динамически, во время выполнения функции.
Продемонстрируем как это сделать.
Подготовим тестовый запрос - пусть это будет запрос из таблицы 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