[Из песочницы] Oracle – табличные конвейерные функции
В промышленных системах часто требуется выполнить преобразования данных с использованием 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 кода и не просесть в плане производительности, а в ряде случаем даже ее увеличить.
