Работа с коллекциями PL/SQL на примерах

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

• Обработка исключений при работе с коллекциями.
• Объявление типов коллекций.
• Объявление и инициализация переменных коллекций.
• Присваивание значений коллекциям.
• Использование коллекций составных типов данных (например, коллекций, элементы которых представляют собой коллекции).
• Работа с последовательными и непоследовательными ассоциативными массивами.
• Возможности коллекций со строковым индексированием.
• Работа с коллекциями PL/SQL в командах SQL.

Объявление типов коллекций

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

Существует два способа определения типов коллекций:

• Тип коллекции можно объявить в программе PL/SQL с применением синтаксиса TYPE. Такой тип будет доступен лишь в том блоке, где он был объявлен. Тип, определяемый в спецификации пакета, будет доступен всем программам, схема которых обладает правами EXECUTE для данного пакета.
• Определение типа вложенной таблицы или VARRAY как объекта уровня схемы в базе данных Oracle с использованием команды CREATE TYPE. В дальнейшем такой тип может использоваться в качестве типа столбцов таблиц баз данных и атрибутах объектных типов, а также для объявления переменных в программах PL/SQL. Он может использоваться в любой программе схемы, обладающей соответствующими правами EXECUTE.

Объявление ассоциативного массива

Конструкция TYPE для определения ассоциативного массива имеет следующий формат: 

TYPE имя_типа_таблицы IS TABLE OF тип_данных [ NOT NULL ]
INDEX BY тип_индекса;

Здесь имя_типа_таблицы — имя создаваемой коллекции, тип_данных — тип данных единственного столбца коллекции, а тип_индекса — тип данных индекса, используемого для упорядочения содержимого коллекции. При желании можно добавить ограничение NOT NULL, указывающее, что каждая строка таблицы обязательно должна содержать значение.

Имена табличных типов подчиняются тем же правилам, что и имена других идентификаторов PL/SQL: имя должно начинаться с буквы, иметь длину до 30 символов и содержать некоторые специальные символы (решетка, подчеркивание, знак доллара). А если заключить имя в двойные кавычки, оно может содержать до 30 любых символов.

Типом данных элементов коллекции может быть почти любой тип данных PL/SQL. Допускается использование большинства скалярных базовых типов данных, подтипов, типов с привязкой и типов, определяемых пользователем. Исключение составляют типы REF CURSOR (нельзя создать коллекцию курсорных переменных) и исключения.

Тип_индекса определяет тип данных, которые определяют местонахождение данных, размещаемых в коллекции. До Oracle9i Release 2 поддерживался только один способ определения индекса ассоциативного массива:

INDEX BY PLS_INTEGER

Теперь тип данных индекса может определяться и по-другому:

INDEX BY BINARY_INTEGER
INDEX BY PLS_INTEGER
INDEX BY POSITIVE
INDEX BY NATURAL
INDEX BY SIGNTYPE /* Только три значения индекса: 1, 0 and 1 ! */
INDEX BY VARCHAR2(32767)
INDEX BY таблица.столбец%TYPE
INDEX BY курсор.столбец%TYPE
INDEX BY пакет.переменная%TYPE
INDEX BY пакет.подтип 

Несколько примеров объявлений типов ассоциативных массивов:

-- Список дат
TYPE birthdays_tt IS TABLE OF DATE INDEX BY PLS_INTEGER;
-- Список идентификаторов компаний
TYPE company_keys_tt IS TABLE OF company.company_id%TYPE NOT NULL
INDEX BY PLS_INTEGER;
-- Список записей книг; эта структура позволяет создать
-- "локальную" копию таблицы книг в программе PL/SQL.
TYPE booklist_tt IS TABLE OF books%ROWTYPE
INDEX BY NATURAL; -- Каждая коллекция упорядочивается по имени автора.
TYPE books_by_author_tt IS TABLE OF books%ROWTYPE
INDEX BY books.author%TYPE;
-- Коллекция коллекций
TYPE private_collection_tt IS TABLE OF books_by_author_tt
INDEX BY VARCHAR2(100);

В предыдущем примере я объявил обобщенный тип коллекции (список дат), но присвоил ему конкретное имя: birthdays_tt. Конечно, это всего лишь один из способов объявления типа ассоциативных массивов дат. Вместо того чтобы создавать набор определений TYPE коллекций, различающихся только по имени и разбросанных по всему приложению, стоит рассмотреть возможность создания одного пакета с набором заранее определенных, стандартных типов коллекций. Следующий пример находится в файле colltypes.pks: 

PACKAGE collection_types
IS
   -- Типы ассоциативных массивов
   TYPE boolean_aat IS TABLE OF BOOLEAN INDEX BY PLS_INTEGER;
   TYPE date_aat IS TABLE OF DATE INDEX BY PLS_INTEGER;
   TYPE pls_integer_aat IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;
   TYPE number_aat IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
   TYPE identifier_aat IS TABLE OF VARCHAR2(30)
   INDEX BY PLS_INTEGER;
   TYPE vcmax_aat IS TABLE OF VARCHAR2(32767)
   INDEX BY PLS_INTEGER;
   -- Типы вложенных таблиц
   TYPE boolean_ntt IS TABLE OF BOOLEAN;
   TYPE date_ntt IS TABLE OF DATE;
   TYPE pls_integer_ntt IS TABLE OF PLS_INTEGER;
   TYPE number_ntt IS TABLE OF NUMBER;
   TYPE identifier_ntt IS TABLE OF VARCHAR2(30);
   TYPE vcmax_ntt IS TABLE OF VARCHAR2(32767)
END collection_types;
/

При наличии такого пакета можно предоставить полномочия EXECUTE группе PUBLIC, и тогда все разработчики смогут использовать пакетные определения TYPE для объявления своих коллекций. Пример: 

DECLARE
family_birthdays collection_types.date_aat;

Объявление вложенной таблицы или VARRAY

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

Тип данных вложенной таблицы, которая существует в базе данных (а не только в коде PL/SQL), создается следующим образом: 

CREATE [ OR REPLACE ] TYPE имя_типа AS | IS
TABLE OF тип_элемента [ NOT NULL ];

Тип данных VARRAY, который существует в базе данных (а не только в коде PL/SQL), создается следующим образом: 

CREATE [ OR REPLACE ] TYPE имя_типа AS | IS
VARRAY (максимальный_индекс) OF тип_элемента [ NOT NULL ];

Удаление типа осуществляется следующей командой:

DROP TYPE имя_типа [ FORCE ]; 

А вот как в программе PL/SQL создается тип вложенной таблицы:

TYPE имя_типа IS TABLE OF тип_элемента [ NOT NULL ]; 

Для объявления типа данных VARRAY в PL/SQL используется объявление:

TYPE имя_типа IS VARRAY (максимальный_индекс)
OF тип_элемента [ NOT NULL ]; 

Основные параметры, используемые при определении типов:

• Опция OR REPLACE позволяет переопределить существующий тип данных с сохранением всех существующих привилегий (вместо удаления и повторного создания типа).
• Параметр имя_типа — допустимый идентификатор SQL или PL/SQL, который позднее будет использоваться в объявлениях переменных и столбцов.
• Параметр тип_элемента определяет тип данных элементов коллекции. Все элементы относятся к одному типу, которым может быть почти любой скалярный или объектный (в том числе REF) тип данных. При определении типа на уровне схемы тип данных должен быть типом данных SQL (логические значения недопустимы!).
• Опция NOT NULL указывает, что переменная данного типа не должна содержать элементов NULL. При этом вся коллекция может быть равна NULL (не инициализирована).
• Параметр максимальный — значение, определяющее максимальное количество элементов в массиве VARRAY.
• Опция FORCE приказывает Oracle удалить тип данных, даже если на него ссылается другой тип. Например, если в определении объектного типа указывается тип коллекции, его можно удалить, добавив в соответствующую команду ключевое слово FORCE.

За инструкцией CREATE TYPE должен следовать символ косой черты (/), как при создании процедуры, функции или пакета.

Обратите внимание: единственным различием в синтаксисе определения типов вложенной таблицы и ассоциативного массива является отсутствие в первом случае предложения INDEX BY.

Синтаксис определения массива VARRAY отличается от синтаксиса определения типа вложенной таблицы наличием ключевого слова VARRAY и ограничением количества элементов.

Изменение характеристик вложенных таблиц и массивов VARRAY

Инструкция ALTER TYPE позволяет изменить некоторые характеристики вложенной таблицы или типа VARRAY, созданных в базе данных. Количество элементов типа VARRAY увеличивается командой ALTER TYPE ... MODIFY LIMIT: 

ALTER TYPE list_vat MODIFY LIMIT 100 INVALIDATE;
/

Если элемент типа VARRAY или вложенной таблицы относится к символьному типу переменной длины, RAW или числовому типу, вы можете увеличить его размер или точность. Пример:

CREATE TYPE list_vat AS VARRAY(10) OF VARCHAR2(80);
/
ALTER TYPE list_vat MODIFY ELEMENT TYPE VARCHAR2(100) CASCADE;
/

Опции INVALIDATE и CASCADE соответственно объявляют недействительными все зависимые объекты и распространяют внесенные изменения как в зависимых типах, так и в таблицах.

Объявление и инициализация переменных-коллекций

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

имя_коллекции тип_коллекции [:= тип_коллекции (...)];

Здесь имя_коллекции — имя переменной-коллекции, а тип_коллекции — имя ранее объявленного типа и (для вложенных таблиц и VARRAY) одноименной функции-конструктора.

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

ORA-06531: Reference to uninitialized collection

В следующем примере определяется общий тип коллекции, повторяющий структуру таблицы company. Затем на базе этого типа объявляются две переменные: 

DECLARE
TYPE company_aat IS TABLE OF company%ROWTYPE INDEX BY PLS_INTEGER;
   premier_sponsor_list company_aat;
   select_sponsor_list company_aat;
BEGIN
   ...
END;

Вложенную таблицу или массив VARRAY можно немедленно инициализировать при объявлении вызовом функции-конструктора. Пример: 

DECLARE
TYPE company_aat IS TABLE OF company%ROWTYPE;
   premier_sponsor_list company_aat := company_aat();
BEGIN
   ...
END;

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

DECLARE
TYPE company_aat IS TABLE OF company%ROWTYPE;
   premier_sponsor_list company_aat;
BEGIN
   premier_sponsor_list:= company_aat();
END; 

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

Давайте поближе познакомимся с инициализацией вложенных таблиц и VARRAY. Предыдущий пример показывает, как инициализировать коллекцию вызовом функции-конструктора без параметров. Также можно указать исходный набор значений. Предположим, я создаю тип уровня схемы с именем color_tab_t: 

CREATE OR REPLACE TYPE color_tab_t AS TABLE OF VARCHAR2(30)

после чего объявляю переменные PL/SQL на основании этого типа:

DECLARE
   my_favorite_colors color_tab_t := color_tab_t();
   his_favorite_colors color_tab_t := color_tab_t('PURPLE');
   her_favorite_colors color_tab_t := color_tab_t('PURPLE', 'GREEN'); 

В первом объявлении коллекция инициализируется как пустая; она не содержит строк. Второе объявление присваивает единственное значение PURPLE строке 1 вложенной таблицы. Третье объявление присваивает два значения, PURPLE и GREEN, строкам 1 и 2 вложенной таблицы.

Так как я не присвоил никаких значений my_favorite_colors при вызове конструктора, я должен расширить коллекцию перед размещением элементов. Коллекции his и her уже были неявно расширены в соответствии со списком значений, переданным конструктору. Присваивание через функцию-конструктор подчиняется тем же ограничениям, которые действуют при прямом присваивании. Если, например, массив VARRAY ограничивается пятью элементами, а вы пытаетесь инициализировать его конструктором с шестью элементами, база данных выдаст ошибку ORA-06532: Subscript outside of limit error.

Неявная инициализация путем непосредственного присваивания

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

Следующий пример демонстрирует неявное присваивание, выполняемое при присваивании переменной wedding_colors значения переменной earth_colors. 

DECLARE
   earth_colors color_tab_t := color_tab_t ('BRICK', 'RUST', 'DIRT');
   wedding_colors color_tab_t;
BEGIN
   wedding_colors := earth_colors;
   wedding_colors(3) := 'CANVAS';
END;

Этот код инициализирует переменную wedding_colors и создает в ней три элемента, соответствующие элементам earth_colors. В результате мы получаем две независимые переменные с одинаковыми значениями (в отличие от указателей на идентичные значения); скажем, если присвоить третьему элементу wedding_colors значение CANVAS, третий элемент коллекции earth_colors останется неизменным.

Учтите, что простой «совместимости типов данных» для этого недостаточно. Даже если объявить два типа коллекций с одинаковыми определениями, вы получите два разных типа данных, причем переменные одного типа нельзя будет присвоить переменным другого типа. Таким образом, следующий блок кода не будет компилироваться: 

DECLARE
   TYPE tt1 IS TABLE OF employees%ROWTYPE;
   TYPE tt2 IS TABLE OF employees%ROWTYPE;
   t1 tt1 := tt1();
   t2 tt2 := tt2();
BEGIN
   /* Ошибка "PLS-00382: expression is of wrong type" */
   t1 := t2;
END;

Неявная инициализация путем выборки

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

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

CREATE TABLE color_models (
   model_type VARCHAR2(12)
   , colors color_tab_t
   )
   NESTED TABLE colors STORE AS color_model_colors_tab
   /
BEGIN
   INSERT INTO color_models
   VALUES ('RGB', color_tab_t ('RED','GREEN','BLUE'));
END;
   /

А теперь — самое главное. За один цикл обращения к базе данных мы считываем все значения из столбца colors заданной строки и помещаем их в локальную переменную: 

DECLARE
   l_colors color_tab_t;
BEGIN
   /* Выборка всех вложенных значений за одну операцию выборки.
   || Это самое примечательное!
   */
   SELECT colors INTO l_colors FROM color_models
   WHERE model_type = 'RGB';
   ...
END;

Удобно, верно? Обратите внимание на несколько важных моментов:

• При выборке значений из базы данных индексами коллекции l_colors управляет Oracle, а не программист.
• Начальное значение индекса, присваиваемое Oracle, равно 1 (а не 0, как в некоторых других языках). Далее индексы увеличиваются на 1; коллекция всегда заполняется плотно (или является пустой).
• Операция присваивания удовлетворяет требованию инициализации локальной переменной перед присваиванием значений элементам. Мы не инициализировали l_colors с помощью конструктора, но PL/SQL известно, как ее обрабатывать.

Измененное содержимое коллекции с такой же легкостью записывается обратно в базу данных. Давайте шутки ради создадим цветовую модель Fuschia-Green-Blue:

DECLARE
   color_tab color_tab_t;
BEGIN
   SELECT colors INTO color_tab FROM color_models
   WHERE model_type = 'RGB';
   FOR element IN 1..color_tab.COUNT
   LOOP
      IF color_tab(element) = 'RED'
      THEN
         color_tab(element) := 'FUSCHIA';
      END IF;
   END LOOP;
   /* Самая интересная часть примера. Достаточно всего
   || одной инструкции вставки - и вся вложенная таблица
   || отправляется обратно в базу данных, в таблицу color_models. */
   INSERT INTO color_models VALUES ('FGB', color_tab);
END; 

Интеграция массивов VARRAY

Резонно спросить, реализована ли интеграция баз данных с PL/SQL и для массивов VARRAY? Конечно, хотя у нее есть свои особенности.

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

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

Указанное свойство массивов VARRAY открывает широкие возможности для написания новых интересных функций. Например, вы можете запрограммировать вставку дополнительного элемента в начало списка со сдвигом всех остальных элементов.

Существует еще одно отличие структур VARRAY от вложенных таблиц, связанное с интеграцией базы данных и PL/SQL: некоторые инструкции SELECT для выборки вложенных таблиц нельзя использовать с массивами VARRAY «как есть» — в них необходимо внести изменения (примеры приведены в разделе «Работа с коллекциями в SQL»).

Заполнение коллекций данными

Только что инициализированная коллекция пуста. В ней нет ни одного определенного элемента. Элемент определяется при присваивании ему значения. Присваивание выполняется стандартной командой присваивания PL/SQL, выборкой данных из одной или нескольких реляционных таблиц в коллекцию, конструкцией RETURNING BULK COLLECT или агрегатным присваиванием (по сути, копированием одной коллекции в другую).

При работе с ассоциативным массивом значение (соответствующего типа) может быть присвоено по любому допустимому индексу в коллекции. Если ассоциативный массив индексируется целыми числами, то значения индекса должны лежать в интервале от –2³¹ и 2³¹ –1. Простая операция присваивания создает элемент и размещает в нем значение.

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

Прежде чем пытаться присваивать значение по индексу вложенной таблицы или VARRAY, необходимо убедиться в том, что (1) коллекция была инициализирована, и (2) индексы были определены. Для создания новых индексов во вложенных таблицах и VARRAY используется оператор EXTEND.

Использование команды присваивания

Значения элементов коллекции могут задаваться стандартной командой присваивания PL/SQL:

countdown_test_list (43) := 'Internal pressure';
company_names_table (last_name_row + 10) := 'Johnstone Clingers'; 

Тот же синтаксис применяется для присваивания элементу коллекции целой записи или составного типа данных:

DECLARE
       TYPE emp_copy_t IS TABLE OF employees%ROWTYPE;
      l_emps emp_copy_t := emp_copy_t();
      l_emprec employees%ROWTYPE;
BEGIN
    l_emprec.last_name := 'Steven';
    l_emprec.salary := 10000;
    l_emps.EXTEND;
    l_emps (l_emps.LAST) := l_emprec;
END; 

Если структура данных в правой стороне оператора присваивания соответствует типу коллекции, присваивание будет выполнено без ошибок.

Какие значения индексов можно использовать?

При присваивании данных ассоциативному массиву необходимо задать позицию (значение индекса) в коллекции. Тип значения и диапазон значений, используемых для обозначения этой позиции, зависят от способа определения секции INDEX BY ассоциативного массива. Они перечислены в следующей таблице.

Также можно выполнить индексирование по любому из субтипов или воспользоваться типом, привязанным к столбцу базы данных VARCHAR2 (например, имя_таблицы. имя_столбца%TYPE).

Агрегатное присваивание

Также возможно «агрегатное присваивание» содержимого всей коллекции другой коллекции точно такого же типа. Пример:

1 DECLARE
2    TYPE name_t IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
3     old_names name_t;
4     new_names name_t;
5 BEGIN
6     /* Присваивание значений элементам таблицы old_names */
7     old_names(1) := 'Smith';
8     old_names(2) := 'Harrison';
9
10     /* Присваивание значений элементам таблицы new_names */ 
11     new_names(111) := 'Hanrahan';
12     new_names(342) := 'Blimey';
13
14     /* Перенос значений из старой коллекции в новую */
15     old_names := new_names;
16
17     /* Выводит 'Hanrahan' */
18     DBMS_OUTPUT.PUT_LINE (
19     old_names.FIRST || ': ' || old_names(old_names.FIRST));
20 END;

Результат:

111: Hanrahan 

Присваивание уровня коллекции полностью заменяет ранее определенные элементы. В нашем примере перед последним, агрегатным присваиванием в коллекции old_names определены только строки 1 и 2. После присваивания данные будут содержать только элементы 111 и 342.

Присваивание из реляционной таблицы

Значения элементов коллекции также могут присваиваться на основании выборки данных из реляционной таблицы. Следующий пример демонстрирует различные способы копирования данных из реляционных таблиц в коллекции. Неявная конструкция SELECT INTO применяется для выборки одной строки данных в коллекцию: 

DECLARE
   TYPE emp_copy_t IS TABLE OF employees%ROWTYPE;
   l_emps emp_copy_t := emp_copy_t();
BEGIN
   l_emps.EXTEND;
   SELECT *
   INTO l_emps (1)
   FROM employees
   WHERE employee_id = 7521;
END;

Для копирования нескольких строк в коллекцию с последовательным заполнением элементов можно воспользоваться курсорным циклом FOR:

DECLARE
   TYPE emp_copy_t IS TABLE OF employees%ROWTYPE;
   l_emps emp_copy_t := emp_copy_t();
BEGIN
   FOR emp_rec IN (SELECT * FROM employees)
   LOOP
      l_emps.EXTEND;
      l_emps (l_emps.LAST) := emp_rec;
   END LOOP;
END;

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

DECLARE
   TYPE emp_copy_t IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;
   l_emps emp_copy_t;
BEGIN
   FOR emp_rec IN (SELECT * FROM employees)
   LOOP
      l_emps (emp_rec.employee_id) := emp_rec;
   END LOOP;
END;

А еще можно воспользоваться конструкцией BULK COLLECT для выборки всех строк таблицы за одну операцию присваивания, с сохранением данных в любом из трех типов коллекций. При использовании вложенной таблицы или VARRAY явная инициализация коллекции не нужна. Пример: 

DECLARE
   TYPE emp_copy_nt IS TABLE OF employees%ROWTYPE;
   l_emps emp_copy_nt;
BEGIN
   SELECT * BULK COLLECT INTO l_emps FROM employees;
END;

Преимущества непоследовательного заполнения коллекций

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

Во многих приложениях вам приходится снова и снова писать и выполнять одинаковые вопросы. В некоторых случаях запросы осуществляют выборку статических данных (скажем, редко изменяющихся кодов и описаний). Но если данные остаются неизменными — особенно во время одного пользовательского сеанса, — зачем многократно извлекать информацию из базы данных? Даже если данные кэшируются в области SGA (System Global Area), вам все равно придется обратиться к SGA, убедиться в том, что запрос уже разобран, найти эту информацию в буферах данных и вернуть ее в область сеанса (PGA).

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

Рассмотрим пример: в таблице hairstyles хранятся числовые коды (первичный ключ) и описания стрижек. Эти данные не устаревают и изменяются относительно редко.

Ниже представлено тело пакета, использующего коллекцию для кэширования пар «код- описание» и тем самым сокращающего количество обращений к базе: 

1 PACKAGE BODY justonce
2 IS
3    TYPE desc_t
4 IS
5    TABLE OF hairstyles.description%TYPE
6    INDEX BY PLS_INTEGER;
7
8    descriptions desc_t;
9
10 FUNCTION description (code_in IN hairstyles.code%TYPE)
11 RETURN hairstyles.description%TYPE
12 IS
13    return_value hairstyles.description%TYPE;
14
15 FUNCTION desc_from_database
16 RETURN hairstyles.description%TYPE
17 IS
18    l_description hairstyles.description%TYPE;
19 BEGIN
20    SELECT description
21    INTO l_description
22    FROM hairstyles
23    WHERE code = code_in;
24    RETURN l_description;
25 END;
26 BEGIN
27    RETURN descriptions (code_in);
28    EXCEPTION
29    WHEN NO_DATA_FOUND
30    THEN
31       descriptions (code_in) := desc_from_database ();
32    RETURN descriptions (code_in);
33 END;
34 END justonce;

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

Итак, к каким последствиям приводит кэширование? Тесты на моем компьютере показали, что выполнение 10 000 запросов к таблице hairstyles заняло менее двух секунд. Несомненно, это хорошая скорость. Однако для получения той же информации 10 000 раз с использованием приведенной функции потребовалось около 0,1 секунды. Улучшение больше чем на порядок!

Напоследок несколько замечаний по кэшированию в коллекциях:

• Это решение является классическим компромиссом между затратами вычислительных ресурсов и памяти. Каждый сеанс использует собственную копию коллекции (данные программы хранятся в PGA). При 10 000 пользователях общие затраты памяти на 10 000 небольших кэшей будут довольно значительными.
• Кэширование часто оказывается уместным в следующих сценариях: небольшие статические таблицы в многопользовательских приложениях; большие статические таблицы, в которых конкретному пользователю доступна лишь небольшая часть данных; манипуляции с большими таблицами в пакетных процессах (простая команда CONNECT, которая может занимать много памяти).

Обращение к данным в коллекциях

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

• При попытке чтения по неопределенному индексу в коллекции база данных инициирует исключение NO_DATA_FOUND. Из этого следует, что вам стоит по возможности избегать использования циклов FOR со счетчиком для перебора коллекции, если только вы не уверены, что она плотно заполнена — и всегда будет оставаться таковой (то есть между FIRST и LAST нет неопределенных значений индексов). Если коллекция заполнена неплотно, при первом же обращении к «дыре» между значениями, возвращаемыми методами FIRST и LAST, произойдет сбой с исключением NO_DATA_FOUND.
• При попытке чтения строки, выходящей за пределы расширенных (посредством EXTEND) строк таблицы или VARRAY, база данных инициирует следующее исключение: ORA-06533: Subscript beyond count. При работе с вложенными таблицами и VARRAY следует постоянно следить за расширением коллекции, чтобы в ней помещались строки, которые вы хотите прочитать или присвоить.
• При попытке чтения строки, индекс которой выходит за границы определения типа VARRAY, база данных выдает следующее исключение: ORA-06532: Subscript outside of limit.

Помните, что вы всегда можете вызвать метод LIMIT для определения максимального допустимого количества строк в VARRAY. Так как индексы в таких коллекциях всегда начинаются с 1, вы можете легко определить, осталось ли в структуре данных место для дополнительной информации.

В остальном обращения к отдельным строкам коллекции не вызывают никаких сложностей: просто укажите индекс (или индексы — синтаксис коллекций с элементами- коллекциями рассматривается в разделе «Коллекции составных типов данных») после имени коллекции.

Коллекции со строковыми индексами

В Oracle9i Release 2 состав типов, которые могут использоваться для индексирования ассоциативных массивов, был значительно расширен. Тип VARCHAR2 обладает наибольшей гибкостью и потенциальными возможностями. Поскольку с этим типом возможно индексирование по строкам, фактически появляется возможность индексирования по любым данным, которые могут быть преобразованы в строку длиной не более 32 767 байт.

Следующий блок кода демонстрирует основные принципы строкового индексирования:

DECLARE
   SUBTYPE location_t IS VARCHAR2(64);
   TYPE population_type IS TABLE OF NUMBER INDEX BY location_t;
   l_country_population population_type;
   l_continent_population population_type;
   l_count PLS_INTEGER;
   l_location location_t;
BEGIN
   l_country_population('Greenland') := 100000;
   l_country_population('Iceland') := 750000;
   l_continent_population('Australia') := 30000000;
   l_continent_population('Antarctica') := 1000;
   l_continent_population('antarctica') := 1001;
   l_count := l_country_population.COUNT;
   DBMS_OUTPUT.PUT_LINE ('COUNT = ' || l_count);
   l_location := l_continent_population.FIRST;
   DBMS_OUTPUT.PUT_LINE ('FIRST row = ' || l_location);
   DBMS_OUTPUT.PUT_LINE ('FIRST value = ' || l_continent_population(l_location));
   l_location := l_continent_population.LAST;
   DBMS_OUTPUT.PUT_LINE ('LAST row = ' || l_location);
   DBMS_OUTPUT.PUT_LINE ('LAST value = ' || l_continent_population(l_location));
END;

Результат выполнения сценария:

COUNT = 2
FIRST row = Antarctica
FIRST value = 1000
LAST row = antarctica
LAST value = 1001 

В этом коде следует обратить внимание на некоторые моменты:

• В коллекции со строковым индексированием значения, возвращаемые методами FIRST, LAST, PRIOR и NEXT, представляют собой строки, а не целые числа.
• Обратите внимание: «antarctica» стоит на последнем месте, после «Antarctica» и «Australia». Дело в том, что буквы нижнего регистра имеют большие значения ASCII-кодов, чем буквы верхнего регистра. Порядок сохранения строк в ассоциативном массиве определяется набором символов.
• Между коллекциями со строковым и целочисленным индексированием нет различий в синтаксисе использования.
• Я определяю подтип location_t, который будет использоваться как тип индексирования в моем объявлении типа коллекции, а также при объявлении переменной l_location. При работе с коллекциями, индексируемыми строками (и особенно многоуровневыми коллекциями), подтипы напоминают, какие данные используются в качестве значений индексов.

Далее приводятся другие примеры, демонстрирующие применение этой возможности.

Упрощение логики алгоритмов при использовании строковых индексов

Разумное использование коллекций со строковым индексированием может сильно упростить ваши программы; по сути сложность выносится из алгоритмов в структуру данных, которые и выполняют всю «черную работу» (вернее, ее выполняет база данных). Следующий пример наглядно демонстрирует это перемещение сложности.

В 2006 и 2007 годах я руководил проектом по созданию программы автоматизации тестирования PL/SQL — Quest Code Tester — для Oracle. Одно из важнейших преимуществ этой программы заключалось в том, что она генерировала тестовый пакет по описаниям ожидаемого поведения программы. В процессе генерирования тестового кода необходимо отслеживать имена объявленных переменных, чтобы случайно не объявить другую переменную с тем же именем.

Первая версия пакета string_tracker выглядела примерно так: 

1 PACKAGE BODY string_tracker
2 IS
3    SUBTYPE name_t IS VARCHAR2 (32767);
4    TYPE used_aat IS TABLE OF name_t INDEX BY PLS_INTEGER;
5    g_names_used used_aat;
6
7    PROCEDURE mark_as_used (variable_name_in IN name_t) IS
8    BEGIN
9       g_names_used (g_names_used.COUNT + 1) := variable_name_in;
10   END mark_as_used;
11
12   FUNCTION string_in_use (variable_name_in IN name_t) RETURN BOOLEAN
13   IS
14      c_count CONSTANT PLS_INTEGER := g_names_used.COUNT;
15      l_index PLS_INTEGER := g_names_used.FIRST;
16      l_found BOOLEAN := FALSE;
17   BEGIN
18      WHILE (NOT l_found AND l_index <= c_count)
19      LOOP
20         l_found := variable_name_in = g_names_used (l_index);
21         l_index := l_index + 1;
22      END LOOP;
23
24      RETURN l_found;
25   END string_in_use;
26 END string_tracker;

В таблице поясняются наиболее интересные места в теле пакета.

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

Вторая версия пакета string_tracker: 

1 PACKAGE BODY string_tracker
2 IS
3    SUBTYPE name_t IS VARCHAR2 (32767);
4    TYPE used_aat IS TABLE OF BOOLEAN INDEX BY name_t;
5    g_names_used used_aat;
6
7    PROCEDURE mark_as_used (variable_name_in IN name_t) IS
8    BEGIN
9       g_names_used (variable_name_in) := TRUE;
10    END mark_as_used;
11
12    FUNCTION string_in_use (variable_name_in IN name_t) RETURN BOOLEAN
13    IS
14    BEGIN
15       RETURN g_names_used.EXISTS (variable_name_in);
16    END string_in_use;
17 END string_tracker;

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

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

Моделирование первичных ключей и уникальных индексов

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

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

DECLARE
   c_delimiter CONSTANT CHAR (1) := '^';
   TYPE strings_t IS TABLE OF employees%ROWTYPE
      INDEX BY employees.email%TYPE;
   TYPE ids_t IS TABLE OF employees%ROWTYPE
      INDEX BY PLS_INTEGER;
   by_name strings_t;
   by_email strings_t;
   by_id ids_t;
   ceo_name employees.last_name%TYPE
      := 'ELLISON' || c_delimiter || 'LARRY';
PROCEDURE load_arrays
IS
BEGIN
   /* Заполнение всех трех массивов по строкам таблицы. */
   FOR rec IN (SELECT *
               FROM employees)
   LOOP
      by_name (rec.last_name || c_delimiter || rec.first_name) := rec;
      by_email (rec.email) := rec;
      by_id (rec.employee_id) := rec;
   END LOOP;
END;
BEGIN
   load_arrays;
   /* Выборка информации по имени или идентификатору. */
   IF by_name (ceo_name).salary > by_id (7645).salary
   THEN
      make_adjustment (ceo_name);
   END IF;
END;

Производительность коллекций со строковым индексированием

Какую цену приходится платить за использование строкового индексирования вместо целочисленного? Все зависит исключительно от длины строк. При использовании строкового индексирования база данных берет строку и «хеширует» (преобразует) ее в целочисленное значение. Таким образом, дополнительные затраты определяются быстродействием хеширующей функции, а также необходимостью разрешения возможных конфликтов, так как уникальность результатов хеширования не гарантируется — известно лишь то, что ее вероятность чрезвычайно высока.

На моем компьютере тестирование дало следующий результат: 

Сравнение строкового и целочисленного индексирования. Итерации = 10000 Длина = 100
Индексирование по PLS_INTEGER Время: 4.26 с.
Индексирование по VARCHAR2 Время: 4.75 с.
Сравнение строкового и целочисленного индексирования. Итерации = 10000 Длина = 1000
Индексирование по PLS_INTEGER Время: 4.24 с.
Индексирование по VARCHAR2 Время: 6.4 с.
Сравнение строкового и целочисленного индексирования. Итерации = 10000 Длина = 10000
Индексирование по PLS_INTEGER Время: 4.06 с.
Индексирование по VARCHAR2 Время: 24.63 с.

Вывод: при относительно небольших строках (100 символов и менее) нет существенных различий по производительности между строковым и целочисленным индексированием. Однако с увеличением длины строк затраты на хеширование существенно возрастают. Будьте внимательны при выборе строк, используемых в качестве индексов!

Другие примеры коллекций со строковым индексированием

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

Файл genaa.sp получает имя таблицы в аргументе. На основании информации, хранящейся в словаре данных этой таблицы (первичный ключ и уникальные индексы), он генерирует реализацию кэширования для этой таблицы. Затем он заполняет коллекцию на основании целочисленного первичного ключа и дополнительную коллекцию для каждого уникального индекса, определенного для таблицы (с индексированием по PLS_INTEGER или VARCHAR2 в зависимости от типа(-ов) столбца(-ов) в индексе).

Кроме того, файл summer_reading.pkg содержит пример использования ассоциативных массивов, индексируемых по VARCHAR2, для работы со списками в программах PL/SQL.

Обратите внимание: «antarctica» стоит на последнем месте, после «Antarctica» и «Australia». Дело в том, что буквы нижнего регистра имеют большие значения ASCII- кодов, чем буквы верхнего регистра. Порядок сохранения строк в ассоциативном массиве определяется набором символов.

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

Коллекции составных типов данных

В Oracle9i Release 2 появилась возможность определения типов коллекций произвольной сложности. Поддерживаются следующие структуры:

• Коллекции записей, определяемых на базе таблиц с атрибутом %ROWTYPE. Эти структуры позволяют легко и быстро моделировать реляционные таблицы в программах PL/SQL.
• Коллекции пользовательских записей. Поля записи также могут относиться к скалярным или составным типам данных. Например, можно определить коллекцию записей, одно из полей которых само по себе является коллекцией.
• Коллекции объектных типов. Элементы коллекции могут относиться к любому из объектных типов, ранее определенных инструкцией CREATE TYPE. Так же легко определяются коллекции LOB, документов XML и т. д.
• Коллекции коллекций. Возможно определение многоуровневых коллекций, в том числе коллекций коллекций и коллекций типов данных, содержащих другие коллекции в своих атрибутах или полях.

Рассмотрим пример использования каждой из этих разновидностей.

Коллекции записей

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

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

PACKAGE compensation_pkg
IS
   TYPE reward_rt IS RECORD (
      nm VARCHAR2(2000), sal NUMBER, comm NUMBER);
   TYPE reward_tt IS TABLE OF reward_rt INDEX BY PLS_INTEGER;
END compensation_pkg;

С такими определениями типов в спецификации пакета я могу объявлять коллекции в других программах:

DECLARE
   holiday_bonuses compensation_pkg.reward_tt; 

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

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

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

• Встроить весь код коллекций в основную программу.
• Создать отдельный пакет для инкапсуляции доступа к данным в коллекции.

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

PACKAGE bidir
IS
   FUNCTION rowforid (id_in IN employees.employee_id%TYPE)
   RETURN employees%ROWTYPE;
   FUNCTION firstrow RETURN PLS_INTEGER;
   FUNCTION lastrow RETURN PLS_INTEGER;
   FUNCTION rowCount RETURN PLS_INTEGER;
   FUNCTION end_of_data RETURN BOOLEAN;
   PROCEDURE setrow (nth IN PLS_INTEGER);
   FUNCTION currrow RETURN employees%ROWTYPE;
   PROCEDURE nextrow;
   PROCEDURE prevrow;
END;

Как использовать этот API? Ниже приведен пример программы, использующей этот API для чтения итогового набора для таблицы employees — сначала в прямом, а затем в обратном направлении:

DECLARE
   l_employee employees%ROWTYPE;
BEGIN
   LOOP
      EXIT WHEN bidir.end_of_data;
      l_employee := bidir.currrow;
      DBMS_OUTPUT.put_line (l_employee.last_name);
      bidir.nextrow;
   END LOOP;
   bidir.setrow (bidir.lastrow);
   LOOP
      EXIT WHEN bidir.end_of_data;
      l_employee := bidir.currrow;
      DBMS_OUTPUT.put_line (l_employee.last_name);
      bidir.prevrow;
   END LOOP;
END;

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

Начнем со второго вопроса. Коллекция не видна потому, что я скрыл ее в спецификации пакета. Пользователь пакета никогда не соприкасается с коллекцией и ничего не знает о ней. Собственно, для этого и создавался API. Вы просто вызываете ту или иную программу, которая выполняет за вас всю работу по перебору коллекции (набора данных).

Когда и как заполняется коллекция? На первый взгляд все кажется каким-то волшебством. Заглянув в тело пакета, вы найдете в нем раздел инициализации, которая выглядит так: 

BEGIN -- инициализация пакета
   FOR rec IN (SELECT * FROM employees)
   LOOP
      g_employees (rec.employee_id) := rec;
   END LOOP;
   g_currrow := firstrow;
END;

Таким образом, при первом обращении к любому элементу из спецификации пакета автоматически выполняется этот код, который передает содержимое таблицы employees в коллекцию g_employees. Когда это происходит в приведенном примере? В цикле, когда я вызываю функцию bidir.end_of_data, чтобы проверить, не завершил ли я просмотр набора данных!

Переменная g_currrow определяется в теле пакета и поэтому не указывается в приведенной выше спецификации.

Я рекомендую просмотреть реализацию пакета. Ее код прост и понятен, а в некоторых ситуациях такое решение способно принести огромную пользу.

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

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

Пример работы с коллекциями объектов: 

CREATE TYPE pet_t IS OBJECT (
   tag_no INTEGER,
   name VARCHAR2 (60),
   MEMBER FUNCTION set_tag_no (new_tag_no IN INTEGER) RETURN pet_t);
/
DECLARE
   TYPE pets_t IS TABLE OF pet_t;
   pets pets_t :=
   pets_t (pet_t (1050, 'Sammy'), pet_t (1075, 'Mercury'));
BEGIN
   FOR indx IN pets.FIRST .. pets.LAST
   LOOP
      DBMS_OUTPUT.put_line (pets (indx).name);
   END LOOP;
END;
/

Результат:

Sammy
Mercury 

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

Многоуровневые коллекции

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

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

CREATE TYPE vet_visit_t IS OBJECT (
   visit_date DATE,
   reason VARCHAR2 (100)
);
/

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

CREATE TYPE vet_visits_t IS TABLE OF vet_visit_t;
/

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

CREATE TYPE pet_t IS OBJECT (
   tag_no INTEGER,
   name VARCHAR2 (60),
   petcare vet_visits_t,
   MEMBER FUNCTION set_tag_no (new_tag_no IN INTEGER) RETURN pet_t);
/ 

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

set_tag_no.

Итак, я объявил объектный тип, содержащий атрибут вложенной таблицы. Для хранения информации о посещениях ветеринара не нужна отдельная таблица базы данных; она является частью моего объекта.

А теперь мы воспользуемся возможностями многоуровневых коллекций:

1 DECLARE
2    TYPE bunch_of_pets_t
3 IS
4    TABLE OF pet_t INDEX BY PLS_INTEGER;
5
6    my_pets bunch_of_pets_t;
7 BEGIN
8    my_pets (1) :=
9    pet_t (
10       100
11       , 'Mercury'
12       , vet_visits_t (vet_visit_t ('01-Jan-2001', 'Clip wings')
13       , vet_visit_t ('01-Apr-2002', 'Check cholesterol')
14       )
15    );
16    DBMS_OUTPUT.put_line (my_pets (1).name);
17    DBMS_OUTPUT.put_line
18       (my_pets(1).petcare.LAST).reason);
19    DBMS_OUTPUT.put_line (my_pets.COUNT);
20    DBMS_OUTPUT.put_line (my_pets (1).petcare.LAST);
21 END;
Результат выполнения этой программы:
Mercury
Check cholesterol
12

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

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

Безымянные многоуровневые коллекции: моделирование многомерных массивов

Вложенные многоуровневые коллекции могут использоваться для моделирования многомерных массивов в PL/SQL. Многомерные коллекции объявляются за несколько шагов, при этом на каждом шаге добавляется новое измерение (что сильно отличается от синтаксиса объявления массивов в 3GL).

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

DECLARE
   SUBTYPE temperature IS NUMBER;
   SUBTYPE coordinate_axis IS PLS_INTEGER;
   TYPE temperature_x IS TABLE OF temperature INDEX BY coordinate_axis;
   TYPE temperature_xy IS TABLE OF temperature_x INDEX BY coordinate_axis;
   TYPE temperature_xyz IS TABLE OF temperature_xy INDEX BY coordinate_axis;
   temperature_3d temperature_xyz;
BEGIN
   temperature_3d (1) (2) (3) := 45;
END;
/

Имена типов и подтипов поясняют смысл содержимого основной коллекции (temperature_3d), типов коллекций (temperature_X, temperature_XY, temperature_XYZ) и индексов (coordinate_axis).

Хотя выбор имен четко указывает, какие данные содержатся в каждом из типов коллекций и для чего они предназначены, при обращении к элементам по индексу такой же ясности нет; иначе говоря, в каком порядке задаются измерения? Из кода неочевидно, присваивается ли температура 45° точке (X:1, Y:2, Z:3) или (X:3, Y:2, Z:1).

А теперь мы перейдем к более общей обработке трехмерного массива. Пакет multdim позволяет объявить трехмерный массив с возможностью чтения и записи отдельных ячеек. Здесь я создаю простой пакет, инкапсулирующий операции с трехмерной ассоциативной таблицей с элементами VARCHAR2, индексируемый по всем измерениям значением PLS_INTEGER. Следующие объявления содержат основные структурные элементы пакета:

CREATE OR REPLACE PACKAGE multdim
IS
   TYPE dim1_t IS TABLE OF VARCHAR2 (32767) INDEX BY PLS_INTEGER;
   TYPE dim2_t IS TABLE OF dim1_t INDEX BY PLS_INTEGER;
   TYPE dim3_t IS TABLE OF dim2_t INDEX BY PLS_INTEGER;
   PROCEDURE setcell (
      array_in IN OUT         dim3_t,
      dim1_in                 PLS_INTEGER,
      dim2_in                 PLS_INTEGER,
      dim3_in                 PLS_INTEGER,
      value_in IN             VARCHAR2
   );
   FUNCTION getcell (
      array_in IN        dim3_t,
      dim1_in            PLS_INTEGER,
      dim2_in            PLS_INTEGER,
      dim3_in            PLS_INTEGER
   )
      RETURN VARCHAR2;
   FUNCTION EXISTS (
      array_in IN        dim3_t,
      dim1_in            PLS_INTEGER,
      dim2_in            PLS_INTEGER,
      dim3_in            PLS_INTEGER
   )
      RETURN BOOLEAN;

Я последовательно определяю три типа коллекций:

• TYPE dim1_t — одномерная ассоциативная таблица с элементами VARCHAR2.
• TYPE dim2_t — ассоциативная таблица с элементами dim1_t.
• TYPE dim3_t — ассоциативная таблица с элементами dim2_t.

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

Я также определил для своего типа трехмерной коллекции простейший интерфейс чтения и записи ячеек, а также средства проверки существования значения заданной ячейки в коллекции.

Программный интерфейс multdim

Рассмотрим основные компоненты интерфейса. Процедура записи значения в ячейку трехмерного массива по координатам не могла бы быть проще: 

PROCEDURE setcell (
   array_in IN OUT           dim3_t,
   dim1_in              PLS_INTEGER,
   dim2_in              PLS_INTEGER,
   dim3_in              PLS_INTEGER,
   value_in     IN      VARCHAR2
   )
IS
BEGIN
   array_in(dim3_in )(dim2_in )(dim1_in) := value_in;
END;

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

Если работать с коллекцией dim3_t напрямую, трудно сразу сказать, какому индексу соответствует третья координата — первому или последнему. А то, что неочевидно из кода, рано или поздно приведет к ошибкам. Тот факт, что все индексы коллекций относятся к одному типу данных, только усложняет дело, потому что смешанные команды присваивания не породят исключений, а приведут к искажению результатов где-то в цепочке. Без тщательного тестирования такие ошибки проникнут в поставляемый код, устроят хаос в данных и повредят моей репутации.

Функция получения значения ячейки тоже тривиальна, но безусловно полезна: 

FUNCTION getcell (
   array_in IN           dim3_t,
   dim1_in               PLS_INTEGER,
   dim2_in               PLS_INTEGER,
   dim3_in               PLS_INTEGER
   )
   RETURN VARCHAR2
IS
BEGIN
   RETURN array_in(dim3_in )(dim2_in )(dim1_in);
END;

Если array_in не содержит ячейки, соответствующей указанным координатам, getcell инициирует исключение NO_DATA_FOUND. Но если какие-либо из переданных координат равны NULL, выдается следующее, уже не столь понятное исключение VALUE_ERROR: 

ORA-06502: PL/SQL: numeric or value error: NULL index table key value

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

Код функции EXISTS уже не столь тривиален. Функция EXISTS должна возвращать TRUE, если ячейка, определяемая координатами, содержится в коллекции, и FALSE в противном случае: 

FUNCTION EXISTS (
   array_in IN    dim3_t,
   dim1_in        PLS_INTEGER,
   dim2_in        PLS_INTEGER,
   dim3_in        PLS_INTEGER
   )
RETURN BOOLEAN
IS
   l_value VARCHAR2(32767);
BEGIN
   l_value := array_in(dim3_in )(dim2_in )(dim1_in);
   RETURN TRUE;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN RETURN FALSE;
END;

Функция перехватывает исключение NO_DATA_FOUND, инициируемое при обращении к несуществующей ячейке при присваивании, и преобразует его в соответствующее логическое значение. Это очень простой и прямолинейный способ получения нужного результата, который демонстрирует творческое применение обработки исключений для управления «условной логикой» функций. Казалось бы, в данной ситуации и нужно использовать оператор EXISTS, но его пришлось бы вызвать для каждого уровня вложенных коллекций. Пример сценария, использующего этот пакет:

DECLARE
   my_3d_array multdim.dim3_t;
BEGIN
   multdim.setcell (my_3d_array, 1, 5, 800, 'def');
   multdim.setcell (my_3d_array, 1, 15, 800, 'def');
   multdim.setcell (my_3d_array, 5, 5, 800, 'def');
   multdim.setcell (my_3d_array, 5, 5, 805, 'def');
   DBMS_OUTPUT.PUT_LINE (multdim.getcell (my_3d_array, 1, 5, 800));
   /*
   Oracle 11g Release позволяет вызывать PUT_LINE с логическими данными!
   */
   DBMS_OUTPUT.PUT_LINE (multdim.EXISTS (my_3d_array, 1, 5, 800));
   DBMS_OUTPUT.PUT_LINE (multdim.EXISTS (my_3d_array, 6000, 5, 800));
   DBMS_OUTPUT.PUT_LINE (multdim.EXISTS (my_3d_array, 6000, 5, 807));
   /*
   Если вы не работаете с Oracle 11g Release 2, используйте процедуру,
   созданную в bpl.sp:
   bpl (multdim.EXISTS (my_3d_array, 1, 5, 800));
   bpl (multdim.EXISTS (my_3d_array, 6000, 5, 800));
   bpl (multdim.EXISTS (my_3d_array, 6000, 5, 807));
   */
   DBMS_OUTPUT.PUT_LINE (my_3d_array.COUNT);
END;

Файл multdim2.pkg содержит доработанную версию пакета multdim, в которой реализована возможность получения «срезов» трехмерной коллекции (одно измерение фиксируется, а из пространства выделяется двумерная плоскость, определяемая зафиксированным измерением). Например, срез температурного пространства даст мне диапазон температур по заданной широте или долготе.

Кроме трудностей, связанных с реализацией срезов, возникает интересный вопрос: будут ли чем-нибудь отличаться срезы по плоскости XY, XZ или YZ в этом симметричном кубе данных? Существенные различия могут повлиять на организацию многомерных коллекций.

Я рекомендую вам самостоятельно изучить эту тему и реализацию пакета multdim2.pkg.

Расширение string_tracker для многоуровневых коллекций

Рассмотрим другой пример применения многоуровневых коллекций: расширение пакета string_tracker из раздела, посвященного строковому индексированию, для поддержки множественных списков строк.

Пакет string_tracker удобен, но он позволяет отслеживать только один набор «используемых» строк в любой момент времени. А если мне потребуется отслеживать сразу несколько списков? Это очень легко делается при помощи многоуровневых коллекций: 

1 PACKAGE BODY string_tracker
2 IS
3    SUBTYPE maxvarchar2_t IS VARCHAR2 (32767);
4    SUBTYPE list_name_t IS maxvarchar2_t;
5    SUBTYPE variable_name_t IS maxvarchar2_t;
6
7    TYPE used_aat IS TABLE OF BOOLEAN INDEX BY variable_name_t;
8
9    TYPE list_rt IS RECORD (
10       description maxvarchar2_t
11       , list_of_values used_aat
12       );
13
14    TYPE list_of_lists_aat IS TABLE OF list_rt INDEX BY list_name_t;
15
16    g_list_of_lists list_of_lists_aat;
17
18    PROCEDURE create_list (
19       list_name_in IN list_name_t
20       , description_in IN VARCHAR2 DEFAULT NULL
21    )
22 IS
23 BEGIN
24    g_list_of_lists (list_name_in).description := description_in;
25 END create_list;
26
27 PROCEDURE mark_as_used (
28    list_name_in IN list_name_t
29    , variable_name_in IN variable_name_t
30    )
31 IS
32 BEGIN
33    g_list_of_lists (list_name_in)
34       .list_of_values (variable_name_in) := TRUE;
35 END mark_as_used;
36
37 FUNCTION string_in_use (
38    list_name_in IN list_name_t
39    , variable_name_in IN variable_name_t
40    )
41    RETURN BOOLEAN
42 IS
43 BEGIN
44    RETURN g_list_of_lists (list_name_in)
45       .list_of_values.EXISTS (variable_name_in);
46    EXCEPTION
47       WHEN NO_DATA_FOUND
48       THEN
49          RETURN FALSE;
50 END string_in_use;
51 END string_tracker;

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

Заметьте, что в третьей реализации string_tracker я использую именованные подтипы во всех объявлениях формальных параметров и особенно в секциях INDEX BY объявлений типов коллекций. Использование подтипов вместо жестко запрограммированных объявлений VARCHAR2 способствует самодокументированию кода. Если этого не сделать, в один прекрасный день вы почешете в затылке и спросите себя: «А что, собственно, я использую как индекс этой коллекции?»

Максимальная глубина вложения

В процессе экспериментов с двух- и трехмерными массивами меня начал интересовать вопрос, до какой же глубины можно вкладывать эти многоуровневые коллекции. Чтобы получить ответ, я построил небольшой генератор кода, который позволяет передавать количество уровней вложения. Генератор строит процедуру, которая объявляет N типов коллекций, каждый из которых является таблицей с элементами типа предыдущей таблицы. Наконец, генератор присваивает значение строке, находящейся на полной глубине вложения коллекций.

Мне удалось создать коллекцию из по крайней мере 250 вложенных коллекций, прежде чем мой компьютер выдал ошибку памяти! Вряд ли какому-нибудь разработчику PL/SQL когда-либо потребуется такой уровень сложности. Если вы захотите провести подобный эксперимент в своей системе, скачате файл gen_multcoll.sp.

Работа с коллекциями в SQL

Я работаю с Oracle уже больше 22 лет, а с PL/SQL — более 18 лет, но мне еще не приходилось ломать голову над семантикой SQL так, как при первом знакомстве с псевдофункциями коллекций, появившимися в Oracle8. Основная цель псевдофункций — заставить таблицы баз данных работать как коллекции, и наоборот. Так как некоторые операции с данными лучше всего работают с данными, находящимися в некоторой конкретной форме, эти функции открывают программисту доступ к богатому и интересному набору структур и операций.

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

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

• CAST — отображает коллекцию одного типа на коллекцию другого типа. В частности, может использоваться для отображения VARRAY на вложенную таблицу
• COLLECT — агрегирует данные в коллекцию в SQL. Эта функция, впервые появившаяся в Oracle Database 10g, была усовершенствована в 11.2 для поддержки упорядочения данных и устранения дубликатов.
• MULTISET — отображает таблицу базы данных на коллекцию. С псевдофункциями MULTISET и CAST появляется возможность выборки строки из таблицы базы данных как из столбца с типом коллекции.
• TABLE — отображает коллекцию на таблицу базы данных. Функция является обратной по отношению к MULTISET: она возвращает один столбец, содержащий отображаемую таблицу.

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

Да, в ходе изучения псевдофункций могут возникнуть проблемы. Но тех, кому доставляет удовольствие разбираться в нетривиальном коде, эти расширения SQL приведут в полный восторг.

Псевдофункция CAST

Оператор CAST может использоваться в командах SQL для преобразования одного встроенного типа данных или типа коллекции в другой встроенный тип данных или тип коллекции. Иначе говоря, в коде SQL конструкция CAST может использоваться вместо TO_CHAR для преобразования чисел в строки.

У CAST также имеется другое полезное применение — преобразование между типами коллекций. В следующем примере рассматривается преобразование именованной коллекции. Допустим, таблица color_models создана на основе типа VARRAY: 

TYPE color_nt AS TABLE OF VARCHAR2(30)
TYPE color_vat AS VARRAY(16) OF VARCHAR2(30)
TABLE color_models (
   model_type VARCHAR2(12),
   colors color_vat);

Столбец colors можно преобразовать во вложенную таблицу с последующим применением к результату псевдофункции TABLE (см. далее). База данных присваивает единственному столбцу полученной виртуальной таблицы имя COLUMN_VALUE. Его можно заменить любым другим именем при помощи псевдонима столбца: 

SELECT COLUMN_VALUE my_colors
FROM TABLE (SELECT CAST(colors AS color_nt)
            FROM color_models
            WHERE model_type = 'RGB')

CAST преобразует тип коллекции color_vat к типу color_nt. При этом CAST не может служить приемником для команд INSERT, UPDATE или DELETE.

Начиная с Oracle Database 10g явное преобразование коллекции в операторе TABLE уже не требуется. Вместо этого база данных автоматически определяет правильный тип.

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

Псевдофункция COLLECT

Агрегатная функция COLLECT, появившаяся в Oracle 10g, позволяет объединить данные из команды SQL в коллекцию. В 11.2 эта функция была усовершенствована, и разработчик получил возможность упорядочить агрегированные результаты и устранить дубликаты в процессе агрегирования. Пример вызова COLLECT с упорядочением результатов (приводятся только первые три строки):

SQL> CREATE OR REPLACE TYPE strings_nt IS TABLE OF VARCHAR2 (100)
2 /
SQL> SELECT department_id,
2 CAST (COLLECT (last_name ORDER BY hire_date) AS strings_nt)
3 AS by_hire_date
4 FROM employees
5 GROUP BY department_id
6 /
DEPARTMENT_ID BY_HIRE_DATE
------------- -----------------------------------------------------------------
           10 STRINGS_NT('Whalen')
           20 STRINGS_NT('Hartstein', 'Fay')
           30 STRINGS_NT('Raphaely', 'Khoo', 'Tobias', 'Baida', 'Colmenares')
Следующий пример демонстрирует удаление дубликатов в процессе агрегирования:
SQL> SELECT department_id,
2 CAST (COLLECT (DISTINCT job_id) AS strings_nt)
3 AS unique_jobs
4 FROM employees
5 GROUP BY department_id
6 /
DEPARTMENT_ID UNIQUE_JOBS
------------- --------------------------------------
           10 STRINGS_NT('AD_ASST')
           20 STRINGS_NT('MK_MAN', 'MK_REP')
           30 STRINGS_NT('PU_CLERK', 'PU_MAN')

Превосходная статья о COLLECT размещена на сайте Oracle Developer.

Псевдофункция MULTISET

Функция MULTISET может использоваться только в сочетании с CAST. MULTISET позволяет получить набор данных и «на ходу» преобразовать его к типу коллекции. (Не путайте функцию SQL MULTISET с операторами PL/SQL MULTISET для вложенных таблиц, рассматриваемыми в разделе «Операции мультимножеств с вложенными таблицами».)

Простейшая форма MULTISET выглядит так: 

SELECT CAST (MULTISET (SELECT поле FROM таблица) AS тип_коллекции) FROM DUAL;

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

SELECT outerfield,
   CAST(MULTISET(SELECT field FROM whateverTable
                 WHERE correlationCriteria)
   AS collectionTypeName)
FROM outerTable 

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

CREATE TABLE birds (
   genus VARCHAR2(128),
   species VARCHAR2(128),
   colors color_tab_t,
   PRIMARY KEY (genus, species)
);
CREATE TABLE bird_habitats (
   genus VARCHAR2(128),
   species VARCHAR2(128),
   country VARCHAR2(60),
   FOREIGN KEY (genus, species) REFERENCES birds (genus, species)
);
CREATE TYPE country_tab_t AS TABLE OF VARCHAR2(60);

Главная и подчиненная таблицы объединятся в инструкции SELECT, преобразующей подчиненные записи в коллекцию. Данная возможность чрезвычайно важна для программ «клиент/сервер», потому что она позволяет сократить количество циклов пересылки данных без затрат на дублирование главной записи в каждой подчиненной записи:

DECLARE
   CURSOR bird_curs IS
      SELECT b.genus, b.species,
      CAST(MULTISET(SELECT bh.country FROM bird_habitats bh
                     WHERE bh.genus = b.genus
                     AND bh.species = b.species)
      AS country_tab_t)
      FROM birds b;
   bird_row bird_curs%ROWTYPE;
BEGIN
   OPEN bird_curs;
   FETCH bird_curs into bird_row;
   CLOSE bird_curs;
END;

Как и псевдофункция CAST, MULTISET не может служить приемником для команд INSERT, UPDATE или DELETE.

Псевдофункция TABLE

Оператор TABLE преобразует столбец со значениями-коллекциями в источник, из которого можно получать данные инструкцией SELECT. Вроде бы сложно, но в этом разделе приведен пример, разобраться в котором будет легко.

Допустим, имеется таблица базы данных со столбцом, относящимся к типу коллекции. Как определить, какие строки таблицы содержат коллекцию, удовлетворяющую определенному критерию? Иначе говоря, как осуществить выборку из таблицы с условием WHERE для содержимого коллекции? Конечно, хотелось бы использовать запись следующего вида:

SELECT *
FROM table_name
WHERE collection_column
         HAS CONTENTS 'whatever'; -- НЕДОПУСТИМО! Такого синтаксиса нет!

Именно эту задачу можно решить при помощи функции TABLE. Вернемся к примеру с таблицей color_models; как получить список всех цветовых моделей, содержащих цвет RED? Вот как это делается: 

SELECT *
FROM color_models c
WHERE 'RED' IN
   (SELECT * FROM TABLE(c.colors));

В SQL*Plus будет получен следующий результат:

MODEL_TYPE     COLORS
------------  -------------------------------------
RGB            COLOR_TAB_T('RED', 'GREEN', 'BLUE')

Приведенный запрос означает: «перебрать содержимое таблицы color_models и вернуть все записи, список цветов которых содержит как минимум один элемент RED». Если бы в таблице нашлись другие строки, в которых в столбце colors присутствует элемент RED, то они бы тоже были выведены в результирующем наборе SQL*Plus.

Как было показано выше, в единственном аргументе TABLE передается коллекция, в качестве которой может передаваться столбец, уточненный псевдонимом: 

TABLE(псевдоним.имя_коллекции)

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

/* Создание типа уровня схемы. */
CREATE OR REPLACE TYPE list_of_names_t
   IS TABLE OF VARCHAR2 (100);
/
/* Заполнение коллекции с последующим использованием цикла FOR
с курсором для выборки всех элементов и их вывода. */
DECLARE
   happyfamily list_of_names_t := list_of_names_t ();
BEGIN
   happyfamily.EXTEND (6);
   happyfamily (1) := 'Eli';
   happyfamily (2) := 'Steven';
   happyfamily (3) := 'Chris';
   happyfamily (4) := 'Veva';
   happyfamily (5) := 'Lauren';
   happyfamily (6) := 'Loey';
   FOR rec IN ( SELECT COLUMN_VALUE family_name
                FROM TABLE (happyfamily)
                ORDER BY family_name)
   LOOP
      DBMS_OUTPUT.put_line (rec.family_name);
   END LOOP;
END;
/

До выхода Oracle Database 12c псевдофункция TABLE могла использоваться только с вложенными таблицами и массивами VARRAY и только в том случае, если их типы определялись на уровне схемы конструкцией CREATE OR REPLACE TYPE (у этого правила было одно исключение: конвейерные табличные функции могли работать с типами, определенными в спецификациях пакета). Однако начиная с Oracle Database 12c псевдофункция TABLE может использоваться с вложенными таблицами, массивами VARRAY и ассоциативными массивами с целочисленным индексированием при условии, что их типы определяются в спецификации пакета: 

/* Создание типа на базе пакета. */
CREATE OR REPLACE PACKAGE aa_pkg
IS
   TYPE strings_t IS TABLE OF VARCHAR2 (100)
   INDEX BY PLS_INTEGER;
END;
/
/* Заполнение коллекции с последующим использованием цикла FOR
с курсором для выборки всех элементов и их вывода. */
DECLARE
   happyfamily aa_pkg.strings_t;
BEGIN
   happyfamily (1) := 'Me';
   happyfamily (2) := 'You';
   FOR rec IN ( SELECT COLUMN_VALUE family_name
                FROM TABLE (happyfamily)
                ORDER BY family_name)
   LOOP
      DBMS_OUTPUT.put_line (rec.family_name);
   END LOOP;
END;
/

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

DECLARE
   TYPE strings_t IS TABLE OF VARCHAR2 (100)
         INDEX BY PLS_INTEGER;
   happyfamily strings_t;
BEGIN
   happyfamily (1) := 'Me';
   happyfamily (2) := 'You';
   FOR rec IN ( SELECT COLUMN_VALUE family_name
                FROM TABLE (happyfamily)
                ORDER BY family_name)
   LOOP
      DBMS_OUTPUT.put_line (rec.family_name);
   END LOOP;
END;
/
ERROR at line 12:
ORA-06550: line 12, column 32:
PLS-00382: expression is of wrong type
ORA-06550: line 12, column 25:
PL/SQL: ORA-22905: cannot access rows from a non-nested table item

Еще раз повторим: псевдофункции коллекций недоступны в коде PL/SQL, но программисту PL/SQL определенно стоит научиться пользоваться ими в командах SQL!

Псевдофункции (особенно TABLE) очень удобны при использовании табличных функций, появившихся в Oracle9i Database. Табличная функция возвращает коллекцию и может использоваться в секции FROM запроса.

Сортировка содержимого коллекций

Одна из самых замечательных особенностей псевдофункций заключается в том, что они позволяют применять операции SQL к содержимому структур данных PL/SQL (по крайней мере к вложенным таблицам и VARRAY). Например, при помощи ORDER BY можно извлечь информацию из вложенной таблицы в нужном порядке. Сначала таблица базы данных заполняется именами писателей: 

   TYPE names_t AS TABLE OF VARCHAR2 (100)
   TYPE authors_t AS TABLE OF VARCHAR2 (100)
   TABLE favorite_authors (name varchar2(200));
BEGIN
   INSERT INTO favorite_authors VALUES ('Robert Harris');
   INSERT INTO favorite_authors VALUES ('Tom Segev');
   INSERT INTO favorite_authors VALUES ('Toni Morrison');
END;

А теперь мне хотелось бы объединить эту информацию с данными из программы PL/SQL:

DECLARE
   scifi_favorites authors_t
      := authors_t ('Sheri S. Tepper', 'Orson Scott Card', 'Gene Wolfe');
BEGIN
   DBMS_OUTPUT.put_line ('I recommend that you read books by:');
   FOR rec IN (SELECT COLUMN_VALUE favs
               FROM TABLE (CAST (scifi_favorites AS names_t))
               UNION
               SELECT NAME
               FROM favorite_authors)
   LOOP
      DBMS_OUTPUT.put_line (rec.favs);
   END LOOP;
END;

Обратите внимание на использование UNION для объединения данных из таблицы и коллекции. Этот прием также можно применить только к данным PL/SQL с целью сортировки: 

DECLARE
   scifi_favorites authors_t
      := authors_t ('Sheri S. Tepper', 'Orson Scott Card', 'Gene Wolfe');
BEGIN
   DBMS_OUTPUT.put_line ('I recommend that you read books by:');
   FOR rec IN (SELECT COLUMN_VALUE Favs
               FROM TABLE (CAST (scifi_favorites AS authors_t))
               ORDER BY COLUMN_VALUE)
   LOOP
      DBMS_OUTPUT.put_line (rec.favs);
   END LOOP;
END;

COLUMN_VALUE в приведенном выше запросе — сгенерированное системой имя столбца, созданного оператором TABLE (при выборке только одного столбца). Если запрос обращен к нескольким столбцам, вместо COLUMN_VALUE будут использованы имена атрибутов соответствующего объектного типа.

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

Управление приложениями PL/SQL... 4651 просмотров Stas Belkov Thu, 16 Jul 2020, 06:20:48

Встроенные методы коллекций PL... 14838 просмотров sepia Tue, 29 Oct 2019, 09:54:01

Основы языка PL/SQL: использов... 4700 просмотров Ирина Светлова Tue, 06 Feb 2018, 14:04:03

Программирование динамического... 4919 просмотров Максим Николенко Sun, 09 Sep 2018, 06:56:23