Объектные типы данных в Oracle

Ссылки на объекты

Благодаря наличию внутренних идентификаторов object ID у объектов из таблиц объектов и возможности на них ссылаться локализовать такие объекты становится возможным с применением не только обычной техники SQL, но и навигации с помощью ссылок.

Пример создания обычной таблицы со столбцом для ссылки на хранимый в таблице объектов (типа ADDRESS_TYPE) элемент-объект:

CREATE TABLE odept2 (
  dname    VARCHAR2 ( 50 )
, deptno   NUMBER CONSTRAINT pk_odept PRIMARY KEY
, addr     REF address_type SCOPE IS addresses1
);

Здесь описание ссылки сужено возможностью адресоваться только к объектам из таблицы ADDRESSES1. Допускаются варианты описания ссылки: ее нацеленность на содержимое конкретной таблицы можно не применять или же, напротив, усилить до аналогии со ссылочной целостностью (в этом примере аналогия с правилом внешнего ключа неполная).

Пример заполнения поля ADDR значением-ссылкой:

INSERT INTO odept2
   ( dname, deptno, addr )
VALUES
   ( 'RESEARCH', 10, ( SELECT REF ( a )
                       FROM addresses1 a
                       WHERE a.location = 'Archangelsk'
   )                 )
;

Пример допустимых оформлений обращения к свойствам объекта через ссылку:

COLUMN deref(d.addr) FORMAT A40
SELECT d.dname, DEREF ( d.addr ), d.addr.zip FROM odept2 d
;

Навигация по объектам в БД с помощью ссылок и в том числе извлечение объекта из БД возможны не только в запросах SQL, но и в программе на PL/SQL.

Пример использования методов объектов

Более сложные конструкции в описании типа позволяют задавать методы объектов и типов. Пример указания в типе метода:

CREATE TYPE employee_type AS OBJECT (
  name     VARCHAR2 ( 50 )
, hiredate DATE
, home     REF address_type
,
  MEMBER FUNCTION days_at_company RETURN NUMBER
)
/

Когда методов много, их заголовки перечисляются по очереди через запятую, общим списком со свойствами.

В описании типа приводится только заголовок методов. Для описания тела метода необходимо создать тело типа (полная аналогия пары пакет — тело пакета, имеющейся в PL/SQL):

CREATE TYPE BODY employee_type AS
MEMBER FUNCTION days_at_company RETURN NUMBER IS
   BEGIN
   RETURN TRUNC ( SYSDATE - hiredate );
   END;
END;
/

Пример использования типа в создании таблицы:

CREATE TABLE sailors ( ship VARCHAR2 ( 30 ), emp employee_type );
-- в этом контексте EMPLOYEE_TYPE — это имя типа

Использование конструктора типа для заполнения таблицы:

INSERT INTO sailors VALUES
   ( 'Ninna', employee_type ( 'Frank Naude', SYSDATE, NULL ) )
;
-- в этом контексте EMPLOYEE_TYPE — это конструктор

Использование свойств и метода типа для запроса к таблице:

COLUMN emp FORMAT a60
SELECT * FROM sailors;
SELECT x.ship, x.emp.name, x.emp.days_at_company ( ) FROM sailors x;
-- указание скобок после DAYS_AT_COMPANY сообщает, что это метод, а не свойство

Коллекции

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

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

Начиная с версии 8 в диалекте SQL Oracle имеется два вида коллекций: вложенные таблицы (неупорядоченное множество) и массивы VARRAY (упорядоченный список). Они соответствуют двум видам коллекций в стандарте SQL, но реализованы с некоторыми вольностями.

Вложеные таблицы

Вложеные таблицы (nested tables) в Oracle есть термин для обозначения возможности хранить в поле строки сразу множество значений ("таблицу" значений). Обычную таблицу в Oracle, некоторые столбцы которой описаны как "вложеные таблицы", всегда можно перепроектировать в информационно равносильный набор обычных таблиц с "единичными" столбцами.

Пример употребления вложеных таблиц:

CREATE TYPE colourset_typ AS TABLE OF VARCHAR2 ( 32 )
/
CREATE TABLE colour_models (
    model_type VARCHAR2 ( 12 )
  , colours    colourset_typ
)
NESTED TABLE colours STORE AS colour_model_colours_tab
-- фраза NESTED TABLE вынужденная, но может содержать указания оптимизации хранения
;
INSERT INTO colour_models VALUES (
  'RGB'
, colourset_typ ( 'RED', 'GREEN', 'BLUE' ) 
);
-- в этом контексте COLOURSET_TYP — конструктор коллекции
COLUMN colours FORMAT A60
SELECT * FROM colour_models
;

Пример приоткрывает то обстоятельство, что технически перечень самих значений из хранимых в столбце наборов располагается не непосредственно в основной таблице, а в отдельной вспомогательной (служебной), и ей мы обязаны дать по меньшей мере имя. Однако помимо этого во фразе NESTED TABLE при создании основной таблицы мы имеем право указать некоторые подробности организации доступа к этой служебной таблице и особенности хранения (например, табличное пространство).

Массивы VARRAY

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

CREATE TYPE addresslist_typ IS VARRAY ( 5 ) OF address_type;
/
CREATE TABLE participants (
  name      VARCHAR2 ( 20 )
, locations addresslist_typ
);

При создании таблицы со столбцом-массивом VARRAY не требуется приводить дополнительных указаний (как для столбца вложеной таблицы), однако имеется возможность их применить при необходимости в том.

Добавление и выборка данных внешне не отличается от осуществляемых для вложеной таблицы, например:

INSERT INTO participants VALUES

( 'Einstein'

, addresslist_typ ( address_type ( '123456', 'Archangelsk' )

, address_type ( '789012', 'Samara' )

-- конструкторы простого объекта

)

-- конструктор массива VARRAY

);

Перевод данных в коллекции к табличному представлению и другие возможности

Хотя столбец-коллекция в таблице может показаться удобным для моделирования данных предметной области, работать с такими данными в SQL не обязательно просто. На помощь в этом приходит особая функция TABLE. Она придумана для "разворачивания" элементов коллекции в список строк, к которому можно уже привычным образом применять охватывающие запросы.

Примеры:

SELECT * 
FROM   TABLE ( SELECT colours
               FROM   colour_models
               WHERE  model_type = 'RGB' 
);
SELECT * 
FROM   TABLE ( SELECT locations
               FROM   participants
               WHERE  name = 'Einstein' 
);

Упражнение. Проверьте работу последних приведенных запросов.

Для разворачивания многоуровневых коллекций предусмотрен особый случай употребления функции TABLE в сочетании с соединением (join).

Кроме того, ряд возможностей по программной обработке данных-коллекций предусмотрен в PL/SQL.

Различия в употреблении

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

SQL> SELECT 'ok' FROM dual WHERE colourset_typ ( ) IS EMPTY;
'O
--
ok

Тип XMLTYPE

Этот встроенный объектный тип для работы в БД с документами XML появился в версии 9.0. До этого наиболее подходящим для хранения документов XML был тип CLOB. Тип XMLTYPE технически может либо по-прежнему базироваться на CLOB, либо иметь в БД структуру объекта (начиная с версии 9.2 и при использовании XML DB). Помимо пользовательской направленности тип XMLTYPE активно применяется в последних версиях Oracle для внутренней организации БД.

СУБД и БД Oracle предлагают широкий спектр возможностей по использованию типа XMLTYPE в связи с документами XML. Ниже приводятся только простые ознакомительные примеры.

Простой пример

Создание и пополнение таблицы, в которой решено хранить описания книг в формате XML — ради сохранения формата источника или же в силу отсутствия фиксированной структуры у описания книги:

CREATE TABLE books (
  id          NUMBER
, description XMLTYPE
);
INSERT INTO books VALUES (
  100 
, xmltype (
    '<?xml version="1.0"?>
     <cover>
        <title>Java Programming with Oracle JDBC</title>
        <author>Donald Bales</author>
        <publisher>OReilly and Associates</publisher>
        <pubdate>December 2001</pubdate>
        <isbn>0-596-00088-x</isbn>
        <pages>496</pages>
     </cover>'
  )
);
-- ... здесь Oracle разрешает вместо конструктора XMLTYPE ( '...' ) написать просто '...'
SET LONG 1000
SELECT id, description FROM books;
SELECT id, b.description.XMLDATA FROM books b;

XMLDATA — специально созданный для XMLTYPE "псевдостолбец". В данном примере его может заменить метод GETCLOBVAL() типа XMLTYPE, один из многих существующих.

Упражнение. Попробуйте занести в поле DESCRIPTION неправильно оформленный документ XML и проследите реакцию СУБД.

Пример выборки с использованием условия отбора на языке XPath:

SELECT id, b.description.GETCLOBVAL ( )
FROM   books b 
WHERE  b.description.EXISTSNODE('/cover[author="Donald Bales"]')=1;

Таблицы данных XMLTYPE

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

CREATE TABLE xbooks OF XMLTYPE;

Работать с ними можно как и с прочими таблицами объектов:

INSERT INTO xbooks VALUES (
   xmltype (
      '<?xml version="1.0"?>
       <cover>
          <title>Java Programming with Oracle JDBC</title>
          <author>Donald Bales</author>
          <publisher>OReilly and Associates</publisher>
          <pubdate>December 2001</pubdate>
          <isbn>0-596-00088-x</isbn>
          <pages>496</pages>
       </cover>'
   )
);
-- ... здесь Oracle разрешает вместо XMLTYPE ( '...' ) указать просто '...'
SELECT x.GETCLOBVAL ( ) FROM xbooks x;

В этом примере данные XML будут храниться как CLOB. Более сложный пример — создание таблиц объектов типа XMLTYPE, где документы XML хранятся в виде таблицы объектов, а не как CLOB. Вот как это могло бы выглядеть в какой-нибудь БД типа лицами объективно, и в силу отсутствия фиксированной структуры описания книгой области:

CREATE TABLE oxbooks OF XMLTYPE
   XMLSCHEMA "http://www.oracle.com/xbooks.xsd"
   ELEMENT "Book"
;

Чтобы таблица была предварительно определена ("зарегистрирована") в "репозитарии" XML DB (сама XML DB со своим репозитарием автоматически включена в состав типовым образом созданной БД). Достоинство такого описания столбца XMLTYPE в том, что он позволяет хранить не произвольные документы XML, а только типизированные схемой XML. Тем самым зарегистрированная схема XML используется как средство ограничения целостности хранимых данных XML, налагаемое в таблице Oracle по правилам технологии XML.

Преобразование табличных данных в тип XMLTYPE

Ряд функций, объединенных названием SQL/XML в стандарте SQL:2003 (другое название — SQLX), позволяет просто осуществлять преобразование обычных табличных данных в формат XML. В Oracle реализованы следующие функции из этого стандартного набора:

[10.2-] начиная с версии 10.2

Вдобавок к этому Oracle SQL содержит ряд расширений SQL/XML и собственных функций для выполнения подобных преобразований:

[10.2-] начиная с версии 10.2

Следующие примеры поясняют действие некоторых из перечисленных функций:

SET LONG 2000
SELECT XMLELEMENT ( "employee", ename ) AS employee
FROM emp;
SELECT
  XMLELEMENT (
    "employee"
  , XMLATTRIBUTES ( ename AS "name", comm AS "commission" )
  ) AS employee 
FROM emp;
SELECT
  XMLELEMENT (
    "employee"
  , XMLFOREST ( ename AS "name", comm AS "commission" )
  ) AS employee
FROM emp;
SELECT
  XMLELEMENT (
    "employee"
  , XMLCOLATTVAL ( ename AS "name", comm AS "commission" )
  ) AS employee
FROM emp;
SELECT
  XMLELEMENT (
    "department"
  , XMLATTRIBUTES ( deptno AS no )
  ) AS department
, XMLAGG ( XMLELEMENT ( "employee", ename ) ) AS employees
FROM emp
GROUP BY deptno
;
Обратите внимание, что в результатах выдаются поля типа XMLTYPE:
CREATE TABLE xtable ( n ) 
AS 
SELECT XMLELEMENT ( "name", ename ) FROM emp
;
DESCRIBE xtable

Тип ANYDATA

Имеется начиная с версии 9. Позволяет хранить в столбце таблицы данные одновременно разных типов. Используется с объектным типом ANYDATA, имеющим свои конструкторы и методы.

Пример:

CREATE TABLE t ( x ANYDATA );
INSERT INTO t VALUES ( ANYDATA.CONVERTNUMBER   ( 5 ) );
INSERT INTO t VALUES ( ANYDATA.CONVERTDATE     ( SYSDATE ) );
INSERT INTO t VALUES ( ANYDATA.CONVERTVARCHAR2 ( 'hello world' ) );
SELECT t.x.GETTYPENAME ( ) typename FROM t t;
INSERT INTO t VALUES (
  ANYDATA.CONVERTOBJECT ( address_type ( '789012', 'Murmansk' ) )
);

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

SET SERVEROUTPUT ON
DECLARE 
 val     VARCHAR2 ( 30 );
 anumber ANYDATA;
BEGIN
 SELECT t.x INTO anumber
 FROM   t t 
 WHERE  t.x.GETTYPENAME ( ) = 'SYS.NUMBER'
 -- в нашей таблице такая строка единственная
 ;
 DBMS_OUTPUT.PUT_LINE ( 'Ret code: ' || anumber.GETNUMBER ( val ) );
 DBMS_OUTPUT.PUT_LINE ( 'A number: ' || val );
END;
/

Методы типа ANYDATA описаны в документации по Oracle.

Тип ANYDATA, как и XMLTYPE, находит употребление во внутренней организации БД. В частности он используется в построении внутренних "автоматических очередей", лежащих в основе "потоков данных", используемых, в свою очередь, для автоматического переноса данных в Oracle.