Все о коллекциях в Oracle из песочницы

Oracle*http://habrahabr.ru/post/254355/

Статья имеет довольно таки тезисный стиль. Более подробное содержание можно найти в приложенном внизу статьи видео с записью лекции по коллекциям Oracle.

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

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

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

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

Создание коллекции происходит в два этапа
1. Сначала мы объявляем тип(type) коллекции (конструкции assoc_array_type_def, varray_type_def и nested_table_type_def будут приведены далее)
2. Затем объявляем переменную этого типа
Обращение к элементу коллекции имеет следующий синтаксис:
```
variable_name(index)
```
Переменные типа коллекции могут принимать значение NULL (и сами элементы коллекций тоже).
Возможны многомерные коллекции (коллекции коллекций)

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

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

Ассоциативный массив

Также его называют index by table или pl/sql table.
Тип описывается следующим образом (assoc_array_type_def):.

Набор пар ключ-значение
Данные хранятся в отсортированном по ключу порядке
Не поддерживает 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
Инициализируется конструктором
```
collection_type ( [ value [, value ]... ] )
```
Если параметры в конструктор не передаются, возвращается пустая коллекция
Datatype – это любой тип данных, кроме ref cursor

Используется, если:

Знаем максимально возможное количество элементов
Доступ к элементам последовательный

Restrictions:
Максимальный размер – 2 147 483 647 элементов

Nested table

Тип описывается следующим образом (nested_table_type_def):

Размер коллекции изменяется динамически
Может быть в разряженном состоянии, как показано на картинке
<
Инициализируется конструктором
```
collection_type ( [ value [, value ]... ] )
```
Если параметры в конструктор не передаются, возвращается пустая коллекция
Datatype – это любой тип данных, кроме ref cursor
Если содержит только одно скалярное значение, то имя колонки – Column_Value
```
SELECT column_value FROM   TABLE(nested_table)
```
В комментариях к этой статье предлагают более предпочтительный вариант — он более универсальный, допускает не только скалярное значение в таблице:
```
SELECT value(t) x FROM   TABLE(nested_table) t 
```
Если параметры в конструктор не передаются, возвращается пустая коллекция

Set operations с nested tables

Операции возможны только с коллекциями nested table. Обе коллекции, участвующие в операции, должны быть одного типа.
Результатом операции также является коллекция nested table.

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

Небольшой пример

Логические операции с коллекциями

Операция	Описание
IS NULL (IS NOT NULL)	Сравнивает коллекцию со значением NULL
Сравнение =	Две коллекции nested table можно сравнить, если они одного типа и не содержат записей типа record. Они равны, если имеют одинаковые наборы элементов (не зависимо от порядка хранения элементов внутри коллекции)
IN	Сравнивает коллекцию с перечисленными в скобках
SUBMULTISET OF	Проверяет, является ли коллекция подмножеством другой коллекции
MEMBER OF	Проверяет, является ли конкретный элемент(объект) частью коллекции
IS A SET	Проверяет, содержит ли коллекция дубли
IS EMPTY	Проверяет, пуста ли коллекция

Небольшой пример использования логический операций с коллекциями

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

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

collection_name.method

Метод	Тип	Описание	Index by table	Varray	Nested table
DELETE	Процедура	Удаляет элементы из коллекции	Да	Только версия без параметров	Да
TRIM	Процедура	Удаляет элементы с конца коллекции (работает с внутренним размером коллекции)	Нет	Да	Да
EXTEND	Процедура	Добавляет элементы в конец коллекции	Нет	Да	Да
EXISTS	Функция	Возвращает TRUE, если элемент присутствует в коллекции	Да	Да	Да
FIRST	Функция	Возвращает первый индекс коллекции	Да	Да	Да
LAST	Функция	Возвращает последний индекс коллекции	Да	Да	Да
COUNT	Функция	Возвращает количество элементов в коллекции	Да	Да	Да
LIMIT	Функция	Возвращает максимальное количество элементов, которые может хранить коллекция	Нет	Да	Нет
PRIOR	Функция	Возвращает индекс предыдущего элемента коллекции	Да	Да	Да
NEXT	Функция	Возвращает индекс следующего элемента коллекции	Да	Да	Да

Delete

Delete удаляет все элементы. Сразу же очищает память, выделенную для хранения этих элементов.

Delete(n) удаляет элемент с индексом n. Память не освобождает. Элемент можно восстановить (т.е. задать новый) и он займет ту же память, что занимал предыдущий.

Delete(n, m) удаляет элементы с индексами в промежутке n..m

Если удаляемого элемента в коллекции нет, ничего не делает.

Для коллекций типа varray доступна только версия метода без параметров

Пример использования

Trim

Trim() – удаляет один элемент в конце коллекции. Если элемента нет, генерирует исключение SUBSCRIPT_BEYOND_COUNT
Trim(n) – удаляет n элементов в конце коллекции. Если элементов меньше, чем n, генерируется исключение SUBSCRIPT_BEYOND_COUNT
Работает с внутренним размером коллекции. Т.е. если последний элемент был удален с помощью Delete, вызов Trim() удалит уже удаленный ранее элемент.
Сразу очищает память, выделенную для хранения этих элементов
Лучше не использовать в сочетании с Delete()

Пример использования

Extend

EXTEND добавляет один элемент со значением null в конец коллекции
EXTEND(n) добавляет n элементов со значением null в конец коллекции
EXTEND(n,i) добавляет n копий элемента с индексом i в конец коллекции. Если коллекция имеет NOT NULL констрейнт, только этой формой можно пользоваться.
Если элементы были ранее удалены с помощью метода Delete, Extend не будет использовать сохранившиеся за коллекцией ячейки памяти, а добавит новый элемент (выделит новую память)

Пример использования

Exists

Для удаленных элементов возвращает false
При выходе за границы коллекции возвращает false

Пример использования

First и Last

Для varray First всегда возвращает единицу, Last всегда возвращает то же значение, что и Count

Пример использования

Count

Пример использования

Limit

Для varray возвращает максимально допустимое количество элементов в коллекции, для остальных коллекций возвращает null

Пример использования

Prior и Next

Позволяют перемещаться по коллекции
Возвращают индекс предыдущего/следующего элемента (или null, если элемента нет)

Пример использования

Bulk Collect

Возвращает результаты sql-оператора в PL/SQL пачками, а не по одному
SELECT BULK COLLECT INTO
FETCH BULK COLLECT INTO [LIMIT]
RETURNING BULK COLLECT INTO
Не работает с ассоциативными массивами (кроме тех, что индексированы pls_integer)

Пример использования

Цикл forall

посылает DML операторы из PL/SQL в SQL пачками, а не по одному
может содержать только один DML оператор
для разряженных коллекций используется форма:
```
FORALL i IN INDICES OF cust_tab
```
(конструкция не работает для ассоциативных массивов, индексированных строками)
с разряженными коллекциями (или с частью коллекции) удобно работать с помощью индекс-коллекций (of pls_integer). Пример использования:
```
FORALL i IN VALUES OF rejected_order_tab
```
Некоторые детали работы forall можно найти в этой статье habrahabr.ru/post/125893/

SQL%BULK_ROWCOUNT – коллекция, содержит количество строк, на которые повлиял каждый dml оператор
SQL%ROWCOUNT – общее количество строк, на которые повлияли dml-операторы в цикле forall

Пример использования

Exceptions in forall

При возникновении исключения в любом из dml-операторов в цикле, транзакция полностью откатывается
Если описать обработчик ошибок, в нем можно зафиксировать успешно выполнившиеся операторы dml (это те операторы, которые выполнились до возникновения исключения).
Конструкция
```
FORALL j IN collection.FIRST.. collection.LAST SAVE EXCEPTIONS 
```
Генерит ORA-24381 в конце, если в цикле возникали исключения
SQL%BULK_EXCEPTIONS – коллекция, содержащая информацию о всех сгенерированных во время выполнения цикла исключениях
.Count
.ERROR_INDEX – значение индекса j, при котором произошло исключение (sql%bulk_exception(i).error_index)
.ERROR_CODE — код возникшей ошибки. Информацию об ошибке можно извлечь с помощью функции sqlerrm: SQLERRM(-(SQL%BULK_EXCEPTIONS(i).ERROR_CODE))

Collection exceptions

COLLECTION_IS_NULL – попытка работать с неинициализированной коллекцией
NO_DATA_FOUND – попытка прочитать удаленный элемент
SUBSCRIPT_BEYOND_COUNT – выход за границы коллекции
SUBSCRIPT_OUTSIDE_LIMIT – индекс вне предела допустимого диапазона
VALUE_ERROR – индекс равен null или не конвертируется в integer

Примеры ситуаций, генерирующих исключения

DBMS_SESSION.FREE_UNUSED_USER_MEMORY

Процедура DBMS_SESSION.FREE_UNUSED_USER_MEMORY возвращает неиспользуемую более память системе
В документации Oracle процедуру советуют использовать «редко и благоразумно».
В случае подключения в режиме Dedicated Server вызов этой процедуры возвращает неиспользуемую PGA память операционной системе
В случае подключения в режиме Shared Server вызов этой процедуры возвращает неиспользуемую память в Shared Pool

В каких случаях нужно освобождать память:

Большие сортировки, когда используется вся область sort_area_size
Компиляция больших PL/SQL пакетов, процедур или функций
Хранение больших объемов данных в индексных таблицах PL/SQL

Пример использования

Видео-запись лекции, по материалам которой и была написана эта статья:

+15

7028

144

MoscowDevelopmentTeam 12,4

Автоматизированное
продвижение сайтов

50% экономии на ссылках
Запуск проекта за 10 минут
Вывод и удержание в ТОП 10

Подробнее

Похожие публикации

Опубликован официальный Node.JS-драйвер для базы данных Oracle 31 января в 10:02

Partition Option & Oracle Server SE One 9 декабря 2014 в 10:04

Oracle Database Appliance – обзор после опыта использования 25 марта 2014 в 15:24

Oracle WebLogic Server, Oracle Database 12c, Oracle Linux официально доступны в Windows Azure 24 сентября 2013 в 09:43

Партнерство Oracle и Microsoft: Oracle Database, WebLogic Server, Oracle Linux и Java в облаке Windows Azure 26 июня 2013 в 09:56

Часть 1. Как установить Oracle Database 11g Release 2 RAC на Linux используя VirtualBox – первый шаг к кластеризации 11 декабря 2012 в 10:56

Разбираем XML средствами Oracle database 23 сентября 2011 в 14:01

Представляем данные Lotus Domino в Oracle Database с помощью Oracle Data Cartridge Interface и Domino Java API 30 августа 2011 в 16:51

10 июня 2011 — Oracle Database Machine была сертифицирована SAP 7 июля 2011 в 11:56

Важные изменения в Oracle Database Patch Sets начиная с 11.2.0.2 13 сентября 2010 в 09:24

Комментарии (9)

xtender30 марта 2015 в 15:26#

Зачет! Времени на это ушло, наверное, много…

Небольшие дополнения/правки от меня:

«Использование в SQL»: ассоциативные в 12c уже могут биндиться в SQL из PL/SQL если index by PLS_INTEGER:
Link1
Link2
Мне кажется, что надо уточнить то, что вы имели ввиду под "Не поддерживает DML-операции" у ассоциативных массивов. Я бы лучше сказал, что ассоциативные массивы, в отличие от varray и nested table, не могут храниться в таблицах.
У VARRAY метод DELETE() существует, но нет методов delete(m), delete(m,n)
«Индекс не может принимать значение null» — зато может принимать пустой литерал ''
«Процедура DBMS_SESSION.FREE_UNUSED_USER_MEMORY возвращает неиспользуемую более память системе» — не сразу и еще может быть полезно

MoscowDevelopmentTeam1 апреля 2015 в 14:08#

↵

↑

Привет!
Спасибо за комменты и ссылки, немного подправил статью по ним.

В наших лекциях материал актуален для версии 11.2 – именно такая версия у нас на продуктиве и в ближайшие как минимум пару лет вряд ли будем апгрейдить. Возможно, позже запишем несколько презентаций по нововведениям версии 12с (заодно и сами ознакомимся).

По поводу «не поддерживает dml операции» — это фраза из документации «Cannot be manipulated with DML statements» :)

Про метод Delete у varray – отличное замечание, спасибо! Поправил в статье.

С пустой строкой в качестве индекса – тоже интересная деталь. Добавил в статью.

А инфа про процедуру DBMS_SESSION.FREE_UNUSED_USER_MEMORY добавлена в статью «факультативно». Тема с памятью (выделение/освобождение) уже ближе к админству и конкретно для меня туманна.В наших первых лекциях материал пока не настолько глубокий, чтобы в эту тему нырять. Может в будущем попробуем разобраться в вопросе.

xtender30 марта 2015 в 15:41#

Еще насчет column_value: все-таки для унификации желательнее использовать value(), тогда легко и однотипно можно работать с конкретным возвращаемым типом. Пример:

declare 
   cursor c1 is SELECT value(t) x FROM TABLE(ku$_objnumset(1)) t;
   cursor c2 is SELECT value(t) x FROM TABLE(ku$_objnumpairlist(ku$_objnumpair(10,20))) t;
begin
   for r in c1 loop
      dbms_output.put_line(r.x);
   end loop;
   for r in c2 loop
      dbms_output.put_line(r.x.num1);
   end loop;
end;
.

MoscowDevelopmentTeam1 апреля 2015 в 14:08#

↵

↑

Супер! Добавил в статью.

xtender30 марта 2015 в 16:10#

Ещё про нюансы forall я тут писал: habrahabr.ru/post/125893/

vosolovskiy30 марта 2015 в 16:24 (комментарий был изменён)#

Стоило бы добавить о применении varray и nested table в чистом SQL:
1. Функции TABLE(), THE(), COLLECT() и др.: www.toadworld.com/platforms/oracle/w/wiki/1797.collection-nested-table-functions.aspx
2. Необходимость CAST() в некоторых случаях.
3. Unnesting вложенных коллекций в запросах.
4. Различия в хранении varray vs nested table: docs.oracle.com/database/121/ADOBJ/adobjdes.htm#ADOBJ7449

xtender30 марта 2015 в 17:00#

↵

↑

«THE» был deprecated еще в 9i: docs.oracle.com/cd/A91202_01/901_doc/server.901/a90120/ch4_dep.htm#6669

Use of «THE (subquery)» Expression

In releases prior to Oracle8, the table_collection_expression clause was expressed as «THE (subquery)». That usage is desupported.

Хотя он еще и работает для совместимости

vosolovskiy30 марта 2015 в 17:21#

↵

↑

Спасибо за замечание про deprecated. Упомянул ее скорее «автоматом», глянув на список по ссылке. Сам им не пользовался за ненадобностью при наличии TABLE().

xtender30 марта 2015 в 17:15#

↵

↑

Кстати, очень показателен EXPAND_SQL для запроса с THE:
Исходный:

select value(c) x, the(select dummy from dual) x2 from the(select ku$_vcnt('RED2', 'GREEN2', 'BLUE2') x3 from dual) c

После трансформации:

SELECT "A1"."COLUMN_VALUE" "X"
      ,(SELECT "A3"."DUMMY" "DUMMY" FROM "SYS"."DUAL" "A3") "X2"
FROM  (
      SELECT VALUE(A4) "COLUMN_VALUE" 
      FROM TABLE(
                  (
                   SELECT "KU$_VCNT"('RED2','GREEN2','BLUE2') "X3" 
                   FROM "SYS"."DUAL" "A2"
                  )
                ) "A4"
      ) "A1"

Из записной книжки ...

Страницы

суббота, 4 апреля 2015 г.

ВСЕ О КОЛЛЕКЦИЯХ В ORACLE.habrahabr.ru

Все о коллекциях в Oracle из песочницы

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

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

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

Ассоциативный массив

Varray

Nested table

Set operations с nested tables

Логические операции с коллекциями

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

Delete

Trim

Extend

Exists

First и Last

Count

Limit

Prior и Next

Bulk Collect

Цикл forall

Exceptions in forall

Collection exceptions

DBMS_SESSION.FREE_UNUSED_USER_MEMORY

Комментарии (9)

Комментариев нет:

Отправить комментарий

Страницы

суббота, 4 апреля 2015 г.

ВСЕ О КОЛЛЕКЦИЯХ В ORACLE.habrahabr.ru

Все о коллекциях в Oracle из песочницы

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

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

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

Ассоциативный массив

Varray

Nested table

Set operations с nested tables

Логические операции с коллекциями

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

Delete

Trim

Extend

Exists

First и Last

Count

Limit

Prior и Next

Bulk Collect

Цикл forall

Exceptions in forall

Collection exceptions

DBMS_SESSION.FREE_UNUSED_USER_MEMORY

Комментарии (9)

Комментариев нет:

Отправить комментарий

суббота, 4 апреля 2015 г.