699
.pdfПереименуем столбец 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