Отличие PIPELINED функций и TYPE в Oracle PL/SQL

Что такое 'TYPE'?

'TYPE' в Oracle — это способ определить собственные типы данных.

Чаще всего используется:

1. для создания PL/SQL-коллекций (таблиц/массивов),
2. объектов, если нужно работать со структурированными данными,
3. в pipelined-функциях — как возвращаемое значение.

Пример:

-- Объявляем тип таблицы

CREATE OR REPLACE TYPE t_num_tab AS TABLE OF NUMBER;

Что такое PIPELINED FUNCTION?

PIPELINED-функции позволяют возвращать набор данных построчно, как будто это обычная таблица, и использовать их напрямую в SQL-запросах.

Выглядят как обычные функции, но с ключевым словом PIPELINED.

Это очень мощный инструмент, если:

1. нужно возвращать коллекции в SQL-стиле,
2. вы обрабатываете много данных в PL/SQL, но хотите использовать SQL-join/where/group by и т.д.

Пример: TYPE + PIPELINED FUNCTION

1) Создаём тип строки и таблицы:

CREATE OR REPLACE TYPE emp_row_type AS OBJECT (
emp_name VARCHAR2(100),
emp_salary NUMBER
);
/

CREATE OR REPLACE TYPE emp_table_type AS TABLE OF emp_row_type;
/

2) Создаём PIPELINED-функцию:

CREATE OR REPLACE FUNCTION get_employees
RETURN emp_table_type PIPELINED
AS
BEGIN
PIPE ROW(emp_row_type('Alice', 5000));
PIPE ROW(emp_row_type('Bob', 7000));
PIPE ROW(emp_row_type('Charlie', 6000));
RETURN;
END;
/

3) Вызываем как таблицу:

SELECT * FROM TABLE(get_employees);

В результате получаем следующее:

Результат выполнения SQL-запроса

Отличия

Главные отличия

TYPE в Oracle используется для определения пользовательских типов данных — например, одной строки (OBJECT TYPE) или коллекции строк (TABLE OF TYPE). Он сам по себе не возвращает данные, а лишь описывает структуру этих данных.

А вот PIPELINED FUNCTION — это функция, которая построчно возвращает данные, используя заранее описанный TYPE. Она позволяет работать с результатом как с обычной таблицей в SQL. Это особенно полезно при больших объёмах данных или для интеграции с SQL-запросами и APEX-интерфейсом.

Проще говоря:

• TYPE — описание формы.
• PIPELINED FUNCTION — источник данных, который использует TYPE и умеет "выдавать" строки по одной.

Важные особенности PIPELINED FUNCTION

1. Возвращает строки по одной — удобно для обработки больших объемов данных.
2. Работает как виртуальная таблица — можно использовать в SELECT * FROM TABLE(...).
3. Ускоряет производительность — результат не хранится целиком в памяти, строки «передаются по трубе».
4. Обязателен возврат типа (RETURN TABLE OF …) — заранее объявленный TYPE.
5. Поддерживает PARALLEL_ENABLE — можно распараллеливать выполнение.
6. Можно использовать в представлениях и APEX — как источник данных.
7. Хорошо подходит для оборачивания курсоров — CURSOR → PIPE ROW.

Важные особенности TYPE

1. Создает пользовательские типы данных — объекты (OBJECT TYPE) и коллекции (TABLE OF OBJECT).
2. Может использоваться в SQL и PL/SQL — как в теле запроса, так и для переменных.
3. Бывает в памяти (PL/SQL TYPE) и в БД (CREATE TYPE):
   PL/SQL типы — только внутри блоков, не видны из SQL.
   SQL типы — можно использовать в таблицах, функциях и т.д.
4. Незаменим для PIPELINED функций — обязательно описывать структуру возвращаемых данных.
5. Работает с BULK COLLECT и FORALL — помогает ускорять массовые операции.
6. Можно использовать в параметрах процедур/функций — передавать таблицы значений.
7. Совместим с JSON/XML генерацией — часто используется в Web API.

🧙‍♂️ Если хочешь прокачать навыки до гуру — этот раздел для тебя:

1. PIPELINED FUNCTION + CURSOR

• Сценарий: Используется, когда нужно "развернуть" данные из курсора в таблицу.

Пример:

FOR rec IN (SELECT * FROM emp) LOOP
PIPE ROW(emp_row_type(rec.ename, rec.sal));
END LOOP;

🔹 2. PIPELINED FUNCTION + PIPELINED FUNCTION

• Сценарий: Когда одна pipelined функция вызывает другую (например, постобработка).
• Пример:
  Внешняя функция вызывает внутреннюю через SELECT * FROM TABLE(...), применяя фильтрацию, сортировку и т.д.

🔹 3. RETURN TYPE в функциях

• Сценарий: Используется для указания возвращаемого типа — чаще всего TABLE OF OBJECT TYPE.

Пример:
RETURN emp_table_type PIPELINED

🔹 4. TYPE в SELECT

• Сценарий: Используется для явного создания объекта "на лету".

Пример:

SELECT emp_row_type('John', 5000) FROM DUAL;

🔹 5. Использование TYPE в PL/SQL переменных

• Сценарий: Создание переменных, массивов и структур для промежуточной обработки данных.
• Пример:
  

v_emp emp_row_type := emp_row_type('Alice', 4000);

🔹 6. TYPE как параметр в процедурах / функциях

• Сценарий: Передача таблицы значений как параметра.

Пример:

PROCEDURE process_emps(p_list IN emp_table_type)

🔹 7. TYPE + JSON/XML

• Сценарий: Преобразование объектного типа в JSON или XML для API-интеграций.
• Пример:
  

SELECT JSON_OBJECT(emp_name, emp_salary)

FROM TABLE(get_employees());

Полезные ссылки

1. Oracle Docs — Pipelined Functions

2. Oracle Live SQL Demo

3. Oracle TYPE и COLLECTIONS

Файлы и скрипты

Исходные файлы можно найти в GIT.

Контакты

Написать автору | Telegram | Сайт автора