Что такое коллекция oracle
Обновлено: 07.07.2024
Коллекцией называется упорядоченная группа элементов одного типа. Язык PL/SQL поддерживает три вида коллекций:
- вложенные таблицы (nested tables) ;
- индексированные таблицы ;
- varray-массивы (variable-size arrays) .
Доступ к любому элементу вложенной таблицы или varray-массива осуществляется по его индексу, который указывается в скобках после имени переменной типа коллекции . Коллекция может быть передана в качестве параметра. Коллекцию можно использовать:
- для обмена с таблицами баз данных и столбцами данных;
- для передачи столбца данных из приложения клиента в хранимую процедуру или обратно.
Для создания коллекции следует определить тип коллекции - TABLE или VARRAY - и объявить переменную этого типа. Определение типа выполняется в секции объявлений блока PL/SQL , подпрограммы или пакета.
Вложенные таблицы
Определение типа вложенной таблицы может иметь следующее формальное описание:
Параметр type_name указывает имя определяемого типа, а element_type - это любой допустимый тип данных PL/SQL, исключая некоторые типы, в том числе VARRAY , TABLE , BOOLEAN , LONG , REF CURSOR и т.п.
Вложенную таблицу можно рассматривать как одномерный массив , в котором индексами служат значения целочисленного типа в диапазоне от 1 до 2147483647 . Вложенная таблица может иметь пустые элементы, которые появляются после их удаления встроенной процедурой DELETE . Вложенная таблица может динамически увеличиваться.
Индексированные таблицы
Индексированные таблицы позволяют работать со столбцами как с единой переменной - массивом.
Определение индексированной таблицы (index-by tables ) может иметь следующее формальное описание:
Индексированная таблица - это вариант вложенной таблицы , в которой элементы могут иметь произвольные целочисленные значения индексов. Такой тип данных очень удобен, если в качестве индекса использовать значение первичного ключа.
VARRAY-массивы
Определение типа Varray-массива может иметь следующее формальное описание:
Параметр type_name указывает имя определяемого типа, size_limit - максимальное количество элементов, а element_type - это любой допустимый тип данных PL/SQL, исключая некоторые типы, такие как VARRAY , TABLE , BOOLEAN , LONG , REF CURSOR и т.п.
Если типом элемента является тип "запись" , то каждое поле записи должно быть скалярного или объектного типа .
Максимальное количество элементов в Varray-массиве указывается при определении типа и не может изменяться динамически. Доступ к каждому элементу Varray-массива осуществляется по индексу. Varray-массивы можно передавать в качестве параметров. Varray-массивы не могут иметь пустот, так как для них нет операции удаления произвольного элемента массива.
Инициализация коллекций
Для инициализации коллекции используется конструктор - автоматически создаваемая функция, одноименная с типом коллекции .
Конструктор создает коллекцию из значений переданного ему списка параметров. Конструктор может быть вызван как в секции объявлений через знак присваивания после указания типа, так и в теле программы. Вызов конструктора без параметров означает инициализацию коллекции как пустой, но не устанавливает ее равной NULL .
Оператор CREATE TYPE позволяет сохранить определяемый тип в базе данных.
Конструктор можно вызывать в любом месте, где допустим вызов функции. Для того чтобы добавить в таблицу базы данных строку, одно из полей которой имеет тип коллекции, следует использовать конструктор .
Коллекции присутствую в том или ином виде в большинстве языков программирования и везде имеют схожую суть в плане использования. А именно – позволяют хранить набор объектов одного типа и проводить над всем набором какие-либо действия, либо в цикле проводить однотипные действия со всеми элементами набора.
Таким же образом коллекции используются и в Oracle.
Содержание статьи
Общие сведения о коллекциях в pl/sql
-
Создание коллекции происходит в два этапа
Сначала мы объявляем тип(type) коллекции (конструкции assoc_array_type_def, varray_type_def и nested_table_type_def будут приведены далее)
Типы коллекций
| Тип коллекции | Количество элементов | Тип индекса | Плотная или разреженная | Без инициализации | Где объявляется | Использование в SQL |
|---|---|---|---|---|---|---|
| Ассоциативный массив (index by table) | Не задано | String Pls_integer | Плотная и разреженная | Empty | PL/SQL block Package | Нет |
| Varray (variable-size array) | Задано | Integer | Только плотная | Null | PL/SQL block Package Schema level | Только определенные на уровне схемы |
| Nested table | Не задано | Integer | При создании плотная, может стать разреженной | Null | PL/SQL block Package Schema level | Только определенные на уровне схемы |
Плотность коллекции означает, что между элементами коллекции нет пропусков, пустых мест. Некоторые коллекции, как видно из таблицы, могут быть разреженными – т.е. могут иметь разрывы между элементами. Это значит, что в коллекции, например, могут быть элементы с индексом 1 и 4, а с индексом 2 и 3 элементов нет. При этом слоты памяти под 2-й и 3-й элементы будут существовать и будут принадлежать коллекции (в случае nested table), но не содержать при этом объектов и попытка прочитать содержимое этих элементов вызовет ошибку no_data_found.
Подробности можно узнать из видео-лекции в конце статьи.
Ассоциативный массив
- Набор пар ключ-значение
- Данные хранятся в отсортированном по ключу порядке
- Не поддерживает DML-операции (не может участвовать в селектах, не может храниться в таблицах)
- При объявлении как константа должен быть сразу инициализирован функцией
- Порядок элементов в ассоциативном массиве с строковым индексом зависит от параметров NLS_SORT и NLS_COMP
- Нельзя объявить тип на уровне схемы, но можно в пакете
- Не имеет конструктора
- Индекс не может принимать значение null (но допускает пустую строку — подробности и ссылка на пример в первом комментарии)
- Datatype – это любой тип данных, кроме ref cursor
- Для помещения в память небольших таблиц-справочников
- Для передачи в качестве параметра коллекции
Restrictions:
При изменении параметров NLS_SORT и NLS_COMP во время сессии после заполнения ассоциативного массива, можем получать неожиданные результаты вызовов методов first, last, next, previous. Также могут возникнуть проблемы при передаче ассоциативного массива в качестве параметра на другую БД с иными настройками NLS_SORT и NLS_COMP
Varray
Представляет собой массив последовательно хранящихся элементов
Тип описывается следующим образом (varay_type_def):
- Размер задается при создании
- Индексируется с 1
- Инициализируется конструктором
- Если параметры в конструктор не передаются, возвращается пустая коллекция
- Datatype – это любой тип данных, кроме ref cursor
- Знаем максимально возможное количество элементов
- Доступ к элементам последовательный
Restrictions:
Максимальный размер – 2 147 483 647 элементов
Nested table
Тип описывается следующим образом (nested_table_type_def):
- Размер коллекции изменяется динамически
- Может быть в разряженном состоянии, как показано на картинке
< - Инициализируется конструктором
- Если параметры в конструктор не передаются, возвращается пустая коллекция
- Datatype – это любой тип данных, кроме ref cursor
- Если содержит только одно скалярное значение, то имя колонки – Column_Value
Set operations с nested tables
Операции возможны только с коллекциями nested table. Обе коллекции, участвующие в операции, должны быть одного типа.
Результатом операции также является коллекция nested table.
| Операция | Описание |
|---|---|
| MULTISET UNION | Возвращает объединение двух коллекций |
| MULTISET UNION DISTINCT | Возвращает объединение двух коллекций с дистинктом (убирает дубли) |
| MULTISET INTERSECT | Возвращает пересечение двух коллекций |
| MULTISET INTERSECT DISTINCT | Возвращает пересечение двух коллекций с дистинктом (убирает дубли) |
| SET | Возвращает коллекцию с дистинктом (т.е. коллекцию без дублей) |
| MULTISET EXCEPT | Возвращает разницу двух коллекций |
| MULTISET EXCEPT DISTINCT | Возвращает разницу двух коллекций с дистинктом (убирает дубли) |
Небольшой пример (обратите внимание на результат операции MULTISET EXCEPT DISTINCT)
Составной тип данных хранит значения, которые имеют внутренние компоненты. Вы можете передать целые составные переменные подпрограммам в качестве параметров, а также получить доступ к внутренним компонентам составных переменных по отдельности. Внутренние компоненты могут быть скалярными или составными. Вы можете использовать скалярные компоненты везде, где вы можете использовать скалярные переменные. PL/SQL позволяет вам определять два вида составных типов данных: коллекция и запись. Вы можете использовать составные компоненты везде, где вы можете использовать составные переменные одного типа.
Записи
Коллекции
Методы коллекций
Метод коллекции - это подпрограмма PL/SQL - либо функция, которая возвращает информацию о коллекции, либо процедура, которая работает с коллекцией. Методы сбора данных упрощают использование коллекций и упрощают поддержку ваших приложений.
С нулевой коллекцией EXISTS является единственным методом сбора, который не вызывает предопределенное исключение COLLECTION_IS_NULL.
| Метод коллекции | Тип | Описание |
|---|---|---|
| DELETE | Процедура | Удаляет элементы из коллекции. |
| TRIM | Процедура | Удаляет элементы из конца varray или Nested Tables. |
| EXTEND | Процедура | Добавляет элементы в конец varray или Nested Tables. |
| EXISTS | Функция | Возвращает TRUE тогда и только тогда, когда существует определенный элемент varray или Nested Tables. |
| FIRST и LAST | Функции | FIRST возвращает первый, LAST последний индекс в коллекции. |
| COUNT | Функция | Возвращает количество элементов в коллекции. |
| LIMIT | Функция | Возвращает максимальное количество элементов, которые может иметь коллекция. |
| PRIOR и NEXT | Функции | PRIOR возвращает индекс, который предшествует указанному индексу, NEXT возвращает индекс, который следует за указанным индексом. |
Операции над коллекциями
| MULTISET UNION | Возвращает объединение двух коллекций |
| MULTISET UNION DISTINCT | Возвращает объединение двух коллекций с дистинктом (убирает дубли) |
| MULTISET INTERSECT | Возвращает пересечение двух коллекций |
| MULTISET INTERSECT DISTINCT | Возвращает пересечение двух коллекций с дистинктом (убирает дубли) |
| SET | Возвращает коллекцию с дистинктом (т.е. коллекцию без дублей) |
| MULTISET EXCEPT | Возвращает разницу (усечение) двух коллекций |
| MULTISET EXCEPT DISTINCT | Возвращает разницу (усечение) двух коллекций с дистинктом (убирает дубли) |
Исключительные ситуации коллекции.
В большинстве случаев, если вы ссылаетесь на несуществующий элемент коллекции, PL/SQL вызывает предопределенное исключение. Рассмотрим следующий пример:
/*Предположим, что выполнение продолжается, несмотря на возникшие исключения.*/- В первом случае вложенная таблица атомарно равна NULL.
- Во втором случае индекс равен NULL.
- В третьем случае индекс находится за пределами допустимого диапазона.
- В четвертом случае индекс превышает количество элементов в таблице.
- В пятом случае индекс обозначает удаленный элемент.
Следующий список показывает, когда возникает данное исключение:
| Исключение коллекции | Когда возникает |
|---|---|
| COLLECTION_IS_NULL | вы пытаетесь оперировать с атомарно нулевой коллекцией. |
| NO_DATA_FOUND | обозначает элемент, который был удален, или несуществующий элемент ассоциативного массива. |
| SUBSCRIPT_BEYOND_COUNT | индекс превышает количество элементов в коллекции. |
| SUBSCRIPT_OUTSIDE_LIMIT | индекс находится за пределами допустимого диапазона. |
| VALUE_ERROR | индекс не имеет значения или не может быть преобразован в тип ключа. Это исключение может возникнуть, если ключ определен как диапазон PLS_INTEGER, а индекс находится за пределами этого диапазона. |
В некоторых случаях вы можете передавать недопустимые индексы в метод, не вызывая исключения. Например, когда вы передаете нулевой индекс процедуре DELETE, она ничего не делает. Кроме того, вы можете заменить удаленные элементы, без возникновения NO_DATA_FOUND, как показано в следующем примере:
Из пары дестков кандидатов, с которыми довелось беседовать за три последних года, подавляющее большинство более или менее уверенно рассказывали об index-by table. Только один кандидат рассказал обо всех трех разновидностях коллекций в PL/SQL:
- ассоциативный массив, или index-by table,
- вложенная таблица, или nested table, и
- массив переменной длинны, или varray.
О них и пойдет речь в этой статье.
Все три разновидности коллекций в PL/SQL содержат элементы одного типа, заданного при определении типа коллекции, и предоставляют доступ к своим элементам по индексам. Это то общее, что их объединяет.
А их особенности приведены в следующей таблице:
| Характеристика | Ассоциативный массив | Вложенная таблица | Массив переменной длины |
|---|---|---|---|
| Количество элементов | не ограничено | не ограничено | ограничено при определении |
| Тип индекса | VARCHAR2 или PLS_INTEGER | целое число, начиная с 1 | целое число, начиная с 1 |
| Может быть разреженным? | да | да, если удалить элементы | нет |
| Может быть NULL ? | нет, будет пустым | да | да |
| Как инициализируется? | при объявлении переменной | конструктором | конструктором |
| Где определяется? | на уровне пакета или локально | на уровне схемы, пакета или локально | на уровне схемы, пакета или локально |
| Может быть атрибутом объектного типа? | нет | да, если определен на уровне схемы | да, если определен на уровне схемы |
| Можно сохранить в таблице? | нет | да, если определен на уровне схемы | да, если определен на уровне схемы |
Прежде чем пояснить и проиллюстрировать особенности коллекций, приведу методы коллекций:
| Метод коллекции | Что делает | Ограничение |
|---|---|---|
| COUNT | возвращает кол-во элементов коллекции | |
| DELETE[(index)] | удаляет элемент с индексом index , или все элементы, если index не указан | |
| EXISTS(index) | проверяет, существует ли элемент с индексом index | |
| EXTEND[(number [, index])] | добавляет в конец коллекции number элементов со значением NULL или со значением элемента index , или один NULL элемент, если не указаны number и index | не используется с ассоциативным массивом |
| FIRST | возвращает индекс первого элемента | |
| LAST | возвращает индекс последнего элемента | |
| LIMIT | возвращает максимально возможное кол-во элементов коллекции для varray и NULL для nested table | не используется с ассоциативным массивом |
| NEXT(index) | возвращает индекс следующего элемента | |
| PRIOR(index) | возвращает индекс предыдущего элемента | |
| TRIM[(number)] | удаляет number последних элементов коллекции, или один, если number не указан | не используется с ассоциативным массивом |
Разреженность коллекции означает, что при последовательном переборе всех числовых значений индекса от первого (метод FIRST ) до последнего (метод LAST ) для некоторых промежуточных значений индекса в коллекции может не быть элементов. Поскольку ассоциативный массив и вложенная таблица могут быть разреженными, то
- для проверки существования элемента нужно использовать метод EXISTS ,
- для получения следующего индекса - метод NEXT ,
- для получения предыдущего индекса - метод PRIOR .
Элементы ассоциативного массива автоматически упорядочиваются по индексам для последовательного доступа, что видно из следующего примера. Пример демонстрирует работу с ассоциативным массивом и дает неплохое представлеие о его свойствах и методах:
Следующий пример демонстрирует свойства и методы вложенной таблицы. В отличие от ассоциативного массива, элементы в который добавляются просто присваиванием значения элементу с новым индексом, для добавления элементов во вложенную таблицу необходимо вызывать метод EXTEND (и затем присваивать добавленным элементам значения):
Следующий пример демонстрирует свойства и методы массива переменной длины. Если во вложенную таблицу после ее инициализации можно добавить сколько угодно новых элементов, то максимальный размер массива переменной длины ограничен при его определении.
Поскольку массив переменной длины не может быть разреженным, то для обхода всех его элементов можно безопасно использовать цикл FOR i IN <varray>.FIRST .. <varray>.LAST LOOP .
Теперь рассмотрим работу со столбцами таблиц, в которых хранятся вложенные таблицы и массивы переменной длины. (Хранить в таблице ассоциативные массивы нельзя.)
Прежде всего, определю соответствующие коллекционные типы и таблицу со столбцами этих типов:
Как видим, для хранения вложенных таблиц - значений столбца FRIENDS - одновременно с таблицей AY_TEST "за кулисами" была создана таблица AY_FRIENDS . А вот для хранения значений массива переменной длины отдельная таблица не требуется, - значения столбца ALIASES будут храниться непосредственно в таблице AY_TEST .
Вставим запись в таблицу AY_TEST и посмотрим, что получилось:
Последний запрос показывает, что выборка прямо из таблицы, в которой хранятся значения столбца типа nested table, невозможна. Для работы с вложенной таблицей и ее отдельными строками используется конструкция TABLE() :
Теперь проделаю аналогичные манипуляции со столбцом ALIASES , который хранит массивы переменной длины.
Попытка изменить значение одного элемента varray при помощи команды UPDATE не удалась! Что дозволено вложенным таблицам, то не дозволено массивам переменной длины. Манипулировать отдельными элементами varray - обновлять, добавлять и удалять их - можно только с помощью PL/SQL и нельзя с помощью SQL.
Покажу, что конструкция TABLE() работает с вложенными таблицами и массивами переменной длины, инициализированными на месте:
С ассоциативным массивом TABLE() не работает. Более того, с ассоциативными массивами нельзя работать в SQL, можно только в PL/SQL. Ассоциативный массив нельзя инициализировать подобно тому, как инициализируются varray и nested table - у него нет инициализирующего конструктора.
Итак, были рассмотрены три разновидности коллекций PL/SQL и особенности работы с ними. За рамками статьи осталось наполнение коллекций с помощью SELECT . BULK COLLECT INTO . и использование коллекций с конструкцией FORALL для многократного выполнения одной команды DML с разными значениями связанных переменных. (Это был намек, с чем еще стоит поэкспериментировать самостоятельно.)
Читайте также: