Объекты в Oracle 8

Мирончик Игорь

Используемые в статье скрипты вы можете получить здесь…

Новые объектно-реляционные возможности Oracle расширяют систему типов данных, предоставляемых сервером, инкапсулируя в последних бизнес-правила и логику серверного приложения. Сложная логика реляционной модели может быть представлена объектным представлением – уникальной технологией Oracle, и таким образом соединить реляционные модели с объектной технологией Oracle 8.

Постановка задачи.

Создать схему, представляющую движение материалов по складам предприятия. Для каждого склада назначать ответственное лицо из штатного списка предприятия (таблица BASE). На складе размещается материал, для которого необходимо учитывать всю хронологию поступления от внутренних и внешних заказчиков - покупателей. Внешний заказчик имеет статус 0 и должен выбираться из справочника агентов, при наличии статуса 0 у поставщика - покупателя материала, он должен выбираться из справочника складов - внутренняя преброска. Постановка задачи далека от реальной жизни, она придумана в учебных целях.

Итак приступим:

Создание объектных типов.

Пример создания атрибутов и методов класса с помощью Oracle Schema Manager. Соответственно генерируется SQL текст следующего вида:

CREATE TYPE "IGOR"."OTVETSTV"

AS OBJECT (
datarojden DATE,
base NUMBER(10)
);

Добавляем в класс новые методы c помощью Oracle Schema Manager:

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

CREATE OR REPLACE TYPE "IGOR"."OTVETSTV"

AS
OBJECT (
DATAROJDEN DATE,
BASE NUMBER(11, 0),
MEMBER FUNCTION VOZRAST RETURN NUMBER,
MEMBER FUNCTION atribut(k IN NUMBER) RETURN VARCHAR2,
PRAGMA RESTRICT_REFERENCES (VOZRAST,WNDS),
PRAGMA RESTRICT_REFERENCES (atribut,WNDS)
);

Реализация методов класса осуществляется в теле класса, таким образом можно скрыть от пользователя класса отдельные методы и свойства. Ниже приводится скрипт для реализации методов класса:

CREATE OR REPLACE TYPE BODY "IGOR"."OTVETSTV" is

MEMBER FUNCTION vozrast RETURN NUMBER
as
begin
return trunc((sysdate-datarojden)/365);
end;
MEMBER FUNCTION atribut(k IN NUMBER) RETURN VARCHAR2
as
s VARCHAR2(200);
begin
SELECT famil||' '||adress ||' т.' ||nomer INTO s FROM igor.BASE
WHERE igor.BASE.kod=k;
return s;
exception when others then
return 'Нет заполнения';
end;
end;
/

При описании метода класса можно воспользоваться одним из модификаторов – MAP или ORDER. При использовании модификатора ORDER метод должен возвращать число и использоваться для сортировки объектов в предложении ORDER BY. Модификатор MAP используется для сравнения собственно объекта с собой подобным, который передается ему в качестве параметра. Метод должен возвращать –1, 0, 1, соответственно, если передаваемый объект меньше, равен или больше собственного объекта.

Объекты в колонках таблицы

Следующий этап – создание таблицы, одним из полей которой будет поле с объектным типом OTVETSTV. Поле otvetstven новой таблицы наследует свойства класса OTVETSTV.

CREATE TABLE “IGOR”.sklad

(
kod NUMBER(10) PRIMARY KEY,
name VARCHAR2(50) UNIQUE,
otvetstven IGOR.OTVETSTV
) ;

Доступ к данным и записям осуществляется через алиасы. Ниже приводятся команды DML для доступа к атрибутам и методам поля таблицы, представленным как объект класса, от которого он унаследован.

INSERT INTO sklad (name,otvetstven)

VALUES
(
'Сыпучие материалы',
IGOR.OTVETSTV(to_date('30.10.59','dd.mm.yy'),30213)
);

SELECT s.otvetstven.atribut(s.otvetstven.base) AS a,

s.otvetstven.vozrast()||' лет', 'Склад '||s.name AS sk from sklad s ORDER BY sk;

UPDATE sklad s SET s.otvetstven.base=10000

WHERE s.name='Обмуровочные материалы';

Объектные таблицы

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

--Создается новый класс на основе которого будет создана таблица

--Тип BANKTYPE будет использован для объектной таблицы

CREATE OR REPLACE TYPE

"IGOR"."BANKTYPE"
AS OBJECT
(
BIK VARCHAR2(11),
SCHET VARCHAR2(20),
NAME VARCHAR2(50),
ADRESS VARCHAR2(70),
PRIMECH VARCHAR2(150)
);
/

--Создаем таблицу с одним объектным типом

CREATE TABLE bankObject OF BANKTYPE;

Триггеры реляционных таблиц распространяются аналогичным образом и на объектные таблицы.

CREATE TRIGGER bankObject_insert_trigger

BEFORE INSERT
ON bankObject
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
Begin
select new_kod.nextval into :new.kod from dual;
End;
/

Накладываем ограничение на поле класса, на который ссылается объект

ALTER TABLE bankObject ADD

(CONSTRAINT bankObject_PK PRIMARY KEY (kod));

--Заполняем таблицу данными с помощью конструктора класса BANKTYPE, за источник данных взята реляционная таблица.

BEGIN

FOR b IN
(SELECT bik,schet,name,adres,primech FROM igor.bank)
LOOP
INSERT INTO bankObject
VALUES
(BANKTYPE(null,b.bik,b.schet,b.name,b.adres,
b.primech));
END LOOP;
END;
/

В конструкторе класса неиспользуемый аттрибут (KOD Primary Key) инициализируется значением NULL. При выполнении оператора INSERT производится преобразование данных к типу соответствующего класса. Следующие операторы манипулируют данными объектного типа. Оператор VALUE Извлекает запись (Объект) из таблицы, используя при этом алиас.

SELECT VALUE(b) FROM bankObject b;

Следующие операторы модифицируют атрибуты объекта в поле таблицы:

SELECT b.Primech FROM bankObject b

WHERE
b.primech LIKE ‘ЭЛЕКТРОСТ%’;

UPDATE bankObject b

SET b.adress='Затишье' WHERE b.adress LIKE 'ЭЛЕКТРОСТ%';

SELECT b.adress, b.primech, b.adress FROM bankObject b

WHERE b.adress LIKE 'Затишье%';

ROLLBACK;

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

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

select REF(b) FROM bankObject b WHERE

b.primech LIKE

'ВЕСТ Адрес: ЭЛЕКТРОСТАЛЬ, УЛ.СОВЕТСКАЯ,Д.5 т.(8257)43260,44284%';

В результате получаем ссылку следующего вида:

REF(B)

---------------------------------------------------------------------------------------

000028020958E89574822D11D29781008048E2703DE33AAB34821511D29781008048E2703D00C00AE70002

6. Создание таблицы, с колонкой типа ссылка на объект, хранящийся в объектной таблице

--Таблица агентов содержит ссылку на банк (Type BANKTYPE)

CREATE TABLE "IGOR".AGENT

(
kod NUMBER(10) PRIMARY KEY,
inn VARCHAR2(12) NULL,
name VARCHAR2(100) NOT NULL,
okpo VARCHAR2(50) NULL,
okonh VARCHAR2(50) NULL,
bank REF IGOR.BANKTYPE
) ;

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

CREATE TABLE "IGOR".AGENT

(
kod NUMBER(10) PRIMARY KEY,
inn VARCHAR2(12) NULL,
name VARCHAR2(100) NOT NULL,
okpo VARCHAR2(50) NULL,
okonh VARCHAR2(50) NULL,
bank REF IGOR.BANKTYPE SCOPE IS bankObject
) ;

Здесь кроме типа ссылки вводится ограничение и на таблицу, на которую будет указывать ссылка. Ссылочный тип отличается от реляционного FOREIGN KEY ограничения тем, что он обеспечивает выборку данных по значению, на которое указывает ссылка. Кроме того, в отличии от внешнего ключа, поле – ссылка может указывать на несуществующий объект, другими словами возникает понятие зависшей (не существующей ссылки). Этот факт можно выявить с помощью оператора DANGLING:

DELETE FROM bankObject b WHERE b.name='Тест';

SELECT a.name FROM agent a WHERE bank IS NOT DANGLING;

Результат

NAME

-------------------------------------------------

Электростальский Котельностроительный комбинат

Из двух записей выбрана одна , так как вторая имеет зависшую ссылку в поле BANK.

Следующий пример демонстрирует получение доступа к свойству объекта, через ссылку, размещенную в поле таблицы:

select a.name , a.bank.name FROM agent a

7. Вложенные таблицы

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

--Создаем тип, определяющий структуру вложенной таблицы

CREATE OR REPLACE TYPE "IGOR"."DVIGENIETYPE"

AS
OBJECT
(
prihod NUMBER(15, 3),
rashod NUMBER(15, 3),
data DATE,
klient IGOR.KLIENTTYPE
);
/

--Определяем табличный тип как ссылку на выше созданную структуру

CREATE OR REPLACE TYPE "IGOR"."DVIGENIE_TABLE"

AS
TABLE OF REF IGOR.DVIGENIETYPE;
/

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

CREATE TABLE "IGOR".material

(kod NUMBER(10) ,
name VARCHAR2(100) ,
sklad NUMBER(10) ,
atribut IGOR.MATERIALATRIBUTTYPE ,
dvigenie IGOR.DVIGENIE_TABLE NULL
)
NESTED TABLE "DVIGENIE" STORE AS DVIGENIE_NESTED_TABLE;

Следует обратить внимание, что таблица – хранилище недоступна в обычных операторах DML. Oracle расширил SQL и предоставил пользователю несколько способов доступа к Nested Table.

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

declare

s number(10);
begin
SELECT kod into s FROM sklad WHERE name='Сыпучие материалы';
INSERT INTO material (name,sklad,atribut,dvigenie)
VALUES
('Перлит цементная мука', s,
MATERIALATRIBUTTYPE('ГОСТ 38012-89','Марка 0', 'Кг.',
'Применяется для обмуровки котлов'),
DVIGENIE_TABLE());
commit;
end;
/

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

INSERT INTO

THE
(
SELECT "DVIGENIE" FROM "MATERIAL" A
WHERE "NAME"='Перлит цементная мука'
)
VALUES ( 100, 0 , 3000, sysdate, KLIENTTYPE(1, 0) );

Важно, чтобы запрос в предложении THE возвращал одну запись с полем вложенной таблицы, в противном случае будет сгенерирована ошибка Oracle сервером. Следующий пример производит обновление записи во вложенной таблице:

UPDATE

THE (
SELECT d.dvigenie FROM material d
WHERE "NAME"='Перлит цементная мука') pdvig
SET pdvig.klient.kod =
(SELECT kod FROM agent WHERE
name='Электростальский Котельностроительный комбинат');

Удаление записей во вложенных таблицах производится аналогичным образом:

DELETE FROM

THE (
SELECT d.dvigenie FROM material d
WHERE "NAME"='Перлит цементная мука') pdvig
WHERE pdvig.summa<10;

8. Объектные представления (Views)

Эти объекты базы данных позволяют использовать реляционные данные из существующих таблиц и новые объектные данные:

CREATE TYPE skladViewType AS OBJECT

(
kod NUMBER(10),
name VARCHAR2(50),
otvetstv VARCHAR2(50)
);
/

--создаем представление

CREATE OR REPLACE VIEW skladView OF skladViewType

WITH OBJECT OID (kod)
AS
SELECT s.kod, s.name,s.otvetstven.atribut(s.otvetstven.base) otvetstv
FROM sklad s;

Объектное представление – это обычное представление с одной колонкой (очень похожи на таблицы объектов), в качестве строк выступают объекты со своими идентификаторами, которые определяет пользователь, на стадии создания представления, предложение WITH OBJECT OID (kod). Созданное приложение для редактирования такого представления например в Delphi 4 будет выглядеть следующим образом:

Поле KOD и NAME могут редактироваться из клиентского приложения, однако поле с атрибутами заведующего складом не может быть изменено в виду его виртуальных свойств, а именно, значение этого поля возвращает метод класса OTVETSTV.ATRIBUT. В этом случае для представления необходимо создать INSTEAND OF триггер, который будет отрабатывать в случае попытки модификации объектного представления.

CREATE OR REPLACE TRIGGER

insert_new_sklad_tr INSTEAD OF INSERT ON skladview
DECLARE
n NUMBER(10);
BEGIN
SELECT kod INTO n
FROM base WHERE nomer ='33422';
INSERT INTO sklad s (s.name ,s.otvetstven)
VALUES ('Новый склад'||new_kod.nextval,
otvetstv(to_date('30.10.59','dd.mm.yy'), n));
END;
/

Триггер срабатывает в момент вставки новой записи и обновляет связанные с представлением таблицы. Используя выражение CAST можно создать представление с вложенной таблицей:

--Этот тип абсолютно точно описывает поля вложенной таблицы (порядок следования, имя, размер)

CREATE OR REPLACE TYPE view_dvigenie_type AS OBJECT

(
data DATE,
prihod NUMBER(15,3),
rashod NUMBER(15,3),
summa NUMBER(15,2),
kod NUMBER(10),
status NUMBER(10),
atribut VARCHAR2(100)
);

--Объектная таблица, в представлении будет вложенной таблицей

CREATE OR REPLACE TYPE view_dvigenie_table

AS TABLE OF view_dvigenie_type;

CREATE OR REPLACE VIEW ViewDvigenieMaterial AS

select mat.kod, mat.name,mat.atribut ,
CAST(
MULTISET(
SELECT
view_dvigenie_type(
m.data,
m.prihod,
m.rashod,
m.summa,
m.klient.kod,
m.klient.status,
m.klient.atribut()
)
FROM
THE( SELECT d.dvigenie FROM material d
WHERE mat.kod=d.kod) m
) AS view_dvigenie_table
) dvigenie

from material mat;

select * from ViewDvigenieMaterial;

В этом случае будет возвращен запрос с вложенными таблицами.

Вот примерно так он будет выглядеть в Delphi 4:

Все приводимее в этой сатье скрипты SQL тестировались на Oracle server 8.04 Enterprise Edition. Клиентские приложения создавались в среде Delphi 4 Patch 2.

Interface Ltd.

Tel: +7 (095) 135-5500, 135-2519

E-mail: mail@interface.ru

http://www.interface.ru

Ваши замечания и предложения направляйте по адресу: webmaster@interface.ru