INSTEAD OF-триггеры в Oracle

Применимы только к представлениям, выполняются непосредственно вместо вызвавшего оператора. Могут быть только уровня строки.

Пример:

CREATE OR REPLACE TRIGGER BAO_1

BEFORE INSERT OR UPDATE OR DELETE ON AAA

BEGIN

IF INSERTING THEN

DBMS_OUTPUT.PUT_LINE('BAO_1 TRIGGER_INSERT');

ELSIF UPDATING THEN

DBMS_OUTPUT.PUT_LINE('BAO_1 TRIGGER_UPDATE');

ELSIF DELETING THEN

DBMS_OUTPUT.PUT_LINE('BAO_1 TRIGGER_DELETE');

END IF;

END;

При работе с триггерами уровня строки мы можем обращаться к изменяемым данным (строкам) при помощи ключевых слов new и old:

CREATE OR REPLACE TRIGGER BAS_1

BEFORE INSERT OR UPDATE OR DELETE ON AAA

FOR EACH ROW

BEGIN

IF INSERTING THEN

INSERT INTO WIKILEAKS(R, O, T, M) VALUES(CURRENT_TIMESTAMP, 'I', 'BAS_1', 'OLD_FACULTY='||:OLD.FACULTY||';OLD_FACULTY_NAME='||:OLD.FACULTY_NAME||';NEW_FACULTY='||:NEW.FACULTY||';NEW_FACULTY_NAME='||:NEW.FACULTY_NAME);

ELSIF UPDATING THEN

INSERT INTO WIKILEAKS(R, O, T, M) VALUES(CURRENT_TIMESTAMP, 'U', 'BAS_1', 'OLD_FACULTY='||:OLD.FACULTY||';OLD_FACULTY_NAME='||:OLD.FACULTY_NAME||';NEW_FACULTY='||:NEW.FACULTY||';NEW_FACULTY_NAME='||:NEW.FACULTY_NAME);

ELSIF DELETING THEN

INSERT INTO WIKILEAKS(R, O, T, M) VALUES(CURRENT_TIMESTAMP, 'D', 'BAS_1', 'OLD_FACULTY='||:OLD.FACULTY||';OLD_FACULTY_NAME='||:OLD.FACULTY_NAME||';NEW_FACULTY='||:NEW.FACULTY||';NEW_FACULTY_NAME='||:NEW.FACULTY_NAME);

END IF;

END;

40. Процедурные объекты: типы, объекты и объектные таблицы.

Помимо сравнительно простых встроенных типов данных — как перешедших из стандартов SQL, так и собственных, — в Oracle имеется возможность использовать составные. Это конструируемые типы объектов, рассчитанные на хранение в БД данных, имеющих внутреннюю структуру. Эта структура известна СУБД, и СУБД позволяет с ней работать. Объектные типы позволяют хранить и обрабатывать средствами СУБД "сложно устроенные данные" более продвинутым образом, нежели это позволяет техника "больших неструктурированных объектов" типов LOB. Ввиду наличия вполне определенного типа (даже если это тип коллекции), единичное объектное значение можно полагать за скаляр, хотя оно и не будет атомарным.

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

Привилегии: CREATE TYPE или CREATE ANY TYPE.

Ниже приводится простой пример использования программируемых (объектных) типов.

Вначале требуется создать "тип", как разновидности хранимых элементов БД. Пример создания типа объекта (в SQL*Plus):

CREATE TYPE address_type AS OBJECT (

zip CHAR (6)

, location VARCHAR2 (200)

)

/

Здесь типу ADDRESS_TYPE приписаны два "свойства" (по объектной терминологии): ZIP и LOCATION. В реальной жизни для представления адреса в типе наверняка будет указано большее количество свойств, однако в ознакомительном примере их более пространный перечень излишен и не добавит понимания техники.

Помимо свойств тип может содержать методы (member) и статические методы (static).

Объектные типы: constructor всегда есть, определенный по умолчанию с числом аргументов равным числу атрибутов, можно создать свой. Конструкторы можно перегрузить.

SELF – ссылка на объект (автоматически передается первым параметром в функции(IN) и процедуры (IN, OUT))

MAP-метод, может быть только один, используются для сравнения и сортировки объектов. Может вернуть: DATE, NUMBER, VARCHAR2, CHAR, REAL. MAP-функция может получить (и получает)только один параметр SELF.

ORDER-методы может быть только один, используются для сравнения и сортировки объектов. Возвращает всегда NUMBER. ORDER-функция получает два параметра SELF и сравниваемый объект.

Map и Order отличает то, что map просто возвращает значение сравнимого ораклом типа(к примеру number), а сам процесс сравнения осуществляет оракл стандартными методами, в то время как order сам осуществляет сравнение, получая в качестве параметра сравниваемые объекты.

Опция instantiable - разрешается создавать объекты данного типа, not instantiable – тип можно использовать только как базовый (абстрактный класс).

Опция final - не разрешается использовать тип в качестве базового, not final - разрешается использовать тип в качестве базового.

Определение типа напоминает определение таблицы, однако в отличие от таблицы (а также стандарта SQL и от реляционного подхода) тип объекта в Oracle не имеет права содержать ограничений целостности (которые в таком случае можно было бы назвать "ограничениями целостности типа"). Если необходимо их указать, сделать это придется только по месту употребления типа, то есть в описании таблицы.

Таблицы объектов

Созданый в БД тип можно употребить и для создания "таблиц объектов":

CREATE TABLE addresses1 OF address_type;

CREATE TABLE addresses2 OF address_type;

Хотя для этой категории хранимых элементов используется термин "таблица", такая таблица всегда содержит ровно один столбец, и именно объектного типа.

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

INSERT INTO addresses1 VALUES ('123456', 'Archangelsk');

Пример запроса:

SELECT a.*, UPPER (location) FROM addresses1 a;

Объекты в таких таблицах хранятся как самостоятельные сущности, у которых имеется автоматически порождаемый СУБД внутренний уникальный идентификатор object ID, в соответствии с классическим объектным подходом позволяющий ссылаться на конкретные объекты из других таблиц или из программы. Сравнение элементов-"строк" в таблице объектов друг с другом происходит уже не по значениям свойств, как в случае объектного столбца в обычной таблице, а по значению object ID. Перейти на сравнение значений свойств позволяет функция VALUE, например:

SELECT dname FROM odept1 d, addresses1 a WHERE d.addr = VALUE (a);

Сделан запрос об отделах, расположенных по адресам из таблицы ADDRESS1.

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

Пример создания обычной таблицы со столбцом для ссылки на хранимый в таблице объектов (типа ADDRESS_TYPE) элемент-объект:

CREATE TABLE odept2 (

dname VARCHAR2 (50)

, deptno NUMBER CONSTRAINT pk_odept PRIMARY KEY

, addr REF address_type SCOPE IS addresses1

);

Здесь описание ссылки сужено возможностью адресоваться только к объектам из таблицы ADDRESSES1. Допускаются варианты описания ссылки: ее нацеленность на содержимое конкретной таблицы можно не применять или же, напротив, усилить до аналогии со ссылочной целостностью (в этом примере аналогия с правилом внешнего ключа неполная).

Пример заполнения поля ADDR значением-ссылкой:

INSERT INTO odept2

(dname, deptno, addr)

VALUES

('RESEARCH', 10, (SELECT REF (a)

FROM addresses1 a

WHERE a.location = 'Archangelsk'

))

;

Пример допустимых оформлений обращения к свойствам объекта через ссылку:

COLUMN deref(d.addr) FORMAT A40

SELECT d.dname, DEREF (d.addr), d.addr.zip FROM odept2 d

;

Навигация по объектам в БД с помощью ссылок и в том числе извлечение объекта из БД возможны не только в запросах SQL, но и в программе на PL/SQL.

Пример использования методов объектов

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

CREATE TYPE employee_type AS OBJECT (

name VARCHAR2 (50)

, hiredate DATE

, home REF address_type

,

MEMBER FUNCTION days_at_company RETURN NUMBER

)

/

Когда методов много, их заголовки перечисляются по очереди через запятую, общим списком со свойствами.

В описании типа приводится только заголовок методов. Для описания тела метода необходимо создать тело типа (полная аналогия пары пакет — тело пакета, имеющейся в PL/SQL):

CREATE TYPE BODY employee_type AS

MEMBER FUNCTION days_at_company RETURN NUMBER IS

BEGIN

RETURN TRUNC (SYSDATE - hiredate);

END;

END;

/

Пример использования типа в создании таблицы:

CREATE TABLE sailors (ship VARCHAR2 (30), emp employee_type);

-- в этом контексте EMPLOYEE_TYPE — это имя типа

Использование конструктора типа для заполнения таблицы:

INSERT INTO sailors VALUES

('Ninna', employee_type ('Frank Naude', SYSDATE, NULL))

;

-- в этом контексте EMPLOYEE_TYPE — это конструктор

Использование свойств и метода типа для запроса к таблице:

COLUMN emp FORMAT a60

SELECT * FROM sailors;

SELECT x.ship, x.emp.name, x.emp.days_at_company () FROM sailors x;

-- указание скобок после DAYS_AT_COMPANY сообщает, что это метод, а не свойство

Так же к предопределенным объектным типам относится тип XMLTYPE, рассмотренный в вопросе 35.

Дата добавления: 2015-11-16