Оглавление
- Введение
- Таблицы хранимых и синтезированных объектов
- Коллекции
- Вложенные таблицы
- Работа в PL/SQL
- Массивы типа VARRAY
- Преобразования коллекций
Объектный подход к моделированию БД, безусловно, имеет свою притягательность, хотя, как замечалось в предыдущей статье ценность его преувеличивать не стоит, так как и он не лишен собственных проблем и ограничений (к слову сказать, известных задолго до середины 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 служебную таблицу. Таким образом при использовании вложенных таблиц значения элементов хранимых списков физически всегда хранятся в специальной отдельной таблице, которой мы обязаны придумать название. В таком решении есть своя логика, так как для этой специальной таблицы мы можем указать собственные характеристики хранения и некоторые другие вещи.
Вот как можно заполнить таблицу отделов:
INSERT INTO department VALUES (
'Operations',
employee_nlist_typ (
employee_typ ('Scott', 'Manager'),
employee_typ ('Smith', 'Salesman')
)
);
В отделе Operations теперь два сотрудника:
COLUMN emps FORMAT A60 WORD
SELECT * FROM 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;
Однако такое преобразование на практике менее востребовано.