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

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

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

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

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

Коллекции классифицируются на основе структуры, индекса и хранилища, как показано ниже.

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

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

Варрайс

Varray — это метод сбора данных, в котором размер массива фиксирован. Размер массива не может превышать его фиксированное значение. Индекс Varray имеет числовое значение. Ниже приведены атрибуты Varrays.

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

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

индекс	1	2	3	4	5	6	7
Значение	Xyz	Дфв	Сде	Cxs	ВВС	Nhu	Qwe

Синтаксис VARRAY:

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

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

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

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

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

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

индекс	1	2	3	4	5	6	7
Значение (плотное)	Xyz	Дфв	Сде	Cxs	ВВС	Nhu	Qwe
Значение (редкое)	Qwe		ASD	афг		ASD	Кто

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

TYPE <tvpe name> IS TABLE OF <DATA TYPE>;

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

Индекс по таблице

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

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

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

Индекс (varchar)	ПЕРВЫЙ	ВТОРОЙ	ТРЕТИЙ	ЧЕТВЕРТЫЙ	ПЯТЫЙ	ШЕСТОЙ	СЕДЬМОЙ
Значение (редкое)	Qwe		ASD	афг		ASD	Кто

Синтаксис для индексирования по таблице

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

В приведенном выше синтаксисе имя_типа объявлено как коллекция индексов по таблицам типа «DATA_TYPE». Тип данных может быть простым или сложным. Переменная subsciprt/index имеет тип VARCHAR2 с максимальным размером 10.

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

Конструкторы — это встроенные функции, предоставляемые оракулом и имеющие то же имя, что и объект или коллекции. Они выполняются первыми всякий раз, когда объект или коллекции обращаются впервые в сеансе. Ниже приведены важные детали конструктора в контексте коллекции:

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

Методы сбора

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

Способ доставки	Описание	СИНТАКСИС
СУЩЕСТВУЕТ (н)	Этот метод вернет логические результаты. Он вернет «ИСТИНА», если n^th элемент существует в этой коллекции, иначе он вернет FALSE. В неинициализированной коллекции можно использовать только функции EXISTS.	.EXISTS(позиция_элемента)
СЧИТАТЬ	Возвращает общее количество элементов, присутствующих в коллекции.	.СЧИТАТЬ
ОГРАНИЧЕНИЯ	Возвращает максимальный размер коллекции. Для Varray он вернет определенный фиксированный размер. Для вложенной таблицы и индекса по таблице это дает NULL	.LIMIT
ПЕРВЫЙ	Возвращает значение первой индексной переменной (индекса) коллекций.	.ПЕРВЫЙ
LAST	Возвращает значение последней индексной переменной (индекса) коллекций.	.ПОСЛЕДНИЙ
ПРИОР (н)	Возвращаемые значения предшествуют индексной переменной в коллекции n^th элемент. Если нет предшествующего индекса, возвращается значение NULL.	.ПРИОР(н)
СЛЕДУЮЩИЙ (н)	Возвращает успешную индексную переменную в коллекции n^th элемент. Если нет успеха, возвращается значение индекса NULL.	.ДАЛЕЕ(н)
ПРОДЛИТЕ	Расширяет один элемент в коллекции в конце	.ПРОДЛЕВАТЬ
ПРОДЛИТЬ (н)	Расширяет n элементов в конце коллекции.	.РАСШИРИТЬ(н)
РАСШИРИТЬ (n,i)	Расширяет n копий i^th элемент в конце коллекции	.EXTEND(n,i)
TRIM	Удаляет один элемент из конца коллекции	.ПОДРЕЗАТЬ
ОБРЕЗКА (н)	Удаляет n элементов из конца коллекции.	.TRIM (н)
УДАЛИТЬ	Удаляет все элементы из коллекции. Делает коллекцию пустой	.УДАЛИТЬ
УДАЛИТЬ (н)	Удаляет n-й элемент из коллекции. Если н^th элемент равен NULL, то это ничего не даст	.DELETE(н)
УДАЛИТЬ (м, н)	Удаляет элемент в диапазоне m^th к n^th в коллекции	.DELETE(м,п)

Пример 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;
/

Пояснение к коду:

Строка кода 2–8: Тип записи 'emp_det' объявляется со столбцами emp_no, emp_name, зарплата и менеджер типа данных NUMBER, VARCHAR2, NUMBER, NUMBER.
Строка кода 9: Создание коллекции emp_det_tbl элемента типа записи emp_det.
Строка кода 10: Объявление переменной «guru99_emp_rec» как типа «emp_det_tbl» и инициализация нулевым конструктором.
Строка кода 12–15: Вставка образца данных в таблицу emp.
Строка кода 16: Завершение транзакции вставки.
Строка кода 17: Получение записей из таблицы «emp» и массовое заполнение переменной коллекции с помощью команды «BULK COLLECT». Теперь переменная «guru99_emp_rec» содержит все записи, присутствующие в таблице «emp».
Строка кода 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
----------------------------------------------

Вы могли бы: