Столбец таблицы может быть объявлен как varray (массив переменной длины) или table (вложенная таблица). Вот несколько инструкций create type и create table, в которых для создания таблиц, аналогичных представленным на рис. 23.6, используются два новых типа данных:

CREATE TABLE REPS (

EMPL NUM INTEGER,

NAME NAME TYPE, ADDRESS ADDR TYPE,

SLS MGR INTEGER, /* идентификатор менеджера */

SALARY MONEY{9,2), /* годовой оклад */

QUOTA MONEY(9,2), /* плановый объем продаж */

QTR TGT VARRAY(4) OF NUMBER(9,2)); /* четыре квартальных плановых

объема продаж */

CREATE TYPE DEGR TYPE AS OBJECT {{ DEGREE VARCHAR(3), SCHOOL VARCHAR(15));

CREATE TABLE ENGINEERS ( EMPL NUM INTEGER,

NAME NAME TYPE, ADDRESS ADDR TYPE, SALARY MONEY(9,2), /* годовой оклад */

YRS EXPER INTEGER, /* стаж */

DEGREES TABLE OF DEGR TYPE); NESTED TABLE DEGREES STORE AS DEGREES TABLE;

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

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

Коллекции и запросы на выборку

Наличие столбцов, содержащих коллегащш, усложняет формулирование запросов к таблицам и обработку их результатов. Объектно-реляционные СУБД обычно поддерживают набор специальных расщирений SQL, позволяющих вьтолнять простейщие запросы, включающие коллекции данных. В случае более сложных запросов обычно приходится писать хранимые процедуры с циклами для обработки элементов коллекций.

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

SELECT EMPL NUM, NAME FROM TECHNICIANS WHERE bingo IN (PROJECTS);

Имя столбца, содержащего коллекцию (в данном случае столбца projects), указывается в скобках после оператора in. В интерактивном режиме можно включать такие столбцы (типа set, list или multiset) в предложение select - выполнив запрос, Informix выведет все содержимое этих столбцов на экран. Для обработки результатов подобных запросов в приложениях (использующих встроенный SQL или библиотеку API-функций) нужно использовать специальные API-функции или расширения Informix SPL.

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

SELECT NEST.SCHOOL

FROM THE (SELECT DEGREES

FROM ENGINEERS WHERE EMPL NUM = 1234) NEST;

Запрос в скобках обращен к главной таблице (engineers). Он извлекает из нее столбец, содержащий вложенную таблицу, но может извлекать также и др>тие столбць[. Оператор the, примененнь[й к таблице результатов этого запроса, делает ее плоской , создавая по одной строке для каждой строки каждой вложенной таблицы. Полученной таблице присваивается псевдоним (в данном случае nest), и она становится источником строк для запроса верхнего уровня. Последний предельно прост: он извлекает из таблицы единственный столбец school.

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

Манипулирование коллекциями данных

Для добавления новых строк в таблицы со столбцами, содержащими коллекции, в Informix используются три конструктора: конструктор множества (set), конструктор мультимножества (multiset) и конструктор списка (list). Они преобразуют наборы отдельных значений в коллекции соответствующего типа. Вот пара инструкций insert, добавляющих строки в таблицы, представленные на рис. 23.6:

INSERT INTO TECHNICIANS VALUES (1279,

ROW С Sam, R, Jones),

ROW(164 Elm St. , Highland, il, ROW(12345, 6789)), SET{atlas , checkmate, bmgo) );

INSERT INTO ENGINEERS VALUES (1281,

ROW (Jeff, R, .Ames),

ROW I1648 Green St. , Elgin, IL, ROW(12345, 6789)), MOLTISET{ROW(BS, Michigan),

ROWCBS, Michigan), ROW(PhD, Stanford)) );

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

В Oracle выбран другой подход к построению коллекций, предназначенньгх для вставки в таблицы. Обсуждая абстрактные типы данных Oracle, мы говорили о том, что с каждым АТД автоматически связывается метод-конструктор. Эта концепция распространяется также на массивы переменной длины и вложенные таблицы. Для каждого из этих типов данных автоматически создается конструктор, который можно использовать для вставки значений этих типов в таблицы. Вот как следует применять конструкторы в инструкции insert:

insert into TECHNICIANS VALUES (1279,

NAME TYPE(Sam, R, Jones), addr TYPE(164 Elm St., Highland, IL,

POST TYPE(12345, 6789)), PROJECTS(atlas, checkmate, bingo));

insert into ENGINEERS VALUES (1281,

NAME TYPE(Jeff, R, Ames), ADDR TYPE(1648 Green St., Elgin, IL

POST TYPE(12345, 6789)), DEGREES(DEGREE TYPE(bs, Michigan), degree TYPE(bs, Michigan), DEGREE TYPE(PhD, Stanford)));

Коллекции и хранимые процедуры

Для хранимых процедур, работающих с таблицами, коллекции представляют ряд новых проблем. С целью их рещения и в Oracle, и в Informix язык хранимых процедур дополнен специальными возможностями. В Informix для работы с коллекциями вводится новый тип переменных - collection. Вот фрагмент хранимой процедуры, обрабатывающей столбец projects из таблицы technicians.

define proj coll collection; /* коллекция проектов */

define a project varchar(15); /* отдельный проект */

define pro3 cnt integer; /* число проектов */

define empl name name type; /* буфер для имени технолога */

/* Выясняем, в скольких проектах участвует технолог */ select cardinality(pro3ects) into proj cnt from technicians where empl num = 1234;

/* Если проектов слишком много, отказываемся добавить еще один */ if (proj cnt > 6) then ...