Logo Море(!) аналитической информации!
IT-консалтинг Software Engineering Программирование СУБД Безопасность Internet Сети Операционные системы Hardware
VPS/VDS серверы. 30 локаций на выбор

Серверы VPS/VDS с большим диском

Хорошие условия для реселлеров

4VPS.SU - VPS в 17-ти странах

2Gbit/s безлимит

Современное железо!

Бесплатный конструктор сайтов и Landing Page

Хостинг с DDoS защитой от 2.5$ + Бесплатный SSL и Домен

SSD VPS в Нидерландах под различные задачи от 2.6$

✅ Дешевый VPS-хостинг на AMD EPYC: 1vCore, 3GB DDR4, 15GB NVMe всего за €3,50!

🔥 Anti-DDoS защита 12 Тбит/с!

2004 г.

Моделирование групп объектов в 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;

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

VPS в 21 локации

От 104 рублей в месяц

Безлимитный трафик. Защита от ДДоС.

🔥 VPS до 5.7 ГГц под любые задачи с AntiDDoS в 7 локациях

💸 Гифткод CITFORUM (250р на баланс) и попробуйте уже сейчас!

🛒 Скидка 15% на первый платеж (в течение 24ч)

Скидка до 20% на услуги дата-центра. Аренда серверной стойки. Colocation от 1U!

Миграция в облако #SotelCloud. Виртуальный сервер в облаке. Выбрать конфигурацию на сайте!

Виртуальная АТС для вашего бизнеса. Приветственные бонусы для новых клиентов!

Виртуальные VPS серверы в РФ и ЕС

Dedicated серверы в РФ и ЕС

По промокоду CITFORUM скидка 30% на заказ VPS\VDS

Новости мира IT:

Архив новостей

IT-консалтинг Software Engineering Программирование СУБД Безопасность Internet Сети Операционные системы Hardware

Информация для рекламодателей PR-акции, размещение рекламы — adv@citforum.ru,
тел. +7 495 7861149
Пресс-релизы — pr@citforum.ru
Обратная связь
Информация для авторов
Rambler's Top100 TopList liveinternet.ru: показано число просмотров за 24 часа, посетителей за 24 часа и за сегодня This Web server launched on February 24, 1997
Copyright © 1997-2000 CIT, © 2001-2019 CIT Forum
Внимание! Любой из материалов, опубликованных на этом сервере, не может быть воспроизведен в какой бы то ни было форме и какими бы то ни было средствами без письменного разрешения владельцев авторских прав. Подробнее...