Oracle что такое collection

Обновлено: 05.07.2024

PL/SQL lets you define two kinds of composite data types: collection and record.

A composite data type stores values that have internal components. You can pass entire composite variables to subprograms as parameters, and you can access internal components of composite variables individually. Internal components can be either scalar or composite. You can use scalar components wherever you can use scalar variables. You can use composite components wherever you can use composite variables of the same type.

If you pass a composite variable as a parameter to a remote subprogram, then you must create a redundant loop-back DATABASE LINK , so that when the remote subprogram compiles, the type checker that verifies the source uses the same definition of the user-defined composite variable type as the invoker uses.

In a collection , the internal components always have the same data type, and are called elements . You can access each element of a collection variable by its unique index, with this syntax: variable_name ( index ) . To create a collection variable, you either define a collection type and then create a variable of that type or use %TYPE .

In a record , the internal components can have different data types, and are called fields . You can access each field of a record variable by its name, with this syntax: variable_name.field_name . To create a record variable, you either define a RECORD type and then create a variable of that type or use %ROWTYPE or %TYPE .

You can create a collection of records, and a record that contains collections.

Метод EXTEND

Чтобы добавить элемент во вложенную таблицу или массив VARRAY , необходимо сначала выделить для него область памяти. Соответствующая операция, не зависящая от присваивания элементу значения, выполняется методом EXTEND . Следует помнить, что указанный метод неприменим к ассоциативным массивам.

Метод EXTEND , как уже было сказано, добавляет элементы в коллекцию. При вызове без аргументов он добавляет один элемент со значением NULL . Вызов EXTEND ( n ) присоединяет n элементов со значением NULL , а вызов EXTEND ( n,i ) — n элементов, и всем им присваивает значение i -го элемента. Последняя форма метода применяется к коллекциям, для элементов которых задано ограничение NOT NULL .

Синтаксис перегруженного метода EXTEND :

В следующем примере процедура push добавляет в список один элемент и присваивает ему новое значение:

В другом фрагменте кода метод EXTEND используется для включения в коллекцию 10 новых элементов с одинаковыми значениями. Сначала в коллекцию добавляется один элемент, которому явно присваивается нужное значение. При повторном вызове метода EXTEND в коллекцию добавляется еще 9 элементов, которым присваивается значение первого элемента коллекции new_value :

Граничные условия

Если параметр n имеет значение NULL , метод не выполнит никаких действий.

Возможные исключения

При вызове метода EXTEND для неинициализированной вложенной таблицы или VARRAY инициируется исключение COLLECTION_IS_NULL . Попытка добавить в массив VARRAY элементы, индекс которых превышает максимальный индекс массива в его объявлении, инициирует исключение SUBSCRIPT_BEYOND_LIMIT .

Oracle – табличные конвейерные функции

В промышленных системах часто требуется выполнить преобразования данных с использованием pl/sql кода с возможностью обращения к этим данным в sql запросе. Для этого в oracle используются табличные функции.

Табличные функции – это функции возвращающие данные в виде коллекции, к которой мы можем обратиться в секции from запроса, как если бы эта коллекция была реляционной таблицей. Преобразование коллекции в реляционный набор данных осуществляется с помощью функции table().

Однако такие функции имеют один недостаток, так как в них сначала полностью наполняется коллекция, а только потом эта коллекция возвращается в вызывающую обработку. Каждая такая коллекция храниться в памяти и в высоконагруженных системах это может стать проблемой. Так же в вызывающей обработке происходит простой на время наполнения коллекции. Решить данный недостаток призваны табличные конвейерные функции.

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

Рассмотрим, как создаются такие функции. В данном примере будет использована учебная схема hr и три ее таблицы: employees, departments, locations.

• employees — таблица сотрудников.
• departments — таблица отделов.
• locations — таблица географического местонахождения.

Данная схема и таблицы есть в каждой базовой сборке oracle по умолчанию.

В схеме hr я создам пакет test, в нем будет реализован наш код. Создаваемая функция будет возвращать данные по сотрудникам в конкретном отделе. Для этого в спецификации пакета нужно описать тип возвращаемых данных:

• employee_id – ид сотрудника
• first_name – имя
• last_name – фамилия
• email – электронный адрес
• phone_number – телефон
• salary – зарплата
• salary_recom – рекомендуемая зарплата
• department_id – ид отдела
• department_name — наименование отдела
• city – город

Далее опишем саму функцию:

Функция принимает на вход ид отдела и возвращает коллекцию созданного нами типа t_employees_table. Ключевое слово pipelined делает эту функцию конвейерной. В целом спецификация пакета следующая:

Рассмотрим тело пакета, в нем описано тело функции get_employees_dep:

В функции мы получаем набор данных по сотрудникам конкретного отдела, каждую строчку этого набора анализируем на предмет того, что если зарплата сотрудника меньше 8 000, то рекомендуемую зарплату устанавливаем в значение 10 000, дальше каждая строчка не дожидаясь окончания наполнения всей коллекции, отдается в вызывающую обработку. Обратите внимание, что в теле функции отсутствует ключевое слово return и присутствует pipe row (rec).

Осталось вызвать созданную функцию в pl/sql блоке:

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

Коллекции

Метод EXISTS

Метод EXISTS используется с вложенными таблицами, ассоциативными массивами и массивами VARRAY для определения наличия в коллекции заданного элемента. Если таковой имеется, метод возвращает значение TRUE , а если отсутствует — значение FALSE . Значение NULL не возвращается ни при каких условиях. Кроме того, EXISTS возвращает FALSE и в том случае, если заданный элемент был удален из коллекции с помощью метода TRIM или DELETE .

Следующий блок проверяет, присутствует ли заданный элемент в коллекции, и при положительном ответе присваивает ему значение NULL :

Граничные условия

Возможные исключения

Метод EXISTS не инициирует исключения.

Записи

Все о коллекциях в Oracle

Коллекции присутствую в том или ином виде в большинстве языков программирования и везде имеют схожую суть в плане использования. А именно – позволяют хранить набор объектов одного типа и проводить над всем набором какие-либо действия, либо в цикле проводить однотипные действия со всеми элементами набора.

Таким же образом коллекции используются и в Oracle.

Содержание статьи

Общие сведения о коллекциях в pl/sql

Типы коллекций

Тип коллекции	Количество элементов	Тип индекса	Плотная или разреженная	Без инициализации	Где объявляется	Использование в 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)

Методы PRIOR и NEXT

Методы PRIOR и NEXT используются для перемещения по коллекциям — вложенным таблицам, ассоциативным массивам и массивам VARRAY . Метод PRIOR возвращает индекс предыдущего, а метод NEXT — следующего элемента коллекции. Следующая функция возвращает сумму чисел, хранящихся в коллекции list_t :

Та же программа, но с перебором элементов от последней к первой определенной записи коллекции:

В данном случае направление перемещения не имеет значения, но в других программах оно может повлиять на результат обработки.

Граничные условия

Методы PRIOR и NEXT для инициализированной коллекции, не содержащей ни одного элемента, возвращают NULL . Если значение i больше или равно COUNT , метод NEXT возвращает NULL ; если i меньше или равно FIRST , метод PRIOR возвращает NULL .

Вплоть до версии Oracle12c, если коллекция не пуста, а параметр i больше или равен COUNT , метод PRIOR возвращает LAST ; если параметр i меньше FIRST , метод NEXT возвращает FIRST . Однако сохранение такого поведения в будущих версиях Oracle не гарантировано.

Возможные исключения

Вызов методов PRIOR и NEXT для неинициализированной вложенной таблицы или массива VARRAY генерирует исключение COLLECTION_IS_NULL .

Метод TRIM

Метод TRIM удаляет n последних элементов коллекции — вложенной таблицы или массива VARRAY . Если метод вызывается без аргументов, он удалит только один элемент. Как упоминалось ранее, при совместном использовании методов TRIM и DELETE возможна накладка: если заданный в вызове метода TRIM элемент был уже удален методом DELETE , метод TRIM «повторит» удаление, но считает его частью n, поэтому количество реально удаленных элементов окажется меньшим, чем вы рассчитывали.

Попытка вызова метода TRIM для ассоциативного массива приведет к ошибке компиляции.

Синтаксис метода TRIM :

Следующая функция извлекает из списка последнее значение и возвращает его вызывающему блоку. Операция извлечения реализуется как выборка значения с последующим усечением коллекции на один элемент:

Граничные условия

Если значение n равно NULL , метод не выполнит никаких действий.

Возможные исключения

При попытке удалить больше элементов, чем имеется в коллекции, инициируется исключение SUBSCRIPT_BEYOND_COUNT . Если метод TRIM вызывается для неинициализированной вложенной таблицы или массива VARRAY , инициируется исключение COLLECTION_IS_NULL .

Вызывая методы TRIM и DELETE для одной и той же коллекции, можно получить неожиданные результаты. На сколько элементов станет меньше в коллекции, если удалить последний элемент методом DELETE , а затем вызвать метод TRIM с тем же значением параметра? Казалось бы, это приведет к удалению двух элементов, но в действительности оба метода удалят один и тот же элемент. Чтобы избежать накладок, компания Oracle рекомендует использовать только один из этих двух методов при работе с конкретной коллекцией.

Коллекции и записи Oracle PL/SQL

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

Метод LIMIT

Метод LIMIT возвращает максимальное количество элементов, которое можно определить в массиве VARRAY . В случае вложенной таблицы или ассоциативного массива он возвращает NULL . Синтаксис этого метода:

В следующем примере перед добавлением нового элемента в конец массива VARRAY мы сначала проверяем, есть ли в нем еще свободное место:

Граничные условия

У метода LIMIT граничных условий не существует.

Возможные исключения

Вызов метода LIMIT для неинициализированной вложенной таблицы или массива VARRAY генерирует исключение COLLECTION_ IS_NULL .

Метод COUNT

Метод COUNT возвращает количество элементов в ассоциативном массиве, вложенной таблице или массиве VARRAY . В значении не учитываются элементы, удаленные из коллекции методом DELETE или TRIM .

Синтаксис вызова:

Рассмотрим пример. Прежде чем что-либо делать с коллекцией, мы проверяем, содержит ли она хотя бы один элемент:

Граничные условия

Для инициализированной коллекции, не содержащей ни одного элемента, COUNT возвращает нуль. Это же значение возвращается при вызове COUNT для пустого ассоциативного массива.

Возможные исключения

При вызове метода COUNT для неинициализированной вложенной таблицы или VARRAY инициируется заранее определенное исключение COLLECTION_IS_NULL . Такое исключение не может возникнуть при работе с ассоциативными массивами, не требующими инициализации.

Операции над коллекциями

MULTISET UNION	Возвращает объединение двух коллекций
MULTISET UNION DISTINCT	Возвращает объединение двух коллекций с дистинктом (убирает дубли)
MULTISET INTERSECT	Возвращает пересечение двух коллекций
MULTISET INTERSECT DISTINCT	Возвращает пересечение двух коллекций с дистинктом (убирает дубли)
SET	Возвращает коллекцию с дистинктом (т.е. коллекцию без дублей)
MULTISET EXCEPT	Возвращает разницу (усечение) двух коллекций
MULTISET EXCEPT DISTINCT	Возвращает разницу (усечение) двух коллекций с дистинктом (убирает дубли)

Методы коллекций

Метод коллекции - это подпрограмма 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 возвращает индекс, который следует за указанным индексом.

Исключительные ситуации коллекции.

В большинстве случаев, если вы ссылаетесь на несуществующий элемент коллекции, PL/SQL вызывает предопределенное исключение. Рассмотрим следующий пример:

/*Предположим, что выполнение продолжается, несмотря на возникшие исключения.*/

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

Следующий список показывает, когда возникает данное исключение:

Исключение коллекции	Когда возникает
COLLECTION_IS_NULL	вы пытаетесь оперировать с атомарно нулевой коллекцией.
NO_DATA_FOUND	обозначает элемент, который был удален, или несуществующий элемент ассоциативного массива.
SUBSCRIPT_BEYOND_COUNT	индекс превышает количество элементов в коллекции.
SUBSCRIPT_OUTSIDE_LIMIT	индекс находится за пределами допустимого диапазона.
VALUE_ERROR	индекс не имеет значения или не может быть преобразован в тип ключа. Это исключение может возникнуть, если ключ определен как диапазон PLS_INTEGER, а индекс находится за пределами этого диапазона.

В некоторых случаях вы можете передавать недопустимые индексы в метод, не вызывая исключения. Например, когда вы передаете нулевой индекс процедуре DELETE, она ничего не делает. Кроме того, вы можете заменить удаленные элементы, без возникновения NO_DATA_FOUND, как показано в следующем примере:

Встроенные методы коллекций PL/SQL

PL/SQL предоставляет для создаваемых вами коллекций множество встроенных функций и процедур, называемых методами коллекций. Эти методы предназначены для получения информации о содержимом коллекции и ее изменения. Их полный список приведен в табл. 1.

Метод (функция или процедура)	Описание
COUNT (функция)	Возвращает текущее значение элементов в коллекции
DELETE (процедура)	Удаляет из коллекции один или несколько элементов. Уменьшает значение, возвращаемое функцией COUNT, если заданные элементы еще не удалены. Со структурами VARRAY может использоваться только для удаления всего содержимого
EXISTS (функция)	Возвращает значение TRUE или FALSE , определяющее, существует ли в коллекции заданный элемент
EXTEND (процедура)	Увеличивает количество элементов во вложенной таблице или VARRAY , а также значение, возвращаемое функцией COUNT
FIRST, LAST (функции)	Возвращают индексы первого ( FIRST ) и последнего ( LAST ) элемента в коллекции
LIMIT (функция)	Возвращает максимальное количество элементов в массиве VARRAY
PRIOR, NEXT (функции)	Возвращают индексы элементов, предшествующих заданному ( PRIOR ) и следующему за ним ( NEXT ). Всегда используйте PRIOR и NEXT для перебора коллекций, особенно при работе с разреженными (или потенциально разреженными) коллекциями
TRIM (функция)	Удаляет элементы, начиная с конца коллекции (элемент с наибольшим индексом)

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

Рассмотрим синтаксис вызова методов на примере LAST . Эта функция возвращает наибольший индекс элемента ассоциативного массива. Стандартный вызов этой функции выглядел бы так:

Иначе говоря, ассоциативный массив передается ей в качестве аргумента. Но поскольку функция LAST является методом, она «принадлежит» объекту — в данном случае ассоциативному массиву. Правильный синтаксис ее вызова выглядит так:

В общем случае синтаксис вызова методов ассоциативного массива выглядит так:

Операция, не требующая передачи аргументов:

Операция, аргументами которой являются индексы элементов:

Например, следующая команда возвращает TRUE , если в ассоциативном массиве company_tab определена запись 15:

Методы коллекций недоступны из SQL; их можно использовать только в программах PL/SQL.

Методы FIRST и LAST

Методы FIRST и LAST возвращают соответственно наименьший и наибольший индексы элементов вложенной таблицы, ассоциативного массива или массива VARRAY . Для ассоциативных массивов, индексируемых строками, эти методы возвращают строки; «наименьшее» и «наибольшее» значения определяются порядком набора символов, используемого данным сеансом. Для других типов коллекций методы возвращают целые числа.

Синтаксис этих функций:

Так, следующий фрагмент перебирает все элементы коллекции от начала к концу:

Запомните, что такой цикл будет выполнен корректно (то есть не породит исключения NO_DATA_FOUND ) лишь при условии, что коллекция является плотной.

В следующем примере для добавления элементов в конец ассоциативного массива используется оператор COUNT . Цикл FOR с курсором используется для копирования данных из базы в ассоциативный массив. При выборке первой записи коллекция companies пуста, поэтому COUNT возвращает 0.

Граничные условия

Если методы FIRST и LAST вызываются для инициализированных коллекций, не содержащих ни одного элемента, они возвращают NULL . Для массива VARRAY , всегда содержащего хотя бы один элемент, FIRST всегда возвращает 1, а LAST — то же значение, что и метод COUNT .

Возможные исключения

При вызове методов FIRST и LAST для неинициализированной вложенной таблицы или массива VARRAY инициируется исключение COLLECTION_ IS_NULL.

Метод DELETE

Метод DELETE предназначен для удаления одного, нескольких или всех элементов ассоциативного массива, вложенной таблицы или массива VARRAY . При вызове без аргументов он удаляет все элементы коллекции. Вызов DELETE (i) удаляет i-й элемент вложенной таблицы или ассоциативного массива. А вызов DELETE ( i,j ) удаляет все элементы с индексами от i до j включительно. Если коллекция представляет собой ассоциативный массив, индексируемый строками, i и j должны быть строковыми значениями; в противном случае они являются целыми числами.

При вызове с аргументами метод резервирует место, занимавшееся «удаленным» элементом, и позднее этому элементу можно присвоить новое значение.

Фактически PL/SQL освобождает память лишь при условии, что программа удаляет количество элементов, достаточное для освобождения целой страницы памяти. (Если же метод DELETE вызывается без параметров и очищает всю коллекцию, память освобождается немедленно.)

Применительно к массивам VARRAY метод DELETE может вызываться только без аргументов. Иначе говоря, с помощью указанного метода из этой структуры нельзя удалять отдельные элементы, поскольку в таком случае она станет разреженной, что недопустимо. Единственный способ удалить из VARRAY один или несколько элементов — воспользоваться методом TRIM , предназначенным для удаления группы расположенных рядом элементов, начиная с конца коллекции.

Следующая процедура удаляет из коллекции все элементы, кроме последнего. В ней используются четыре метода: FIRST — для получения номера первого удаляемого элемента; LAST — для получения номера последнего удаляемого элемента; PRIOR — для определения номера предпоследнего элемента; DELETE — для удаления всех элементов, кроме последнего:

Несколько дополнительных примеров:

Удаление всех строк из таблицы names :

Удаление 77-й строки из таблицы globals :

Удаление из таблицы temp_reading всех элементов, начиная с индекса –15 000 и до индекса 0 включительно:

Граничные условия

Если значения индексов i и/или j указывают на несуществующие элементы, DELETE пытается «сделать наилучшее» и не генерирует исключение. Например, если таблица содержит три элемента с индексами 1, 2 и 3, то вызов метода DELETE (–5,1) удалит только один элемент с индексом 1, а вызов DELETE (–5) не изменит состояния коллекции.

Возможные исключения

Вызов метода DELETE для неинициализированной вложенной таблицы или массива VARRAY инициирует исключение COLLECTION_ IS_NULL .

Читайте также: