Oracle Коллекции PL/SQL: массивы Varrays, вложенность и индексирование по таблицам

Что такое коллекция?

Коллекция — это упорядоченная группа элементов определенного типа данных. Это может быть коллекция простых типов данных или сложных типов данных, таких как определяемые пользователем типы или записи.

В коллекции каждый элемент обозначается термином, называемым «индекс». Каждому элементу присваивается уникальный индекс, и данные можно обрабатывать или получать, обращаясь к этому уникальному индексу.

Коллекции наиболее полезны, когда необходимо обработать или манипулировать большим объемом данных одного типа. Коллекции можно заполнять и обрабатывать целиком, используя опцию «BULK» в настройках. Oracle.

Классификация коллекций основана на структуре, индексе и способе хранения, как показано ниже:

• Таблицы, индексируемые по (также известные как ассоциативные массивы)
• Вложенные таблицы
• Варрайс

В любой момент времени данные в коллекции могут быть обозначены тремя терминами: именем коллекции, индексом и именем поля или столбца, например, « ( ). «Вы узнаете об этих категориях коллекций в разделах ниже».

Типы коллекций вкратце

Три типа коллекций предполагают различные компромиссы. В таблице ниже они представлены в сравнительном виде, после чего каждый из них рассматривается подробно.

Варрайс

Varray — это коллекция, размер которой фиксирован и не может быть превышен. Индекс Varray — это числовое значение. Атрибуты Varray:

• Верхний предельный размер фиксирован.
• Заполняется последовательно, начиная с нижнего индекса «1».
• Этот тип коллекции всегда плотный; мы не можем удалять отдельные элементы массива. Varray можно удалить целиком или обрезать с конца.
• Поскольку оно всегда плотное, оно обладает очень низкой гибкостью.
• Это более целесообразно, когда размер массива известен и аналогичные действия выполняются со всеми элементами.
• Индекс и количество предметов в коллекции всегда остаются стабильными.
• Его необходимо инициализировать перед использованием. Любая операция, кроме EXISTS, над неинициализированной коллекцией вызовет ошибку.
• Его можно создать как объект базы данных, видимый во всей базе данных, или внутри подпрограммы, предназначенный для использования только в ней.

На рисунке ниже показано распределение памяти для массива Varray (dense).

Синтаксис VARRAY:

TYPE <type_name> IS VARRAY (<SIZE>) OF <DATA_TYPE>;

• В приведенном выше синтаксисе type_name объявляется как массив типа 'DATA_TYPE' с заданным ограничением размера. Тип данных может быть простым или сложным.

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

Вложенная таблица — это коллекция, в которой размер массива не фиксирован. Она имеет числовой индекс. Подробнее о типе вложенной таблицы:

• Вложенная таблица не имеет верхнего предела размера.
• Поскольку верхний предел не фиксирован, память необходимо расширять каждый раз перед использованием с помощью ключевого слова 'EXTEND'.
• Заполняется последовательно, начиная с нижнего индекса «1».
• Этот тип коллекции может быть и тем, и другим. плотный и редкийМы можем создать его как плотный, так и произвольно удалять отдельные элементы, что сделает его разреженным.
• Это обеспечивает большую гибкость при удалении элементов массива.
• Она хранится в созданной системой таблице базы данных и может использоваться в запросе SELECT для извлечения значений.
• Нижний индекс и количество могут варьироваться.
• Его необходимо инициализировать перед использованием. Любая операция, кроме EXISTS, над неинициализированной коллекцией вызовет ошибку.
• Его можно создать как объект базы данных, видимый во всей базе данных, или внутри подпрограммы, предназначенный для использования только в ней.

На рисунке ниже показано распределение памяти вложенной таблицы (плотной и разреженной). Пустое место для элемента обозначает разреженный элемент.

Синтаксис вложенной таблицы:

TYPE <type_name> IS TABLE OF <DATA_TYPE>;

• В приведенном выше синтаксисе type_name объявляется как вложенная коллекция таблиц типа 'DATA_TYPE'. Тип данных может быть простым или сложным.

Указатель по таблицам

Таблица с индексом — это коллекция, в которой размер массива не фиксирован. В отличие от других типов коллекций, индекс таблицы с индексом может быть определен пользователем. Атрибуты таблицы с индексом:

• Индекс может быть целым числом или строкой. Тип индекса следует указывать при создании коллекции.
• Эти коллекции не сохраняются последовательно.
• В природе они всегда редки.
• Размер массива не фиксирован.
• Они не могут храниться в столбце базы данных. Они создаются и используются в рамках конкретной сессии.
• Они обеспечивают большую гибкость при сохранении нижнего индекса.
• Нижние индексы могут представлять собой отрицательную последовательность.
• Они больше подходят для относительно небольших сумм, собираемых в рамках одной подпрограммы.
• Их не нужно инициализировать перед использованием.
• Они не могут быть созданы как объекты базы данных; они создаются только внутри подпрограммы.
• Функция BULK COLLECT не может использоваться с этим типом коллекции, поскольку индекс должен быть указан явно для каждой записи.

На рисунке ниже показано распределение памяти для таблицы с индексом (разреженной). Пустое место для элемента обозначает разреженный элемент.

Синтаксис для индексации таблиц:

TYPE <type_name> IS TABLE OF <DATA_TYPE> INDEX BY VARCHAR2 (10);

• В приведенном выше синтаксисе type_name объявлен как коллекция таблиц с индексом типа 'DATA_TYPE'. Переменная с индексом задана как тип VARCHAR2 с максимальным размером 10.

Конструктор и концепция инициализации в коллекциях

Конструкторы — это встроенные функции, предоставляемые Oracle Конструкторы, имеющие то же имя, что и объект или коллекция, выполняются первыми при первом обращении к объекту или коллекции в рамках сессии. Важные детали конструктора в контексте коллекции:

• Для коллекций эти конструкторы необходимо вызывать явно для инициализации коллекции.
• И массивы типа Varray, и вложенные таблицы должны быть инициализированы с помощью этих конструкторов, прежде чем к ним можно будет обратиться в программе.
• Конструктор неявно расширяет область выделения памяти для коллекции (за исключением Varray), поэтому он также может присваивать значения переменным из коллекции.
• Присвоение значений через конструкторы никогда не приводит к разреженности коллекции.

Методы сбора

Oracle Предоставляет множество функций для манипулирования коллекциями и работы с ними. Эти функции определяют и изменяют различные атрибуты коллекции. В таблице ниже приведены различные функции и их описания.

Пример 1: Тип записи на уровне подпрограммы

В этом примере мы видим, как заполнить коллекцию, используя 'ОПТОВЫЙ СБОРи как ссылаться на данные коллекции.

DECLARE
TYPE emp_det IS RECORD
(
EMP_NO NUMBER,
EMP_NAME VARCHAR2(150),
MANAGER NUMBER,
SALARY NUMBER
);
TYPE emp_det_tbl IS TABLE OF emp_det;
guru99_emp_rec emp_det_tbl:= emp_det_tbl();
BEGIN
INSERT INTO emp (emp_no,emp_name, salary, manager) VALUES (1000,'AAA',25000,1000);
INSERT INTO emp (emp_no,emp_name, salary, manager) VALUES (1001,'XXX',10000,1000);
INSERT INTO emp (emp_no, emp_name, salary, manager) VALUES (1002,'YYY',15000,1000);
INSERT INTO emp (emp_no,emp_name,salary, manager) VALUES (1003,'ZZZ',7500,1000);
COMMIT;
SELECT emp_no,emp_name,manager,salary BULK COLLECT INTO guru99_emp_rec
FROM emp;
dbms_output.put_line ('Employee Detail');
FOR i IN guru99_emp_rec.FIRST..guru99_emp_rec.LAST
LOOP
dbms_output.put_line ('Employee Number: '||guru99_emp_rec(i).emp_no);
dbms_output.put_line ('Employee Name: '||guru99_emp_rec(i).emp_name);
dbms_output.put_line ('Employee Salary:'|| guru99_emp_rec(i).salary);
dbms_output.put_line('Employee Manager Number:'||guru99_emp_rec(i).manager);
dbms_output.put_line('--------------------------------');
END LOOP;
END;
/

Code объяснение

• Code строки 2-8: Тип записи Объект 'emp_det' объявлен с столбцами emp_no, emp_name, manager и salary типов данных NUMBER, VARCHAR2, NUMBER и NUMBER.
• Code строка 9: Создание коллекции 'emp_det_tbl', содержащей элементы типа записи 'emp_det'.
• Code строка 10: Объявление переменной 'guru99_emp_rec' как переменной типа 'emp_det_tbl' и инициализация её нулевым конструктором.
• Code строки 12-15: Вставка образца данных в таблицу emp.
• Code строка 16: Завершение транзакции вставки.
• Code строка 17: Получение записей из таблицы 'emp' и массовое заполнение переменной коллекции с помощью функции «BULK COLLECT». Теперь переменная 'guru99_emp_rec' содержит все записи, присутствующие в таблице 'emp'.
• Code строки 19-26: Настройте цикл 'FOR' для вывода всех записей из коллекции по одной. Методы коллекции FIRST и LAST используются в качестве нижнего и верхнего пределов. поиска.

Выход: При выполнении приведенного выше кода вы получите следующий результат.

Employee Detail
Employee Number: 1000
Employee Name: AAA
Employee Salary: 25000
Employee Manager Number: 1000
----------------------------------------------
Employee Number: 1001
Employee Name: XXX
Employee Salary: 10000
Employee Manager Number: 1000
----------------------------------------------
Employee Number: 1002
Employee Name: YYY
Employee Salary: 15000
Employee Manager Number: 1000
----------------------------------------------
Employee Number: 1003
Employee Name: ZZZ
Employee Salary: 7500
Employee Manager Number: 1000
----------------------------------------------

Часто задаваемые вопросы (FAQ)