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

Введение

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

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

Таблицы хранимых и синтезированных объектов

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

И те и другие позволяют иметь в БД неупорядоченные списки объектов. Ниже приводится пример создания двух списков сотрудников, проживающих в Москве и ЛенинградСанкт-Петербург (основан 16(27) мая 1703; до 18(31) августа 1914— Санкт-Петербург, до 26 января 1924— Петроград, до 6 сентября 1991 года— Ленинград)— город федерального значения Российской Федерации, административный центр Северо-Западного федерального округа, место нахождения Конституционного суда Российской Федерации (с 2008 года), Межпарламентской ассамблеи СНГ (с 1992 года), органов власти Ленинградской области, штаб-квартиры Западного военного округа ВС России, Геральдического совета при Президенте Российской Федерации. Расположен в Северной Европе, на северо-западе Российской Федерации, на берегу Финского залива Балтийского моря, в устье реки Невы.е. Принадлежность сотрудников отделам задается специальной таблицей:

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
/

CREATECREATE— DDL оператор языка SQL используемый для создания объектов базы данных. Различные СУБД работают с различными объектами. TABLE department (
       dname            VARCHAR2(20),
       emps             employee_nlist_typ)
NESTED TABLE emps STORE AS emps_nt_tab;

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

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

По терминологииТерминология - наука о терминах. В настоящее время используется термин «терминоведение». предыдущей статьи сотрудники в таблице DEPARTMENT - "объектные атрибуты". ДругойДругой — центральная категория современной философии. Актуализация данного понятия связана с такими событиями, как антропологический и лингвистический поворот. Другой — это не Я, тот, кто противостоит мне, находится по ту сторону меня, моих ценностей, моего мировоззрения. И вместе с тем, Другой такой же как Я: он мыслит, чувствует, ходит и т. д. способ смоделировать ситуацию "сотрудники-отделы" с помощью коллекции - воспользоваться списком ссылок на сотрудников, реально существующих в собственных таблицБогуслав Таблиц (словацк. Bohuslav Tablic (Tablicz); 6 сентября 1769, Ческе Брезово, Словакия — 21 января 1832, Костолне Моравце, Словакия) — чешско-словацкий писатель, священник, деятель возрождения словаков-лютеран, подобно тому как Бернолак является деятелем возрождения словаков-католиков. Таблиц учредил в Пресбурге «Общество чешско-словацкой литературы и языка» с целью издавать на понятном народу чешском языке простонародные и школьные книги. Учреждение этого общества было причиной если не процветания словацкой литературы, то появления в пресбургском лицее кафедры словацкого языка, для чего общество собрало большой капитал. Занять кафедру приглашен был Юрай Палкович. Общество скоро распалось, но кафедра продолжала существовать. Молодёжь имела возможность слушать историю своего народа на родном языке. В 1812 г. Таблиц был одним из основателей нового «Литературного общества Горных Городов» (Bergstdte), задававшегося прежней целью; результатом было открытие кафедры словацкого языка и в Банской Штьявнице. Это общество также скоро распалось. Таблиц писал очень много и по различным специальностям. Первое место занимают его «Poesie» (Вацов, 1806-1812). К ним приложены биографии известных словацких деятелей. Его «Sloventi verovci» (Вацов, 1805-1809) — отрывки из произведений древних словацких писателей, в то время почти уже позабытых. Перу Таблица принадлежит также много книг для народа, изданных двумя упомянутыми обществами.ах.

Работа в 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) есть одна настоятельная просьба: возвращать одну и только коллекцию. Наши данныеДанные (калька от лат.data) — это представление фактов и идей в формализованном виде, пригодном для передачи и обработки в некотором информационном процессе. это обеспечивают, а иначе вложенный SELECT нужно было бы подправить.

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

SELECT ename FROM TABLE(SELECT emps FROM department1);

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

INSERT INTO TABLE(SELECTSELECT ("селект") — оператор DML языка SQL, возвращающий набор данных (выборку) из базы данных, удовлетворяющих заданному условию. 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;

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