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

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

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

• XMLELEMENT
• XMLATTRIBUTES
• XMLAGG
• XMLCONCAT
• XMLFOREST
• XMLPI[10.2-]
• XMLCOMMENT[10.2-]
• XMLROOT[10.2-]
• XMLSERIALIZE[10.2-]
• XMLPARSE[10.2-]

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

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

• XMLCOLATTVAL
• SYS_XMLGEN (распространяется на строки)
• SYS_XMLAGG (распространяется на группы GROUP BY)
• XMLSEQUENCE (только курсорный вариант)
• XMLCDATA[10.2-]

[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.