Select из функции в Oracle. Pipelined-функция.

В Oracle есть очень полезная возможность — способность функций возвращать таблицу данных, которую можно включать в другие выборки.

Такие возвращаемые таблицы являются коллекциями типа nested table, с ними могут работать и обычные функции, но чтобы получить максимальное быстродействие имеет смысл использовать pipelined-функции, пример которой я приведу ниже.

Готовим типы данных:

  1. create type TypeTestObject #b1b100;">as object
  2. (
  3. object_name varchar2(#cc66cc;">500),
  4. object_id number,
  5. object_type varchar2(#cc66cc;">10)
  6. );
  7. /*Создаем коллекцию типа nested table*/
  8. create type TypeTestList #b1b100;">as table of TypeTestObject;
  9.  

Создаем функцию:

  1. create or replace function testFunction(pObject_type in varchar2)
  2. #b1b100;">return TypeTestList pipelined #b1b100;">as
  3. begin
  4. #b1b100;">for i in (
  5. select tao.OBJECT_NAME, tao.OBJECT_ID, tao.OBJECT_TYPE
  6. from all_objects tao
  7. where tao.OBJECT_TYPE=pObject_type
  8. )
  9. loop
  10. pipe row (TypeTestObject(i.OBJECT_NAME, i.OBJECT_ID, i.OBJECT_TYPE));
  11. end loop;
  12. #b1b100;">return;
  13.  

Как это работает.

Открывается неявный курсор, извлекаются записи, из них создаются объекты типа TypeTestObject и помещаются в коллекцию.

Вот как эту функцию можно использовать в SQL-запросах:

  1. select *
  2. from table(testFunction('TABLE')) t
  3. where t.object_id=#cc66cc;">20;
  4.  

Как это применить.

Часто в конкретной системе есть сложная логика, которая кочует из одного запроса/отчета в другой, такую логику сложно понимать, и, если требуется, изменять. Поэтому часто бывает целесообразно общие логические блоки вынести в отдельные библиотеки и использовать уже их, вместо «копипастного» клонирования. В этом деле хорошо помогают представления (view), но сложную логику в них не запихнуть, и тут уже работают nested table и pipelined-функции.

Источники
09:41
1.76K
Нет комментариев. Ваш будет первым!
Используя этот сайт, вы соглашаетесь с тем, что мы используем файлы cookie.