Select из функции в Oracle. Pipelined-функция.
В Oracle есть очень полезная возможность — способность функций возвращать таблицу данных, которую можно включать в другие выборки.
Такие возвращаемые таблицы являются коллекциями типа nested table, с ними могут работать и обычные функции, но чтобы получить максимальное быстродействие имеет смысл использовать pipelined-функции, пример которой я приведу ниже.
Готовим типы данных:
Создаем функцию:
create or replace function testFunction(pObject_type in varchar2) begin #b1b100;">for i in ( select tao.OBJECT_NAME, tao.OBJECT_ID, tao.OBJECT_TYPE from all_objects tao where tao.OBJECT_TYPE=pObject_type ) loop pipe row (TypeTestObject(i.OBJECT_NAME, i.OBJECT_ID, i.OBJECT_TYPE)); end loop; #b1b100;">return; end;
Как это работает.
Открывается неявный курсор, извлекаются записи, из них создаются объекты типа TypeTestObject и помещаются в коллекцию.
Вот как эту функцию можно использовать в SQL-запросах:
select * from table(testFunction('TABLE')) t
Как это применить.
Часто в конкретной системе есть сложная логика, которая кочует из одного запроса/отчета в другой, такую логику сложно понимать, и, если требуется, изменять. Поэтому часто бывает целесообразно общие логические блоки вынести в отдельные библиотеки и использовать уже их, вместо «копипастного» клонирования. В этом деле хорошо помогают представления (view), но сложную логику в них не запихнуть, и тут уже работают nested table и pipelined-функции.