699

Переименуем столбец supplier_name в sname:

ALTER TABLE supplier RENAME COLUMN supplier_name to sname;

2.15. Триггер DML уровня таблицы

Три́ггер (англ. trigger) – это хранимая процедура особого типа, которую пользователь не вызывает непосредственно – ее исполнение обусловлено действием по модификации данных: добавлением INSERT, удалением DELETE строки в заданной таблице или изменением UPDATE данных в определенном столбце заданной таблицы реляционной базы данных. Триггер запускается сервером автоматически при попытке изменения данных в таблице, с которой он связан.

Кроме того, триггеры могут быть привязаны не к таблице, а к представлению (VIEW). В этом случае с их помощью реализуется механизм «обновляемого представления». В этом случае ключевые слова BEFORE и AFTER влияют лишь на последовательность вызова триггеров, так как собственно событие (удаление, вставка или обновление) не происходит.

Триггеры уровня таблицы Oracle

Oracle поддерживает три вида триггеров: предваряющие (BEFORE), замещающие (INSTEAD OF) и завершаю-

щие (AFTER).

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

Пример (Oracle):

/* Триггер на уровне таблицы */ CREATE OR REPLACE TRIGGER

DistrictUpdatedTrigger

AFTER UPDATE ON district

141

BEGIN

INSERT INTO info VALUES ('table "district" has changed');

END;

В этом случае для отличия табличных триггеров от строчных вводятся дополнительные ключевые слова при описании строчных триггеров. В Oracle это словосочетание

FOR EACH ROW [20].

Пример:

/* Триггер на уровне строки */ CREATE OR REPLACE TRIGGER

DistrictUpdatedTrigger

AFTER UPDATE ON district FOR EACH

ROW

BEGIN

INSERT INTO info VALUES ('one string in table "district" has changed');

END;

CREATE OR REPLACE TRIGGER TRANS_SalesPriceCheck

BEFORE UPDATE ON TRANSACTION FOR EACH ROW

BEGIN

IF:new.SalesPrice < 0.9 *:old.AskingPrice THEN

UPDATE TRANSACTION SET

SalesPrice =:old.AskingPrice, AskingPrice =:old.AskingPrice; END IF;

END;

Логика работы триггера очевидна. Если новая продажная цена составляет менее 90 % от запрашиваемой цены, продажная цена устанавливается равной запрашиваемой цене. Следует обратить внимание, что новая продажная цена сравнивается со старой запрашиваемой ценой; в противном случае мож-

142

но было бы, изменив обе цены, успешно совершить обновление, нарушающее данное ограничение. На тот случай, если именно так и произошло, столбец AskingPrice в операторе

UPDATE устанавливаетсяравным: old. AskingPrice.

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

[28].

CREATE	OR	REPLACE	TRIGGER
CustomerlnterestsJJpdate INSTEAD			OF UPDATE
ON Customerlnterests
FOR EACH ROW
BEGIN
UPDATE CUSTOMER C1 SET C1.Name
=:new.Customer		WHERE	C1.Name

=:old.Customer AND NOT EXISTS (SELECT * FROM CUSTOMER C2 WHERE C2.Name = C1.Name

AND C2.CustomerlD <> C1.CustomerlD); END;

/

Триггер, обновляющий представление

Представление order_info получает информацию о покупателях и заказах:

CREATE VIEW order_info AS

SELECT c.customer_id, c.cust_last_name, c.cust_first_name,

o.order_id, o.order_date, o.order_status

FROM customers c, orders o

WHERE c.customer_id = o.customer_id;

Пример триггера INSTEAD, где представление order_info обновляется из таблиц customers и orders, а при вводе повторяющихся записей выдается ошибка:

CREATE OR REPLACE TRIGGER order_info_insert

INSTEAD OF INSERT ON order_info

143

DECLARE

duplicate_info EXCEPTION;

PRAGMA EXCEPTION_INIT (duplicate_info, -00001);

BEGIN

INSERT INTO customers

(customer_id, cust_last_name, cust_first_name)

VALUES ( :new.customer_id, :new.cust_last_name, :new.cust_first_name);

INSERT INTO orders (order_id, order_date, customer_id)

VALUES ( :new.order_id, :new.order_date, :new.customer_id);

EXCEPTION

WHEN duplicate_info THEN RAISE_APPLICATION_ERROR ( num=> -20107,

msg=> 'Duplicate customer or order

ID');

END order_info_insert;

/

Триггеры и последовательности в Oracle. Автовычисляемый первичный ключ

Для автовычисляемого первичного ключа нужно создать последовательность и триггер для таблицы.

Последовательность можно создать в приложении Oracle. Нужно указать шаг и максимальное значение.

CREATE SEQUENCE seq START WITH 1;

Вызов seq.nextval будет выдавать следующее значение:

SELECT seq.nextval FROM dual;

144

Пример создания таблицы «корпус», последовательности "CORP_SEQ" и триггера для вычисления первичного ключа “CC”:

CREATE table "CORP" ( "CC" NUMBER NOT NULL, "CORP" VARCHAR2(60), "ABR" VARCHAR2(15), "ADR" VARCHAR2(40), "TEL" VARCHAR2(20), "EMAIL" VARCHAR2(15),

constraint "CORP_PK" primary key

("CC")

)

/

CREATE sequence "CORP_SEQ"

/

CREATE trigger "BI_CORP" before insert on "CORP" for each row

begin

select "CORP_SEQ".nextval into:NEW.CC from dual;

end;

/

ALTER TRIGGER " BI_CORP " ENABLE

/

2.16. Создание индексов Create Index

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

145

–поиск ведется по индексу, а не по таблице;

–доступ начинается с первой строки, удовлетворяющей запросу;

–строки в индексе просматриваются последовательно начиная с первой найденной записи.

Создание индексов не предусмотрено стандартом SQL, однако большинство диалектов поддерживают как минимум следующий оператор:

CREATE [ UNIQUE ] INDEX имя_индекса

ON имя_таблицы(имя_столбца[ASC|DESC][,...n])

Создание индексов Create Index Oracle

Создание индекса является методом увеличения производительности работы СУБД при извлечении записей. В индексе создается запись для каждого значения, которое появляется в индексируемом столбце.

Синтаксис создания индекса:

CREATE [UNIQUE] INDEX index_name ON table_name (column1, column2,. column_n) [ COMPUTE STATISTICS ];

Параметр UNIQUE указывает, что комбинация значений в индексируемых столбцах таблицы должна быть уникальной.

Параметр COMPUTE STATISTICS командует Oracle

собирать статистику в процессе создания индекса. Эта статистика впоследствии используется оптимизатором при выборе “plan of execution” в процессе выполнения SQLзапроса.

Например:

CREATE INDEX supplier_idx ON supplier (supplier_name);

Вэтом примере мы создали индекс на таблице supplier

сименем supplier_idx. Он содержит только одно поле – supplier_name.

146

Также мы можем создать индексы с большим, чем одно, количеством полей, как в следующем примере:

CREATE INDEX supplier_idx ON supplier (supplier_name, city);

Мы также можем включить сбор статистики, создав индекс следующим образом:

CREATE INDEX supplier_idx ON supplier (supplier_name, city) COMPUTE STATISTICS;

Создание индексов на основе функций

В Oracle нет ограничений на создание индексов только на столбцах таблиц. Можно создавать индексы, основанные на функциях.

Синтаксис создания индекса на основе функции:

CREATE [UNIQUE] INDEX		index_name
ON	table_name	(function1,
function2,. function_n)		];
[ COMPUTE STATISTICS

Например:

CREATE INDEX supplier_idx ON supplier (UPPER(supplier_name));

В этом примере мы создали индекс, основанный на функции uppercase, примененной к полю supplier_name.

Однако, чтобы быть уверенным, что Oracle оптимизатор использует этот индекс, когда выполняет ваши SQLзапросы, следует убедиться в том, что значение

UPPER(supplier_name) не возращает NULL. Чтобы это проверить, добавьте выражение UPPER(supplier_name) IS NOT NULL в оператор WHERE следующим образом:

SELECT supplier_id, supplier_name, UPPER(supplier_name)

FROM supplier

WHERE UPPER(supplier_name) IS NOT

NULL

ORDER BY UPPER(supplier_name);

147

Переименование индекса

Синтаксис переименования индекса:

ALTER INDEX index_name RENAME TO new_index_name;

Вэтом примере мы переименовали индекс supplier_idx

вsupplier_index_name:

ALTER INDEX supplier_idx RENAME TO supplier_index_name;

Сбор статистики по индексу

Если вы хотите включить сбор статистики по индексу после его создания или хотите обновить статистику, воспользуйтесь командой ALTER INDEX.

Синтаксис подключения сбора статистики по индексу:

ALTER INDEX index_name REBUILD COMPUTE STATISTICS;

В этом примере мы собираем статистику для индекса

supplier_idx:

ALTER INDEX supplier_idx REBUILD COMPUTE STATISTICS;

Удаление индекса (Drop an Index)

Синтаксис удаления индекса:

DROP INDEX index_name;

В этом примере мы удалили индекс supplier_idx:

DROP INDEX supplier_idx;

2.17. Полнотекстовое индексирование

Полнотекстовый поиск – это поиск документов в базе данных текстов с использованием полнотекстового индекса. Полнотекстовый индекс – это индекс, в котором перечислены все слова, встречающиеся в тексте, и указаны позиции, на которых эти слова встречаются. Полнотекстовый индекс позволяет производить поиск очень быстро, в отличие от поиска при помощи оператора like. Кроме того, системы полнотекстового поиска обычно включают в себя

148

функционал, расширяющий возможности поиска, например поиск с учетом морфологии языка, синонимов, созвучных слов, с учетом меры близости между словами в запросе и другие [3].

Полнотекстовое индексирование Oracle

Oracle Text – это бесплатная компонента, входящая в состав Oracle Database, предназначенная для создания приложений с использованием полнотекстового поиска [3].

(SQL*TextRetrieval -> Text Server -> Oracle ConText -> Oracle Text)

Текстовые возможности СУБД Oracle основаны на использовании специального вида индекса, являющегося одним из встроенных в систему вариантов «предметного» индекса (domain index), используемого для организации работы со сложно устроенными данными. Oracle Text имеет в готовом виде три вида текстового индекса:

CTXSYS.CONTEXT – для выполнения полнотекстового поиска по текстовым документам;

CTXSYS.CTXCAT – для выполнения упрощенного и ускоренного поиска по «каталогам» (одно-двустрочным текстовым описаниям);

CTXSYS.CTXRULE – для построения «классификаций» документов, при том что класс описывается набором характерных запросов.

Здесь рассматриваются общие возможности наиболее популярной разновидности индекса CTXSYS.CONTEXT. Этот вид текстового индекса позволяет хранить в БД текстовые документы и выполнять полнотекстовые запросы к документам как внутреннего, так и внешнего хранения (файловая система, интернет) [3].

Для удобства создадим специального пользователя:

> CONNECT / AS SYSDBA

149

SYS> CREATE USER ctx IDENTIFIED BY ctx DEFAULT TABLESPACE users;

SYS> GRANT connect, resource, ctxapp TO ctx;

SYS> CONNECT ctx/ctx CTX>

Роли CONNECT и RESOURCE приписаны пользователю CTX для простоты примера, и использовать их в рабочей БД неправильно; роль же CTXAPP употреблена по существу, так как без нее пользователь CTX не сможет обращаться к необходимым объектам схемы CTXSYS [3].

Выполним:

CREATE TABLE docs ( doc_id			NUMBER (
10 ), vc2doc VARCHAR2 ( 4000 ) );			1, 'Mary
INSERT INTO docs VALUES (
had a little lamb' );			(	2,
INSERT	INTO	docs VALUES
'Twinkle, twinkle little star' );
INSERT INTO docs VALUES ( 3, 'This
Lamb is my lamb' );		docs_vc2doc_idx	ON	docs
CREATE	INDEX

( vc2doc ) INDEXTYPE IS ctxsys.context; DOCS_VC2DOC_IDX «текстовый» индекс

CTXSYS.CONTEXT.

Примеры запросов с использованием словаря полнотекстового индексирования Oracle

Основой для запросов к документам по индексу типа

CTXSYS.CONTEXT является «оператор» CONTAINS.

Оператор CONTAINS возвращает меру, иначе – степень, соответствия документа текстовому запросу (relevance) [3].

Пример подготовки блока для запроса к текстовому индексу vc2doc таблицы docs:

SELECT CONTAINS ( vc2doc, '&1' ) AS score, vc2doc FROM docs

150