В 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 XMLDB
(набор 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.