Объекты в Oracle – это очень просто

Владимир Пржиялковский,
опубликовано в Мастерской Oracle, Interface Ltd

• Введение
• Хранимые объекты
  • Простой пример
  • Создание таблицы объектов
  • Ссылки на объект
  • Методы объектов
• Виртуальные объекты

Введение

В версии 8 в Oracle появилась возможность хранения неатомарных (нескалярных) значений в поле таблицы, а именно объекта в смысле объектного подхода (в рамках так называемой “объектно-реляционной модели” Oracle). Некоторые существенные пробелы этой первой реализации были устранены в версии 9. Примеры ниже используют возможности версии 9.2.

Сразу надо предостеречь от преувеличений достоинств объектного подхода в базах данных вообще. Действительно, неискушенный читатель некоторых руководств или рекламных материалов быстро впадет в недоумение: зачем же такие маститые разработчики СУБД, как фирмы IBM, Informix или Oracle так долго занимались табличной организацией данных, когда все это время рядом существовала более совершенная, удобная и т. д. объектная, первая реализация которой фирмой Xerox известна с 1980 года?

Непредвзятый ответ состоит в том, что ни табличная организация (часто вольно называемая “реляционной” применительно к конкретным СУБД), ни объектная не являются универсально “хорошими”, и что имеются свои достоинства и недостатки у одной и у другой. Некоторые соображения относительно областей применения обоих подходов к хранению данных можно найти в статье “Что объектам здорово, то реляциям смерть, и наоборот, и еще пол-оборота”.

В целом объектная реализация в Oracle традиционна для объектного подхода вообще. В основе лежит понятие объекта как совокупности свойств (атрибутов), причем действия с объектом регламентируются формулируемым набором методов (процедур или функций). Тип объекта задается сохраняемым в БД объектом TYPE.

Хранимые объекты

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

Рассмотрим схему БД, где хранятся данные о сотрудниках и отделах. Будем работать в схеме SCOTT, из которой на время нужно удалить таблицы EMP и DEPT (позже мы их восстановим).

Предположим, что и те, и другие имеют адреса: сотрудники - домашний, а отделы – юридический. Адрес имеет несколько полей (например, “индекс”, “район”, “населенный пункт”, “место”). В традиционной табличной реализации есть два способа промоделировать наличие адреса:

- включить одинаковые группы полей в таблицы сотрудников и отделов;
- создать отдельную таблицу адресов и включить в таблицы сотрудников и отделов ссылки на нее.

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

Объектные возможности последних версий Oracle дают возможность более приемлемой альтернативы. Для описания адреса создадим тип (здесь и далее предполагается использование в качестве рабочего инструмента SQL*Plus):

CREATE TYPE address_typ AS OBJECT (
zip CHAR(6),
location VARCHAR2(200))
/

Воспользуемся этим типом для описания сотрудников и отделов:

CREATE TABLE dept (
dname VARCHAR2(50),
deptno NUMBER CONSTRAINT pk_dept PRIMARY KEY,
addr address_typ);

CREATE TABLE emp (
ename VARCHAR2(50),
empno NUMBER CONSTRAINT pk_emp PRIMARY KEY,
deptno NUMBER CONSTRAINT fk_emp REFERENCES dept,
home address_typ);

Проверим описания созданных объектов:

DESCRIBE address_typ
DESCRIBE dept
DESCRIBE emp

Пример заведения сотрудников и отделов:

INSERT INTO dept VALUES (
'Sales',
10,
address_typ('123456', 'Boston 123... '));

INSERT INTO emp VALUES (
'Smith',
1001,
10,
address_typ('123333', 'Boston 567... '));

Здесь выражение ADDRESS_TYP('123333', 'Boston 567... ') означает обращение к конструктору объекта, то есть к функции, автоматически создаваемой СУБД при заведении нового типа для возможности создавать новые объекты этого типа с нужными значениями атрибутов. Понятие конструктора общепринято в объектном подходе. В приведенных предложениях INSERT простановку адреса можно оформить чуть иначе, добавив, в соответствии с духом объектного подхода, ключевое слово NEW перед обращением к конструктору:

INSERT INTO emp VALUES (
'Allen',
1002,
10,
NEW address_typ('123456', 'Boston 123... '));

Проверка:

COLUMN dname FORMAT A20
COLUMN ename FORMAT A20
COLUMN addr FORMAT A40
COLUMN home FORMAT A40

SELECT * FROM dept;

SELECT * FROM emp;

Другие примеры:

SELECT ename, home FROM emp;

SELECT e.ename, d.dname FROM emp e, dept d WHERE e.home = d.addr;

SELECT e.ename, e.home.zip FROM emp e;

UPDATE emp
SET home = address_typ('123457', 'Boston 777... ')
WHERE ename = 'Allen';

UPDATE emp e SET e.home.zip = '123458' WHERE ename = 'Allen';

Создание таблицы объектов

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

CREATE TABLE addr_list1 OF address_typ;

CREATE TABLE addr_list2 OF address_typ;

Таблицы объектов в Oracle было бы точнее называть списками объектов, так как это всегда таблицы ровно из одного столбца объектного типа.

Заполнение данными происходит как и ранее:

INSERT INTO addr_list1 VALUES
(NEW address_typ('123456', 'Boston 123... '));

или

INSERT INTO addr_list1 VALUES
(address_typ('123458', 'Boston 123... '));

INSERT INTO addr_list2 VALUES
(address_typ('123333', 'Boston 567... '));

Просмотр:

COLUMN location FORMAT A30

SELECT * FROM addr_list1;

SELECT VALUE(a) FROM addr_list1 a;

SELECT e.ename, e.home
FROM addr_list1 a, emp e
WHERE VALUE(a) = e.home;

(Функция VALUE специально придумана для возвращения значений объектов, а не атрибутов объектов по отдельности).

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

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

COLUMN ref FORMAT A90
COLUMN value FORMAT A40

SELECT REF(a) ref, VALUE(a) FROM addr_list1 a;

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

ALTER TABLE dept DROP (addr);

ALTER TABLE dept ADD (addr REF address_typ SCOPE IS addr_list1);

SELECT * FROM dept;

UPDATE dept d
SET d.addr =
(SELECT REF(a)
FROM addr_list1 a
WHERE VALUE(a)= address_typ('123458', 'Boston 123... '))
WHERE d.deptno = 10;

SELECT * FROM dept;

Фраза SCOPE IS при определении типа как ссылки на существующий объект необязательна, но позволяет фактически ссылаться только на объекты какой-нибудь объектной таблицы.

Раскрытие ссылки делается с помощью специальной функции DEREF:

COLUMN deref(addr) FORMAT A40

SELECT d.dname, DEREF(addr) FROM dept d;

Однако при обращении к нижележащим атрибутам раскрытие может выполняться и неявно (неявное преобразование типов, присутствующее в Oracle-диалекте SQL):

SELECT d.dname, d.addr.zip FROM dept d;

вместо более правильного

SELECT d.dname, DEREF(d.addr).zip FROM dept d;

Методы объектов

Выше было рассмотрено определение типа, содержащее описание атрибутов (“свойств”). Создадим тип сотрудников, в котором определен еще и метод:

CREATE TYPE employee_typ AS OBJECT (
ename VARCHAR2(50),
hiredate DATE,
deptno NUMBER,
home REF address_typ,
MEMBER FUNCTION days_at_company RETURN NUMBER)
/

Для описания тела метода-функции необходимо создать тело типа (аналогия пакет – тело пакета в PL/SQL):

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

Создадим таблицу объектов-сотрудников:

DROP TABLE emp;

CREATE TABLE emp OF employee_typ;

INSERT INTO emp VALUES (
'Scott',
SYSDATE,
10,
(SELECT REF(a) FROM addr_list1 a
WHERE VALUE(a) = address_typ('123458', 'Boston 123... ')));

Пример обращения к методу:

COLUMN home.location FORMAT A20

SELECT e.ename, e.home.location, e.days_at_company() FROM emp e;

Виртуальные объекты

Переводить в существующей БД табличные описания данных в объектные не всегда возможно, а иногда и не нужно. В силу разных обстоятельств может оказаться удобной имитация объектов на основе данных, хранимых в традиционных таблицах. Тогда к одним и тем же данным можно обращаться и через объектный интерфейс, и через табличный. Достигается это с помощью виртуальных объектов (object views), которых можно так назвать по аналогии с виртуальными таблицами (views).

Для примера вернем описания и наполнение традиционным таблицам схемы SCOTT: EMP и DEPT.

@?/sqlplus/admin/demobld

sqlplus scott/tiger

(Сценарий demobld.sql выводит нас из SQL*Plus).

Упростим для примера описание типа EMPLOYEE_TYP:

ALTER TYPE employee_typ DROP ATTRIBUTE (home);

ALTER TYPE employee_typ ADD ATTRIBUTE (empno NUMBER);

CONNECT scott/tiger

ALTER TYPE employee_typ COMPILE;

Построим таблицу виртуальных объектов типа EMPLOYEE_TYP по исходным данным, хранящимся в EMP:

CREATE VIEW emp_ov OF employee_typ
WITH OBJECT IDENTIFIER (empno) AS
SELECT e.ename, e.hiredate, e.deptno, e.empno FROM emp e;

По своему поведению виртуальные объекты ничем не отличаются от первичных. Проверка (“объектного доступа” к табличным данным):

SELECT e.ename, e.days_at_company () FROM emp_ov e;

SELECT VALUE(e) FROM emp_ov e;

SELECT REF(e) FROM emp_ov e;

UPDATE emp_ov e SET e.ename = INITCAP(e.ename)
WHERE e.empno = 7934;

SELECT ename FROM emp_ov;

Возможность выполнения традиционных DML-операторов над базовыми таблицами, естественно, сохраняется:

UPDATE emp SET ename = UPPER(ename) WHERE empno = 7934;

SELECT ename FROM emp;