Язык PL/SQL
всегда поддерживал традиционные процедурные стили программирования, в частности структурное проектирование и функциональную декомпозицию. Пакеты PL/SQL
позволяют использовать также объектно-ориентированный подход, применяя в работе с реляционными таблицами принципы абстракции и инкапсуляции. В новых версиях Oracle
введена непосредственная поддержка объектно-ориентированного программирования (ООП). Программистам стали доступны богатая и сложная система типов, иерархия, а также взаимозаменяемость типов.
Хотя тема объектно-ориентированного программирования в Oracle
могла бы стать предметом отдельной книги, мы рассмотрим лишь несколько примеров, демонстрирующих важнейшие аспекты объектно-ориентированного программирования на PL/SQL
:
- создание и использование объектных типов;
- наследование и взаимозаменяемость;
- эволюция типов;
- выборка данных на основе
REF
-ссылок; - объектные представления, в том числе
INSTEAD OF
.
Не рассчитывайте найти в этой статье:
- Полные диаграммы синтаксиса команд
SQL
для работы с объектными типами. - Обсуждение вопросов, относящихся к компетенции администраторов базы данных, — например, импортирования и экспортирования объектных данных.
- Описания низкоуровневых аспектов (структуры хранения данных на диске). Начнем с краткого исторического экскурса.
История объектных возможностей Oracle
Впервые появившиеся в 1997 году как дополнение к Oracle8
, объектные возможности позволили разработчикам расширить набор встроенных типов данных Oracle
абстрактными типами данных. Также в Oracle8
были введены определяемые программистом коллекции, оказавшиеся очень удобными. Объектная модель Oracle
обеспечивает много интересных возможностей, в частности доступ к данным через указатели, но она не поддерживает ни наследования, ни динамического полиморфизма, и поэтому объектно-ориентированные средства Oracle8
вряд ли произведут впечатление на приверженцев настоящего ООП. Сложность и низкая производительность объектных функций также не способствуют их успеху.
В Oracle8i
была введена поддержка хранимых процедур Java
, которые позволяли программировать на менее специализированном языке, чем PL/SQL
, и упростили разработку хранимых процедур для сторонников ООП. Появился способ преобразования объектных типов, определенных на сервере, в Java
-классы, что делало возможным совместную работу с данными в Java
и в базе данных. Версия Oracle8i
вышла в период наивысшего интереса к языку Java
, поэтому мало кто заметил, что объектные функции Oracle
почти не изменились, разве что начали понемногу интегрироваться с базовым сервером. В то время я спросил одного из представителей Oracle
о будущем ООП на языке PL/SQL
, и тот ответил: «Если вам требуется настоящее объектно-ориентированное программирование, пользуйтесь Java
».
Однако в Oracle9i
встроенная поддержка объектов была значительно расширена. Введена поддержка наследования и полиморфизма в базах данных, PL/SQL
был оснащен новыми объектными средствами. Имеет ли смысл расширять объектную модель системы на структуру базы данных? Следует ли переписать существующие приложения клиентского и промежуточного уровней? Как показано в табл. 1, в Oracle
были реализованы значительные достижения в ООП, и переход на эту технологию выглядит очень заманчиво. Также в таблице перечислены полезные возможности, которые еще не реализованы.
Таблица 1. Возможности Oracle
Таблица 1 (продолжение)
В Oracle
Database
10g
было включено несколько полезных улучшений в области коллекций, но только одна новая возможность, относящаяся к объектным типам: она описана во врезке «Псевдостолбец OBJECT_VALUE
» (см. с. 936).
Если вы еще не применяете объектно-ориентированное программирование в своих разработках, многие термины в этой таблице покажутся вам незнакомыми. Однако из оставшейся части этой статьи вы поймете их смысл и получите представление о более масштабных архитектурных решениях, которые вам придется принимать.
Пример объектного приложения PL/SQL
Идея для примера этого блога взята из книги Learning Oracle PL/SQL
(изд-во O’Reilly). Мы построим систему на базе Oracle
, в которой объектно-ориентированный подход используется для реализации библиотечного каталога. В каталоге хранится информация о книгах, периодических изданиях (журналах и газетах) и других печатных изданиях.
Рис. 1. Иерархия типов библиотечного каталога
Графическое представление типов верхнего уровня каталога можно увидеть на рис. 1. В дальнейшем иерархия будет дополнена объектами, обозначенными пунктиром.
Создание базового типа
Корневой элемент (вершина иерархии) представляет общие характеристики всех подтипов. Допустим, что у книг и периодики имеются только две общие характеристики: библиотечный идентификатор и название. Таким образом, объектный тип для элемента каталога создается в SQL*Plus
следующей командой SQL
:
CREATE OR REPLACE TYPE catalog_item_t AS OBJECT (
id INTEGER,
title VARCHAR2(4000),
NOT INSTANTIABLE MEMBER FUNCTION ck_digit_okay
RETURN BOOLEAN,
MEMBER FUNCTION print
RETURN VARCHAR2
) NOT INSTANTIABLE NOT FINAL;
Команда создает объектный тип — аналог класса Java
или C++
. Если провести аналогию с реляционной моделью, объектный тип подобен типу записи с набором связанных с ним функций и процедур (называемых методами).
Ключевые слова NOT FINAL
в конце определения типа указывают, что он может служить базовым или супертипом, то есть на его основе могут определяться другие типы. В данном случае мы собираемся создать подтипы для книг и периодических изданий; если опустить эти ключевые слова, по умолчанию Oracle
использует слово FINAL
, запрещающее создание подтипов на основе данного типа.
Обратите внимание на то, что спецификация данного типа содержит предложение NOT INSTANTIABLE
. Это означает, что PL/SQL
позволит объявлять переменные типа catalog_item_t
, но им нельзя будет присваивать значения — ни явно, ни другим способом. Подобно абстрактному классу Java
, данный тип предназначен исключительно для использования в качестве основы для создания подтипов (для которых, конечно, можно будет создавать и экземпляры объектов).
Для удобства в тип включен метод print
(кстати, это не зарезервированное слово), который позволяет получить описание объекта в виде одной строки. При создании подтипа метод будет перегружен, то есть подтип будет содержать метод с тем же именем, но возвращающий атрибуты подтипа. Обратите внимание: вместо оформления print
в виде процедуры, из-за чего в программе было бы зафиксировано использование DBMS_OUTPUT. PUT_LINE
или чего-нибудь в этом роде, я решил использовать функцию, вывод которой можно будет позднее перенаправить. Такое решение не является объектно-ориентированным; просто признак хорошего проектирования.
Также определяется метод ck_digit_okay
, который возвращает TRUE
или FALSE
в зависимости от того, совпала ли контрольная цифра. Предполагается, что все объекты подтипов catalog_item_t
будут содержать идентификаторы, включающие контрольную цифру для проверки правильности записи. Поскольку мы собираемся работать только с книгами и периодикой, которые обычно идентифицируются кодами ISBN
и ISSN
, то к этим подтипам применима концепция контрольной цифры.
Прежде чем переходить к следующей части примера, обратите внимание на некоторые обстоятельства:
- Приведенная выше команда
CREATE
TYPE
создает спецификацию объектного типа. Соответствующее тело типа с реализацией методов создается отдельно командойCREATE
TYPE BODY
. - Для объектных типов используется то же пространство имен, что для таблиц и программ
PL/SQL
верхнего уровня. Это одна из причин, по которым в именах типов используется префикс «_t». - Объектные типы всегда принадлежат создавшему их пользователю (схеме)
Oracle
,который может предоставлять привилегиюEXECUTE
для них другим пользователям. - Синонимы объектных типов поддерживаются только начиная с
Oracle9i Release 2
и выше. - Как и традиционные программы
PL/SQL
, объектные типы можно определять с разрешениями создателя (по умолчанию) или вызывающего (см. эту статью). - В отличие от объектных моделей некоторых языков, в объектной модели
Oracle
отсутствует корневой класс, от которого образуются все остальные пользовательские классы. - Если вы столкнетесь с ошибкой компиляции
PLS
-00103, вероятно, вы допустили распространенную ошибку и завершили метод символом «;». В этой спецификации типа должна использоваться запятая.
Создание подтипа
Поскольку для типа catalog_item_t
не могут создаваться экземпляры, нужно определить его подтипы. Начнем с подтипа для объектов-книг. Так как книга является одним из элементов каталога, в нашем примере все экземпляры подтипа book_t
будут обладать четырьмя атрибутами:
-
id
— наследуется от базового типаcatalog_item_t
; -
title
— также наследуется от базового типа; -
isbn
— соответствует кодуISBN
книги; -
pages
— количество страниц книги.
Соответствующий программный код выглядит так:
1 TYPE book_t UNDER catalog_item_t (
2 isbn VARCHAR2(13),
3 pages INTEGER,
4
5 CONSTRUCTOR FUNCTION book_t (id IN INTEGER DEFAULT NULL,
6 title IN VARCHAR2 DEFAULT NULL,
7 isbn IN VARCHAR2 DEFAULT NULL,
8 pages IN INTEGER DEFAULT NULL)
9 RETURN SELF AS RESULT,
10
11 OVERRIDING MEMBER FUNCTION ck_digit_okay
12 RETURN BOOLEAN,
13
14 OVERRIDING MEMBER FUNCTION print
15 RETURN VARCHAR2
16 );
Интересные части кода описаны в следующей таблице.
Строки | Описание |
1 | Для определения подтипа используется ключевое слово UNDER . Конструкция AS OBJECT здесь не используется, потому что она будет лишней: производным от объектного типа может быть только другой объектный тип |
2-3 | Перечисляются только те атрибуты, которые уникальны для подтипа; атрибуты родительского типа включаются в подтип автоматически. Oracle сначала создает атрибуты базового типа, а затем атрибуты подтипа в порядке их следования в спецификации |
5-15 | Объявления методов (см. следующий раздел) |
Методы
В приведенном выше определении типа используется два вида методов:
- Конструктор — функция, которая получает значения всех атрибутов и помещает их в объект заданного типа. Объявлена в строках 5-9.
- Методы экземпляров — функции или процедуры, выполняемые в контексте экземпляра объекта, то есть имеющие доступ к текущим значениям атрибутов. Методы экземпляров объявляются в строках 11-12 и 14-15.
В нашем примере продемонстрировано определение пользовательского конструктора (такая возможность появилась в Oracle9i
Release
2
). В предыдущих версиях Oracle
поддерживались только конструкторы, автоматически генерируемые системой. Создание собственного конструктора типа позволяет управлять созданием экземпляров этого типа. В конструкторе можно проверить и инициализировать данные, а также реализовать полезные побочные эффекты. Кроме того, можно определить несколько перегруженных версий пользовательского конструктора для разных вариантов его вызова.
Чтобы увидеть методы и типы в действии, выполните следующий анонимный блок:
1 DECLARE
2 generic_item catalog_item_t;
3 abook book_t;
4 BEGIN
5 abook := NEW book_t(title => 'Out of the Silent Planet',
6 isbn => '0-6848-238-02');
7 generic_item := abook;
8 DBMS_OUTPUT.PUT_LINE('BOOK: ' || abook.print());
9 DBMS_OUTPUT.PUT_LINE('ITEM: ' || generic_item.print());
10 END;
Вызовы метода print
объектов abook
и generic_item
(строки 8 и 9) возвращают идентичные результаты:
BOOK: id=; title=Out of the Silent Planet; isbn=0-6848-238-02; pages=
ITEM: id=; title=Out of the Silent Planet; isbn=0-6848-238-02; pages=
Строки | Описание |
5-6 | Конструктор строит новый объект и помещает его в переменную. В данном примере аргументы передаются по именам. Мы задали значения только двух атрибутов из четырех, но конструктор все равно создал объект. Синтаксис использования конструктора: Ключевое слово NEW , появившееся в Oracle9i Database Release 2, не является обязательным, но оно наглядно указывает на то, что команда создаст новый объект |
7 | Хотя экземпляры экземпляра каталога создавать запрещено, представляющей его переменной можно присвоить экземпляр подтипа, и в ее значении будут содержаться атрибуты, уникальные для данного подтипа. Этот факт демонстрирует важный аспект «взаимозаменяемости» типов в PL/SQL : ее суть заключается в том, что в объектной переменной может содержаться экземпляр любого подтипа типа данных этой переменной |
8-9 | Для вызова метода print () используется классический точечный синтаксис: поскольку этот метод является методом экземпляра и его можно вызвать только для уже объявленного и инициализированного экземпляра объекта. Всегда вызывается метод самого специализированного подтипа (расположенного на самом нижнем уровне иерархии), связанного с текущим экземпляром объекта. Выбор метода откладывается до этапа выполнения — эта технология называется динамической диспетчеризацией методов. Она очень удобна, хотя и ухудшает производительность |
Чтобы лучше понять полученный результат, рассмотрим тело метода book_t
. В реализации данного типа используется две важные концепции, о которых будет рассказано далее.
1 TYPE BODY book_t
2 AS
3 CONSTRUCTOR FUNCTION book_t (id IN INTEGER,
4 title IN VARCHAR2,
5 isbn IN VARCHAR2,
6 pages IN INTEGER)
7 RETURN SELF AS RESULT
8 IS
9 BEGIN
10 SELF.id := id;
11 SELF.title := title;
12 SELF.isbn := isbn;
13 SELF.pages := pages;
14 IF isbn IS NULL OR SELF.ck_digit_okay
15 THEN
16 RETURN;
17 ELSE
18 RAISE_APPLICATION_ERROR(-20000, 'ISBN ' || isbn
19 || ' has bad check digit');
20 END IF;
21 END;
22
23 OVERRIDING MEMBER FUNCTION ck_digit_okay
24 RETURN BOOLEAN
25 IS
26 subtotal PLS_INTEGER := 0;
27 isbn_digits VARCHAR2(10);
28 BEGIN
29 /* Удаление дефисов и пробелов */
30 isbn_digits := REPLACE(REPLACE(SELF.isbn, '-'), ' ');
31 IF LENGTH(isbn_digits) != 10
32 THEN
33 RETURN FALSE;
34 END IF;
35
36 FOR nth_digit IN 1..9
37 LOOP
38 subtotal := subtotal +
39 (11 - nth_digit) *
40 TO_NUMBER(SUBSTR(isbn_digits, nth_digit, 1));
41 END LOOP;
42
43 /* check digit can be 'X', which has value of 10 */
44 IF UPPER(SUBSTR(isbn_digits, 10, 1)) = 'X'
45 THEN
46 subtotal := subtotal + 10;
47 ELSE
48 subtotal := subtotal + TO_NUMBER(SUBSTR(isbn_digits, 10, 1));
49 END IF;
50
51 RETURN MOD(subtotal, 11) = 0;
52
53 EXCEPTION
54 WHEN OTHERS
55 THEN
56 RETURN FALSE;
57 END;
58
59 OVERRIDING MEMBER FUNCTION print
60 RETURN VARCHAR2
61 IS
62 BEGIN
63 RETURN 'id=' || id || '; title=' || title
64 || '; isbn=' || isbn || '; pages=' || pages;
65 END;
66 END;
Пользовательский конструктор должен подчиняться определенным правилам:
- Он должен быть объявлен с ключевыми словами
constructor
function
(строка 3). - Предложение, определяющее возвращаемое значение, должно выглядеть так:
RETURN SELF AS RESULT
(строка 7). - Конструктор может присваивать значения любым атрибутам текущего объекта (строки 10-13).
- Он должен завершаться оператором
RETURN
или исключением (строка 16; строки 18-19).
Обычно конструктор присваивает значения большинству атрибутов объекта. Как видно из строки 14, перед окончанием работы конструктор проверяет контрольную цифру. Как видно из строки 30, атрибуты объекта (такие, как SELF.isbn
) доступны еще до завершения проверки — интересная и полезная возможность.
Код в строках 18-19 представляет собой простую заготовку. В реальном приложении обработка исключений, специфических для конкретного приложения, не столь тривиальна.
Теперь обратимся к ключевому слову SELF
, которое несколько раз встречается в коде объектного типа (не только в конструкторе, но и других методах), — аналогу ключевого слова this
в языке Java
. SELF
— это ссылка на вызывающий (текущий) экземпляр, используемая исключительно в реализации методов. Ее можно применять для ссылки на весь объект, а с точечным синтаксисом — для ссылки на атрибут или метод объекта:
IF SELF.id ...
IF SELF.ck_digit_okay() ...
В методах экземпляров ключевое слово SELF
не обязательно (см. строки 63-64), поскольку идентификаторы текущего объекта всегда видны в методе. В строках 10-13 оно было необходимо — в данном случае имена формальных параметров конструктора совпадают с именами атрибутов, и компилятор идентифицировал бы их как ссылки на параметры. Кроме того, они делают код более понятным.
Еще несколько рекомендаций, касающихся применения ключевого слова SELF
:
- Ключевое слово
SELF
не может использоваться в статических методах, поскольку у них нет «текущего объекта». - По умолчанию в функциях ключевое слово
SELF
интерпретируется как входная переменная (IN
), а в процедурах и конструкторах — как входная и выходная переменная (in out
). - Ссылку на текущий объект можно передать явно в первом формальном параметре. Вычисление контрольной цифры в строках 23-57 приведено только для примера. К сожалению, наш алгоритм не использует в полной мере все новые объектно-ориентированные средства. Кроме того, не полностью реализован обработчик исключений. Он должен обрабатывать такие ситуации, как передача функции
TO_NUMBER
строки вместо числа.
Перейдем ко второму подтипу — периодическим изданиям:
TYPE serial_t UNDER catalog_item_t (
issn VARCHAR2(10),
open_or_closed VARCHAR2(1),
CONSTRUCTOR FUNCTION serial_t (id IN INTEGER DEFAULT NULL,
title IN VARCHAR2 DEFAULT NULL,
issn IN VARCHAR2 DEFAULT NULL,
open_or_closed IN VARCHAR2 DEFAULT NULL)
RETURN SELF AS RESULT,
OVERRIDING MEMBER FUNCTION ck_digit_okay
RETURN BOOLEAN,
OVERRIDING MEMBER FUNCTION print
RETURN VARCHAR2
) NOT FINAL;
И снова в спецификации типа нет ничего нового. Объектный тип serial_t
в нашей модели имеет собственный конструктор, свою версию функции проверки контрольной цифры и способ возврата информации о себе при вызове метода print
.
Помимо конструктора и методов экземпляров, Oracle
поддерживает еще две разновидности методов:
- Статические методы — функции и процедуры, работа которых не зависит от экземпляра объекта. Такие методы являются аналогами обычных процедур и функций
PL/SQL
. - Методы сравнения (MAP и ORDER) — методы, определяющие критерии соответствия или сортировки. Это специальные методы экземпляров, позволяющие определить процедуру сравнения двух объектов данного типа, например при проверке на эквивалентность в
PL/SQL
или при сортировке объектов вSQL
.
На объекты распространяется правило Oracle
, гласящее, что значение неинициализированной переменной автоматически устанавливается равным NULL
(более точный термин для объектов — атомарный NULL
; за дополнительной информацией обращайтесь к этому блогу). Как и в случае с коллекциями, в этом случае значение атрибутам объекта присваивать нельзя. Рассмотрим пример:
DECLARE
mybook book_t; -- объект объявлен, но не инициализирован
BEGIN
IF mybook IS NULL -- проверка вернет TRUE
THEN
mybook.title := 'Learning Oracle PL/SQL'; -- в этой строке выдается...
END IF;
EXCEPTION
WHEN ACCESS_INTO_NULL -- ...стандартное исключение
THEN
...
END;
Прежде чем присваивать значения атрибутам, вы обязаны инициализировать объект (создать экземпляр) одним из трех способов: с помощью метода-конструктора, путем непосредственного присваивания экземпляра другого объекта или посредством выборки из базы данных (см. раздел «Запись, выборка и использование объектов»).
Вызов методов супертипа в Oracle 11g
Одно из ограничений объектно-ориентированных средств Oracle
, которое было снято в Oracle11g
, заключалось в возможности вызова метода супертипа, переопределенного в текущем подтипе (или подтипе более высокого уровня). До выхода Oracle11g
, если метод супертипа переопределялся в подтипе, вызвать метод супертипа в экземпляре подтипа было невозможно. Сейчас такая возможность появилась.
Предположим, мы создали корневой тип для обработки и отображения информации о еде:
CREATE TYPE food_t AS OBJECT (
NAME VARCHAR2 (100),
food_group VARCHAR2 (100),
grown_in VARCHAR2 (100),
MEMBER FUNCTION to_string RETURN VARCHAR2
)
NOT FINAL;
/
CREATE OR REPLACE TYPE BODY food_t
IS
MEMBER FUNCTION to_string RETURN VARCHAR2
IS
BEGIN
RETURN 'FOOD! ' || self.name || ' - '
|| self.food_group || ' - ' || self.grown_in;
END;
END;
/
Далее создается подтип для представления десертов, переопределяющий метод to_string
. При выводе информации о десерте нам хотелось бы вывести как информацию, относящуюся только к десертам, так и более общие атрибуты, но по возможности обойтись без копирования/вставки кода — лучше повторно использовать имеющийся код из типа food_t
. До выхода Oracle11g
это было невозможно, однако благодаря новому механизму вызова (SELF
AS
супертип) подтип теперь можно определить в следующем виде:
CREATE TYPE dessert_t UNDER food_t (
contains_chocolate CHAR (1)
, year_created NUMBER (4)
, OVERRIDING MEMBER FUNCTION to_string RETURN VARCHAR2
);
/
CREATE OR REPLACE TYPE BODY dessert_t
IS
OVERRIDING MEMBER FUNCTION to_string RETURN VARCHAR2
IS
BEGIN
/* Включение строки супертипа (food_t) в строку подтипа */
RETURN 'DESSERT! With Chocolate? '
|| contains_chocolate
|| ' created in '
|| SELF.year_created
|| chr(10)
|| (SELF as food_t).to_string;
END;
END;
/
Теперь при выводе представления десертов в формате to_string
также выводятся общие атрибуты:
DECLARE
TYPE foodstuffs_nt IS TABLE OF food_t;
fridge_contents foodstuffs_nt
:= foodstuffs_nt (
food_t ('Eggs benedict', 'PROTEIN', 'Farm')
, dessert_t ('Strawberries and cream'
, 'FRUIT', 'Backyard', 'N', 2001)
);
BEGIN
FOR indx in 1 .. fridge_contents.COUNT
LOOP
DBMS_OUTPUT.put_line (RPAD ('=', 60, '='));
DBMS_OUTPUT.put_line (fridge_contents (indx).to_string);
END LOOP;
END;
/
Результат:
============================================================
FOOD! Eggs benedict - PROTEIN - Farm
============================================================
DESSERT! With Chocolate? N created in 2001
FOOD! Strawberries and cream - FRUIT - Backyard
В отличие от некоторых других объектно-ориентированных языков, в Oracle
при вызове метода супертипа используется не обобщенное ключевое слово SUPERTYPE
, а конкретный супертип из иерархии. Такое решение обладает большей гибкостью (вы можете указать нужный супертип), однако оно также означает, что имя супертипа жестко фиксируется в коде подтипа.
Запись, выборка и использование объектов
До сих пор мы рассматривали определение типов данных и создание экземпляров объектов в памяти, выделяемой для выполняющихся программ. Однако это только небольшой фрагмент «объектной картины» — конечно, Oracle
позволяет сохранять объекты в базе данных.
Библиотечный каталог из нашего примера можно сохранить в базе данных как минимум двумя способами: либо в виде одной большой таблицы объектов, соответствующих элементам каталога, либо в виде набора меньших таблиц, представляющих подтипы элементов каталога. Первый способ реализуется так:
CREATE TABLE catalog_items OF catalog_item_t
(CONSTRAINT catalog_items_pk PRIMARY KEY (id));
Эта команда предписывает Oracle
создать таблицу объектов catalog_items
, каждая строка которой представляет собой объект типа catalog_item_t
. Объектная таблица обычно содержит по одному столбцу на атрибут:
SQL > DESC catalog_items
Name Null? Type
------------------------------------ -------- -------------------------
ID NOT NULL NUMBER(38)
TITLE VARCHAR2(4000)
Однако помните, что объектный тип catalog_item_t
не допускает создания экземпляров и каждая строка таблицы на самом деле будет объектом одного из его подтипов, например книгой или периодическим изданием. Куда же попадают дополнительные атрибуты? Рассмотрим следующие допустимые команды[1]:
INSERT INTO catalog_items
VALUES (NEW book_t(10003, 'Perelandra', '0-684-82382-9', 222));
INSERT INTO catalog_items
VALUES (NEW serial_t(10004, 'Time', '0040-781X', 'O'));.
В таблице необходимо выделить место для хранения значений атрибутов каждого из возможных подтипов. Чтобы выделить данную операцию, в таблицу добавляются скрытые столбцы, по одному на каждый уникальный атрибут подтипа. С точки зрения программирования объектов это удобно, поскольку позволяет сохранить абстракцию элементов каталога.
Секция CONSTRAINT
указывает, что столбец id
является первичным ключом. Да, у объектных таблиц тоже могут быть первичные ключи. Кроме того, при отсутствии предложения CONSTRAINT
Oracle
автоматически сгенерирует идентификатор объекта (OID
).
Идентификация объектов
В реляционных базах данных каждая строка имеет уникальный идентификатор. Объектно-ориентированные системы также обычно присваивают своим объектам уникальные идентификаторы. Программист, использующий объектно-ориентированные возможности базы данных, может объединять эти два способа идентификации. В следующей таблице перечислены основные программные конструкции, в которых могут быть задействованы идентификаторы объектов.
Что и где | Используются ли идентификаторы объектов |
Объект строки в объектной таблице | Да |
Объектный столбец в любой таблице (или при выборке в программе PL/SQL ) | Нет; используйте первичный ключ строки |
Временный объект, создаваемый в программе PL/SQL | Нет; используйте весь объект |
Объект строки, выбираемый из объектной таблицы в программе PL/SQL | Да, но доступен в программе только при явной выборке REF (см. далее «Использование REF ») |
Пример таблицы, которая может содержать объектные столбцы:
CREATE TABLE my_writing_projects (
project_id INTEGER NOT NULL PRIMARY KEY,
start_date DATE,
working_title VARCHAR2(4000),
catalog_item catalog_item_t -- this is a "объектный столбец"
);
Oracle
Corporation
считает, что объектный столбец зависит от первичного ключа строки, и он не должен идентифицироваться независимо.
Для любой объектной таблицы идентификатор любого объекта может быть создан на основе одного из следующих элементов:
- Первичный ключ — в конец команды
CREATE
TABLE
добавляется секцияOBJECT IDENTIFIER IS PRIMARY KEY
. - Значение, генерируемое системой, —
Oracle
добавляет в таблицу скрытый столбец с именемSYS_NC_OID$
и помещает в него уникальное 16-байтовое значение типаRAW
. Идентификаторы на основе первичных ключей обычно занимают меньше места, чем системно-генерируемые, но последние обладают определенными преимуществами. Более подробное обсуждение достоинств и недостатков двух указанных методов вы найдете в документацииOracle
Application Developer’s Guide
—Object-Relational Features
. Пока же достаточно знать, что системно-генерируемые идентификаторы обладают следующими качествами: - Непрозрачность. Хотя программы могут использовать их неявно, значение такого идентификатора обычно невозможно получить.
- Потенциальная глобальная уникальность. Пространство
OID
имеет объем, достаточный для хранения 2128 объектов, и теоретически позволяет идентифицировать объекты в распределенных базах данных без явных ссылок на базы данных. - Неизменяемость. В данном контексте это означает, что идентификаторы нельзя обновлять. Даже после экспорта и импорта объекта его
OID
остается тем же, в отличие отROWID
. Для измененияOID
нужно удалить объект и создать его заново.
Функция VALUE
Для извлечения объектов из базы данных Oracle
предоставляет SQL
-функцию VALUE
. Она получает единственный аргумент, в котором передается указанный в условии FROM
псевдоним таблицы, и возвращает объект того типа, на основе которого определена данная таблица. В команде SELECT
это выглядит следующим образом:
SELECT VALUE(c)
FROM catalog_items c;
Функция VALUE
возвращает вызывающей программе последовательность битов, а не текстовое представление значений столбцов. Однако в SQL*Plus
существует встроенная возможность вывода объектов, поэтому результаты приведенного запроса выводятся следующим образом:
VALUE(C)(ID, TITLE)
-------------------------------------------------
BOOK_T(10003, 'Perelandra', '0-684-82382-9', 222)
SERIAL_T(10004, 'Time', '0040-781X', 'O')
В PL/SQL
также имеются средства для работы с извлеченными из базы данных объектами. Сначала объявляется объектная переменная соответствующего типа:
DECLARE
catalog_item catalog_item_t;
CURSOR ccur IS
SELECT VALUE(c)
FROM catalog_items c;
BEGIN
OPEN ccur;
FETCH ccur INTO catalog_item;
DBMS_OUTPUT.PUT_LINE('I fetched item #' || catalog_item.id);
CLOSE ccur;
END;
В аргументе PUT_LINE
используется синтаксис переменная.атрибут, а возвращает она значение указанного атрибута:
I fetched item #10003
Команда FETCH
присваивает объект локальной переменной catalog_item
, которая объявлена как переменная базового объектного типа. Это естественно, поскольку мы не знаем заранее, объект какого подтипа будет выбран из базы данных.
Заодно этот код показывает (на примере вывода значения атрибута catalog_item.id
), что мы имеем прямой доступ к атрибутам базового типа.
Также можно использовать обычные атрибуты курсоров. Например, приведенный выше анонимный блок можно переписать таким образом:
DECLARE
CURSOR ccur IS
SELECT VALUE(c) obj
FROM catalog_items c;
arec ccur%ROWTYPE;
BEGIN
OPEN ccur;
FETCH ccur INTO arec;
DBMS_OUTPUT.PUT_LINE('I fetched item #' || arec.obj.id);
CLOSE ccur;
END;
Если нам потребуется вывести все атрибуты объекта, можно использовать метод print
. Это вполне допустимо, поскольку он объявлен в объектном типе корневого уровня и реализован в подтипах. На этапе выполнения Oracle
найдет для данного метода перегруженную версию из подтипа.
Функция VALUE
поддерживает точечный синтаксис, обеспечивающий доступ к атрибутам и методам, но только к тем из них, которые определены в базовом типе. Например, команда
SELECT VALUE(c).id, VALUE(c).print()
FROM catalog_items c;
возвращает результат:
VALUE(C).ID VALUE(C).PRINT()
----------- ----------------------------------------------------------
10003 id=10003; title=Perelandra; isbn=0-684-82382-9; pages=222
10004 id=10004; title=Time; issn=0040-781X; open_or_closed=Open
При работе в клиентской среде, не поддерживающей объектов Oracle
, можно воспользоваться именно этой функцией.
А если попытаться прочитать только те атрибуты, которые являются уникальными для конкретного подтипа? Выполните такую команду:
SELECT VALUE(c).issn /* Error; subtype attributes are inaccessible */
FROM catalog_items c;
В ответ Oracle
выдает сообщение об ошибке. Дело в том, что объект родительского типа не предоставляет прямого доступа к атрибутам подтипа. Можно попробовать объявить переменную book
типа book_t
и присвоить ей объект данного подтипа в надежде, что она покажет скрытые атрибуты:
book := catalog_item; /* Ошибка; Oracle не выполняет предполагаемое
понижающее преобразование типа */
На этот раз я получаю ошибку PLS-00382
(неправильный тип выражения). Что происходит? Неочевидный ответ на этот вопрос приведен в следующем разделе.
Несколько завершающих замечаний по поводу выполнения DML
-операций в объектных реляционных таблицах:
- Для объектной таблицы, созданной на основе объектного типа, не имеющего подтипов, возможны выборка, вставка, обновление и удаление значений всех столбцов с использованием обычных команд
SQL
. Таким образом, объектно-ориентированные и реляционные программы могут совместно использовать одни и те же данные. - Традиционные команды
DML
не имеют доступа к скрытым столбцам, представляющим атрибуты подтипов. Для работы с такими столбцами используется «объектныйDML
». - Для обновления всего объекта в таблице базы данных из программы
PL/SQL
можно использовать объектную командуDML
, которая обновит все атрибуты (столбцы), включая уникальные для подтипа:
UPDATE catalog_items c SET c = object_variable WHERE ...
- Единственным известным способом обновления заданного столбца, уникального для подтипа, является обновление всего объекта. Например, чтобы установить количество страниц книги с идентификатором 10007 равным 1000, потребуется такая команда:
UPDATE catalog_items c
SET c = NEW book_t(c.id, c.title, c.publication_date, c.subject_refs,
(SELECT TREAT(VALUE(y) AS book_t).isbn
FROM catalog_items y
WHERE id = 10007),
1000)
WHERE id = 10007;
А теперь вернемся к проблеме, о которой я упоминал выше.
Функция TREAT
Допустим, в объявлении типа переменной PL/SQL
указано имя супертипа, а ее значение принадлежит к подтипу. Как получить доступ к атрибутам и методам, специфическим для этого подтипа? Допустим, мы хотим интерпретировать элемент каталога как книгу. Эта операция называется понижающим преобразованием (downcasting
), и иногда компилятор не может выполнить ее автоматически. В таких случаях приходится использовать функцию Oracle
TREAT
:
DECLARE
book book_t;
catalog_item catalog_item_t := NEW book_t();
BEGIN
book := TREAT(catalog_item AS book_t); /* Using 9i R2 or later */
END;
или с помощью SQL (до выхода Oracle9i Release 2 функция TREAT в PL/SQL не под-
держивалась):
DECLARE
book book_t;
catalog_item catalog_item_t := book_t(NULL, NULL, NULL, NULL);
BEGIN
SELECT TREAT (catalog_item AS book_t)
INTO book
FROM DUAL;
END;
Общий синтаксис функции TREAT
выглядит так:
TREAT (object_instance AS subtype) [ . { attribute | method( args...) } ]
Здесь экземпляр объекта — это значение конкретного супертипа в объектной иерархии, а подтип — имя подтипа в этой иерархии. Если вы попытаетесь преобразовать объект к типу, принадлежащему другой объектной иерархии, компилятор выдаст ошибку. Когда функции TREAT
передается объект из правильной иерархии, она возвращает либо преобразованный объект, либо NULL
, но не ошибку.
Как и при использовании функции VALUE
, при обращении к функции TREAT
можно задать атрибут или метод преобразованного объекта с использованием точечного синтаксиса:
DBMS_OUTPUT.PUT_LINE(TREAT (VALUE(c) AS serial_t).issn);
Если вам понадобится перебрать все объекты в таблице и выполнить определенные операции с учетом их типов, это можно сделать так:
DECLARE
CURSOR ccur IS
SELECT VALUE(c) item FROM catalog_items c;
arec ccur%ROWTYPE;
BEGIN
FOR arec IN ccur
LOOP
CASE
WHEN arec.item IS OF (book_t)
THEN
DBMS_OUTPUT.PUT_LINE('Found a book with ISBN '
|| TREAT(arec.item AS book_t).isbn);
WHEN arec.item IS OF (serial_t)
THEN
DBMS_OUTPUT.PUT_LINE('Found a serial with ISSN '
|| TREAT(arec.item AS serial_t).issn);
ELSE
DBMS_OUTPUT.PUT_LINE('Found unknown catalog item');
END CASE;
END LOOP;
END;
Данный блок демонстрирует применение предиката IS OF
для проверки объектного типа. Синтаксис предиката выглядит многообещающе:
object IS OF ( [ ONLY ] имя_типа )
Однако на самом деле возможности предиката ограничены — он работает только с объектными типами данных Oracle
и не работает с базовыми, такими как NUMBER
или DATE
. Компилятор выдаст сообщение об ошибке, если при использовании предиката тип объекта не принадлежит той же иерархии, что и тип имя_типа.
Обратите внимание на ключевое слово ONLY
. По умолчанию (то есть без ONLY
) предикат возвращает TRUE
, если объект относится к заданному типу или одному из его подтипов. Если ключевое слово ONLY
присутствует, предикат IS OF
не сравнивает подтипы и возвращает TRUE
только в случае точного совпадения типов.
На уровне синтаксиса вывод любого выражения TREAT
всегда должен использоваться как функция, даже если TREAT
вызывается для активизации процедуры. Например, если тип book_t
содержит процедуру set_isbn
, можно предположить, что строка следующего вида допустима:
TREAT(item AS book_t).set_isbn('0140714154'); -- неверно
Но компилятор выдает странную ошибку PLS-00363
(выражение ‘SYS_TREAT
’ не может использоваться для присваивания).
Вместо этого необходимо сохранить результат во временной переменной, а затем вызвать процедуру:
book := TREAT(item AS book_t);
book.set_isbn('0140714154');
Предикат IS OF
, как и функция TREAT
, может использоваться в SQL
Oracle9i
, но в PL/SQL
до Oracle9i Release 2
он не поддерживается. Чтобы обойти это ограничение, в Release
1 можно определить в дереве типов один или несколько дополнительных методов и, пользуясь динамической диспетчеризацией, выполнить нужную операцию на соответствующем уровне иерархии. Выбор подходящего метода понижающего преобразования зависит не только от номера версии, но и от выполняемых приложением операций.
А теперь я хочу перейти к другой интересной области: полезным возможностям, которые Oracle
вам предоставляет при (неизбежном!) изменении архитектуры приложения.
Эволюция и создание типов
По сравнению с Oracle8i
в Oracle9i
достигнут огромный прогресс в области эволюции типов. Теперь Oracle
позволяет вносить в объектные типы различные изменения, причем это возможно даже тогда, когда таблицы уже созданы и заполнены объектами.
Ранее в этой статье мы на скорую руку определили объектный тип catalog_item_t
. Однако любой библиотекарь скажет, что для каждой книги или периодического издания желательно хранить в каталоге дату издания. Поэтому мы делаем следующее:
ALTER TYPE catalog_item_t
ADD ATTRIBUTE publication_date VARCHAR2(400)
CASCADE INCLUDING TABLE DATA;
Oracle
распространяет изменение на соответствующие таблицы, автоматически выполняя все необходимые модификации. В конец списка атрибутов супертипа добавляется новый атрибут, а за последним столбцом супертипа в соответствующую объектную таблицу добавляется новый столбец. Если теперь выполнить команду DESCRIBE
, которая выводит описание заданного объекта, результат будет таким:
SQL> DESC catalog_item_t
catalog_item_t is NOT FINAL
catalog_item_t is NOT INSTANTIABLE
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER(38)
TITLE VARCHAR2(4000)
PUBLICATION_DATE VARCHAR2(400)
METHOD
------
MEMBER FUNCTION CK_DIGIT_OKAY RETURNS BOOLEAN
CK_DIGIT_OKAY IS NOT INSTANTIABLE
METHOD
------
MEMBER FUNCTION PRINT RETURNS VARCHAR2
And a DESCRIBE of the table now looks like this:
SQL> DESC catalog_items
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(38)
TITLE VARCHAR2(4000)
PUBLICATION_DATE VARCHAR2(400)
Фактически команда ALTER
TYPE
изменяет почти все, но, к сожалению, она не настолько умна, чтобы переписать наши методы. Особенно важны конструкторы, поскольку придется изменить их сигнатуры. Проблема решается удалением и повторным добавлением метода. Для удаления метода из спецификации book_t
необходимо выполнить такую команду:
ALTER TYPE book_t
DROP CONSTRUCTOR FUNCTION book_t (id INTEGER DEFAULT NULL,
title VARCHAR2 DEFAULT NULL,
isbn VARCHAR2 DEFAULT NULL,
pages INTEGER DEFAULT NULL)
RETURN SELF AS RESULT
CASCADE;
Обратите внимание на полную спецификацию функции. Она гарантирует, что удаляется именно тот метод, который нужен, поскольку могут существовать его перегруженные версии. (Стоит заметить, что значения по умолчанию указывать не обязательно.)
Соответствующая операция добавления метода выглядит так же просто:
ALTER TYPE book_t
ADD CONSTRUCTOR FUNCTION book_t (id INTEGER DEFAULT NULL,
title VARCHAR2 DEFAULT NULL,
publication_date VARCHAR2 DEFAULT NULL,
isbn VARCHAR2 DEFAULT NULL,
pages INTEGER DEFAULT NULL)
RETURN SELF AS RESULT
CASCADE;
Далее нужно аналогичным образом модифицировать тип serial_t
, а затем заменить два соответствующих тела типов командой CREATE OR REPLACE TYPE BODY
.
Кроме того, следует просмотреть все методы на предмет необходимости изменений (например, дату издания желательно включить в метод print
).
Кстати говоря, типы можно удалять, для этого используется следующая команда:
DROP TYPE имя_типа [ FORCE ];
Ключевым словом FORCE
(доступным только в Oracle 11g Release 2
и выше) следует пользоваться осторожно, поскольку отменить последствия выполнения команды уже не удастся. Все объектные типы и объектные таблицы, зависящие от типа, становятся неприменимыми. Если в таблицах имеются столбцы данного типа, Oracle
сделает их недоступными. Для удаления подтипа, который используется в определениях таблиц, можно задать команду DROP
TYPE
в такой форме:
DROP TYPE subtypename VALIDATE;
С ключевым словом VALIDATE Oracle
просматривает таблицы и удаляет подтип только в случае, если таблицы не содержат ни одного экземпляра этого подтипа. Таким образом можно избежать разрушительных последствий от применения ключевого слова FORCE
.
И снова указатели
Среди объектно-ориентированных функций Oracle
появилась возможность хранения значений типа REF
. Они представляют собой логические указатели на конкретную строку объектной таблицы. В ссылке хранится следующая информация:
- первичный ключ строки или системно-генерируемый идентификатор объекта;
- уникальный идентификатор таблицы;
- информация о физическом местоположении строки на диске в виде ее значения типа
ROWID
.
Значение REF
представляет собой длинную шестнадцатеричную строку:
SQL> SELECT REF(c) FROM catalog_items c WHERE ROWNUM = 1;
REF(C)
--------------------------------------------------------------------------------
00002802099FC431FBE5F20599E0340003BA0F1F139FC431FBE5F10599E0340003BA0F1F13024000
Запросы и программы могут использовать значения REF
для выборки строк объектной таблицы без указания ее имени. Давайте посмотрим, как REF
-ссылки могут применяться в библиотечном каталоге.
Использование REF-ссылок
Библиотечный фонд обычно классифицируется по тематике. Например, книга, которую вы сейчас читаете, может относиться к трем категориям:
-
Oracle
(Компьютеры); -
PL/SQL
(Языки программирования); - реляционные базы данных.
Для классификации используется древовидная структура: категория «Компьютеры» является родительской для Oracle
, а категория «Языки программирования» — родительской для PL/SQL
.
Категории и хранящиеся в библиотеке объекты связаны отношением «многие-ко- многим»: книга может относиться к нескольким категориям, а к категории может принадлежать множество книг. В нашем библиотечном каталоге книги всех категорий размещены в одной таблице. В реляционной методологии создается промежуточная таблица, разделяющая отношение «многие-ко-многим» на два отношения «один-ко- многим». Однако объектно-реляционный подход предлагает другое решение.
Начнем с создания объектного типа для категории:
CREATE TYPE subject_t AS OBJECT (
name VARCHAR2(2000),
broader_term_ref REF subject_t
);
Для каждой категории в таблице хранится ее имя и имя вышестоящей категории. Чтобы не размещать один объект в другом, мы будем хранить только ссылку на вышестоящую категорию. Поэтому в третьей строке определения атрибут broader_term_ref
задается как REF
-ссылка на объект того же типа. Далее создается таблица категорий.
О внешнем ключе таблицы стоит упомянуть особо. Он указывает на таблицу с реляционным первичным ключом, но имеет тип REF
, поэтому Oracle
знает, что в качестве внешнего ключа нужно использовать идентификаторы объектов. Поддержка ограничений внешнего ключа типа REF
демонстрирует связь между объектным и реляционным мирами в Oracle
. Несколько команд вставки данных в таблицу (с использованием системного конструктора):
INSERT INTO subjects VALUES (subject_t('Computer file', NULL));
INSERT INTO subjects VALUES (subject_t('Computer program language', NULL));
INSERT INTO subjects VALUES (subject_t('Relational databases', NULL));
INSERT INTO subjects VALUES (subject_t('Oracle',
(SELECT REF(s) FROM subjects s WHERE name = 'Computer file')));
INSERT INTO subjects VALUES (subject_t('PL/SQL',
(SELECT REF(s) FROM subjects s WHERE name = 'Computer program language')));
Теперь можно вывести содержимое таблицы subjects
:
SQL> SELECT VALUE(s) FROM subjects s;
VALUE(S)(NAME, BROADER_TERM_REF)
---------------------------------------------------------------------------------
SUBJECT_T('Computer file', NULL)
SUBJECT_T('Computer program language', NULL)
SUBJECT_T('Oracle', 00002202089FC431FBE6FB0599E0340003BA0F1F139FC431FBE6690599E03
40003BA0F1F13)
SUBJECT_T('PL/SQL', 00002202089FC431FBE6FC0599E0340003BA0F1F139FC431FBE6690599E03
40003BA0F1F13)
SUBJECT_T('Relational databases', NULL)
Интересно, но есть ли от этого какая-нибудь практическая польза? Оказывается, есть: Oracle
умеет автоматически «разрешать» такие ссылки. Например, с помощью функции DEREF
можно перейти к строке, на которую указывает ссылка:
SELECT s.name, DEREF(s.broader_term_ref).name bt
FROM subjects s;
Разрешение похоже на внешнее автоматическое объединение. Иначе говоря, если ссылка равна NULL
или недействительна, строка включается в результирующий набор, но значение целевого объекта (и столбца) будет равно NULL
.
В Oracle
для функции DEREF
введено удобное сокращение — точечный синтаксис определения атрибута, извлекаемого из целевого объекта:
SELECT s.name, s.broader_term_ref.name bt FROM subjects s;
Оба запроса выводят следующий результат:
NAME BT
------------------------------ ------------------------------
Computer file
Computer program language
Oracle Computer file
PL/SQL Computer program language
Relational databases
Обратите внимание на то, что в обеих формах необходим псевдоним таблицы:
SELECT псевдоним_таблицы.столбец_ref
FROM имя_таблицы псевдоним_таблицы
Ссылки могут использоваться и в секции WHERE
. Например, вывести все подкатегории из категории «Computer program language
» можно с помощью следующего запроса:
SELECT VALUE(s).name FROM subjects s
WHERE s.broader_term_ref.name = 'Computer program language';
Хотя в нашем примере в таблице используется ссылка на эту же таблицу, на практике ссылки могут указывать на любую объектную таблицу базы данных. Чтобы продемонстрировать сказанное, вернемся к определению базового типа catalog_item_t
и добавим в него атрибут, предназначенный для хранения коллекции ссылок. Каждый элемент каталога будет связан с набором категорий. Сначала создадим коллекцию ссылок на категории:
CREATE TYPE subject_refs_t AS TABLE OF REF subject_t;
Теперь каждый элемент каталога можно ассоциировать с любым количеством категорий:
ALTER TYPE catalog_item_t
ADD ATTRIBUTE subject_refs subject_refs_t
CASCADE INCLUDING TABLE DATA;
Не будем останавливаться на модификации соответствующих методов зависимых типов и перейдем к добавлению строк в каталог. Это делается с помощью следующей команды SQL
:
INSERT INTO catalog_items
VALUES (NEW book_t(10007,
'Oracle PL/SQL Programming',
'Sept 1997',
CAST(MULTISET(SELECT REF(s)
FROM subjects s
WHERE name IN ('Oracle', 'PL/SQL', 'Relational databases'))
AS subject_refs_t),
'1-56592-335-9',
987));
Конструкция CAST/MULTISET
на ходу преобразует REF
-ссылки на темы в коллекцию.
Вот как выглядит чуть более понятный эквивалентный фрагмент PL/SQL
:
DECLARE
subrefs subject_refs_t;
BEGIN
SELECT REF(s)
BULK COLLECT INTO subrefs
FROM subjects s
WHERE name IN ('Oracle', 'PL/SQL', 'Relational databases');
INSERT INTO catalog_items VALUES (NEW book_t(10007,
'Oracle PL/SQL Programming', 'Sept 1997', subrefs, '1-56592-335-9', 987));
END;
Проще говоря, мы получаем в этом коде REF
-ссылки на три категории и сохраняем их для конкретной книги.
Навигация с использованием REF
-ссылок настолько удобна, что мы рассмотрим еще один пример:
SELECT VALUE(s).name
|| ' (' || VALUE(s).broader_term_ref.name || ')' plsql_subjects
FROM TABLE(SELECT subject_refs
FROM catalog_items
WHERE id=10007) s;
Код извлекает данные из таблицы subjects
, при этом она даже не упоминается по имени. (Функция TABLE
преобразует коллекцию в виртуальную таблицу.) Результаты выполнения приведенного запроса имеют следующий вид:
PLSQL_SUBJECTS
------------------------------------
Relational databases ()
PL/SQL (Computer program language)
Oracle (Computer file)
Что дает эта технология программисту, кроме автоматического перехода по ссылкам в SQL
? К сожалению, почти ничего. Дело в том, что ссылки имеют сильную типизацию — иначе говоря, столбец типа REF
может указывать только на объект того типа, который указан в его определении. Внешние же ключи могут указывать на любые строки, для которых определен первичный ключ или уникальный индекс.
Пакет UTL_REF
Встроенный пакет UTL_REF
выполняет операцию разыменования (получения значения объекта, на который указывает ссылка) без явного вызова SQL
. Это позволяет приложению программным путем блокировать, извлекать, обновлять и удалять объекты по REF
-ссылкам. Чтобы рассмотреть эту возможность, мы добавим в объектный тип subject_t
еще один метод:
MEMBER FUNCTION print_bt (str IN VARCHAR2)
RETURN VARCHAR2
IS
bt subject_t;
BEGIN
IF SELF.broader_term_ref IS NULL
THEN
RETURN str;
ELSE
UTL_REF.SELECT_OBJECT(SELF.broader_term_ref, bt);
RETURN bt.print_bt(NVL(str,SELF.name)) || ' (' || bt.name || ')';
END IF;
END;
Эта рекурсивная процедура просматривает иерархию от текущей категории до самой верхней — родительской.
При использовании процедур из пакета UTL_REF
аргумент REF
должен иметь тот же тип, что и объектный аргумент. Ниже приведен полный список процедур этого пакета:
-
UTL_REF.SELECT_OBJECT
(объектная ссылкаIN
, объектная переменнаяOUT
); Находит объект, на который указывает аргумент объектная ссылка, и извлекает его копию в объектную переменную. -
utl_ref.select_object_with_cr
(объектная ссылка in, объектная переменная out); Является аналогомSELECT_OBJECT
, но делает копию («снимок») объекта. Данная версия позволяет избежать ошибкиORA
-4091, которая может произойти при обновлении объектной таблицы и присваивании значения функции, использующей пакетUTL_REF
для разыменования ссылки объекта из обновляемой таблицы. -
utl_ref.lock_object
(объектная ссылкаin
); Устанавливает блокировку объекта, на который указывает объектная ссылка, но не извлекает его. -
UTL_REF.LOCK_OBJECT
(объектная_ссылкаIN
, объектная_переменнаяOUT
); Устанавливает блокировку объекта, на который указывает объектная_ссылка, и извлекает его копию в объектную переменную. UTL_REF.UPDATE_OBJECT
(объектная ссылкаIN
, объектная переменнаяIN
); Заменяет объект, на который указывает объектная ссылка, значением объектной_ переменной. Операция обновляет все столбцы соответствующей объектной таблицы.-
utl_ref.delete_object
(объектная ссылкаIN
);
Удаляет объект, на который указывает объектная ссылка.
Объектные ссылки и иерархии типов
Все перечисленные подпрограммы являются процедурами, а не функциями, а их параметры имеют квази слабую типизацию. Другими словами, базе данных не нужно знать во время компиляции точный тип данных, если REF
-ссылки соответствуют объектным переменным.
Рассмотрим особенности объектных ссылок, которые проявляются при работе с иерархиями типов. Предположим, в программе объявлены следующие переменные:
DECLARE
book book_t;
item catalog_item_t;
itemref REF catalog_item_t;
bookref REF book_t;
Вы уже видели, что при присваивании извлекаемого по ссылке значения сильнотипизированной переменной все работает нормально:
SELECT REF(c) INTO itemref
FROM catalog_items c WHERE id = 10007;
Подобным образом можно извлечь объект по ссылке с использованием процедуры select_object
:
UTL_REF.select_object(itemref, item);
или:
SELECT DEREF(itemref) INTO item FROM DUAL;
Однако непосредственное понижающее преобразование объектного типа при выполнении этой операции не допускается:
SELECT REF(c)
INTO bookref /* Ошибка */
FROM catalog_items c WHERE id = 10007;
С этой целью можно воспользоваться функцией treat
, которая поддерживает работу со ссылками:
SELECT TREAT(REF(c) AS REF book_t)
INTO bookref
FROM catalog_items c WHERE id = 10007;
Привести объект к родительскому типу можно как явно:
UTL_REF.select_object(TREAT(bookref AS ref catalog_item_t), item);
так и неявно:
SELECT DEREF(bookref) INTO item FROM DUAL;
И хотя непосредственно понизить объект до дочернего типа при использовании функции DEREF
нельзя:
SELECT DEREF(itemref)
INTO book /* Ошибка */
FROM DUAL;
функция TREAT
и здесь придет на помощь:
SELECT DEREF(TREAT(itemref AS REF book_t))
INTO book
FROM catalog_items c WHERE id = 10007;
Неявное понижающее преобразование также можно выполнить с помощью процедуры из пакета UTL_REF
:
UTL_REF.select_object(itemref, book);
Висячие ссылки
Ссылка REF
может не указывать ни на один объект; тогда она называется висячей. Так может случиться, когда объект, на который указывает хранящаяся в базе данных ссылка, удален. Такая аномальная ситуация может возникнуть при отсутствии внешнего ключа, препятствующего подобному удалению.
Для поиска висячих ссылок используется оператор IS DANGLING
:
SELECT VALUE(s) FROM subjects s
WHERE broader_term_ref IS DANGLING;
Перейдем к средствам Oracle
, предназначенным для работы с данными неизвестных или изменяющихся типов.
Типы данных ANY
СУБД Oracle поддерживает тип ANYDATA
, который может содержать данные других встроенных или пользовательских типов. Пользуясь типом ANYDATA
, подпрограмма PL/SQL
может, например, сохранять в базе данных, считывать и обрабатывать элементы данных любого из типов SQL
, и вам не нужно создавать десятки ее перегруженных версий. Эта возможность была предназначена для создания очередей: приложение помещает «нечто» в очередь, при этом очереди не нужно знать, к какому типу данных относится помещенный элемент.
Для работы с произвольными типами данных Oracle
предоставляет следующие типы и пакеты:
- Тип
ANYDATA
— инкапсулирует любые элементы данных типовSQL
в самодокументированные структуры данных. - Тип
ANYTYPE
— совместно с типомANYDATA
применяется для считывания описания структуры данных. Может использоваться и сам по себе для создания временных объектных типов. - Пакет
DBMS_TYPES
— содержит только константы, которые упрощают интерпретацию типов данных, используемых в объектеANYDATA
. - Тип
ANYDATASET
— аналогANYDATA
, но его содержимым является один или несколько экземпляров типа данных.
Чего не умеет ANYDATA
Допустим, вы решили написать функцию, которая преобразует любые данные в строковое представление. Вы начинаете со следующей спецификации:
FUNCTION printany (whatever IN ANYDATA) RETURN VARCHAR2;
DBMS_OUTPUT.PUT_LINE(printany(SYSDATE)); -- нет
DBMS_OUTPUT.PUT_LINE(printany(NEW book_t(111)); -- снова нет
DBMS_OUTPUT.PUT_LINE(printany('Hello world')); -- и еще раз нет
К сожалению, эти вызовы не работают. ANYDATA представляет собой инкапсуляцию других типов, поэтому данные в типе ANYDATA необходимо сначала преобразовать к определенному типу одним из встроенных статических методов:
DBMS_OUTPUT.PUT_LINE(printany(ANYDATA.ConvertDate(SYSDATE));
DBMS_OUTPUT.PUT_LINE(printany(ANYDATA.ConvertObject(NEW book_t(12345)));
DBMS_OUTPUT.PUT_LINE(printany(ANYDATA.ConvertVarchar2('Hello world')));
Короче говоря, ANYDATA не заменяет перегрузки данных.
Обработка данных ANYDATA
Следующая программа возвращает строковую версию содержимого любой переменной. Представленная версия работает только с числами, строками, датами, объектами и ссылками, но ее можно расширить для поддержки практически любых типов данных.
1 FUNCTION printany (adata IN ANYDATA)
2 RETURN VARCHAR2
3 AS
4 aType ANYTYPE;
5 retval VARCHAR2(32767);
6 result_code PLS_INTEGER;
7 BEGIN
8 CASE adata.GetType(aType)
9 WHEN DBMS_TYPES.TYPECODE_NUMBER THEN
10 RETURN 'NUMBER: ' || TO_CHAR(adata.AccessNumber);
11 WHEN DBMS_TYPES.TYPECODE_VARCHAR2 THEN
12 RETURN 'VARCHAR2: ' || adata.AccessVarchar2;
13 WHEN DBMS_TYPES.TYPECODE_CHAR THEN
14 RETURN 'CHAR: ' || RTRIM(adata.AccessChar);
15 WHEN DBMS_TYPES.TYPECODE_DATE THEN
16 RETURN 'DATE: ' || TO_CHAR(adata.AccessDate,
17 'YYYY-MM-DD hh24:mi:ss');
18 WHEN DBMS_TYPES.TYPECODE_OBJECT THEN
19 EXECUTE IMMEDIATE 'DECLARE ' ||
20 ' myobj ' || adata.GetTypeName || '; ' ||
21 ' myad anydata := :ad; ' ||
22 'BEGIN ' ||
23 ' :res := myad.GetObject(myobj); ' ||
24 ' :ret := myobj.print(); ' ||
25 'END;'
26 USING IN adata, OUT result_code, OUT retval;
27 retval := adata.GetTypeName || ': ' || retval;
28 WHEN DBMS_TYPES.TYPECODE_REF THEN
29 EXECUTE IMMEDIATE 'DECLARE ' ||
30 ' myref ' || adata.GetTypeName || '; ' ||
31 ' myobj ' || SUBSTR(adata.GetTypeName,
32 INSTR(adata.GetTypeName, ' ')) || '; ' ||
33 ' myad anydata := :ad; ' ||
34 'BEGIN ' ||
35 ' :res := myad.GetREF(myref); ' ||
36 ' UTL_REF.SELECT_OBJECT(myref, myobj);' ||
37 ' :ret := myobj.print(); ' ||
38 'END;'
39 USING IN adata, OUT result_code, OUT retval;
40 retval := adata.GetTypeName || ': ' || retval;
41 ELSE
42 retval := '<data of type ' || adata.GetTypeName ||'>';
43 END CASE;
44
45 RETURN retval;
46
47 EXCEPTION
48 WHEN OTHERS
49 THEN
50 IF INSTR(SQLERRM, 'component ''PRINT'' must be declared') > 0
51 THEN
52 RETURN adata.GetTypeName || ': <no print() function>';
53 ELSE
54 RETURN 'Error: ' || SQLERRM;
55 END IF;
56 END;
Рассмотрим код более подробно.
Строки | Описание |
5 | Если для хранения результата понадобится временная переменная, мы резервируем для нее 32 Кбайт. Поскольку для больших переменных типа |
6 | Значение переменной |
8 | Тип
Но для использования этого метода необходимо объявить переменную типа |
9, 11, 13, 15, 18, 28 | В этих выражениях используются константы из встроенного пакета DBMS_TYPES |
10, 12, 14, 16 | В этих операторах используются функции |
19-26 | Чтобы вывести информацию об объекте без многочисленных обращений к словарю данных, динамический анонимный блок создает объект нужного типа и вызывает его метод |
29-39 | Задача этого фрагмента — найти объект по указателю и вернуть его содержимое. Он будет работать только при наличии у объекта метода |
45-52 | При попытке вывести информацию об объекте, не имеющем метода |
Выполнение приведенных ранее вызовов:
DBMS_OUTPUT.PUT_LINE(printany(ANYDATA.ConvertDate(SYSDATE));
DBMS_OUTPUT.PUT_LINE(printany(ANYDATA.ConvertObject(NEW book_t(12345)));
DBMS_OUTPUT.PUT_LINE(printany(ANYDATA.ConvertVarchar2('Hello world')));
дает следующий результат:
DATE: 2005-03-10 16:00:25
SCOTT.BOOK_T: id=12345; title=; publication_date=; isbn=; pages=
VARCHAR2: Hello world
Как видите, тип данных ANYDATA не так удобен, как иерархии наследования, поскольку он требует явного преобразования данных. Однако он позволяет создавать столбцы таблиц и атрибуты объектов, в которых можно хранить данные практически любого типа.
На момент написания статьи в таблице не могли храниться значения ANYDATA, инкапсулирующие расширяемый объект или объект, входящий в состав иерархии типов.
Создание временного типа данных
PL/SQL не поддерживает определения новых объектных типов в разделе объявлений программы, но с помощью встроенных типов ANY
можно создавать «временные» типы, существующие только во время выполнения программы. Значения типа, созданного с помощью ANYTYPE
, можно передавать в качестве параметра. Кроме того, можно создавать его экземпляры как значения типа ANYDATA
. Рассмотрим следующий код:
/* Создание (анонимного) временного типа с двумя атрибутами: number, date */ FUNCTION
FUNCTION create_a_type
RETURN ANYTYPE
AS
mytype ANYTYPE;
BEGIN
ANYTYPE.BeginCreate(typecode => DBMS_TYPES.TYPECODE_OBJECT,
atype => mytype);
mytype.AddAttr(typecode => DBMS_TYPES.TYPECODE_NUMBER,
aname => 'just_a_number',
prec => 38,
scale => 0,
len => NULL,
csid => NULL,
csfrm => NULL);
mytype.AddAttr(typecode => DBMS_TYPES.TYPECODE_DATE,
aname => 'just_a_date',
prec => 5,
scale => 5,
len => NULL,
csid => NULL,
csfrm => NULL);
mytype.EndCreate;
RETURN mytype;
END;
Процесс состоит из трех основных шагов:
- Создание типа начинается с вызова статической процедуры BeginCreate. Она возвращает инициализированный объект
ANYTYPE
. - С помощью процедуры
AddAttr
последовательно добавляются атрибуты. - Вызывается процедура
EndCreate
.
Аналогичным образом перед использованием типа присваиваются значения его атрибутам:
DECLARE
ltype ANYTYPE := create_a_type;
l_any ANYDATA;
BEGIN
ANYDATA.BeginCreate(dtype => ltype, adata => l_any);
l_any.SetNumber(num => 12345);
l_any.SetDate(dat => SYSDATE);
l_any.EndCreate;
END;
Если структура данных заранее не известна, информацию о ней можно получить с помощью методов ANYTYPE
(в частности, GetAttrElemInfo
) в сочетании с методами ANYDATA.Get
.
Сделай сам
Некоторые приверженцы объектно-ориентированного программирования считают, что каждый объектный тип должен быть самодостаточным. Если объект предназначен для хранения в базе данных, он должен «уметь» сохранять себя и содержать методы обновления, удаления и выборки. При таком подходе наш тип следовало бы дополнить такими методами:
ALTER TYPE catalog_item_t
ADD MEMBER PROCEDURE remove
CASCADE;
TYPE BODY catalog_item_t
AS
...
MEMBER PROCEDURE remove
IS
BEGIN
DELETE catalog_items
WHERE id = SELF.id;
SELF := NULL;
END;
END;
(Кстати говоря, методы-деструкторы Oracle не поддерживает.) Определяя метод на уровне супертипа, мы автоматически включаем его и во все подтипы. В данном случае предполагается, что все соответствующие объекты находятся в одной таблице, однако в некоторых приложениях может потребоваться дополнительная логика поиска объектов. (Кроме того, в реальном приложении версия этого метода может включать код для выполнения дополнительных операций, таких как удаление зависимых объектов и/или архивирование данных перед окончательным удалением объекта.)
Если предположить, что перед записью временного объекта на диск приложение должно его модифицировать в памяти, вставку и обновление можно объединить в одном методе, который мы назовем save
:
ALTER TYPE catalog_item_t
ADD MEMBER PROCEDURE save,
CASCADE;
TYPE BODY catalog_item_t
AS
...
MEMBER PROCEDURE save
IS
BEGIN
UPDATE catalog_items c
SET c = SELF
WHERE id = SELF.id;
IF SQL%ROWCOUNT = 0
THEN
INSERT INTO catalog_items VALUES (SELF);
END IF;
END;
Процедура заменяет значения всех столбцов, даже если они не изменились. В результате этой операции могут активизироваться «лишние» триггеры, а также выполняться ненужные операции ввода/вывода. К сожалению, это одно из неизбежных следствий объектной методологии. При более тщательном программировании можно было бы избежать модификации неизменившихся столбцов супертипа, но столбцы подтипа ни в какой поддерживаемой Oracle разновидности команды UPDATE
в отдельности недоступны. Из всех операций над объектными таблицами сложнее всего инкапсулировать выборку, что связано с разнообразием возможных условий в предложении WHERE
и большим количеством форм данных результата. Как следствие, спецификация критерия запроса может значительно усложниться. Что касается результатов запроса, то и они могут быть представлены в одном из следующих видов:
- коллекция объектов;
- коллекция REF-ссылок;
- результирующий набор с конвейерной организацией;
- курсорная переменная (с сильной или слабой типизацией).
На выбор представления могут повлиять требования к приложению и необходимая для него программная среда. В усеченном примере ниже используется курсорная переменная:
ALTER TYPE catalog_item_t
ADD STATIC FUNCTION cursor_for_query (typename IN VARCHAR2 DEFAULT NULL,
title IN VARCHAR2 DEFAULT NULL,
att1 IN VARCHAR2 DEFAULT NULL,
val1 IN VARCHAR2 DEFAULT NULL)
RETURN SYS_REFCURSOR
CASCADE;
Мы используем статический метод, который возвращает встроенный тип SYS_REFCURSOR
(слаботипизированный курсор, который Oracle предоставляет для удобства). Это позволяет клиентской программе организовать перебор результатов. Параметры att1
и val1
предоставляют средства получения информации о парах «атрибут/значение», специфических для подтипа; в реальной версии этой программы стоило бы передавать коллекцию таких пар.
А теперь рассмотрим пример выполнения запроса:
DECLARE
catalog_item catalog_item_t;
l_refcur SYS_REFCURSOR;
BEGIN
l_refcur := catalog_item_t.cursor_for_query(
typename => 'book_t',
title => 'Oracle PL/SQL Programming');
LOOP
FETCH l_refcur INTO catalog_item;
EXIT WHEN l_refcur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Matching item:' || catalog_item.print);
END LOOP;
CLOSE l_refcur;
END;
Результат выглядит так:
Matching item:id=10007; title=Oracle PL/SQL Programming;
publication_date=Sept 1997;
isbn=1-56592-335-9; pages=987
Реализация:
1 MEMBER PROCEDURE save
2 IS
3 BEGIN
4 UPDATE catalog_items c
5 SET c = SELF
6 WHERE id = SELF.id;
7 IF SQL%ROWCOUNT = 0
8 THEN
9 INSERT INTO catalog_items VALUES (SELF);
10 END IF;
11 END;
12
13 STATIC FUNCTION cursor_for_query (typename IN VARCHAR2 DEFAULT NULL,
14 title IN VARCHAR2 DEFAULT NULL,
15 att1 IN VARCHAR2 DEFAULT NULL,
16 val1 IN VARCHAR2 DEFAULT NULL)
17 RETURN SYS_REFCURSOR
18 IS
19 l_sqlstr VARCHAR2(1024);
20 l_refcur SYS_REFCURSOR;
21 BEGIN
22 l_sqlstr := 'SELECT VALUE(c) FROM catalog_items c WHERE 1=1 ';
23 IF title IS NOT NULL
24 THEN
25 l_sqlstr := l_sqlstr || 'AND title = :t ';
26 END IF;
27
28 IF typename IS NOT NULL
29 THEN
30 IF att1 IS NOT NULL
31 THEN
32 l_sqlstr := l_sqlstr
33 || 'AND TREAT(SELF AS '
34 || typename || ').' || att1 || ' ';
35 IF val1 IS NULL
36 THEN
37 l_sqlstr := l_sqlstr || 'IS NULL ';
38 ELSE
39 l_sqlstr := l_sqlstr || '=:v1 ';
40 END IF;
41 END IF;
42 l_sqlstr := l_sqlstr || 'AND VALUE(c) IS OF
43 (' || typename ||') ';
44 END IF;
45
46 l_sqlstr := 'BEGIN OPEN :lcur FOR ' || l_sqlstr || '; END;';
47
48 IF title IS NULL AND att1 IS NULL
49 THEN
50 EXECUTE IMMEDIATE l_sqlstr USING IN OUT l_refcur;
51 ELSIF title IS NOT NULL AND att1 IS NULL
52 THEN
53 EXECUTE IMMEDIATE l_sqlstr USING IN OUT l_refcur, IN title;
54 ELSIF title IS NOT NULL AND att1 IS NOT NULL
55 THEN
56 EXECUTE IMMEDIATE l_sqlstr
57 USING IN OUT l_refcur, IN title, IN att1;
58 END IF;
59
60 RETURN l_refcur;
61 END;
Разобраться в динамическом SQL не так просто, поэтому я приведу функцию, которая будет сгенерирована для предыдущего запроса:
BEGIN
OPEN :lcur FOR
SELECT VALUE(c)
FROM catalog_items c
WHERE 1=1
AND title = :t
AND VALUE(c) IS OF (book_t);
END;
Этот подход хорош еще и тем, что вам не приходится изменять код запросам при добавлении нового подтипа в дерево наследования.
Сравнение объектов
До сих пор в наших примерах использовались объектные таблицы, то есть таблицы, в которых каждая строка представляет собой объект, построенный командой CREATE TABLE...OF
. При работе с ними доступны такие специальные возможности, как переходы по ссылкам и интерпретация целого объекта (а не отдельных его столбцов) как единицы ввода/вывода.
Объектный тип также может использоваться в качестве типа отдельного столбца таблицы (в терминологии Oracle используется название «объектные столбцы»). Допустим, мы хотим завести журнал изменений для таблицы catalog_items
и фиксировать в нем все операции вставки, обновления и удаления строк этой таблицы.
CREATE TABLE catalog_history (
id INTEGER NOT NULL PRIMARY KEY,
action CHAR(1) NOT NULL,
action_time TIMESTAMP DEFAULT (SYSTIMESTAMP) NOT NULL,
old_item catalog_item_t,
new_item catalog_item_t)
NESTED TABLE old_item.subject_refs STORE AS catalog_history_old_subrefs
NESTED TABLE new_item.subject_refs STORE AS catalog_history_new_subrefs;
Псевдостолбец OBJECT_VALUE
У любознательного читателя может возникнуть вопрос — как именно можно заполнить такую таблицу, как catalog_history, включающую объектные столбцы с типом, имеющим подтипы? Хочется надеяться, что это можно сделать в триггере уровня таблицы. Остается понять, как сохранить значения атрибутов всех подтипов. На помощь приходит псевдостолбец. Возьмем следующий фрагмент:
TRIGGER catalog_hist_upd_trg
AFTER UPDATE ON catalog_items
FOR EACH ROW
BEGIN
INSERT INTO catalog_history (id,
action,
action_time,
old_item,
new_item)
VALUES (catalog_history_seq.NEXTVAL,
'U',
SYSTIMESTAMP,
:OLD.OBJECT_VALUE,
:NEW.OBJECT_VALUE);
END;
Oracle предоставляет доступ к подтипам со всеми атрибутами через псевдостолбец
OBJECT_VALUE
. Однако такой способ работает только в Oracle Database 10g и выше; правда, похожий псевдостолбецSYS_NC_ROWINFO$
доступен и в предыдущих версиях, но я обнаружил, что в этом конкретном приложении он не работает.Псевдостолбец
OBJECT_VALUE
также используется для других целей, не связанных с подтипами; например, он может пригодиться при создании объектных представлений с использованием секцииWITH OBJECT IDENTIFIER
.
Но при заполнении таблицы объектами возникает вопрос — как будет действовать Oracle, если мы потребуем отсортировать или проиндексировать таблицу по одному из столбцов типа catalog_item_t
? Существуют четыре метода сравнения объектов:
- Сравнение на уровне атрибутов. При сортировке, создании индексов и сравнении задаются соответствующие атрибуты.
- Стандартный способ SQL. Oracle умеет выполнять простейшую проверку равенства. Два объекта считаются равными, если они определены на основе одного и того же типа, а все их соответствующие атрибуты равны. Это сравнение может быть выполнено, когда объекты имеют только скалярные атрибуты (не коллекции и не LOB) и для них не определен метод
MAP
илиORDER
. - Метод MAP. Можно определить специальную функцию-метод, возвращающую не значение объекта, а соответствующее ему значение одного из типов данных, которые Oracle умеет сравнивать. Метод работает только при отсутствии метода
ORDER
. - Метод
ORDER
. Другая специальная функция, которая сравнивает два объекта и возвращает флаг, обозначающий их относительный порядок. Работает только при отсутствии метода MAP.
От стандартного метода сравнения SQL пользы немного, поэтому мы не будем его рассматривать. О других, более полезных способах сравнения объектов рассказывается в следующем разделе.
Сравнение на уровне атрибутов
Сравнение на уровне атрибутов может быть не совсем тем, что требуется, но зато оно легко реализуется в PL/SQL и даже в SQL, если только вы не забудете задать псевдоним таблицы в команде SQL. Oracle позволяет ссылаться на атрибуты с помощью точечного синтаксиса:
SELECT * FROM catalog_history c
WHERE c.old_item.id > 10000
ORDER BY NVL(TREAT(c.old_item as book_t).isbn, TREAT
(c.old_item AS serial_t).issn)
Создание индекса на основе атрибутов выполняется так же просто:
CREATE INDEX catalog_history_old_id_idx ON catalog_history c (c.old_item.id);
Метод MAP
Методы MAP
и ORDER
позволяют выполнять команды следующего вида:
SELECT * FROM catalog_history
ORDER BY old_item;
IF old_item > new_item
THEN ...
Сначала рассмотрим метод MAP
. Его простейший вариант добавляется в catalog_item_t
следующим образом:
ALTER TYPE catalog_item_t
ADD MAP MEMBER FUNCTION mapit RETURN NUMBER
CASCADE;
TYPE BODY catalog_item_t
AS ...
MAP MEMBER FUNCTION mapit RETURN NUMBER
IS
BEGIN
RETURN id;
END;
...
END;
Если предположить, что сортировка по идентификаторам имеет смысл, то теперь мы можем сортировать и сравнивать элементы каталога, причем Oracle будет автоматически вызывать этот метод при необходимости. Функция не обязательно должна быть такой простой; например, она может представлять собой скалярное значение, вычисляемое на основе всех атрибутов типа, объединенных способом, который имеет для библиотекарей определенный смысл.
У метода MAP
есть один побочный эффект: проверка равенства может определяться неподходящим способом. При наличии такого метода два объекта считаются равными при равенстве возвращаемых значений. Когда вам нужно сравнивать объекты поочередным анализом их атрибутов, создайте собственный метод (не MAP
) или же используйте метод ORDER
.
Метод ORDER
Альтернативой методу MAP
является функция ORDER
, которая сравнивает два объекта: SELF
и один объект того же типа, заданный в качестве ее аргумента. Эта функция должна возвращать целочисленное значение, которое в зависимости от относительного порядка двух объектов может быть положительным, нулевым или отрицательным (табл. 2).
таблица 2. Поведение функции ORDER
Порядок элементов | Значение функции ORDER |
SELF < аргумент | Любое отрицательное число (обычно -1) |
SELF = аргумент | 0 |
SELF > аргумент | Любое положительное число (обычно 1) |
Результат сравнения не определен | NULL |
Рассмотрим пример нетривиального использования метода ORDER
:
1 ALTER TYPE catalog_item_t
2 DROP MAP MEMBER FUNCTION mapit RETURN NUMBER
3 CASCADE;
4
5 ALTER TYPE catalog_item_t
6 ADD ORDER MEMBER FUNCTION orderit (obj2 IN catalog_item_t)
7 RETURN INTEGER
8 CASCADE;
9
10 TYPE BODY catalog_item_t
11 AS ...
12 ORDER MEMBER FUNCTION orderit (obj2 IN catalog_item_t)
13 RETURN INTEGER
14 IS
15 self_gt_o2 CONSTANT PLS_INTEGER := 1;
16 eq CONSTANT PLS_INTEGER := 0;
17 o2_gt_self CONSTANT PLS_INTEGER := −1;
18 l_matching_count NUMBER;
19 BEGIN
20 CASE
21 WHEN obj2 IS OF (book_t) AND SELF IS OF (serial_t) THEN
22 RETURN o2_gt_self;
23 WHEN obj2 IS OF (serial_t) AND SELF IS OF (book_t) THEN
24 RETURN self_gt_o2;
25 ELSE
26 IF obj2.title = SELF.title
27 AND obj2.publication_date = SELF.publication_date
28 THEN
29 IF obj2.subject_refs IS NOT NULL
30 AND SELF.subject_refs IS NOT NULL
31 AND obj2.subject_refs.COUNT = SELF.subject_refs.COUNT
32 THEN
33 SELECT COUNT(*) INTO l_matching_count FROM
34 (SELECT * FROM TABLE
35 (SELECT CAST(SELF.subject_refs AS subject_refs_t)
36 FROM dual)
37 INTERSECT
38 SELECT *FROM TABLE
39 (SELECT CAST(obj2.subject_refs AS subject_refs_t)
40 FROM dual));
41 IF l_matching_count = SELF.subject_refs.COUNT
42 THEN
43 RETURN eq;
44 END IF;
45 END IF;
46 END IF;
47 RETURN NULL;
48 END CASE;
49 END;
50 ...
51 END;
Важнейшие моменты в этом коде:
Строки | Описание |
21-24 | Означает, что при сортировке книги будут размещаться перед периодикой |
26-46 | Проверка равенства. Поскольку Oracle не умеет сравнивать коллекции, мы воспользуемся способностью использовать выборку из коллекции, как из таблицы. Проверив, содержит ли реляционное пересечение двух коллекций ожидаемое количество элементов, можно определить, имеет ли каждый элемент первой коллекции равный элемент во второй коллекции (предполагается именно такой критерий равенства) |
Этот метод приведен исключительно в учебных целях. Вряд ли он подойдет для реального приложения, поскольку не проверяет атрибуты, специфические для подтипа.
Рекомендации по проведению сравнения
В заключение приведем несколько правил и рекомендаций, касающихся методов сравнения объектов.
- Методы
MAP
иORDER
не могут сосуществовать в одном объектном типе; используйте только один из них. - При сортировке или сравнении большого количества объектов, как это бывает в командах SQL, Oracle рекомендует применять метод
MAP
. Дело в том, что внутренняя оптимизация сокращает количество вызовов, тогда как методORDER
должен обязательно вызываться для каждого сравнения. - Oracle игнорирует имена методов; называйте их как угодно.
- Подтипы могут включать метод
MAP
, но только если он есть у супертипа. - Подтипы не могут включать метод
ORDER
; логика сравнения должна определяться в супертипе.
Теперь поговорим об объектных представлениях Oracle (Object View) и о том, что какие возможности они дают программисту PL/SQL.