Коллекции PL/SQL

Коллекции PL/SQL — это структурированные типы данных в языке программирования PL/SQL, предназначенные для хранения и обработки упорядоченных наборов элементов одного типа (гомогенных данных). Они являются аналогом массивов в других языках программирования, но обладают более широкими возможностями, включая динамическое изменение размера, вложенность и интеграцию с реляционными таблицами базы данных Oracle. Коллекции позволяют обрабатывать множества данных как единое целое, что упрощает написание сложной бизнес-логики и повышает производительность за счёт сокращения числа обращений к базе данных.

Типы коллекций

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

Ассоциативные массивы (ранее — индексные таблицы)

Ассоциативные массивы (или таблицы PL/SQL) представляют собой набор пар «ключ — значение», где ключом может быть целое число (тип BINARY_INTEGER, PLS_INTEGER или VARCHAR2) или строка. Элементы не упорядочены по индексу, а доступ к ним осуществляется по ключу. Размер массива динамически изменяется при добавлении новых элементов.

• Хранение: только в памяти сессии PL/SQL (в PGA). Не могут храниться в таблицах базы данных.
• Инициализация: не требуют явного вызова конструктора; пустой массив создаётся при объявлении.
• Индексация: произвольная, не обязательно последовательная.
• Применение: для временного хранения небольших наборов данных, для быстрого поиска по ключу, для передачи параметров в подпрограммы.

Пример объявления: ``sql TYPE t_emp_names IS TABLE OF employees.last_name%TYPE INDEX BY PLS_INTEGER; v_emp_names t_emp_names; ``

Вложенные таблицы

Вложенные таблицы (nested tables) — это неупорядоченные наборы элементов, которые могут иметь произвольное количество элементов, включая ноль. Они похожи на одномерные массивы, но не имеют фиксированного размера. В отличие от ассоциативных массивов, вложенные таблицы могут храниться в столбцах таблиц базы данных (как тип данных TABLE).

• Хранение: в памяти сессии (PGA) и в таблицах базы данных (в отдельном сегменте хранения).
• Инициализация: требуют явного вызова конструктора (например, t_my_table()).
• Индексация: целочисленная, начиная с 1. При удалении элемента индексы не пересчитываются (образуются «дыры»).
• Применение: для хранения наборов данных, которые могут быть пустыми, для моделирования отношений «один-ко-многим» в реляционных таблицах.

Пример объявления: ``sql TYPE t_numbers IS TABLE OF NUMBER; v_numbers t_numbers := t_numbers(10, 20, 30); ``

Массивы VARRAY

Массивы VARRAY (variable-size arrays) — это упорядоченные наборы элементов фиксированной максимальной длины. Размер VARRAY задаётся при объявлении типа и не может быть изменён, хотя количество фактически хранящихся элементов может быть меньше этого максимума. VARRAY хранятся в памяти сессии и могут быть сохранены в столбцах таблицы как единое целое (в том же сегменте, что и строка таблицы).

• Хранение: в памяти сессии (PGA) и в таблицах базы данных (внутри строки, если размер позволяет).
• Инициализация: требуют явного вызова конструктора.
• Индексация: целочисленная, начиная с 1, строго последовательная.
• Применение: для хранения небольших, заранее известных по размеру наборов данных (например, список телефонных номеров, дни недели).

Пример объявления: ``sql TYPE t_days IS VARRAY(7) OF VARCHAR2(10); v_days t_days := t_days('Пн', 'Вт', 'Ср', 'Чт', 'Пт', 'Сб', 'Вс'); ``

Методы коллекций

PL/SQL предоставляет встроенные методы для работы с коллекциями. Эти методы вызываются через точку после имени переменной коллекции.

Операции с коллекциями

Объявление и инициализация

Коллекции объявляются в секции DECLARE блока PL/SQL. Для VARRAY и вложенных таблиц обязательна инициализация конструктором, иначе они считаются атомарно NULL (не пустыми, а неопределёнными). Попытка обращения к неинициализированной коллекции вызывает исключение COLLECTION_IS_NULL.

``sql DECLARE TYPE t_arr IS VARRAY(5) OF VARCHAR2(10); v_arr t_arr; -- NULL, не пусто BEGIN -- v_arr(1) := 'A'; -- Ошибка: коллекция не инициализирована v_arr := t_arr(); -- Инициализация пустой коллекции v_arr.EXTEND(3); v_arr(1) := 'A'; v_arr(2) := 'B'; v_arr(3) := 'C'; END; ``

Доступ к элементам

Доступ к элементам осуществляется по индексу в круглых скобках. Для ассоциативных массивов индекс может быть строкой. При обращении к несуществующему элементу возникает исключение NO_DATA_FOUND (для ассоциативных массивов) или VALUE_ERROR (для вложенных таблиц и VARRAY, если индекс вне диапазона). Рекомендуется перед обращением проверять существование элемента с помощью метода EXISTS.

Циклы по коллекциям

Для перебора элементов коллекций используются циклы FOR...LOOP с индексом, а также цикл FORALL для массовых операций DML (INSERT, UPDATE, DELETE) с коллекциями.

```sql -- Перебор ассоциативного массива FOR i IN v_emp_names.FIRST .. v_emp_names.LAST LOOP IF v_emp_names.EXISTS(i) THEN DBMS_OUTPUT.PUT_LINE(v_emp_names(i)); END IF; END LOOP;

-- Массовая вставка из вложенной таблицы FORALL i IN 1 .. v_employees.COUNT INSERT INTO emp_backup (emp_id, emp_name) VALUES (v_employees(i).emp_id, v_employees(i).emp_name); ```

Сравнение коллекций

Коллекции в PL/SQL нельзя сравнивать напрямую операторами =, <>, IN. Для сравнения необходимо поэлементно перебрать обе коллекции. Исключение составляет случай, когда коллекция сравнивается с NULL — используется оператор IS NULL / IS NOT NULL.

Коллекции и таблицы базы данных

Коллекции, объявленные на уровне схемы (с помощью CREATE TYPE), могут использоваться как типы данных столбцов реляционных таблиц. Это позволяет хранить многозначные атрибуты в одной строке, что особенно полезно при работе с объектно-реляционными расширениями Oracle.

``sql CREATE TYPE t_phone_list AS VARRAY(5) OF VARCHAR2(15); / CREATE TABLE contacts ( contact_id NUMBER PRIMARY KEY, name VARCHAR2(100), phones t_phone_list ); ``

Для работы с коллекциями в SQL-запросах используется оператор TABLE(), который преобразует коллекцию в реляционное представление (набор строк), пригодное для использования в SELECT, FROM и JOIN.

Производительность и ограничения

• Память: Ассоциативные массивы и вложенные таблицы хранятся в области памяти PGA (Program Global Area) сессии. Размер коллекции ограничен доступной памятью сервера. Чрезмерно большие коллекции могут привести к ошибке ORA-04030 (нехватка памяти).
• VARRAY: При хранении в таблице VARRAY размером до 4 КБ хранится внутри строки (inline), что ускоряет чтение, но замедляет запись. Большие VARRAY хранятся отдельно (out-of-line).
• Вложенные таблицы в БД: Хранятся в отдельной таблице хранения (storage table), что может влиять на производительность при больших объёмах данных.
• Исключения: Наиболее частые исключения: COLLECTION_IS_NULL (попытка доступа к неинициализированной коллекции), NO_DATA_FOUND (несуществующий индекс в ассоциативном массиве), SUBSCRIPT_BEYOND_COUNT (выход за границы VARRAY или вложенной таблицы), SUBSCRIPT_OUTSIDE_LIMIT (превышение максимального размера VARRAY).

Примеры использования

Ассоциативный массив как кэш

``sql DECLARE TYPE t_salary_cache IS TABLE OF employees.salary%TYPE INDEX BY employees.employee_id%TYPE; v_cache t_salary_cache; v_emp_id employees.employee_id%TYPE := 100; BEGIN -- Загрузка зарплаты в кэш SELECT salary INTO v_cache(v_emp_id) FROM employees WHERE employee_id = v_emp_id; -- Использование кэша DBMS_OUTPUT.PUT_LINE('Зарплата: ' || v_cache(v_emp_id)); END; ``

Вложенная таблица для массовой обработки

``sql DECLARE TYPE t_emp_list IS TABLE OF employees%ROWTYPE; v_emps t_emp_list; BEGIN -- Загрузка всех сотрудников отдела 50 в коллекцию SELECT BULK COLLECT INTO v_emps FROM employees WHERE department_id = 50; -- Обработка каждого сотрудника FOR i IN 1 .. v_emps.COUNT LOOP v_emps(i).salary := v_emps(i).salary 1.1; END LOOP; -- Массовое обновление FORALL i IN 1 .. v_emps.COUNT UPDATE employees SET salary = v_emps(i).salary WHERE employee_id = v_emps(i).employee_id; COMMIT; END; ``

Источники

• Oracle Database PL/SQL Language Reference (документация Oracle)
• Стивен Фейерштейн, «Oracle PL/SQL Programming» (O'Reilly Media)
• Oracle Database SQL Language Reference
• Статьи на Oracle Technology Network (OTN)