Август/Сентябрь 2003
Тема номера: Объектно-реляционная технология Oracle9i
Чак Кизер
Предмет желаний
(The Object of Your Desire,
by Chuck Kueser)
Источник: журнал “Oracle Professional”, март, 2003,
http://www.pinnaclepublising.com/op/OPmag.nsf/WebIndexByIssueSecure/268b0ff29259898585256ce100677bd7
Oracle уже много лет поддерживает объектно-ориентированную технологию, однако она кажется незаметной и недоработанной возможностью. В этой статье Чак Кизер показывает, как объектные возможности Oracle могут использоваться при разработке эксплуатируемых бизнес-приложений.
Объектно-ориентированная технология появилась в Oracle версии 8, а в следующих релизах расширялась и развивалась. Однако она казалась незаметной и недоработанной возможностью. Основной причиной этого являются несовместимость с существующими системами и методологический дискомфорт у разработчиков. Мы столкнулись с этим фактом недавно, когда нас попросили помочь спроектировать новую схему. Это было приложение для ведения Главной Книги (GL), которое казалось весьма подходящим для объектно-ориентированного (OO) проектирования. Мы провели предварительное ОО-проектирование и были поражены негативными результатами. Разработчикам было очень неудобно работать с ОО и его SQL. В этой статье я помогу перевести упрощенное GL-приложение из традиционной реляционной методологии в объектно-ориентированную.
Поговорим об отношениях
Наше приложение для ведения главной книги будет состоять из четырех таблиц:
- Таблица acct_lookup, которая содержит все системные ID и соответствующий текст с описанием.
- Таблица gl_master с системными ID активных субсчетов и различными датами.
- Таблица acct_pay, которая связана с таблицей gl_master по системному ID и содержит ID активных субсчетов.
- Таблица acct_money, которая связана с таблицей acct_pay по ID субсчета и содержит суммы, связанные с этим ID и признак “по дебету/по кредиту”.
Следовало бы создать еще одну таблицу со списком ID субсчетов, однако нам она не потребуется. На Рис. 1 изображена традиционная реляционная модель нашей схемы.
Из схемы видно, что это более-менее третья нормальная форма с отношением один-ко-многим между таблицами acct_pay и acct_money, и между таблицами acct_lookup и gl_master. Есть также отношение один-к-одному между таблицами acct_pay и gl_master. Эти таблицы создаются и управляются с помощью обычных DDL (data definition language – языка определения данных) и DML (data manipulation language – языка манипулирования данными) команд. В схеме может потребоваться также логика, чтобы гарантировать, что в таблицу acct_lookup записан корректный sysid до того, как он распространится на таблицы gl_master и acct_pay. Похожая логика может потребоваться для столбца acct в таблицах acct_pay и acct_money. Так как все суммы денег хранятся в виде абсолютных значений, то поле db_cr обозначает знак. Чтобы определить знак некоторого значения суммы, необходимо использовать следующий запрос:
select decode(db_cr,'c',(-1)*money,'d',money)
from acct_money
where acct='account id' and money='money value';
Сумму денег на заданном субсчете и отчет по ID субсчетов с суммами для каждого sysid можно получить с помощью следующих запросов:
select sum(decode(db_cr,'c',(-1)*money,'d',money))
from acct_money
where acct='account_id';
select a.sysid,entry_date,settle_date,call_date,b.acct,
decode(db_cr,'c',(-1)*money,'d',money)
from gl_master a,acct_pay b,acct_money c
where a.sysid=b.sysid and
b.acct=c.acct;
Перейдем к объектной версии
Объектно-ориентированная версия нашей ERD показана на Рис. 2.
Как видно из рисунка, остались те же четыре таблицы, но с некоторыми изменениями в структуре. Таблица acct_money вложена в таблицу acct_pay, а отношение между таблицами acct_lookup и gl_master реализовано с использованием ref-указателя при описании столбца sysid таблицы gl_master. Отношение один-к-одному между gl_master и acct_pay необходимо поддерживать, так как требуется доступ к таблице acct_pay без таблицы gl_master, а такой дизайн упрощает SQL. Другое отличие – это методы db_cr_cnvrt и total_money. Более подробно рассмотрим их позднее, а сейчас нашей целью является освобождение от функции decode, требовавшейся ранее, и упрощение отчета. Листинг 1 содержит DDL для таблицы acct_lookup и таблицы gl_master.
Листинг 1. DDL для таблиц acct_lookup и gl_master.
create or replace type acct_type as object (
sysid varchar2(4),
acct_text varchar2(20))
/
create table acct_lookup of acct_type
/
create table gl_master (
sysid ref acct_type scope is acct_lookup,
entry_date date,
settle_date date,
call_date date)
/
Как можно заметить, первый шаг – это создание объектного типа с атрибутами, присущими соответствующей таблице. Это тип данных, определенный пользователем, который представляет собой шаблон. Он может использоваться как основа для многих или нескольких таблиц, в которых требуются эти же атрибуты. Первое предложение create table – это просто создание таблицы соответствующего объектного типа. Обратите внимание на оператор ref в таблице gl_master. Этот оператор устанавливает неявное отношение между таблицей gl_master и объектом acct_type. Оператор scope ограничивает эту ссылку только таблицей acct_lookup. Его по возможности следует указывать всегда. Использование этого оператора уменьшает размер требуемой памяти и позволяет оптимизатору более эффективно управлять запросами, связанные по ref. Ссылки по своей сути являются отношениями, как и внешние ключи, за исключением того, что их существование не требуется. Про ref-ссылку без соответствующей строки говорят, что она “висящая”. Предикат "is dangling" ("висящая") предусмотрен для проверки этого условия и используется, в основном, так же как предикат "is null".
Чтобы избежать висящих ссылок, можно создать внешний ключ на ref-столбцы. Это наилучший из вариантов (или неоправданная избыточность, в зависимости от точки зрения). Ref-ссылки удобны и эффективны, когда применяются вместе с внешними ключами. Кроме того, когда это требуется, можно использовать индексированные ref-ссылки.
Таблицы acct_pay и acct_money
Следующие объекты, которые мы рассмотрим, это таблицы acct_pay и acct_money. Как видно из рис.2, таблица acct_money вложена в таблицу acct_pay. Такая структура применена для поддержки отношения один-ко-многим, основанного на поле acct. Кроме того, таблицы отличаются наличием методов. Метод – это часть кода, который реализует логику, связанную с объектом. При рассмотрении метода db_cr_cnvrt вспомните, что наши данные хранятся без знака. Признак db_cr показывает знак. В традиционном подходе это приводит к необходимости формирования предложения select с функцией decode. Оно выполняется, однако неудобно при кодировании и поддержке. Наш метод, который будет кратко продемонстрирован, позволяет сформировать простой select. Листинг 2 содержит DDL для создания этих двух таблиц.
Листинг 2. DDL для создания двух объектных таблиц.
create or replace type money_type as object (
acct varchar2(4),
db_cr char(1) ,
money number(8,2),
member function db_cr_cnvrt return number)
/
create or replace type money_tab as table of money_type
/
create or replace type acct_pay_type as object (
sysid varchar2(4),
acct_money money_tab,
member function total_money return number)
/
create table acct_pay of acct_pay_type (
primary key (sysid))
object id primary key
nested table acct_money store as acct_money_tab
return as locator
/
Первым предложением Листинга 2 просто создается объектный тип, как было рассмотрено выше, содержащий соответствующий метод. Вторым создается тип, который представляет собой таблицу предыдущего типа. Затем обратите внимание на поле acct_money. Оно имеет тип данных money_tab, который является только что описанным типом данных. Четвертое предложение – весьма интересно. С его помощью создается таблица acct_pay_type и первичный ключ, object_id описывается как этот ключ, вложенная таблица именуется как acct_money_tab и сообщается ядру о необходимости возвратить locator. ID создаются и индексируются автоматически для всех объектов. Представьте, что это псевдо-столбцы, которые работают также как первичный ключ, занимающий 16 байтов. Идентифицируя первичный ключ как OID, можно сохранить дополнительную память и уменьшить издержки производительности.
Оператор locator позволяет Oracle передать клиенту указатель на строку во вложенной таблице. Одно из достоинств локаторов – они помогают гарантировать связность. Когда набор строк возвращается через локатор, моментальный снимок таблицы отражает состояние таблицы до первого использования локатора. Другое преимущество – простота кодирования в связанных методах, они делают методы красивыми.
Методы
Методы похожи на пакеты базы данных и состоят из двух частей: спецификации и тела. Спецификации созданы в предыдущем DDL и описывают тип метода и возвращаемые значения. Сам PL/SQL-код описывается в теле, как показано на Листинге 3.
Листинг 3. Тело методов.
create or replace type body money_type as
member function db_cr_cnvrt return number is
mny number(10,2);
begin
if (self.db_cr = 'C') then
mny:=self.money;
else
mny:=self.money*(-1);
end if;
return mny;
end;
end;
create or replace type body acct_pay_type as
member function total_money return number is
--
total_sum number(10,2) :=0;
x int;
--
begin
if (utl_coll.is_locator(acct_money)) then
select sum(a.db_cr_cnvrt()) into total_sum
from table(cast(acct_money as money_tab)) a;
else
for x in 1..acct_money.count loop
total_sum:=total_sum+self.acct_money(x).db_cr_cnvrt();
end loop;
end if;
return total_sum;
end;
end;
Первый метод добавляет знак к соответствующей денежной сумме. Местоимение "self" такого же типа, что и местоимения old и new, используемые в триггерах базы данных. Так как метод ассоциируется с типом, а не с таблицей, self позволяет гибко использовать одно и то же тело для нескольких таблиц без изменений.
Суть метода total_sum (ошибка в статье – не total_sum, а total_money - прим. переводчика) – возвратить итоговую сумму для system ID, он иллюстрирует использование локаторов. Локатор возвращает указатель на объект, а не на значение(я). Это позволяет при обработке данных использовать теорию множеств, а не последовательные методы. Посмотрите на код после предложения else. Это цикл, в котором по очереди просматриваются все записи с заданным system ID, конвертируется знак и суммируются значения. В коде до else тот же результат достигается с использованием локатора. Как видно из этого примера, локатор позволяет использовать функцию sum для суммирования всего набора, используемого в циклическом механизме.
Предложение cast – выражение преобразования, которое возвращает acct_money как тип money_tab. Предложение table – операнд, который делает запросы не вложенными, поэтому они могут обрабатываться реляционным способом, с использованием реляционных операторов – в данном случае функции Sum. Заметьте, что вызывается метод db_cr_cnvrt, который прикреплен к вложенной таблице. Это пример инкапсуляции логики внутри объекта. Логика вычисления итоговых сумм инкапсулирована в объект acct_pay_type, а логика db_cr_cnvrt инкапсулирована в объект money_type.
Манипулирование
На данный момент у нас есть объектные таблицы с инкапсулированной логикой. А что теперь? Далее используется SQL для манипулирования данными. Сначала загрузим данные, а затем выполним несколько select-, update- и последующих delete- предложений. Загрузка записей в таблицу acct_lookup состоит из стандартных операций insert, которые я не буду рассматривать подробно. Однако загрузка таблицы gl_master уже более интересна. Чтобы загрузить одну запись в таблицу gl_master, которая ссылается на запись в таблице acct_lookup, которая имеет sysid, равный 1234, используйте код, представленный на Листинге 4.
Листинг 4. Загрузка одной записи в таблицу gl_master.
insert into gl_master select ref(a),sysdate+540,sysdate+360
from acct_lookup a where a.sysid='1234'
insert into acct_pay values('7890',
money_tab(money_type('4321','D',1.92),money_type ('2468','C',4.75)));
insert into acct_pay values('1234',
money_tab(money_type('4321','C',10.52),money_type ('0864','D',6.25)));
insert into table(select ACCT_MONEY from acct_pay
where sysid='1234')
values('1357','D',12.92);
Первые два предложения добавят записи верхнего уровня '7890' и '1234' с вложенными записями под ними. Последнее вставляет вложенную запись в существующую запись верхнего уровня. Снова можно увидеть использование оператора table для повышения уровня вложенных конструкций. Остальные insert-предложения аналогичны.
Теперь в таблицах есть данные. Что же дальше? На них, конечно, хочется посмотреть. Результат простого select из таблицы acct_pay показан на Листинге 5.
Листинг 5. Просмотр данных таблицы acct_pay.
SYSI ACCT_MONEY(ACCT, DB_CR, MONEY)
----- --------------------------------------------------
7890 MONEY_TAB(MONEY_TYPE('4321', 'D', 1.92), MONEY_TYPE
=> ('2468', 'C', 4.75) )
1234 MONEY_TAB(MONEY_TYPE('4321', 'C', 10.52), MONEY_TYPE
=> ('0864', 'D',6.25) , MONEY_TYPE('1357', 'D', 12.92))
Чтобы извлечь суммарное количество денег или вызвать метод db_cr_cnvrt для заданного значения sysid, просто выполните следующую команду:
select a.total_money() from acct_pay a
where a.sysid='1234';
A.TOTAL_MONEY()
---------------
-8.65
select a.acct,a.db_cr_cnvrt(),a.money,a.db_cr
from table(select acct_money from acct_pay
=>where sysid='1234') a;
ACCT A.DB_CR_CNVRT() MONEY D
---- --------------- ---------- -
4321 10.52 10.52 C
0864 -6.25 6.25 D
1357 -12.92 12.92 D
Соединение таблиц gl_master и acct_lookup дает следующий результат:
select a.sysid.sysid,a.sysid.acct_text,
a.entry_date,a.settle_date
from gl_master a where a.sysid is not dangling
SYSI SYSID.ACCT_TEXT ENTRY_DAT SETTLE_DA
---- ---------------- --------- ---------
1234 Deadbeat Ltd 03-MAY-00 25-OCT-01
Модификация и удаление выполняются так же. Например, изменить значение money (деньги) можно следующим образом:
update table(select ACCT_MONEY
from acct_pay where sysid='1234')
set money=15.18 where money=12.92;
В этом предложении используется оператор table, который мы все уже хорошо знаем и любим. Кроме того, это стандартное предложение для изменения данных. Удаление выполняется, как показано ниже:
delete table(select ACCT_MONEY
from acct_pay where sysid='1234')
where money=15.18;
Перемена мышления, которая того стоит
Объектно-ориентированные возможности Oracle изменяют мышление, и SQL теперь не так прост в использовании, однако он предоставляет некоторые преимущества. Главное преимущество – это возможность включать логику внутрь самих объектов. Два моих метода демонстрируют простоту встраивания логики и вызова методов для использования полученных результатов. Это реализуется совсем не так прозрачно и просто, как в традиционной реляционной модели.
Другим преимуществом является абстракция. Это возможность моделировать сущности с пользовательским типом данных, а затем использовать этот тип для создания стольких таблиц, сколько потребуется. Наследование так же идет рука об руку с ней, так как любая таблица, созданная с абстрактным типом данных, наследуют свойства данного типа. Это, в свою очередь, позволит поддерживать более однородную структуру и сделать проект, который уменьшит головную боль и стоимость поддержки. С другой стороны, ОО потребует пересмотра моделей и процессов, которые уже известны и понятны.
ОО не был завершен, не завершен сейчас и никогда не будет завершен полностью, однако он имеет свое место и по заслугам будет принят во внимание при создании новой схемы. Если вы объективно отнесетесь к нему, то обнаружите, что объекты – это тот путь, по которому стоит идти.
|
