Отличие 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. 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);

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

Важные особенности 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 | Сайт автора