Учебники

7) Коллекции Oracle PL / SQL

Март 11, 2020

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

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

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

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

В этом уроке вы узнаете

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

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

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

VARRAY,

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

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

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

индекс 1 2 3 4 5 6 7
Ценность Xyz DFV Сд CxS Vbc Nhu Qwe

Синтаксис для VARRAY: 

TYPE <type_name> IS VARRAY (<SIZE>) OF <DATA_TYPE>;
  • В приведенном выше синтаксисе type_name объявляется как VARRAY типа ‘DATA_TYPE’ для заданного ограничения размера. Тип данных может быть простым или сложным.

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

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

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

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

индекс 1 2 3 4 5 6 7
Значение (плотное) Xyz DFV Сд CxS Vbc Nhu Qwe
Значение (редкие) Qwe Asd афг Asd Wer

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

TYPE <tvpe name> IS TABLE OF <DATA TYPE>;
  • В приведенном выше синтаксисе type_name объявляется как коллекция вложенных таблиц типа ‘DATA_TYPE’. Тип данных может быть простым или сложным.

Index-на-таблицы

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

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

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

Нижний индекс (varchar) ПЕРВЫЙ ВТОРОЙ В ТРЕТЬИХ ЧЕТВЕРТЫЙ ПЯТЫЙ ШЕСТОЙ СЕДЬМОЙ
Значение (редкие) Qwe Asd афг Asd Wer

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

TYPE <type_name> IS TABLE OF <DATA_TYPE> INDEX BY VARCHAR2 (10);
  • В приведенном выше синтаксисе type_name объявляется как коллекция индекса по таблице типа ‘DATA_TYPE’. Тип данных может быть простым или сложным. Переменная subsciprt / index задается как тип VARCHAR2 с максимальным размером 10.

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

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

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

Методы сбора

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

метод Описание СИНТАКСИС
СУЩЕСТВУЕТ (n) Этот метод будет возвращать логические результаты. Он вернет «ИСТИНА», если n- й элемент существует в этой коллекции, иначе он вернет ЛОЖЬ. Только неиспользуемые функции могут быть использованы в неинициализированной коллекции <Имя_выборки> .EXISTS (element_position)
COUNT Дает общее количество элементов, присутствующих в коллекции <Collection_name> .Count
ПРЕДЕЛ Возвращает максимальный размер коллекции. Для Varray он вернет фиксированный размер, который был определен. Для вложенной таблицы и индекса по таблице она дает NULL <Collection_name> .limit
ПЕРВЫЙ Возвращает значение первой индексной переменной (индекса) коллекций <Collection_name> .Первый
ПРОШЛОЙ Возвращает значение последней индексной переменной (индекса) коллекций <Collection_name> .LAST
ПРИОР (n) Возвращает предшествующую индексную переменную в коллекции n- го элемента. При отсутствии предшествующего значения индекса возвращается NULL <Имя_выборки> .Перед (п)
ДАЛЬШЕ (n) Возвращает преуспевающую индексную переменную в коллекции n- го элемента. Если нет успешных значений индекса, возвращается NULL <Имя_выборки> .next (п)
ПРОДЛИТЕ Расширяет один элемент в коллекции в конце <Collection_name> .extend
EXTEND (n) Расширяет n элементов в конце коллекции <Имя_выборки> .extend (п)
EXTEND (n, i) Расширяет n копий i- го элемента в конце коллекции <Имя_выборки> .extend (п, я)
ОТДЕЛКА Удаляет один элемент из конца коллекции <Collection_name> .trim
TRIM (n) Удаляет n элементов из конца коллекции <collection_name> .TRIM (n)
УДАЛЯТЬ Удаляет все элементы из коллекции. Делает коллекцию пустой <Collection_name> .DELETE
УДАЛИТЬ (n) Удаляет n-й элемент из коллекции. Если n- й элемент НЕДЕЙСТВИТЕЛЕН, то это ничего не сделает <Имя_выборки> .DELETE (п)
УДАЛИТЬ (м, н) Удаляет элемент в диапазоне от m- го до n- го в коллекции <Имя_выборки> .DELETE (т, п)

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

В этом примере мы увидим, как заполнить коллекцию с помощью ‘BULK COLLECT’ и как ссылаться на данные коллекции.

Сложные типы данных в PL / SQL 

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
----------------------------------------------

 

173
Share: