Предопределенные объектные типы PL/SQL: XMLType, URI, Any

В Oracle9i был введен ряд предопределенных объектных типов:

• XMLType — хранение и обработка данных в XML-формате;
• типы URI — хранение унифицированных идентификаторов ресурсов (в частности, HTML-адресов);
• типы Any — определение переменных PL/SQL, в которых могут храниться данные любых типов.

Тип XMLType

В Oracle9i появился встроенный объектный тип XMLType для определения столбцов и переменных PL/SQL, содержащих документы XML. Методы XMLType позволяют создавать экземпляры новых значений XMLType, извлекать фрагменты документов XML и выполнять другие операции с содержимым документов XML.

Язык XML — обширная тема, которую невозможно подробно изложить в книге. Тем не менее при работе с XML из PL/SQL необходимо знать как минимум две вещи:

• XMLType — встроенный объектный тип, который позволяет хранить документы XML в столбце базы данных или в переменной PL/SQL. Тип XMLType был введен в Oracle9i Release 1.
• XQuery — язык запросов для выборки и построения документов XML. Поддержка XQuery появилась в Oracle10g Release 2.

Кроме этих двух технологий, в работе с XML также используются технологии XPath для построения ссылок на части документа, XML Schema для описания структуры документа и т. д. Тип XMLType позволяет легко создать таблицу для хранения данных XML:

CREATE TABLE fallsXML (
fall_id NUMBER,
fall XMLType
);

В этой таблице для XML-данных определен столбец fall с типом XMLType. Чтобы записать в него информацию, необходимо вызвать статический метод CreateXML и передать ему данные в формате XML. Полученный объект возвращается как результат метода и помещается в столбец базы данных. Перегруженные версии метода CreateXML могут получать как данные VARCHAR2, так и данные CLOB.

Следующие инструкции INSERT создают три документа XML и помещают их в таблицу

falls:
INSERT INTO fallsXML VALUES (1, XMLType.CreateXML(
'<?xml version="1.0"?>
<fall>
<name>Munising Falls</name>
<county>Alger</county>
<state>MI</state>
<url>
http://michiganwaterfalls.com/munising_falls/munising_falls.html
</url>
</fall>'));
INSERT INTO fallsXML VALUES (2, XMLType.CreateXML(
'<?xml version="1.0"?>
<fall>
<name>Au Train Falls</name>
<county>Alger</county>
<state>MI</state>
<url>
http://michiganwaterfalls.com/autrain_falls/autrain_falls.html
</url>
</fall>'));
INSERT INTO fallsXML VALUES (3, XMLType.CreateXML(
'<?xml version="1.0"?>
<fall>
<name>Laughing Whitefish Falls</name>
<county>Alger</county>
<state>MI</state>
<url>
http://michiganwaterfalls.com/whitefish_falls/whitefish_falls.html
</url>
</fall>'));

Для выборки XML-данных из таблицы используются методы объекта XMLType. Метод existsNode, вызываемый в следующем примере, проверяет существование в XML- документе заданного узла. Аналогичную проверку выполняет встроенная функция SQL EXISTSNODE. Она, как и указанный метод, идентифицирует узел с помощью выражения XPath1.

Следующие инструкции возвращают одинаковые результаты: 

SQL> SELECT f.fall_id
2 FROM fallsxml f
3 WHERE f.fall.existsNode('/fall/url') > 0;
SQL> SELECT f.fall_id
2 FROM fallsxml f
3 WHERE EXISTSNODE(f.fall,'/fall/url') > 0;
4 /
FALL_ID
----------
1
2

Конечно, с XML-данными можно работать и в PL/SQL. В следующем примере переменной PL/SQL типа XMLType присваивается значение из столбца fall первой добавленной нами строки таблицы. Затем я считываю в программе PL/SQL весь XML-документ с которым после этого можно работать, как с любой другой информацией. В данном случае после выборки документа мы извлекаем и выводим текст из узла /fall/url.

<>
DECLARE
   fall XMLType;
   url VARCHAR2(100);
BEGIN
   --Выборка XML-данных
   SELECT f.fall
   INTO demo_block.fall
   FROM fallsXML f
   WHERE f.fall_id = 1;
   -- Извлечение и вывод URL
   url := fall.extract('/fall/url/text()').getStringVal;
   DBMS_OUTPUT.PUT_LINE(url);
END;

Обратите внимание на следующие строки:

• SELECT f.fall INTO demo_block.fall — имя переменной fall совпадает с именем столбца таблицы, поэтому в запросе SQL имя переменной уточняется именем блока PL/SQL.
• url := fall.extract('/fall/url/text()').getStringVal; — для получения текста URL вызываются два метода объекта XMLType:
• extract — возвращает объект XMLType, содержащий заданный фрагмент исходного XML-документа (для определения требуемого фрагмента используется выражение XPath);
• getStringVal — возвращает текст XML-документа.

В рассмотренном примере метод getStringVal вызывается для XML-документа, возвращаемого методом extract. Метод extract возвращает содержимое узла <url> в виде объекта XMLType, а метод getStringVal извлекает из него содержимое в виде текстовой строки.

Столбцы XMLType даже можно индексировать для повышения эффективности выборки XML-документов. Для создания индекса необходимо обладать привилегиями QUERY REWRITE. В следующем примере индекс строится по первым 80 символам имени водопада из таблицы falls: 

CREATE INDEX falls_by_name
ON fallsxml f (
SUBSTR(
XMLType.getStringVal(
XMLType.extract(f.fall,'/fall/name/text()')
),1,80
))

Обратите внимание на использование функции SUBSTR. Метод getStringVal возвращает строку, слишком длинную для индексирования, в результате чего происходит ошибка. Функция же SUBSTR уменьшает длину полученной строки до приемлемого размера.

Если вы решите задействовать объект XMLType в своих приложениях, за более полной и актуальной информацией обращайтесь к документации Oracle. XML DB Developer’s Guide содержит важную, если не сказать — абсолютно необходимую информацию для разработчиков, работающих с XML. В SQL Reference также представлена полезная информация о XMLType и встроенных функциях SQL, поддерживающих работу с XML. В справочнике Oracle PL/SQL Packages and Types Reference описаны программы, методы и исключения всех предопределенных объектных типов, а также нескольких пакетов для работы с данными XML, включая DBMS_XDB, DBMS_XMLSCHEMA и DBMS_XMLDOM.

Типы данных URI

Семейство типов URI представлено одним основным типом данных и несколькими подтипами, обеспечивающими поддержку хранения URI в переменных PL/SQL и столбцах баз данных. Основной тип для работы с URI называется UriType; в переменной этого типа может храниться экземпляр любого из следующих подтипов:

• HttpUriType — специфический для URL подтип, обычно идентифицирующий вебстраницы;
• DBUriType — подтип для поддержки URL, представленных в виде выражений XPath;
• XDBUriType — подтип для поддержки URL, идентифицирующих объекты Oracle XML DB (набор XML-технологий, встроенных в базу данных Oracle).

Также Oracle предоставляет пакет UriFactory, автоматически генерирующий правильный тип для переданного URI.

Типы URI создаются сценарием $ORACLE_HOME/rdbms/admin/dbmsuri.sql. Владельцем всех типов и подтипов является пользователь SYS. Начиная с Oracle11g, включение сетевого доступа требует создания и настройки списков ACL (Access Control List). Это усовершенствование из области безопасности требует выполнения ряда предварительных условий до выхода в Интернет: вы должны создать сетевой список ACL, предоставить ему необходимые привилегии, а затем определить те адреса, к которым разрешает доступ список ACL.

BEGIN
   -- Создание списка ACL
   DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
      acl => 'oreillynet-permissions.xml'
      ,description => 'Network permissions for www.oreillynet.com'
      ,principal => 'WEBROLE'
      ,is_grant => TRUE
      ,privilege => 'connect'
      ,start_date => SYSTIMESTAMP
      ,end_date => NULL
      );
   -- Назначение привилегий
   DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (
      acl => 'oreillynet-permissions.xml'
      ,principal => 'WEBROLE'
      ,is_grant => TRUE
      ,privilege => 'connect'
      ,start_date => SYSTIMESTAMP
      ,end_date => null
      );
   -- Определение допустимых адресов
   DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
      acl => 'oreillynet-permissions.xml'
      ,host => 'www.orillynet.com'
      ,lower_port => 80
      ,upper_port => 80
      );
   COMMIT; -- Изменения необходимо закрепить.
END;

Теперь можно переходить к выборке веб-страниц с использованием типа HttpUriType:

DECLARE
   WebPageURL HttpUriType;
   WebPage CLOB;
BEGIN
   -- Создание экземпляра объектного типа, идентифицирующего
   -- страницу на сайте издательства O'Reilly
   WebPageURL := HttpUriType.createUri('http://www.oreillynet.com/pub/au/344');-- Получение сообщения по протоколу HTTP
   WebPage := WebPageURL.getclob();
   -- Вывод заголовка страницы
   DBMS_OUTPUT.PUT_LINE(REGEXP_SUBSTR(WebPage,'.*'));
END; 

В результате выполнения выводится следующий текст:

Steven Feuerstein 

За дополнительной информацией о типах семейства UriType обращайтесь к главе 20 документации Oracle XML DB Developer’s Guide.

Типы данных Any

PL/SQL относится к числу языков со статической типизацией. Как правило, типы данных объявляются и проверяются во время компиляции. Иногда бывает не обойтись без средств динамической типизации; для таких случаев в Oracle9i Release 1 были введены типы Any. Они позволяют выполнять операции над данными, тип которых неизвестен до выполнения программы. При этом поддерживается механизм интроспекции, позволяющий определить тип значения во время выполнения и обратиться к этому значению.

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

Типы Any непрозрачны, то есть вы не можете манипулировать с внутренними структурами напрямую, а должны использовать программные средства.

К семейству Any относятся следующие типы данных:

• AnyData — может содержать одиночное значение любого типа: скалярную величину, пользовательский объект, вложенную таблицу, массив VARRAY и т. д.
• AnyDataSet — может содержать набор значений, относящихся к одному типу.
• AnyType — описание типа, своего рода «тип без данных».

Типы Any включаются в исходную поставку базы данных или создаются сценарием dbmsany.sql, хранящимся в каталоге $ORACLE_HOME/rdbms/admin. Их владельцем является пользователь SYS.

Кроме типов Any, сценарий dbmsany.sql создает пакет DBMS_TYPES с определениями именованных констант (таких, как TYPECODE_DATE). Они могут использоваться совместно с анализирующими функциями, и в частности с GETTYPE, для определения типа данных, хранящихся в конкретной переменной AnyData или AnyDataSet. Конкретные числовые значения этих констант для нас несущественны — ведь константы для того и определены, чтобы программисты пользовались именами, а не значениями.

В следующем примере создаются два пользовательских типа, представляющих два географических объекта: водопады и реки. Далее блок кода PL/SQL с помощью функции SYS.AnyType определяет массив разнородных объектов (элементы которого могут относиться к разным типам данным).

Сначала создаются два объектных типа: 

/* Файл в Сети: ch13_anydata.sql */
TYPE waterfall AS OBJECT (
   name VARCHAR2(30),
   height NUMBER
   )
TYPE river AS OBJECT (
   name VARCHAR2(30),
   length NUMBER
   )

Затем выполняется следующий блок PL/SQL:

DECLARE
   TYPE feature_array IS VARRAY(2) OF SYS.AnyData;
   features feature_array;
   wf waterfall;
   rv river;
   ret_val NUMBER;
BEGIN
   -- Создание массива, элементы которого
   -- относятся к разным объектным типам
   features := feature_array(
      AnyData.ConvertObject(
      waterfall('Grand Sable Falls',30)),
      AnyData.ConvertObject(
      river('Manistique River', 85.40))
      );
   -- Вывод данных
   FOR x IN 1..features.COUNT LOOP
   -- Выполнение кода, соответствующего текущему
   -- объектному типу. ВНИМАНИЕ! GetTypeName возвращает
   -- SchemaName.TypeName, поэтому PLUSER следует
   -- заменить именем используемой схемы.
      CASE features(x).GetTypeName
      WHEN 'PLUSER.WATERFALL' THEN
         ret_val := features(x).GetObject(wf);
         DBMS_OUTPUT.PUT_LINE('Waterfall: '
            || wf.name || ', Height = ' || wf.height || ' feet.');
      WHEN 'PLUSER.RIVER' THEN
         ret_val := features(x).GetObject(rv);
         DBMS_OUTPUT.PUT_LINE('River: '
            || rv.name || ', Length = ' || rv.length || ' miles.');
      ELSE
         DBMS_OUTPUT.PUT_LINE('Unknown type '||features(x).GetTypeName);
      END CASE;
   END LOOP;
END; 

Результат выполнения кода будет таким:

Waterfall: Grand Sable Falls, Height = 30 feet.
River: Manistique River, Length = 85.4 miles. 

Давайте разберемся, как работает этот код. Необходимые для его работы объекты хранятся в массиве VARRAY, который инициализируется следующим образом:

features := feature_array(
AnyData.ConvertObject(
waterfall('Grand Sable Falls',30)),
AnyData.ConvertObject(
river('Manistique River, 85.40))
); 

Рассмотрим в общих чертах структуру этого кода:

• waterfall('Grand Sable Falls',30)
  Вызов конструктора типа waterfall для создания объекта типа waterfall.
• AnyData.ConvertObject(
  Преобразование объекта waterfall в экземпляр SYS.AnyData, который после этого можно будет записать в массив объектов SYS.AnyData.
• feature_array (
  Вызов конструктора массива. Каждый аргумент этой функции имеет тип AnyData.
  В данном случае массив состоит из двух передаваемых аргументов.

Напомню, что про массивы VARRAY можно почитать в этом блоге.

Следующий важный фрагмент кода — цикл FOR, в котором последовательно анализируются объекты массива features. Вызов features(x).GetTypeName возвращает полное имя текущего объекта. Для пользовательских объектов перед именем типа ставится имя схемы пользователя, создавшего объект. Это имя схемы включается в условие WHEN:

WHEN 'PLUSER.WATERFALL' THEN

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

При использовании встроенных типов данных, таких как NUMBER, DATE и VARCHAR2, функция GetTypeName возвращает просто имя типа. Имя схемы указывается только для типов, определяемых пользователем (то есть созданных конструкцией CREATE TYPE).

Определив тип данных, мы извлекаем объект из массива:

ret_val := features(x).GetObject(wf);

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

• DBMS_TYPES.SUCCESS — значение, свидетельствующее о том, что переменная типа Any содержит данные определенного типа (в нашем случае объект).
• DBMS_TYPES.NO_DATA — значение, указывающее, что в переменной типа AnyData не оказалось никаких данных.

Когда переменной будет присвоен экземпляр объекта, мы можем относительно легко написать оператор DBMS_OUTPUT для объекта данного типа. Например, информация о водопаде выводится так:

DBMS_OUTPUT.PUT_LINE('Waterfall: '
|| wf.name || ', Height = ' || wf.height || ' feet.');

Также желательно ознакомиться с документами Oracle PL/SQL Packages and Types Reference и Object-Relational Developer’s Guide.

С точки зрения объектно-ориентированного программирования существуют более эффективные способы работы с объектными типами данных. Однако на практике не всегда стоит тратить время на достижение идеала, и рассмотренный пример достаточно хорошо демонстрирует возможности предопределенного объектного типа SYS.AnyData.

Вас заинтересует / Intresting for you:

Преобразования денежных величи... 3046 просмотров Максим Николенко Sun, 20 Oct 2019, 13:48:39

Значения NULL в языке SQL: отс... 2614 просмотров Antoni Mon, 21 Jun 2021, 19:57:18

Преобразование даты и времени ... 46223 просмотров Masha Sun, 10 Jun 2018, 14:01:34

Эффективное программирование н... 4339 просмотров Sergey Wed, 22 Dec 2021, 19:25:05