Массивы в Oracle

Введение

В Oracle массив — это структура, которая позволяет хранить набор значений в одной переменной.

Представьте, что у вас есть список номеров школ — вместо того чтобы заводить 10 переменных `school1`, `school2`, ..., мы кладём все значения в один контейнер и работаем с ними как с единым объектом.

Массивы в Oracle особенно полезны в PL/SQL, при передаче данных между процедурами, а также при интеграции через OCI (Oracle Call Interface).

Основные типы массивов в Oracle

VARRAY (Variable-size array)

• Хранит фиксированное _максимальное_ количество элементов.
• Данные хранятся вместе с родительской записью (inline).
• Хорошо для небольших списков, когда нужен фиксированный лимит.

NESTED TABLE

• Размер не ограничен.
• Данные могут храниться отдельно от основной таблицы (out-of-line).
• Удобно, когда список может расти.

INDEX-BY TABLE (Associative array)

• Хранится только в памяти (не в таблице).
• Индексация по числу или строке.
• Используется в PL/SQL для временных структур.

TABLE OF TYPE

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

OCI Массивы (SYS.OCI...)

• Специальные структуры для обмена данными с клиентскими приложениями через OCI.
• Применяются при работе с драйверами C/C++ или при пакетной загрузке данных.

Массив школ в SYS.ODCINumberList (OCI совместимый тип)

В Oracle уже есть готовые коллекции в пакете SYS, которые используются и в OCI — например:

1. SYS.ODCINumberList — массив чисел
2. SYS.ODCIvarchar2List — массив строк
3. SYS.ODCIRawList — массив RAW

Они часто применяются для bulk insert, передачи в SQL из PL/SQL, и для интеграции с внешними клиентами.

Сравнение

Где применяется

1. Списки — например, список номеров школ для обработки.
2. Передача параметров в процедуру/функцию.
3. Пакетная загрузка данных в таблицу.
4. Интеграция с внешними приложениями через OCI.

Пример SYS.ODCINumberList

DECLARE
v_schools SYS.ODCINumberList := SYS.ODCINumberList(101, 102, 103);

BEGIN
v_schools.EXTEND;
v_schools(4) := 104;
FOR i IN 1 .. v_schools.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('School #' || v_schools(i));
END LOOP;

END;

Пример TYPE

CREATE OR REPLACE TYPE school_varray AS VARRAY(5) OF NUMBER;

DECLARE
v_schools school_varray := school_varray(201, 202);

BEGIN
v_schools.EXTEND;
v_schools(3) := 203;
DBMS_OUTPUT.PUT_LINE('Total schools: ' || v_schools.COUNT);

END;

Пример nested table

CREATE OR REPLACE TYPE school_list AS TABLE OF NUMBER;

DECLARE
v_schools school_list := school_list(101, 102, 103);

BEGIN
FOR i IN 1 .. v_schools.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('School #' || v_schools(i));
END LOOP;

END;

Пример INDEX-BY TABLE

DECLARE
TYPE school_index IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
v_schools school_index;

BEGIN
v_schools(1) := 'School #301';
v_schools(2) := 'School #302';
DBMS_OUTPUT.PUT_LINE(v_schools(1));
DBMS_OUTPUT.PUT_LINE(v_schools(2));

END;

Пример SYS.OCI...

DECLARE
v_schools SYS.ODCINumberList := SYS.ODCINumberList(101, 102, 103);

BEGIN
v_schools.EXTEND;
v_schools(4) := 104;
FOR i IN 1 .. v_schools.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('Школа №' || v_schools(i));
END LOOP;

END;

Вывод

1. Для фиксированных небольших списков — берите VARRAY.
2. Для динамических коллекций — лучше NESTED TABLE.
3. Для временных структур в коде — INDEX-BY.
4. Для интеграции на уровне клиента — OCI массивы.

Совет: всегда думайте, где будет храниться массив — в памяти, в таблице или в пакете, и от этого выбирайте тип.

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

- Документация Oracle — Collections and Records (читать)

- DBMS_XPLAN — вывод плана выполнения (читать)

Контакты

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