|
Стивен Ферстайн
Объектные типы в коллекциях
(On Object Types in Collections, by Steven Feuerstein)
Источник: журнал Oracle Magazine, #6 2006, http://www.oracle.com/technology/oramag/oracle/06-nov/o66plsql.html
Лучшие способы извлечения объектов и атрибутов объектов из других объектов
Я создал объектный тип, массив (varray), который имеет три столбца с типами данных number, varchar2 и другой объект, соответственно. Как мне извлечь третий атрибут (объектного типа) из массива?
Давайте сконструируем массив (это такой тип коллекции, в котором может содержаться максимальное число элементов, описанных в коллекции), который удовлетворяет вашим требованиям, и я покажу, как можно сослаться на каждый из них или на их часть. Так как мне нравится вкусно поесть, то для примера я возьму тему про еду.
Сначала создадим объектный тип для общих параметров некоего блюда; эти три атрибута позволяют узнать о названии блюда, его группе и основной его расцветке:
CREATE TYPE food_t AS OBJECT (
name VARCHAR2 ( 100 )
, food_group VARCHAR2 ( 100 )
, color VARCHAR2 ( 100 )
);
/
Затем создадим объектный тип для трапезы, имеющей атрибуты: количество людей, на которое она рассчитана, тип трапезы, и её оформление:
CREATE TYPE meal_t AS OBJECT (
number_served INTEGER
, meal_type VARCHAR2 ( 100 )
, food_served food_t
);
/
Теперь создадим массив из трех элементов для перечисления всех ежедневных трапез:
CREATE TYPE meals_vat
IS VARRAY ( 3 ) OF meal_t;
/
Затем напишем код Листинга 1 для заполнения массива тремя трапезами: завтрак, обед и ужин.
Листинг 1: Заполнение массива
DECLARE
-- Локально объявленный массив инициализирован без элементов.
l_one_day_of_meals meals_vat := meals_vat ( );
BEGIN
-- Выделяем память для трёх трапез.
l_one_day_of_meals.EXTEND ( 3 );
-- Добавляем завтрак, используя конструктор как для самой трапезы,
-- так и для экземпляра объектного типа, входящего в её состав.
l_one_day_of_meals ( 1 ) :=
meal_t ( 4, 'BREAKFAST'
, food_t ( 'Scrambled Eggs', 'Protein', 'Yellow' ));
-- Добавляем обед
l_one_day_of_meals ( 2 ) :=
meal_t ( 6, 'LUNCH'
, food_t ( 'Deluxe Salad', 'Vegetables', 'Mostly Green' ));
-- Добавляем ужин
l_one_day_of_meals ( 3 ) :=
meal_t ( 10, 'DINNER'
, food_t ( 'Tofu and Rice', 'Protein', 'White' ));
END;
Заметьте, что функции-конструкторы (функции, которые имеют то же самое название, что и тип, для которого они предназначены, создаются Oracle Database) используются для создания экземпляра объектного типа в строке, где выполняется присваивание. Я объявил также локальные переменные для хранения внутренних объектов, как показано ниже:
DECLARE
-- Локально объявленный массив
-- инициализированный без элементов.
l_one_day_of_meals meals_vat
:= meals_vat ( );
-- Локальный экземпляр объектного типа для завтрака
l_breakfast food_t
:= food_t ( 'Scrambled Eggs',
'Protein', 'Yellow' );
BEGIN
l_one_day_of_meals.EXTEND;
l_one_day_of_meals ( 1 )
:= meal_t ( 4, 'BREAKFAST',
l_breakfast );
END;
/
Этот пример показывает, как в массив добавляются элементы. Теперь попробуем обратиться к значениям внутри элемента массива, используя код, показанный на Листинге 2.
Листинг 2: Доступ к элементам массива
SQL> DECLARE
2 -- Локально объявленный массив, инициализированный одним элементом.
3 l_one_day_of_meals meals_vat
4 := meals_vat ( meal_t ( 4
5 , 'BREAKFAST'
6 , food_t ( 'Scrambled Eggs', 'Protein', 'Yellow' )
7 )
8 );
9 BEGIN
10 -- Если в трапезе участвует 2 и более людей,
11 -- покажем название блюда.
12 IF l_one_day_of_meals ( 1 ).number_served > 2
13 THEN
14 DBMS_OUTPUT.put_line (
15 l_one_day_of_meals ( 1 ).food_served.name );
16 END IF;
17 END;
Отметим интересные строки в Листинге 2:
Строки 4-8. В этом блоке инициализируется массив с одним элементом, с помощью обеих функций-конструкторов meal_t и food_t для загрузки первой строки в массив с данными про завтрак.
Строка 12. Я выяснил количество людей, на которое рассчитан завтрак, указав массив и затем порядковый номер в этой коллекции:
l_one_day_of_meals ( 1 )
Строка 15. Показано название блюда, приготовленного на завтрак, с помощью точечной нотации (указав атрибут объекта, как будто бы столбец таблицы: таблица.столбец) для получения атрибута элемента массива:
l_one_day_of_meals ( 1 ).food_served
Атрибут food_served, однако, существенно отличается от number_served. Это не числовое значение, а целый объект. Таким образом, я могу вычислить значение атрибута этого объекта, ещё раз используя точку после названия этого атрибута:
l_one_day_of_meals ( 1 ).food_served.name
Этот пример позволяет понять, как ссылаться на элементы и подэлементы в этих сложных структурах.
Извлечение объектных атрибутов из объектов
Я объявил вложенную таблицу объектного типа, которая имеет три столбца с типами number, varchar2 и другим объектом, соответственно. Затем я описал реляционную таблицу с вложенной таблицей в качестве столбца. Как извлечь атрибут этого объекта из вложенной таблицы в строке реляционной таблицы? Предыдущий ответ этой статьи описывает, как использовать точечную нотацию для того, чтобы добраться до атрибутов объектов коллекции. Теперь давайте рассмотрим, как извлечь эти данные, если они хранятся в столбце таблицы. В этом ответе я работаю с вложенными таблицами вместо массивов. Всё, что я здесь демонстрирую, применимо и к обоим типам коллекций, за исключением отдельно указанных случаев.
Я создал таблицу на основании типов, описанных выше в предыдущем вопросе, и вот этот новый тип вложенной таблицы:
CREATE TYPE meals_nt IS
TABLE OF meal_t;
/
CREATE TABLE all_my_meals (
date_served DATE,
name VARCHAR2(100),
meals_served meals_nt
) NESTED TABLE meals_served
STORE AS i_meals_nt
/
Затем я вставил две строки в эту таблицу как показано на Листинге 3.
Листинг 3: Обращение к элементам массива
DECLARE
-- Локально объявленный массив, инициализированный без элементов.
l_one_day_of_meals meals_nt := meals_nt ( );
BEGIN
-- Выделяем место для трёх трапез.
l_one_day_of_meals.EXTEND ( 3 );
-- Добавляем завтрак, используя конструктор как для трапезы
-- так и для экземпляра объектного типа для блюда.
l_one_day_of_meals ( 1 ) :=
meal_t ( 4, 'BREAKFAST'
, food_t ( 'Scrambled Eggs', 'Protein', 'Yellow' ));
-- Добавляем обед
l_one_day_of_meals ( 2 ) :=
meal_t ( 6
, 'LUNCH'
, food_t ( 'Deluxe Salad', 'Vegetables', 'Mostly Green' )
);
-- Добавляем ужин
l_one_day_of_meals ( 3 ) :=
meal_t ( 10, 'DINNER', food_t ( 'Tofu and Rice', 'Protein', 'White' ));
-- Помещаем трапезы в таблицу.
INSERT INTO all_my_meals
VALUES ( SYSDATE, 'YumYum', l_one_day_of_meals );
-- На следующий день изменяем завтрак и обед
l_one_day_of_meals ( 3 ) :=
meal_t ( 4, 'BREAKFAST', food_t ( 'Donuts', 'Sugar', 'White' ));
l_one_day_of_meals ( 3 ) :=
meal_t ( 4, 'DINNER', food_t ( 'Big Thick Steak', 'Protein', 'Brown' ));
INSERT INTO all_my_meals
VALUES ( SYSDATE, 'Lumberjack', l_one_day_of_meals );
COMMIT;
END;
/
Затем я могу извлечь данные из реляционной таблицы, как показано ниже:
SQL> select * from all_my_meals
2 /
DATE_SERV
------------
MEALS_SERVED(NUMBER_SERVED, MEAL_TYPE, FOOD_SERVED(NAME, FOOD_GROUP, COLOR))
-----------------------------------------------------------------------------
22-JUL-06
MEALS_nt(MEAL_T(4, 'BREAKFAST', FOOD_T('Scrambled Eggs', 'Protein', 'Yellow')),
MEAL_T(6, 'LUNCH', FOOD_T('Deluxe Salad', 'Vegetables', 'Mostly Green')),
MEAL_T(10, 'DINNER', FOOD_T('Tofu and Rice', 'Protein', 'White')))
23-JUL-06
MEALS_nt(MEAL_T(4, 'BREAKFAST', FOOD_T('Scrambled Eggs', 'Protein',
.
.
.
Однако не хочется просто отображать все эти данные. Хочется добраться до атрибута объекта во вложенной таблице. Предположим, что надо увидеть тип трапезы для каждой строки вложенной таблицы.
Запрос в строках ниже выглядит, как если бы применить точечную нотацию:
SQL> SELECT amm.meals_served.meal_type
2 FROM all_my_meals amm
3 /
К сожалению, это не будет работать; вы получите следующую ошибку:
SELECT amm.meals_served.meal_type
*
ERROR at line 1:
ORA-00904: "AMM"."MEALS_SERVED"."MEAL_TYPE": invalid identifier
Жизнь несколько сложнее, но не сильно.
Для получения соответствующего атрибута объекта вложенной таблицы к ней следует применить оператор TABLE:
SQL> SELECT ms.meal_type
2 FROM TABLE (
3 SELECT meals_served
4 FROM all_my_meals
5 WHERE name = 'YumYum' ) ms
6 /
MEAL_TYPE
-------------------------------------
BREAKFAST
LUNCH
DINNER
Другими словами, я извлекаю именно вложенную таблицу из таблицы all_my_meals. Я применяю оператор TABLE к каждой из вложенных таблиц, и затем могу использовать точечную нотацию для получения типа трапезы.
Я могу даже сослаться на атрибуты объектов вложенной таблицы, чтобы ограничить строки, возвращаемые по запросу. Например:
SQL> SELECT ms.meal_type
2 FROM TABLE (
3 SELECT meals_served
4 FROM all_my_meals
5 WHERE name = 'YumYum' ) ms
6 WHERE ms.number_served > 4
7 /
MEAL_TYPE
-------------------------------------
LUNCH
DINNER
Можно не только запросить данные из вложенной таблицы, но и изменить информацию во вложенной таблице с помощью предложения UPDATE. (Заметьте, что при работе с массивом нельзя изменить отдельные элементы массива. Изменить можно только все элементы сразу — то есть заменить массив.)
На основании предыдущих примеров можно ожидать, что изменится не сама таблица (meals_served), а вложенная таблица, извлечённая из этой таблицы.
Предположим, например, что надо изменить количество участвующих в завтраке для строки с наименованием "YumYum", на 15. Предложение для изменения будет выглядеть следующим образом:
SQL> UPDATE TABLE (
2 SELECT meals_served
3 FROM all_my_meals
4 WHERE name = 'YumYum' )
5 SET number_served = 15
6 WHERE meal_type = 'BREAKFAST'
7 /
1 row updated.
Существует, конечно, множество других вариантов SQL-предложений, но я надеюсь, что теперь вы лучше понимаете основной принцип, чтобы в дальнейшем выполнять их самостоятельно.
В документации по Oracle приводится очень примеров из моих разработок. Я надеюсь, что вы ознакомитесь с "Using PL/SQL Collections with SQL Object Types" in the Oracle Database PL/SQL User's Guide and Reference в
download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/objects.htm#sthref2434.
Стивен Фернстайн (Steven Feuerstein (steven@stevenfeuerstein.com)) – проповедник PL/SQL из Quest Software. Он опубликаовал 10 книг по языку Oracle программирования, включая книги “Oracle PL/SQL Programming” и “Oracle PL/SQL Best Practices” (изд. O'Reilly Media). Feuerstein ведет сайт инструментов тестирования программ на PL/SQL (www.unit-test.com).
| 
