Знакомство с коллекциями PL/SQL

Коллекции PL/SQLКоллекцией называется структура данных, по своей функциональности сходная со списком или одномерным массивом. В сущности, коллекция — ближайший аналог традиционных массивов в программах PL/SQL. Эта статья поможет вам решить, какой из трех разных типов коллекций (ассоциативный массив, вложенная таблица, VARRAY) лучше всего соответствует потребностям вашей программы. Также вы познакомитесь с примерами определения этих структур и работы с ними.



Несколько типичных ситуаций для применения коллекций:

  • Ведение списков данных в программах. Вероятно, это самое частое применение коллекций в программах. Да, с таким же успехом можно воспользоваться реляционными таблицами, глобальными временными таблицами (работа с которыми потребует частых переключений контекста) или строками с разделителями, но коллекции чрезвычайно эффективны, а код работы с ними получается очень выразительным и простым в сопровождении.
  • Ускорение многострочных операций SQL на порядок и более. Использование коллекций в сочетании с конструкциями FORALL и BULK COLLECT радикально повышает производительность многострочных операций SQL.
  • Кэширование информации базы данных. Коллекции хорошо подходят для кэширования статической информации, которая часто запрашивается в ходе одного сеанса (или просто многократно запрашивается в ходе выполнения одной программы) для повышения производительности поиска.

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

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

  • Общие сведения о коллекциях — мы начнем со знакомства с основными понятиями: описанием разных видов коллекций, терминологии коллекций, содержательными примерами для каждой разновидности коллекций, рекомендациями по выбору коллекции для конкретных ситуаций. Даже если вы ограничитесь чтением только этого раздела, скорее всего, вы сможете написать базовую логику коллекций. Однако я настоятельно рекомендую читать дальше!
  • Методы коллекций — затем мы изучим многочисленные методы (процедуры и функции), которые Oracle предоставляет для просмотра и выполнения операций с содержимым коллекций. Практически любое применение коллекций сопряжено с использованием этих методов, поэтому вы должны хорошо понимать, что и как они делают.
  • Работа с коллекциями — от «азов» можно перейти к нетривиальным аспектам работы с коллекциями, включая процесс инициализации, необходимый для вложенных таблиц и VARRAY, разные способы заполнения коллекций и обращения к их данным, операции со столбцами на языке SQL и коллекции, индексируемые строками.
  • Операции мультимножеств со вложенными таблицами — в Oracle Database 10g реализация вложенных таблиц была дополнена возможностью выполнения операций множеств с содержимым вложенных таблиц (объединение, пересечение, вычитание и т. д.). Две вложенные таблицы можно сравнить на равенство и неравенство.
  • Сопровождение коллекций на уровне схемы — вложенные таблицы и типы VARRAY можно определять в самой базе данных. В базе данных содержатся представления словарей данных, используемые для сопровождения этих типов.

Начнем с обзора основных концепций и терминологии коллекций, описания разных типов коллекций и примеров.

 

Концепции и терминология

Следующие пояснения помогут читателю быстрее освоить эти структуры данных.

  • Элементы и индексы. Коллекция состоит из множества элементов (фрагментов данных), причем каждый элемент находится в определенной позиции списка, то есть обладает определенным индексом. Иногда элементы называются «строками», а индексы — «номерами строк».
  • Тип коллекции. Каждая переменная, представляющая коллекцию в программе, должна быть объявлена на основании заранее определенного типа коллекции. Как упоминалось ранее, коллекции делятся на три широкие категории: ассоциативные массивы, вложенные таблицы и VARRAY. В этих категориях существуют конкретные типы, которые определяются командой TYPE в разделе объявлений блока. Далее программист объявляет и использует экземпляры этих типов в своих программах.
  • Коллекция, или экземпляр коллекции. Этот термин может иметь несколько значений:
    • переменная PL/SQL типа ассоциативного массива, вложенной таблицы или VARRAY;
    • столбец таблицы, где хранятся значения типа вложенной таблицы или массива VARRAY.

Независимо от конкретного использования коллекция всегда остается списком элементов.

Экземпляром коллекции называется экземпляр конкретного типа коллекции.

Отчасти из-за синтаксиса и названий, которые были выбраны для поддержки коллекций Oracle, коллекции также иногда называются массивами и таблицами.

  • Однородные элементы. Все элементы коллекции должны относиться к одному типу, то есть коллекция является однородной. Впрочем, этот тип данных может быть составным; например, можно объявить коллекцию, состоящую из записей, то есть таблицу. Начиная с Oracle9i, поддерживается возможность объявления многоуровневых коллекций: полем коллекции служит коллекция или запись, полем которой, в свою очередь, также является коллекция или запись и т. д.
  • Одномерная коллекция. В каждой строке коллекции имеется всего одно поле, и с этой точки зрения она похожа на одномерный массив. Нельзя объявить коллекцию, на элементы которой можно было бы ссылаться следующим образом:
       my_collection (10, 44) 

Двумерные структуры в настоящее время напрямую не поддерживаются, однако вы можете создать многомерный массив, объявляя коллекцию коллекцией, — синтаксис будет примерно таким:

      my_collection (44) (10)
  • Ограниченная и неограниченная коллекция. Коллекция называется ограниченной, если заранее определены границы возможных значений индексов (номеров) ее элементов. Если же верхняя или нижняя граница номеров элементов не указана, то коллекция называется неограниченной. Коллекции типа VARRAY (массивы переменной длины) всегда ограничены. При определении такой коллекции следует указать максимальное количество ее элементов (номер первого элемента всегда равен 1). Вложенные таблицы и ассоциативные массивы ограничиваются только
    теоретически. В тексте они будут описываться как неограниченные, потому что с теоретической точки зрения максимальное количество элементов в них может быть произвольным.
  • Разреженные и плотные коллекции. Коллекция (или массив, или список) называется плотной, если все ее элементы, от первого до последнего, определены и каждому из них присвоено некоторое значение (таковым может быть и NULL). Коллекция считается разреженной, если отдельные ее элементы отсутствуют, как в рассмотренном выше примере. Не обязательно определять все элементы коллекции и заполнять ее полностью. Массивы VARRAY всегда являются плотными. Вложенные таблицы первоначально всегда плотные, но по мере удаления некоторых элементов становятся разреженными. Ассоциативные массивы могут быть как разреженными, так и плотными в зависимости от способа их заполнения. Разреженность — это очень ценное свойство, позволяющее добавлять элементы в коллекцию по первичному ключу или другим ключевым данным (например, номеру записи). Таким образом можно задать определенный порядок следования данных в коллекции или значительно ускорить их поиск.
  • Целочисленное индексирование. К любому элементу коллекции можно обратиться по номеру, который представляет собой целочисленное значение. Объявление ассоциативного массива явно выражает это требование условием INDEX BY, но правило действует и для других типов коллекций.
  • Строковое индексирование. Начиная с Oracle9i Release 2, в качестве индексов ассоциативных массивов можно использовать не только номера, но и символьные строки (в настоящее время до 32 Кбайт длиной). Эта возможность не поддерживается ни для вложенных таблиц, ни для массивов VARRAY.
  • Внешняя таблица. Так называют таблицу, содержащую столбец типа вложенной таблицы или массива VARRAY.
  • Внутренняя таблица. Так принято называть коллекцию, содержащуюся в столбце таблицы.
  • Вспомогательная таблица. Физическая таблица, создаваемая Oracle для хранения внутренней таблицы (столбца, содержащего вложенную таблицу).

 

Разновидности коллекций

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

  • Ассоциативные массивы. Это одномерные неограниченные разреженные коллекции, состоящие из однородных элементов, доступные только в PL/SQL. Ранее в PL/SQL 2 (из поставки Oracle7) они назывались таблицами PL/SQL, а в Oracle8 и Oracle8i — индексируемыми таблицами (поскольку при объявлении такой коллекции приходилось явно указывать, что она «индексируется» номером строки). В Oracle9i Release 1 они были названы ассоциативными массивами. Этот термин выбран потому, что предложение INDEX BY может использоваться для индексирования содержимого коллекций посредством значений типа VARCHAR2 или PLS_INTEGER.
  • Вложенные таблицы. Так называются одномерные несвязанные коллекции, также состоящие из однородных элементов. Первоначально они заполняются полностью, но позднее из-за удаления некоторых элементов могут стать разреженными. Вложенные таблицы могут определяться и в PL/SQL, и в базах данных (например, в качестве столбцов таблиц). Вложенные таблицы представляют собой мультимножества, то есть элементы вложенной таблицы не упорядочены.
  • Массив типа VARRAY. Подобно двум другим типам коллекций, массивы VARRAY (массивы переменной длины) также являются одномерными коллекциями, состоящими из однородных элементов. Однако их размер всегда ограничен, и они не бывают разреженными. Как и вложенные таблицы, массивы VARRAY используются и в PL/SQL, и в базах данных. Однако порядок их элементов при сохранении и выборке, в отличие от вложенных таблиц, сохраняется.

 

Примеры коллекций

В этом разделе представлены относительно простые примеры всех разновидностей коллекций с описанием их основных характеристик.

 

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

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

1 DECLARE
2    TYPE list_of_names_t IS TABLE OF person.first_name%TYPE
3       INDEX BY PLS_INTEGER;
4    happyfamily list_of_names_t;
5    l_row PLS_INTEGER;
6 BEGIN
7    happyfamily (2020202020) := 'Eli';
8    happyfamily (-15070) := 'Steven';
9    happyfamily (-90900) := 'Chris';
10  happyfamily (88) := 'Veva';
11
12  l_row := happyfamily.FIRST;
13
14  WHILE (l_row IS NOT NULL)
15  LOOP
16     DBMS_OUTPUT.put_line (happyfamily (l_row));
17     l_row := happyfamily.NEXT (l_row);
18  END LOOP;
19 END;
Результат:
Chris
Steven
Veva
Eli
Строки Описание
2–3 Объявление ассоциативного массива TYPE с характерной секцией INDEX BY. Коллекция, созданная на основе этого типа, содержит список строк, каждая из которых может достигать по длине столбца first_name таблицы person
4 Объявление коллекции happyfamily на базе типа list_of_names_t
9–10 Заполнение коллекции четырьмя именами. Обратите внимание: мы можем использовать любые целочисленные значения по своему усмотрению. Номера строк в ассоциативном массиве не обязаны быть последовательными; они даже могут быть отрицательными! Никогда не пишите код с произвольно выбранными, непонятными значениями индексов! Этот пример всего лишь демонстрирует гибкость ассоциативных массивов
12 Вызов метода FIRST (функция, «прикрепленная» к коллекции) для получения первого (минимального) номера строки в коллекции
14–18 Перебор содержимого коллекции в цикле WHILE, с выводом каждой строки. В строке 17 вызывается метод NEXT, который переходит от текущего элемента к следующему без учета промежуточных пропусков

 

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

Следующий пример начинается с объявления типа вложенной таблицы как типа уровня схемы. На основании этого типа в блоке PL/SQL объявляются три вложенные таблицы. Имена членов семьи сохраняются во вложенной таблице happyfamily, имена детей — во вложенной таблице children, после чего команда MULTISET EXCEPT (появившаяся в Oracle10g) используется для извлечения из вложенной таблицы happyfamily только имен родителей. Более подробное объяснение приводится после листинга. 

REM Раздел A
SQL> CREATE TYPE list_of_names_t IS TABLE OF VARCHAR2 (100);
2 /
Type created.
REM Раздел B
1 DECLARE
2    happyfamily list_of_names_t := list_of_names_t ();
3    children list_of_names_t := list_of_names_t ();
4    parents list_of_names_t := list_of_names_t ();
5 BEGIN
6    happyfamily.EXTEND (4);
7    happyfamily (1) := 'Eli';
8    happyfamily (2) := 'Steven';
9    happyfamily (3) := 'Chris';
10  happyfamily (4) := 'Veva';
11
12  children.EXTEND;
13  children (1) := 'Chris';
14  children.EXTEND;
15  children (2) := 'Eli';
16
17  parents := happyfamily MULTISET EXCEPT children;
18
19  FOR l_row IN parents.FIRST .. parents.LAST
20  LOOP
21     DBMS_OUTPUT.put_line (parents (l_row));
22  END LOOP;
23 END;

Результат:

Steven
Veva 
Строки Описание
Раздел А Команда CREATE TYPE создает тип вложенной таблицы в самой базе данных. Такое решение позволяет объявлять вложенные таблицы в любой схеме, обладающей разрешениями EXECUTE для этого типа. Также можно объявлять столбцы в реляционных таблицах этого типа
2–4 Объявление трех разных вложенных таблиц, созданных на основе типа уровня схемы. Обратите внимание: в каждом случае для инициализации вложенной таблицы вызывается функция-конструктор. Имя этой функции всегда совпадает с именем типа, а ее код автоматически генерируется Oracle. Вложенную таблицу необходимо инициализировать перед использованием
6 Вызов метода EXTEND «выделяет место» во вложенной таблице для данных членов семьи. В отличие от ассоциативных массивов, во вложенных таблицах необходимо явно выполнить операцию создания элемента, прежде чем размещать в нем данные
7–10 Заполнение коллекции happyfamily именами членов семьи
12–15 Заполнение коллекции children. В данном случае данные добавляются в коллекцию по строкам
17 Чтобы получить информацию о родителях, достаточно убрать из happyfamily данные children. Эта задача особенно легко решается, начиная с версии Oracle10g, с появлением высокоуровневых команд для работы с множествами вроде MULTISET EXCEPT (близкий аналог коллекции SQL MINUS). Обратите внимание: перед заполнением parents вызывать метод EXTEND не нужно. База данных делает это автоматически
19–22 Поскольку операция MULTISET EXCEPT плотно заполняет коллекцию parents, для перебора содержимого коллекции можно воспользоваться циклом FOR со счетчиком. При использовании этой конструкции с разреженной коллекцией произойдет исключение NO_DATA_FOUND

 

VARRAY

Следующий пример демонстрирует использование типов VARRAY в качестве столбцов реляционной таблицы. Сначала мы объявляем два разных типа VARRAY уровня схемы, после чего создаем реляционную таблицу family с двумя столбцами VARRAY. Наконец, в коде PL/SQL заполняются две локальные коллекции, используемые при выполнении операции INSERT с таблицей family. Более подробное объяснение приводится после листинга.

REM Раздел A
SQL> CREATE TYPE first_names_t IS VARRAY (2) OF VARCHAR2 (100);
2 /
Type created.
SQL> CREATE TYPE child_names_t IS VARRAY (1) OF VARCHAR2 (100);
2 /
Type created.
REM Раздел B
SQL> CREATE TABLE family (
2    surname VARCHAR2(1000)
3    , parent_names first_names_t
4    , children_names child_names_t
5 );
Table created.
REM Раздел C
SQL>
1 DECLARE
2    parents first_names_t := first_names_t ();
3    children child_names_t := child_names_t ();
4 BEGIN
5    parents.EXTEND (2);
6    parents (1) := 'Samuel';
7    parents (2) := 'Charina';
8    --
9    children.EXTEND;
10  children (1) := 'Feather';
11
12  --
13  INSERT INTO family
14    ( surname, parent_names, children_names )
15  VALUES ( 'Assurty', parents, children );
16 END;
SQL> /
PL/SQL procedure successfully completed.
SQL> SELECT * FROM family
2 /
SURNAME
PARENT_NAMES
CHILDREN_NAMES
--------------------------------------------
Assurty
FIRST_NAMES_T('Samuel', 'Charina')
CHILD_NAMES_T('Feather')
Строки Описание
Раздел А Команда CREATE TYPE используется для создания двух типов VARRAY. Обратите внимание: с типом VARRAY необходимо задать максимальную длину коллекции. По сути мои объявления определяют некое подобие «социальной политики»: не более двух родителей и не более одного ребенка
Раздел B Создание реляционной таблицы из трех столбцов: VARCHAR2 для фамилии и двух столбцов VARRAY (для родителей и детей)
Раздел C, строки 2–3 Объявление двух локальных экземпляров VARRAY на основании типа уровня схемы. По аналогии с вложенными таблицами (и в отличие от ассоциативных массивов), мы должны вызвать функцию-конструктор, имя которой совпадает с именем TYPE, для инициализации структур
5–10 Расширение и заполнение коллекций; добавляются имена родителей и одного ребенка. При попытке добавления второго ребенка произойдет ошибка
13–15 Чтобы вставить строку в таблицу family, достаточно включить VARRAY в список значений. Как видите, в Oracle вставка коллекций в реляционную таблицу выполняется тривиально!

 

Использование коллекций

В следующих разделах описываются особенности объявления и применения коллекций в разных местах программного кода. Поскольку типы коллекций могут определяться в базе данных (только вложенные таблицы и VARRAY), эти структуры можно задействовать не только в программах PL/SQL, но и в таблицах и объектных типах.

 

Коллекции как компоненты записи

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

CREATE OR REPLACE TYPE color_tab_t IS TABLE OF VARCHAR2(100)
/
DECLARE
TYPE toy_rec_t IS RECORD (
manufacturer INTEGER,
shipping_weight_kg NUMBER,
domestic_colors color_tab_t,
international_colors color_tab_t
);

 

Коллекции в качестве параметров программы

Коллекции также могут передаваться в параметрах функций и процедур. Формат объявления параметра остается неизменным: 

имя_параметра [ IN | IN OUT | OUT ] тип_параметра
[ [ NOT NULL ] [ DEFAULT | := значение_по_умолчанию ] ]

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

  • Определение типа на уровне схемы конструкцией CREATE TYPE.
  • Объявление типа коллекции в пакете.
  • Объявление типа из внешней области действия в определении модуля.

Пример использования типа уровня схемы: 

CREATE TYPE yes_no_t IS TABLE OF CHAR(1);
/
CREATE OR REPLACE PROCEDURE act_on_flags (flags_in IN yes_no_t)
IS
BEGIN
...
END act_on_flags;
/

Пример использования типа коллекции, определяемого в спецификации пакета: существует только один способ объявления ассоциативного массива логических значений (и любых других базовых типов данных), так почему бы не определить их в спецификации пакета для последующих ссылок в приложении? 

/* Файл в Сети: aa_types.pks */
CREATE OR REPLACE PACKAGE aa_types
IS
TYPE boolean_aat IS TABLE OF BOOLEAN INDEX BY PLS_INTEGER;
...
END aa_types;
/

Внимание: тип коллекции в списке параметров должен уточняться именем пакета:

CREATE OR REPLACE PROCEDURE act_on_flags (
flags_in IN aa_types.boolean_aat)
IS
BEGIN
...
END act_on_flags;
/ 

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

DECLARE
TYPE birthdates_aat IS VARRAY (10) OF DATE;
l_dates birthdates_aat := birthdates_aat (); 
BEGIN
l_dates.EXTEND (1);
l_dates (1) := SYSDATE;
DECLARE
FUNCTION earliest_birthdate (list_in IN birthdates_aat) RETURN DATE
IS
BEGIN
...
END earliest_birthdate;
BEGIN
DBMS_OUTPUT.put_line (earliest_birthdate (l_dates));
END;
END;

 

Коллекции как типы данных для значений, возвращаемых функцией

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

FUNCTION true_colors (whose_id IN NUMBER) RETURN color_tab_t
AS
l_colors color_tab_t;
BEGIN
SELECT favorite_colors INTO l_colors
FROM personality_inventory
WHERE person_id = whose_id;
RETURN l_colors;
END;

Как использовать эту функцию в программе PL/SQL? Поскольку она способна заменить переменную типа color_type_t, то вы можете либо присвоить возвращаемое ею значение переменной типа коллекции, либо присвоить переменной (тип которой совместим с типом элементов коллекции) один из элементов возвращаемой коллекции. Первый способ весьма прост. Однако обратите внимание, что это один из тех случаев, когда переменную коллекции не обязательно инициализировать явно:

DECLARE
color_array color_tab_t;
BEGIN
color_array := true_colors (8041);
END;

При втором способе сразу после вызова функции необходимо указать индекс требуемого элемента коллекции:

DECLARE
one_of_my_favorite_colors VARCHAR2(30);
BEGIN
one_of_my_favorite_colors := true_colors (8041) (1);
END; 

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

 

Коллекции как «столбцы» таблицы базы данных

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

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

Поле Id
(NUMBER)
Поле Name
(VARCHAR2)
Поле Dependents_ages
(Dependent_birthdate_t)
10010 Zaphod Beeblebrox 12-JAN-1763
4-JUL-1977
22-MAR-2021
10020 Molly Squiggly 15-NOV-1968
15-NOV-1968
10030 Joseph Josephs  
10040 Cepheus Usrbin 27-JUN-1995
9-AUG-1996
19-JUN-1997
10050 Deirdre Quattlebaum 21-SEP-1997

Создать такую таблицу несложно. Сначала нужно определить тип коллекции:

CREATE TYPE Dependent_birthdate_t AS VARRAY(10) OF DATE; 

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

CREATE TABLE employees (
id NUMBER,
name VARCHAR2(50),
...другие столбцы...,
dependents_ages dependent_birthdate_t
); 

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

INSERT INTO employees VALUES (42, 'Zaphod Beeblebrox', ...,
dependent_birthdate_t( '12-JAN-1765', '4-JUL-1977', '22-MAR-2021')); 

Теперь рассмотрим пример использования столбца типа вложенной таблицы. Создавая внешнюю таблицу personality_inventory, Oracle необходимо указать имя таблицы для записи данных столбца типа вложенной таблицы: 

CREATE TABLE personality_inventory (
person_id NUMBER,
favorite_colors color_tab_t,
date_tested DATE,
test_results BLOB)
NESTED TABLE favorite_colors STORE AS favorite_colors_st;

Конструкция NESTED TABLE...STORE AS сообщает Oracle, что таблица для хранения данных столбца favorite_colors (вспомогательная таблица) должна иметь имя favorite_colors_st. Эта таблица будет храниться отдельно от остальных данных таблицы personality_inventory. Ограничений на ее размер не существует.

Данные вспомогательной таблицы нельзя обрабатывать непосредственно. Попытка прямого считывания или записи в нее информации приведет к возникновению ошибки. Чтение или запись атрибутов этой таблицы возможны только через ссылку на внешнюю таблицу (псевдофункции коллекций рассматриваются далее в разделе «Работа с коллекциями в SQL»). Физические атрибуты вспомогательной таблицы также нельзя задать напрямую — они наследуются от «самой внешней» таблицы.

Главное различие между вложенными таблицами и структурами VARRAY проявляется при использовании их в качестве типов данных столбцов. Хотя массив VARRAY, подобно вложенной таблице, позволяет сохранить в одном столбце множество значений, для него необходимо указать максимальную длину массива, который будет храниться в таблице вместе с остальными данными. Поэтому разработчики компании Oracle рекомендуют использовать столбцы типа VARRAY для «маленьких» массивов, а вложенные таблицы — для «больших».

 

Коллекции как атрибуты объектного типа

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

CREATE TYPE auto_spec_t AS OBJECT (
make VARCHAR2(30),
model VARCHAR2(30),
available_colors color_tab_t
);

Поскольку объектный тип не требует места для хранения данных, в его определении конструкцией CREATE TYPE ... AS OBJECT не нужно указывать имя вспомогательной таблицы. Оно задается позже, когда будем создавать таблицу со столбцом этого типа:

CREATE TABLE auto_specs OF auto_spec_t
NESTED TABLE available_colors STORE AS available_colors_st;

Приведенная выше инструкция требует пояснений. Создавая таблицу объектов, Oracle просматривает объявление объектного типа, чтобы определить, какие столбцы должна содержать такая таблица. Обнаружив, что один из столбцов (а именно available_colors) является вложенной таблицей, Oracle создает отдельную таблицу для хранения его данных. Предложение 

...NESTED TABLE available_colors STORE AS available_colors_st

сообщает Oracle, что вспомогательная таблица для хранения данных столбца available_colors должна называться available_colors_st.

 

Выбор типа коллекции

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

Как правило, разработчики PL/SQL инстинктивно склонны к использованию ассоциативных массивов. Почему? Потому что ассоциативные массивы требуют минимального объема кода. Их не нужно инициализировать или расширять. Традиционно они считались самой эффективной разновидностью коллекций (хотя со временем эти различия, вероятно, исчезнут). Но если коллекция должна храниться в таблице баз данных, ассоциативный массив отпадает. Остается вопрос: вложенная таблица или VARRAY?

Следующие рекомендации помогут вам принять решение. Впрочем, если к настоящему моменту еще не составили полного представления о коллекциях.

  • Если вам нужна функциональность разреженных коллекций (например, для реализации «интеллектуального хранения данных»), единственным реальным вариантом остается ассоциативный массив. Да, вы можете выделять и удалять элементы при
  • работе с переменными вложенных таблиц (как показано далее при описании методов NEXT и PRIOR), но эти операции для коллекций сколько-нибудь значительного размера выполняются крайне неэффективно.
Характеристика Ассоциативный массив Вложенная таблица Массив VARRAY
Размерность Одномерная коллекция Одномерная коллекция Одномерная коллекция
Может использоваться в SQL? Нет Да Да
Может использоваться как тип данных
столбца таблицы?
Нет Да; данные хранятся в отдельной вспомогательной
таблице
Да; данные хранятся в той же таблице
Неинициализированное
состояние
Пустая коллекция (не
может быть равна NULL);
элементы не определены
Атомарное значение NULL;
ссылки на элементы не-
действительны
Атомарное значение NULL; ссылки
на элементы не-
действительны
Инициализация Автоматическая при объявлении При вызове конструктора,
выборке или присваивании
При вызове конструктора, выборке
или присваивании
Ссылка в элементах
PL/SQL
BINARY_INTEGER и любые из его подтипов (-2 147 483 647 .. 2,147,483,647) VARCHAR2 (Oracle9i
Release2 и выше)
Положительное
целое число от 1 до
2 147 483 647
Разреженная? Да Изначально нет, но может стать после удалений Нет
Ограниченная? Нет Может расширяться Да
Допускает присваивание любому элементу в любой момент времени? Да Нет; может потребоваться
предварительный вызов
EXTEND
Нет; может потребоваться предварительный вызов
EXTEND, который
не может выходить за верхнюю
границу
Способ расширения Присваивание значения
элементу с новым индексом
Встроенная процедура
EXTEND (или TRIM для
сжатия) без заранее определенного максимума
EXTEND (или TRIM),
но не более объяв-
ленного максимального размера
Поддерживает проверку равенства? Нет Да, Oracle10g и выше Нет
Поддерживает
использование
операций над
множествами?
Нет Да, Oracle10g и выше Нет
Сохраняет порядок
следования элементов и индексы при
сохранении в базе
данных?
Нет Да
  • Если приложение PL/SQL требует использования отрицательных индексов, придется использовать ассоциативные массивы.
  • Если вы работаете в Oracle10g или более поздней версии и с коллекциями выполняются высокоуровневые операции из теории множеств, используйте вложенные таблицы вместо ассоциативных массивов.
  • Если количество строк, хранящихся в таблице, должно быть жестко ограничено, используйте массивы VARRAY.
  • Если в столбцовой коллекции должны храниться большие объемы постоянных данных, единственным реальным вариантом остается вложенная таблица. База данных будет использовать отдельную скрытую таблицу для хранения данных коллекции, что обеспечивает ее почти неограниченный рост.
  • Если вы хотите сохранить порядок элементов, хранимых в столбце коллекции и набор данных будет относительно небольшим, используйте массив VARRAY.
  • Что считать «относительно небольшим»? Ориентируйтесь на объем данных, помещающихся в одном блоке базы данных; при выходе за пределы блока начинаются переходы между записями, снижающие производительность операций.
  • Несколько признаков ситуации, в которой предпочтение отдается массиву VARRAY: вам не нужно беспокоиться о возможных удалениях в середине набора данных; сама природа данных подразумевает наличие четкой верхней границы; в большинстве случаев при выборке извлекается все содержимое коллекции.

Вас заинтересует / Intresting for you:

Управление приложениями PL/SQL...
Управление приложениями PL/SQL... 4634 просмотров Stas Belkov Thu, 16 Jul 2020, 06:20:48
Встроенные методы коллекций PL...
Встроенные методы коллекций PL... 14739 просмотров sepia Tue, 29 Oct 2019, 09:54:01
Тип данных RAW в PL/SQL
Тип данных RAW в PL/SQL 12240 просмотров Doctor Thu, 12 Jul 2018, 08:41:33
Символьные функции и аргументы...
Символьные функции и аргументы... 18546 просмотров Анатолий Wed, 23 May 2018, 18:54:01
Войдите чтобы комментировать

dbstalker аватар
dbstalker ответил в теме #9553 4 года 5 мес. назад
Круть! Спасибо за ценный материал. Есть над чем подумать в плане собственного профессионализма...
anders7777 аватар
anders7777 ответил в теме #9111 5 года 8 мес. назад
Как раз изучаю язык программирования PL/SQL - осваиваю коллекции. Статья очень полезная, "в тему", как говориться. Спасибо!