Моделирование групп объектов в Oracle

Владимир Пржиялковский,
Мастерская Oracle, Interface Ltd
Введение

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

Такая возможность предусматривалась, например, в сетевой модели данных, исторически предшествовавшей реляционной, и проектировалась в виде расширения реляционной создателем последней Э. Коддом (в силу ряда причин это расширение было проигнорировано разработчиками промышленных "реляционных" СУБД). Здесь будут рассмотрены возможности моделирования групп объектов, реализованные в Oracle последних версий (8, 9).
Таблицы хранимых и синтезированных объектов

Первая возможность моделирования групп из объектов в Oracle известна по предыдущей статье: это таблицы "исконных" объектов (object tables) и таблицы "виртуальных", или "синтезированных" объектов (object views). Исконные объекты хранятся как самостоятельные сущности в БД, а синтезированные дают только видимость объектов (по потребительским свойствам почти не отличимую от истинных объектов) на основе данных, хранимых в обычных или объектных таблицах.

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

DROP TYPE employee_typ FORCE;
DROP TABLE e_moscow;
DROP TABLE e_leningrad;
DROP TABLE employment;

CREATE TYPE employee_typ AS OBJECT (
ename VARCHAR2(50),
job VARCHAR2(10))
/

CREATE TABLE e_moscow OF employee_typ;

CREATE TABLE e_leningrad OF employee_typ;

INSERT INTO e_moscow VALUES (
'Scott',
'Manager');
...

INSERT INTO e_leningrad VALUES (
'Smith',
'Salesman');
...

CREATE TABLE employment (
dname VARCHAR2(50),
employee REF employee_typ);

INSERT INTO employment VALUES (
'Operations',
(SELECT REF(m) FROM e_moscow m WHERE m.ename = 'Scott'));
...

Этот способ, однако, не лишен своих ограничений. Например, по данным таблицы EMPLOYMENT нельзя понять, проживает ли сотрудник в Москве или Ленинграде. Нельзя переселить сотрудника из Москвы в Ленинград (можно только удалить его из одной таблицы и создать в другой объект с теми же атрибутами) и так далее.
Коллекции

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

В Oracle они могут быть двух видов: вложенные таблицы и массивы типа VARRAY.
Вложенные таблицы

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

CREATE TYPE employee_nlist_typ AS TABLE OF employee_typ
/

CREATE TABLE department (
dname VARCHAR2(20),
emps employee_nlist_typ)
NESTED TABLE emps STORE AS emps_nt_tab;

Фраза NESTED TABLE - чисто техническая. Она обязана тому, что физически Oracle будет хранить в поле EMPS не список объектов-сотрудников, а список их системных идентификаторов, присвоенных при помещении самих сотрудников в специально создаваемую для таблицы DEPARTMENT служебную таблицу. Таким образом при использовании вложенных таблиц значения элементов хранимых списков физически всегда хранятся в специальной отдельной таблице, которой мы обязаны придумать название. В таком решении есть своя логика, так как для этой специальной таблицы мы можем указать собственные характеристики хранения и некоторые другие вещи.

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



По терминологии предыдущей статьи сотрудники в таблице DEPARTMENT - "объектные атрибуты". Другой способ смоделировать ситуацию "сотрудники-отделы" с помощью коллекции - воспользоваться списком ссылок на сотрудников, реально существующих в собственных таблицах.
Работа в PL/SQL

Вот как можно работать со вложенными таблицами в PL/SQL:

DECLARE
ee employee_nlist_typ;
BEGIN
SELECT emps INTO ee FROM department WHERE dname = 'Operations';
DBMS_OUTPUT.PUT_LINE(ee(1).ename);
DBMS_OUTPUT.PUT_LINE(ee(2).ename);
END;
/

В этом примере для упрощения использованы предпосылки о том, что (а) отдел с названием 'Operations' всего один и (в) сотрудников в нем - [по крайней мере] двое.
Массивы типа VARRAY

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

Пример использования для группировки сотрудников коллекции VARRAY может выглядеть так:

CREATE TYPE employee_vlist_typ AS VARRAY(20) OF employee_typ
/

CREATE TABLE department1 (
dname VARCHAR2(15),
emps employee_vlist_typ
);

Этим типом мы запретили отделам иметь более 20-и сотрудников.

Добавление нового отдела делается как и для вложенных таблиц:

INSERT INTO department1 VALUES (
'Operations',
employee_vlist_typ (
employee_typ ('Scott', 'Manager'),
employee_typ ('Smith', 'Salesman')
)
);

Приведенный выше код на PL/SQL для массива сотрудников VARRAY проработает так же.
Преобразования коллекций

Как и следовало бы ожидать от СУБД Oracle, плотная, а не поверхностная работа с коллекциями в качестве средства моделирования групп объектов требует знания большого числа "деталей". Здесь не место разъяснять их все, но одну важную для коллекций возможность стоит привести. Имеется в виду разворачивание коллекции в список строк, столь привычный для традиционной работы.

Для того, чтобы посмотреть список сотрудников отдела 'Operations' в более привычном виде, следует воспользоваться специальной функцией TABLE:

SELECT * FROM TABLE(SELECT emps FROM department);

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

Аналогичный пример для массива VARRAY:

SELECT ename FROM TABLE(SELECT emps FROM department1);

Замечательно, что это преобразование решает задачу и изменения списка средствами SQL:

INSERT INTO TABLE(SELECT emps FROM department)
VALUES ('Allen', 'Salesman');

SELECT * FROM TABLE(SELECT emps FROM department);

(Эта возможность не сработает, однако, для массива VARRAY, который в БД ведет себя, по сути, как скаляр, допуская изменение поля-списка как единого, уже сформированного целого). Если бы возможность такого преобразования отсутствовала, добавить сотрудника в отдел или удалить можно было бы только программным способом, проще всего в PL/SQL.

Естественно, никто не мешает осуществить и массовую вставку:

INSERT INTO TABLE(SELECT emps FROM department)
SELECT ename, job FROM emp;

Для обратного преобразования, из таблицы в коллекцию, потребуется более сложная конструкция:

SELECT
CAST (MULTISET(SELECT ename, job FROM emp) AS employee_nlist_typ)
FROM DUAL;

Однако такое преобразование на практике менее востребовано.