Oracle тип данных xml

Обновлено: 07.07.2024

Каждое значение, которым манипулирует база данных Oracle, имеет тип данных. Тип данных значения связывает фиксированный набор свойств со значением. Используя эти свойства, Oracle обрабатывает значения одного типа данных иначе, чем значения другого. Например, вы можете добавить значения типа данных NUMBER, но не значения типа данных CHAR.

База данных Oracle предоставляет ряд встроенных типов данных, а также несколько категорий для пользовательских типов, которые могут использоваться в качестве типов данных.

Встроенные типы данных Oracle

В следующей таблице приведены встроенные типы данных Oracle.

Типы	Описание	Размер
VARCHAR2 (размер [BYTE \| CHAR])	Строка символов переменной длины.	От 1 байта до 4КБ.
NVARCHAR2 (размер)	Строка символов Unicode переменной длины, имеющая символы максимального размера.	Максимальный размер определяется национальным набором символов с верхним пределом 4000 байтов. Вы должны указать размер для NVARCHAR2.
NUMBER [(p [, s])]	Число с точностью p и шкалой s. Диапазон р: от 1 до 38. Диапазоны s: от -84 до 127. Точность и масштаб указаны в десятичных цифрах.	Значение NUMBER требует от 1 до 22 байтов.
FLOAT [(p)]	Значение FLOAT внутренне представлено как NUMBER. Диапазон значений p: от 1 до 126 двоичных цифр.	Значение FLOAT требует от 1 до 22 байтов.
ДОЛГО	Символьные данные переменной длины до 2 гигабайт, используемые для обратной совместимости.	2 31 -1 байт
ДАТА	Действительный диапазон дат: с 1 января 4712 г. до н.э. до 31 декабря 9999 г. н.э. Формат по умолчанию определяется явно параметром NLS_DATE_FORMAT или неявно параметром NLS_TERRITORY.	Размер фиксируется в 7 байтов.
BINARY_FLOAT	32-битное число с плавающей точкой.	Этот тип данных требует 4 байта.
BINARY_DOUBLE	64-битное число с плавающей запятой.	Этот тип данных требует 8 байтов.
TIMESTAMP [(fraal_seconds_precision)]	Этот тип данных содержит поля даты и времени YEAR, MONTH, DAY, HOUR, MINUTE и SECOND. Он содержит доли секунды, но не имеет часового пояса.	Размер составляет 7 или 11 байт, в зависимости от точности.
TIMESTAMP [(фракция_seconds_precision)] с зоной времени	Этот тип данных содержит поля даты и времени YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR и TIMEZONE_MINUTE. У него есть доли секунды и явный часовой пояс.	Размер фиксируется в 13 байт.
ИНТЕРВАЛЬНЫЙ ГОД [(year_precision)] ДО МЕСЯЦА	Сохраняет период времени в годах и месяцах, где year_precision - это количество цифр в поле YEAR datetime. Допустимые значения: от 0 до 9. По умолчанию установлено значение 2.	Размер фиксируется в 5 байтах.
ИНТЕРВАЛЬНЫЙ ДЕНЬ [(day_precision)] ДО ВТОРОГО [(фракция_презентация_precision)]	Сохраняет период времени в днях, часах, минутах и секундах, где day_precision - это максимальное количество цифр в поле DAY datetime. Допустимые значения: от 0 до 9. По умолчанию установлено значение 2.	Размер фиксируется в 11 байтов.
RAW (размер)	Необработанные двоичные данные байтов размера длины.	Максимальный размер 2000 байт
ДЛИННАЯ СЫРЬЯ	Необработанные двоичные данные переменной.	Размер до 2 гигабайт.
ROWID	Уникальный адрес (представляющий строку из 64 строк) строки в ее таблице.
UROWID [(размер)]	Логический адрес строки (представляющей строку из 64 строк) организованной по индексу таблицы.	Максимальный размер и значение по умолчанию составляет 4000 байтов.
CHAR [(размер [BYTE \| CHAR])]	Символьные данные фиксированной длины размером в байтах или символах.	Максимальный размер составляет 2000 байтов или символов. Минимальный размер по умолчанию - 1 байт.
NCHAR [(размер)]	Данные символа фиксированной длины символов размера длины. Количество байтов может быть в два раза больше для кодирования AL16UTF16 и в три раза больше для кодирования UTF8.	Максимальный размер определяется национальным набором символов с верхним пределом в 2000 байтов. Минимальный размер по умолчанию - 1 символ.
CLOB	Большой символьный объект, содержащий однобайтовые или многобайтовые символы.	Максимальный размер (4 гигабайта - 1) * (размер блока базы данных).
NCLOB	Большой символьный объект, содержащий символы Юникода.	Максимальный размер (4 гигабайта - 1) * (размер блока базы данных). Хранит данные национального набора символов.
большой двоичный объект	Большой двоичный объект.	Максимальный размер 4 гигабайта.
BFILE	Содержит локатор для большого двоичного файла, хранящегося за пределами базы данных.	Максимальный размер 4 гигабайта.

Типы данных персонажей Oracle

Тип данных CHAR определяет символьную строку фиксированной длины. Если вы вставите значение, которое короче длины столбца, Oracle пустым образом подставит значение в длину столбца, а если значение слишком длинное для столбца, Oracle вернет ошибку. Следующие типы данных используются для символьных данных:

Тип данных Oracle NUMBER

НОМЕР Тип данных:

Тип данных NUMBER хранит нулевые, положительные и отрицательные фиксированные числа.

Числовой формат с фиксированной точкой:

Где p - точность, до 20 цифр от 100 до 100, что эквивалентно 39 или 40 десятичным знакам в зависимости от положения десятичной точки.
s - масштаб, масштаб может варьироваться от -84 до 127.
Положительная шкала - это число значащих цифр справа от десятичной точки и включая наименее значимую цифру.
Отрицательная шкала - это число значащих цифр слева от десятичной точки, но не включая наименее значимую цифру.

Примеры:

Тип данных FLOAT:

Тип данных FLOAT является подтипом NUMBER. Вы можете указать это с точностью или без. Масштаб не может быть указан, но интерпретируется из данных. Каждое значение FLOAT требует от 1 до 22 байтов.

В следующем примере показана разница между NUMBER и FLOAT:

В приведенном выше примере возвращаемое значение FLOAT не может превышать 5 двоичных цифр. Таким образом, 123,45 округляется до 120, который имеет только две значащие десятичные цифры, требующие только 4 двоичных цифр.

Числа с плавающей точкой:

Термин с плавающей запятой происходит от того факта, что не существует фиксированного количества цифр до и после десятичной точки; то есть десятичная точка может плавать. Показатель степени может необязательно использоваться после числа для увеличения диапазона, например, 1.777 e -20 .

Пример:

В базе данных Oracle есть два числовых типа данных исключительно для чисел с плавающей запятой:

BINARY_FLOAT:
BINARY_FLOAT - это 32-битный тип данных с плавающей запятой с одинарной точностью. Каждое значение BINARY_FLOAT требует 4 байта.

BINARY_DOUBLE:
BINARY_DOUBLE - это 64-битный тип данных с плавающей запятой с двойной точностью. Каждое значение BINARY_DOUBLE требует 8 байтов.

Примеры:

Значение	BINARY_FLOAT	BINARY_DOUBLE
Максимальное положительное конечное значение	3.40282E + 38F	1.79769313486231E + 308
Минимальное положительное конечное значение	1.17549E-38F	2.22507485850720E-308

ДОЛГОЙ Тип данных
Используйте столбцы LOB (CLOB, NCLOB, BLOB), поскольку столбцы LONG поддерживаются только для обратной совместимости.

В столбцах LONG хранятся строки символов переменной длины, содержащие до 2 гигабайт -1 или 2 31 -1 байтов. Длинные столбцы имеют многие характеристики столбцов VARCHAR2. Вы можете использовать длинные столбцы для хранения длинных текстовых строк. Длина значений LONG может быть ограничена объемом памяти, доступной на вашем компьютере. ДЛИННЫЕ литералы формируются, как описано для «Текстовых литералов».

Типы данных даты и времени

Ниже приведены типы данных datetime:

ДАТА
TIMESTAMP
TIMESTAMP с зоной времени
TIMESTAMP с локальной зоной времени

Значения типов данных datetime иногда называют datetime.

Поля даты и значения:

Поле даты и времени	Допустимые значения для даты и времени	Допустимые значения для INTERVAL
ГОД	От -4712 до 9999 (исключая год 0)	Любое положительное или отрицательное целое число
МЕСЯЦ	01 до 12	От 0 до 11
ДЕНЬ	С 01 по 31 (ограничено значениями MONTH и YEAR в соответствии с правилами текущего параметра календаря NLS)	Любое положительное или отрицательное целое число
ЧАС	От 00 до 23	От 0 до 23
МИНУТЫ	От 00 до 59	От 0 до 59
ВТОРОЙ	От 00 до 59,9 (n), где 9 (n) - это точность долей времени в секундах. 9 (n) часть не относится к DATE.	От 0 до 59,9 (n), где 9 (n) - точность интервала долей секунд
TIMEZONE_HOUR	От -12 до 14 (этот диапазон учитывает изменения летнего времени.) Неприменимо для DATEor TIMESTAMP.	Непригодный
TIMEZONE_MINUTE	От 00 до 59. Не применимо для даты или времени.	Непригодный
TIMEZONE_REGION	Запросите столбец TZNAME представления словаря данных V $ TIMEZONE_NAMES. Не применимо для DATE или TIMESTAMP.	Непригодный
TIMEZONE_ABBR	Запросите столбец TZABBREV представления словаря данных V $ TIMEZONE_NAMES. Не применимо для DATE или TIMESTAMP.	Непригодный

ДАТА Тип данных:

Тип данных DATE хранит информацию о дате и времени (представленную как символьными, так и числовыми типами данных). Для каждого значения DATE Oracle хранит год, месяц, день, час, минуту и секунду.

Используя юлианские дни:

Юлианский номер дня - это количество дней с 1 января 4712 года до нашей эры. Вы можете использовать модель формата даты "J" с функциями даты TO_DATE и TO_CHAR для преобразования между значениями Oracle DATE и их юлианскими эквивалентами.

Значения даты по умолчанию:

Год - это текущий год, возвращаемый SYSDATE.
Месяц является текущим месяцем, возвращаемым SYSDATE.
День 01 (первый день месяца).
Час, минута и секунда - все 0.

Функция TO_DATE преобразует символьное или числовое значение в дату.

Вы можете использовать модель формата даты "J" с функциями даты TO_DATE и TO_CHAR для преобразования между значениями Oracle DATE и их юлианскими эквивалентами. Следующий оператор возвращает юлианский эквивалент 1 января 2015 года:

Тип данных TIMESTAMP:
Тип данных TIMESTAMP является расширением типа данных DATE и хранит год, месяц и день типа данных DATE, а также значения часов, минут и секунд. Это полезно для хранения точных значений времени, а также для сбора и оценки информации о дате по географическим регионам.

TIMESTAMP с временной зоной Тип данных:
TIMESTAMP WITH TIME ZONE - это вариант TIMESTAMP, который включает в себя имя региона часового пояса или смещение часового пояса в своем значении. Это полезно для сохранения информации о часовом поясе.

TIMESTAMP с локальной зоной времени Тип данных:

TIMESTAMP с локальной зоной времени - еще один вариант TIMESTAMP, чувствительный к информации о часовых поясах. Он отличается от TIMESTAMP WITH TIME ZONE тем, что данные, хранящиеся в базе данных, нормализуются к часовому поясу базы данных, а информация о часовом поясе не сохраняется как часть данных столбца. Когда пользователь получает данные, Oracle возвращает их в часовом поясе локального сеанса пользователя. Этот тип данных полезен для информации о дате, которая всегда должна отображаться в часовом поясе клиентской системы в двухуровневом приложении.

ИНТЕРВАЛ ГОДА В МЕСЯЦ Тип данных:

ИНТЕРВАЛ ГОДА В МЕСЯЦ хранит период времени, используя поля даты и года ГОД и МЕСЯЦ. Этот тип данных полезен для представления разницы между двумя значениями даты и времени, когда значимы только значения года и месяца.

ДЕНЬ ИНТЕРВАЛА ДЛЯ ВТОРОГО Типа данных:

INTERVAL DAY TO SECOND хранит период времени в виде дней, часов, минут и секунд. Этот тип данных полезен для представления точной разницы между двумя значениями даты и времени.

day_precision - количество цифр в поле даты и времени DAY. Допустимые значения: от 0 до 9. По умолчанию установлено значение 2.
fraal_seconds_precision - количество цифр в дробной части поля ВРЕМЯ datetime. Допустимые значения: от 0 до 9. По умолчанию установлено значение 6.

Типы данных ANSI, DB2 и SQL / DS

Oracle распознает имя типа данных ANSI или IBM, которое отличается от имени типа данных Oracle Database, и преобразует тип данных в эквивалентный тип данных Oracle. Следующая таблица показывает конверсии :,

Тип данных ANSI SQL	Тип данных Oracle
СИМВОЛЫ (п) СИМ (п)	СИМ (п)
VARING CHARACTER (n) CHAR VARYING (n)	VARCHAR2 (п)
НАЦИОНАЛЬНЫЙ ХАРАКТЕР (n) НАЦИОНАЛЬНЫЙ ЧАР (n) NCHAR (п)	NCHAR (п)
НАЦИОНАЛЬНЫЙ ХАРАКТЕР ИЗМЕНЕНИЯ (n) НАЦИОНАЛЬНЫЙ ЧАР ВАРЬИНГ (н) NCHAR VARYING (n)	NVARCHAR2 (п)
ЧИСЛОВОЙ [(P, S)] DECIMAL [(p, s)] (примечание 1)	НОМЕР (р, с)
INTEGER INT SMALLINT	НОМЕР (р, 0)
FLOAT (примечание 2) ДВОЙНАЯ ТОЧНОСТЬ (Примечание 3) РЕАЛЬНО (Примечание 4)	ПОПЛАВКОВЫЕ (126) ПОПЛАВКОВЫЕ (126) ПОПЛАВКОВЫЙ (63)

Типы, предоставляемые Oracle

Oracle предоставляет некоторые новые типы данных, которых нет во встроенных или поддерживаемых ANSI типах. Эти типы могут быть реализованы в C / C ++, Java или PL / SQL. Вот подробности:

Любые типы:
Типы Any обеспечивают очень гибкое моделирование параметров процедуры и столбцов таблицы, где фактический тип неизвестен. Эти типы данных позволяют динамически инкапсулировать и получать доступ к описаниям типов, экземплярам данных и наборам экземпляров данных любого другого типа SQL. Эти типы имеют интерфейсы OCI и PL / SQL для создания и доступа.

Типы XML:
Этот тип, предоставляемый Oracle, можно использовать для хранения и запроса данных XML в базе данных. XMLType имеет функции-члены, которые вы можете использовать для доступа, извлечения и запроса данных XML с использованием выражений XPath. XMLType является системным типом, поэтому вы можете использовать его как аргумент функции или как тип данных таблицы или столбца представления. Вы также можете создавать таблицы и представления XMLType. Когда вы создаете столбец XMLType в таблице, вы можете сохранить данные XML в столбце CLOB, как двоичный XML (хранящийся внутри как CLOB), или как объект реляционного типа.

Типы данных URI

Пространственные типы

Oracle Spatial разработан, чтобы сделать управление пространственными данными более простым и естественным для пользователей приложений с поддержкой определения местоположения, приложений геоинформационных систем (ГИС) и приложений геоизображения. После того, как пространственные данные сохранены в базе данных Oracle, вы можете легко манипулировать ими, извлекать и связывать их со всеми другими данными, хранящимися в базе данных. Следующие типы данных доступны, только если вы установили Oracle Spatial.

Тип данных	Описание
SDO_GEOMETRY	Геометрическое описание пространственного объекта хранится в одной строке, в одном столбце типа объекта SDO_GEOMETRY в пользовательской таблице. Любая таблица, имеющая столбец типа SDO_GEOMETRY, должна иметь другой столбец или набор столбцов, определяющих уникальный первичный ключ для этой таблицы. Таблицы такого рода иногда называют таблицами геометрии.
SDO_TOPO_GEOMETRY	Этот тип описывает геометрию топологии, которая хранится в одной строке в одном столбце типа объекта SDO_TOPO_GEOMETRY в пользовательской таблице.
SDO_GEORASTER	В объектно-реляционной модели GeoRaster растровая сетка или объект изображения хранятся в одной строке в одном столбце типа объекта SDO_GEORASTER в пользовательской таблице. Таблицы такого типа называются таблицами GeoRaster.

Типы СМИ

Oracle Multimedia использует типы объектов, подобные классам Java или C ++, для описания мультимедийных данных. Экземпляр этих типов объектов состоит из атрибутов, включая метаданные и медиаданные, а также методов. Мультимедийные типы данных создаются в схеме ORDSYS. Публичные синонимы существуют для всех типов данных, поэтому вы можете получить к ним доступ без указания имени схемы.
Oracle Multimedia предоставляет следующие типы объектов:

Тип данных	Описание
ORDAudio	Поддерживает хранение и управление аудиоданными.
ORDDicom	Поддерживает хранение и управление цифровыми изображениями и коммуникациями в медицине (DICOM), формат, общепризнанный в качестве стандарта для медицинских изображений.
ORDDoc	Поддерживает хранение и управление медиа-данными любого типа, включая аудио, изображения и видео. Используйте этот тип, если вы хотите, чтобы все мультимедиа хранились в одном столбце.
ORDImage	Поддерживает хранение и управление данными изображения.
ORDVideo	Поддерживает хранение и управление видеоданными.
ORDImageSignature	Тип объекта ORDImageSignature устарел и больше не должен вводиться в ваш код. Существующие вхождения этого типа объекта будут продолжать функционировать как в прошлом.

Тип объекта ORDImageSignature устарел и больше не должен вводиться в ваш код. Существующие вхождения этого типа объекта будут продолжать функционировать как в прошлом.

Предыдущая: Oracle Home
Далее: Литералы

Казалось бы, зачем вообще может возникнуть необходимость разбирать XML на стороне БД?

В этой статье я хотел бы продемонстрировать на сколько легко и непринужденно можно разобрать XML различной степени сложности используя инструментальные средства Oracle Database.

Мне совершенно не хотелось бы здесь касаться DOM парсера. Скажу лишь, что он есть, реализуется пакетом DBMS_XMLDOM. Временами он может оказаться крайне полезным разработчику, а разобраться с ним, не составит труда любому, сталкивавшемуся ранее с DOM парсерами от других производителей.

Инновационной особенностью оракла является тип XMLType и средства работы с ним. Этот тип является частью технологии XML DB, которая включена в поставку Oracle Database начиная с версии 9.2.

Исходный текст документа XML может быть передан конструктору XMLType в виде значений типов CLOB, BLOB, VARCHAR2, BFILE. Пожалуй, стоит отметить, что BFILE позволяет загрузить файл с файловой системы сервера — никак не клиента, потому если наш XML находится на стороне клиента и он достаточно велик, чтобы быть переданным в виде строки в запросе, пожалуй, следует озаботиться возможностью доставки файла с XML содержимым на файловую систему сервера.

Пример создания экземпляра XMLType, с содержимым, передаваемым в строке:

Создав экземпляр XMLType, можно попытаться сделать первые робкие шажки для разбора нашего XML. Тип XMLType реализует метод Extract, который, принимая XPatch выражение, возвращает фрагмент XML, сочетающийся с этим выражением. Фрагмент XML (XML Fragment), в отличии от правильно построенного XML (whellformed XML) допускает отсутствие корневого элемента (или же, другими словами, допускающий более одного корневого элемента в своем составе).

Так в примере ниже, три выражения возвращают три фрагмента XML. Первый возвращает все вхождения элемента word, второй возвращает только первое его вхождение, третий возвращает фрагмент текстового содержимого элемента word, для которого значение атрибута seq равняется двум.

Здесь, думаю, стоит поставить жирный акцент на том, что в третьем случае возвращается именно фрагмент XML, никак не значение этого элемента. Различия станут заметны лишь тогда, когда это значение будет содержать подстановочные (escaped) символы, вроде &,>. Для того, чтобы получить значение элемента, следует использовать функцию extractValue. Тут упомяну, что основные методы XMLType, продублированы SQL функциями, или же наоборот, основные функции работы с XMLType реализованы в виде его методов. Однако extractValue — исключение. extractValue представлена только в виде фунации. XMLType, к сожалению, не реализует метод extractValue.

Пожалуй, следует еще упомянуть о правилах работы с пространствами имен. Не всякого интуиция приводит к верному пониманию этих механизмов работы. Функции (и метод) extract, extractValue, как один из параметров принимают описание пространства имен. Описанные в этом параметре пространства имен могут быть использованы в XPath выражении. И именно это я хочу подчеркнуть особо. Обратите внимание на третий случай. Пространства имен в XML и XPatch выражениях имеют разные псевдонимы, но одинаковые URI, потому разбор происходит успешно.

Итак, научившись извлекать значения, теперь следовало бы научиться их разделять. Напомню, в первом случае, для первого примера, мы пытались выбрать все элементы word из XML, и нам это удалось, мы получили два элемента word, однако получили мы их в одном фрагменте. Для того, чтобы представить фрагмент, содержащий несколько корневых элементов в виде последовательности фрагментов, каждый из которых содержит по одному корневому элементу существует конвейерная (pipelined) функция XMLSeqence. Функция возвращает XMLSequenceType, который представляет собой таблицу значений XMLType.

Если вдруг кто запамятовал, напомню, что конвейерные функции возвращают как бы коллекции, потому при вызове оборачиваются выражением table. К результатам этих функций обращаются, используя виртуальный столбец column_value, либо же выражение value(), а потому для табличного выражения(table collection excpression) следует определить псевдоним. Если вдруг кто и не знал этого, рекомендую заучить это как мантру, понимание придет со временем, и то, лишь если понадобится.

Простейший пример использования XMLSequence:

Попытаюсь проговорить что здесь происходит, хоть и опасаюсь, что по-русски это окажется намного более сумбурно, и куда менее понятно, нежели на SQL. В выражении from мы сначала создаем экземпляр XMLType, передавая ему строку, содержащую текст XML. Далее, используя метод extract, мы извлекаем в один фрагмент все элементы b, которые содержит элемент а. Полученный фрагмент XML передается параметром в конвейерную функцию XMLSequence, для вызова которой, согласно правилам грамматики, используется предложение table. Набору записей, описываемым этим предложением, присваивается псевдоним t. В select-list'e мы получаем экземпляр объекта возвращенного табличным выражением t, он у нас имеет тип XMLType. Для каждой строки возвращаемой табличным выражением этот экземпляр содержит один фрагмент элемента b исходного XML. Передаем этот объект в качестве параметра функции extractValue. Результат — на лицо.

На самом деле, все далеко не так сложно, как получается в моем изложении. К этому достаточно лишь малость привыкнуть. Но насилие над мозгом еще не в полной степени завершено. То, что у нас получилось на данном этапе, работает только для одного XML документа. Если у нас исходный текст нескольких XML лежит в табличке и нам нужно разобрать сразу несколько из них, нам придется вспомнить еще что такое левая корреляция (left correlation). Здесь тоже нет ничего военного. Эта штука придумана Ораклом и специально для табличных выражений (table collection expression). Суть сводится к тому, что в табличном выражении могут использоваться значения (столбцы) из наборов данных, определенных в выражении from перед (слева) от самого табличного выражения. На практике это выглядит совсем не так ужасно, как на слух:

Здесь в табличном выражении t используется значение xml таблицы demo3. Выражение будет вычислено для каждой строки таблицы demo3. Это и есть то самое, что называется таким вычурным словом — левая корреляция.

Описанного функционала вполне достаточно, чтобы разобрать XML практически любой сложности. Этими средствами нельзя разобрать, пожалуй, лишь иерархически представленные данные заведомо неизвестной глубины вложенности. Для разбора подобных структур придется прибегнуть к XSLT, чтобы привести XML к более удобочитаемому виду. XSLT преобразование осуществляется функцией XMLTransform, которая в качестве первого параметра принимает XMLType исходного документа, второго XMLType XSL шаблона, а возвращает XMLType результата преобразования.

В принципе, на этом с теорией можно и закончить. В завершение лишь продемонстрирую пример извлечения элементов с разных уровней вложенностей XML. У новичков это, порой, вызывает сложности.

Как видите, здесь нет ничего нового. Все та же левая корреляция. Единственное, на что хотелось бы обратить внимание, на (+) в конце табличного выражения subdtl. Как, наверное, не сложно догадаться, он обозначает, что следует использовать внешнее соединение. Если бы мы его не указали, мы не получили бы строки с detail 3.

Итак, что же предстало пред нашими глазами? Мы имеем один объектный тип, сравнительно ограниченный набор функций, дающий практически не ограниченный набор возможностей. Мне безумно нравится эта реализация. Особенно меня восторгает то, что Oracle corp не пришлось рихтовать семантику их SQL, чтобы вписать в него XML. Все описанные особенности — объекты, конвейерные функции, табличные выражения используются этой технологией, но не созданы специально для нее. Получается, подобную реализацию мог бы воплотить кто угодно. Эта реализация жирной линией подчеркивает мощь и гибкость ораклиного SQL движка.

На этой ноте я мог бы и закончить, однако мне покоя не дает предвосхищаемый мною вопрос, да с упреком. «Дружище, ты в каком веке вообще живешь, ты на календарь давно заглядывал? На дворе близится к концу 2011й год, уже далеко не первый продакшн поднят на 11r2 версии датабазы, а ты все жуешь девятошный функционал». Да, есть такой грешок за мной. Я прекрасно знаю, что в 10й версии ввели чудный XMLTable, полностью задвигающий на задний план только что описанный мною функционал. Позволяющий еще легче и еще более не принужденно разбирать XML. Однако по XMLTable у меня еще не достаточно опыта, чтобы сказать что либо сверх и без того очевидного. Потому ограничусь лишь простой демонстрацией.

Покажу на том же примере:

Казалось бы, букв стало много больше, может возникнуть справедливый вопрос… а в чем же профит инновации? Профит в том, что первым параметром в XMLTable передается уже не XPath выражение, а XQuery. А значит, объединение может быть произведено именно его средствами, а не средствами SQL. XMLTable обещает быть еще той вкуснятиной, но, увы, повторюсь, мне о нем пока нечего рассказать.

Владимир Пржиялковский , преподаватель технологий Oracle

Введение

Тип XMLTYPE появился в Oracle в версии 9.0. До этого наиболее подходящим для хранения документов в формате XML был тип CLOB (и менее подходящим – тип VARCHAR2, ограниченный максимумом 4000 знаков). Сам по себе объектный, новый тип XMLTYPE технически может храниться либо по-прежнему в виде CLOB, либо в виде объекта (начиная с версии 9.2). И еще одно замечание: несмотря на то, что технологии XML и Java идут «рука об руку», рамки приводимых ниже примеров не требуют от вашей БД установленных возможностей Java.

В этой заметке рассмотрены только логические стороны использования XML в Oracle безотносительно к техническим свойствам хранения и доступа.

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

CREATE TABLE books
(id NUMBER PRIMARY KEY
, description XMLTYPE);

INSERT INTO books VALUES
(100
, XMLTYPE('<cover>
<title>Oracle SQL*Loader</title>
<author>Jonathan Gennick</author>
<author>Sanjay Mishra</author>
<pages>269</pages>
</cover>'));

SELECT id, description FROM books;

SELECT id, b.description.XMLDATA FROM books b;

XMLDATA – специально созданный для XMLTYPE «псевдостолбец».

XMLTYPE – тип XML

XMLTYPE дает возможность сообщить БД, что заносимый текст – это не просто строка, а строка документа XML. Следующая попытка приведет к ошибке:

INSERT INTO books VALUES (101, XMLTYPE('<cover><title></title>'));

С дугой стороны, Oracle поймет правильно составленные директивы XML и встроенное в текст описание DTD:

Убедитесь в этом сами, что Oracle действительно соотносит описание DTD самому тексту документа !

Для выборки можно использовать специально придуманные для XMLTYPE функции. Так, функция EXTRACTVALUE извлекает значения элемента из документа XML:

SELECT id, EXTRACTVALUE(description, '/cover/title')
FROM books;

Функция EXISTSNODE дает возможность использовать в SQL условие отбора XPath (язык отбора, принятый в технологиях XML):

SELECT id, b.description.XMLDATA
FROM books b
WHERE b.description.EXISTSNODE('/cover[author="Sanjay Mishra"]')=1;

XMLTYPE – объектный тип Oracle

Доказательством утверждения в заголовке служит создание следующей таблицы объектов типа XMLTYPE, «таблицы документов XML»:

CREATE TABLE xbooks OF XMLTYPE;

Работать с ними можно, как и с XML-атрибутом в обычной таблице:

INSERT INTO xbooks VALUES
(XMLTYPE('<cover>
<title>Oracle SQL*Loader</title>
<author>Jonathan Gennick</author>
<author>Sanjay Mishra</author>
<pages>269</pages>
</cover>'));

INSERT INTO xbooks VALUES
(NEW XMLTYPE('<?xml version="1.0"?>
<cover>
<title>SQL*Plus Pocket Reference</title>
<author>Jonathan Gennick</author>
<pages>94</pages>
</cover>'));

В первом случае объект XML создается с помощью конструктора, а во втором, к тому же, используется оператор NEW. Последний применяется в Oracle для работы с объектами, однако его использование носит лишь рекомендательный характер, так как в SQL он ничего содержательного не дает.

SELECT * FROM xbooks;

SELECT VALUE(x) FROM xbooks x;

SELECT XMLDATA FROM xbooks;

Так же как для таблиц объектов прочих типов, элементы таблицы объектов XML имеют ссылки, то есть позволяют ссылаться на себя через REF в других типах и таблицах:

SELECT REF(x) FROM xbooks x;

SELECT DEREF(REF(x)) FROM xbooks x;

У этого типа нет свойств, но есть методы. В этом можно убедиться, сделав запрос от имени SYS:

COLUMN text FORMAT A80

SELECT text
FROM user_source
WHERE name ='XMLTYPE' AND type='TYPE'
ORDER BY line;

Исследование каталога rdbms/admin позволяет обнаружить и исходное описание этого типа (но не его тела !) в файле dbmsxmlt.sql. К сожалению в документации описания этих методов разбросаны по разным местам, не всегда последовательны и ясны. Так например, EXTRACT и EXISTSNODE (о последней речь шла выше), возведены в ранг функций SQL, то есть описаны в книжке документации по SQL в разделе «Функции», в то время как из предыдущего запроса к словарю-справочнику следует, что это методы. О том же говорит синтаксис употребления. Для EXISTSNODE пример уже приводился, а для EXTRACT он может выглядеть так:

SELECT b.description.EXTRACT('/cover/title') FROM books b;

(Сравните с примером использования функции EXTRACTVALUE выше).

Вот некоторые другие примеры методов XMLTYPE:

SELECT b.description.GETCLOBVAL() FROM books b;

SELECT b.description.GETSTRINGVAL() FROM books b;

SELECT b.description.GETROOTELEMENT() FROM books b;

Обратите внимание, что некоторые методы XMLTYPE, например TOOBJECT, могут использоваться только процедурно, так как сами исполнены в виде процедур, а не функций.

Правда, объектность типа XMLTYPE реализована не в полной степени. Так, попытка создать в таблице столбец из коллекции документов XML (вложенной таблицы или массива VARRAY) в версии 9.2 терпит неудачу. Это относится только к БД; в PL/SQL этих проблем не возникает:

SQL> declare type xml_nt is table of xmltype index by varchar2(10);
2 begin null; end;
3 /

PL/SQL procedure successfully completed.

Взаимные преобразования табличного вида и XMLTYPE

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

Преобразование из XMLTYPE в табличную форму

Для преобразования данных типа XMLTYPE в обычный табличный вид можно использовать функции SQL и методы XMLTYPE, в первую очередь упоминавшуюся метод-функцию EXTRACT:

COLUMN xdoc FORMAT A80

SELECT ROWNUM, id, b.description.EXTRACT('/cover/author') xdoc
FROM books b;

Обратите внимание на возможность и способ обработки нескольких авторов в XML элементах <author>.

Использование функции SQL EXTRACTVALUE, в свою очередь, оставляет возможность отбора не более одного элемента XML для формирования каждой строки результата SELECT, но зато безболезнено убирает обрамляющие значение элемента XML метки:

SELECT id, EXTRACTVALUE(b.description.EXTRACT('/cover/title'), '/title') xdoc
FROM books b;

То же самое можно записать проще, что уже демонстрировалось в начале статьи.

Преобразование из табличной формы в XMLTYPE

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

- XMLElement
- XMLAttributes
- XMLAgg
- XMLConcat
- XMLForest

Вот некоторые примеры использования в схеме SCOTT:

SELECT XMLELEMENT("Employee", ename) FROM emp;

SELECT XMLELEMENT("Employee",
XMLATTRIBUTES(ename AS "Name", empno AS "Number"))
FROM emp;

Обратите внимание, что в результатах выдаются поля типа XMLTYPE:

CREATE TABLE xtable (n) AS SELECT XMLELEMENT("Name", ename) FROM emp;

Следующий пример – агрегирующей функции XMLAGG, допускающей использование в запросах с группировкой GROUP BY, подобно тому, как агрегирующие функции MIN, AVG и другие применяются для обычных данных, а не XMLTYPE:

SELECT XMLELEMENT("department", XMLATTRIBUTES(deptno AS "no")),
XMLAGG(XMLELEMENT("employee", ename))
FROM emp
GROUP BY deptno;

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

CREATE VIEW xview (a, b) AS
SELECT XMLELEMENT("department", XMLATTRIBUTES(deptno AS "no")),
XMLAGG(XMLELEMENT("employee", ename))
FROM emp
GROUP BY deptno;

CREATE TABLE xtable (a, b) AS
SELECT XMLELEMENT("department", XMLATTRIBUTES(deptno AS "no")),
XMLAGG(XMLELEMENT("employee", ename))
FROM emp
GROUP BY deptno;

Это объясняется тем, что столбцы A и B в обоих случаях Oracle пытается создавать как XMLTYPE, а наши данные таковы, что в столбце B содержатся строго говоря некорректные строки XML, например

Однако в случае выводимой таблицы Oracle смотрит на это сквозь пальцы, а в случае базовой – нет. Возможно это есть следствие определенной недоработанности некоторых областей технологий XML в Oracle, что вызвано чересчур быстрыми темпами развития этих технологий.

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

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

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

INSERT INTO participants VALUES

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

, address_type ( '789012', 'Samara' )

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

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

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

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

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

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

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

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

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

Тип XMLTYPE

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

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

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

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

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

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

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

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

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

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

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

В 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 :

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

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

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

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

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

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

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 :

Обратите внимание на использование функции 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 ; в переменной этого типа может храниться экземпляр любого из следующих подтипов:

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

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

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

За дополнительной информацией о типах семейства 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 определяет массив разнородных объектов (элементы которого могут относиться к разным типам данным).

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читайте также: