В промышленных системах часто требуется выполнить преобразования данных с использованием pl/sql кода с возможностью обращения к этим данным в sql запросе. Для этого в oracle используются табличные функции.

Табличные функции – это функции возвращающие данные в виде коллекции, к которой мы можем обратиться в секции from запроса, как если бы эта коллекция была реляционной таблицей. Преобразование коллекции в реляционный набор данных осуществляется с помощью функции table().

Однако такие функции имеют один недостаток, так как в них сначала полностью наполняется коллекция, а только потом эта коллекция возвращается в вызывающую обработку. Каждая такая коллекция храниться в памяти и в высоконагруженных системах это может стать проблемой. Так же в вызывающей обработке происходит простой на время наполнения коллекции. Решить данный недостаток призваны табличные конвейерные функции.

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

Рассмотрим, как создаются такие функции. В данном примере будет использована учебная схема hr и три ее таблицы: employees, departments, locations.

• employees — таблица сотрудников.
• departments — таблица отделов.
• locations — таблица географического местонахождения.

Данная схема и таблицы есть в каждой базовой сборке oracle по умолчанию.

В схеме hr я создам пакет test, в нем будет реализован наш код. Создаваемая функция будет возвращать данные по сотрудникам в конкретном отделе. Для этого в спецификации пакета нужно описать тип возвращаемых данных:

create or replace package hr.test as

type t_employee is record
 (
  employee_id integer,
  first_name varchar2(50),
  last_name varchar2(50), 
  email varchar2(50),   
  phone_number varchar2(12),
  salary number(8,2),
  salary_recom number(8,2),
  department_id integer,
  department_name varchar2(100),
  city varchar2(50)
 );

type t_employees_table is table of t_employee;

end;

• employee_id – ид сотрудника
• first_name – имя
• last_name – фамилия
• email – электронный адрес
• phone_number – телефон
• salary – зарплата
• salary_recom – рекомендуемая зарплата
• department_id – ид отдела
• department_name — наименование отдела
• city – город

Далее опишем саму функцию:

function get_employees_dep(p_department_id integer) return t_employees_table pipelined;

Функция принимает на вход ид отдела и возвращает коллекцию созданного нами типа t_employees_table. Ключевое слово pipelined делает эту функцию конвейерной. В целом спецификация пакета следующая:

create or replace package hr.test as
type t_employee is record
 (
  employee_id integer,
  first_name varchar2(50),
  last_name varchar2(50), 
  email varchar2(50),   
  phone_number varchar2(12),
  salary number(8,2),
  salary_recom number(8,2),
  department_id integer,
  department_name varchar2(100),
  city varchar2(50)
 );

type t_employees_table is table of t_employee;

function get_employees_dep(p_department_id integer) return t_employees_table pipelined;
end;

Рассмотрим тело пакета, в нем описано тело функции get_employees_dep:

create or replace package body hr.test as
function get_employees_dep(p_department_id integer) return t_employees_table pipelined as 
begin
  
  for rec in
  (
    select 
      emps.employee_id,
      emps.first_name,
      emps.last_name, 
      emps.email,   
      emps.phone_number,
      emps.salary,
      0 as salary_recom,
      dep.department_id,
      dep.department_name,
      loc.city
    from 
      hr.employees emps
      join hr.departments dep on emps.department_id = dep.department_id
      join hr.locations loc on dep.location_id = loc.location_id
    where
      dep.department_id = p_department_id  
  )
  loop
   
   if (rec.salary >= 8000) then
     rec.salary_recom := rec.salary;     
   else
     rec.salary_recom := 10000;
   end if;  
   
   pipe row (rec);  
  end loop;
end;
end;

В функции мы получаем набор данных по сотрудникам конкретного отдела, каждую строчку этого набора анализируем на предмет того, что если зарплата сотрудника меньше 8 000, то рекомендуемую зарплату устанавливаем в значение 10 000, дальше каждая строчка не дожидаясь окончания наполнения всей коллекции, отдается в вызывающую обработку. Обратите внимание, что в теле функции отсутствует ключевое слово return и присутствует pipe row (rec).

Осталось вызвать созданную функцию в pl/sql блоке:

declare 
  v_department_id integer :=100;
begin   
   for rec in (
     select 
       *  
     from 
       table (hr.test.get_employees_dep(v_department_id)) emps   
   )loop
      -- какой то код
   end loop;  
end;

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