W Cat - SQL за 24 часа
Триггер можно создать с помощью оператора CREATE TRIGGER. Стандарт ANSI предлагает для этого оператора следующий синтаксис.
CREATE TRIGGER TRIGGER NAME
[[BEFORE | AFTER] TRIGGER EVENT ON TABLE NAME]
[REFERENCING VALUES ALIAS LIST ]
[TRIGGERED ACTION TRIGGER EVENT ::=
INSERT UPDATE | DELETE {OF TRIGGER COLUMN LIST]
TRIGGER COLUMN LIST ::= COLUMN NAME [, COLUMN NAME]
VALUES ALIAS LIST ::=
VALUES ALIAS LIST ::=
OLD [ROW] [AS] OLD VALUES CORRELATION NAME |
NEW {ROW} {AS} NEW VALUES CORRELATION NAME |
OLD TABLE {AS} OLD VALUES TABLE ALIAS |
NEW TABLE {AS} NEW VALUES TABLE ALIAS
OLD VALUES TABLE ALIAS ::= IDENTIFIER
NEW VALUES TABLE ALIAS ::= IDENTIFIER
TRIGGERED ACTION ::=
[FOR EACH [ROW | STATEMENT] [WHEN SEARCH CONDITION]]
TRIGGERED SQL STATEMENT
TRIGGERED SQL STATEMENT ::=
SQL STATEMENT | BEGIN ATOMIC [SQL STATEMENT;]
END
В Microsoft SQL Server синтаксис оператора для создания триггера выглядит следующим образом.
CREATE TRIGGER ИМЯ_ТРИГГЕРА
ON ИМЯ_ТАБЛИЦЫ
FOR { INSERT | UPDATE | DELETE [, ..]}
AS
OnEPATOPH_SQL
[ RETURN ]
В Oracle базовый синтаксис оператора следующий.
CREATE [ OR REPLACE ] TRIGGER ИМЯ_ТРИГГЕРА
[ BEFORE | AFTER]
[ DELETE I INSERT | UPDATE]
ON [ ПОЛЬЗОВАТЕЛЬ.ИМЯ_ТАБЛИЦЫ ]
[ FOR EACH ROW ]
[ WHEN УСЛОВИЕ ]
[ БЛОК PL/SQL ]
Вот пример создания триггера.
CREATE TRIGGER EMP_PAY_TRIG
AFTER UPDATE ON EMPLOYEE_PAY_TBL
FOR EACH ROW
BEGIN
INSERT INTO EMPLOYEE_PAY_HISTORY
(EMP_ID, PREV_PAY_RATE, PAY_RATE, DATE_LAST_RAISE,
TRANSACTION_TYPE)
VALUES
(:NEW.EMP_ID, :OLD.PAY_RATE, :NEW.PAY_RATE,
:NEW.DATE_LAST_RAISE, 'PAY CHANGE');
END;
/
Триггер создан.
В этом примере создается триггер с именем EMP_PAY_TRIG. Этот триггер вставляет строку в таблицу EMPLOYEE_PAY_HISTORY, отражая изменения таблицы EMPLOYEE_PAY_TBL каждый раз, когда данные последней обновляются.
Тело триггера изменить нельзя Для этого триггер придется либо заменить другим, либо воссоздать В некоторых реализациях SQL триггер можно заменить (если триггер с данным именем в системе уже существует) с помощью того же оператора CREATE TRIGGER.
Оператор DROP TRIGGER
Триггер можно удалить с помощью оператора DROP TRIGGER. Синтаксис этого оператора следующий.
DROP TRIGGER ИМЯ_ТРИГТЕPA
Динамический SQL
Динамический SQL дает возможность программисту или конечному пользователю создавать необходимые операторы SQL прямо во время выполнения программы. Создаваемые таким образом операторы передаются базе данных и возвращают данные в переменные программы, существующие во время выполнения последней.
Понять динамический SQL проще всего в сравнении со статическим. Статический SQL - это SQL, обсуждавшийся в книге до сих пор. Статический оператор SQL создается в предположении, что меняться он не будет. Хотя статические операторы SQL могут сохраняться в готовом для выполнения виде, например, в процедурах базы данных, они не обеспечивают той гибкости, которая достигается с помощью динамического SQL.
Проблема статического SQL состоит в том, что даже тогда, когда пользователю предлагается очень много видов запросов на выбор, всегда остается вероятность того, что в каких-то случаях пользователя ни один из этих "законсервированных запросов" не устроит. Динамический SQL используется в качестве средства создания подходящих для конкретной ситуации запросов непосредственно пользователем во время его работы с базой данных. После того как оператор будет приведен в нужный пользователю вид, он будет передан базе данных для проверки синтаксиса, необходимых привилегий, компиляции и, наконец, выполнения сервером базы данных. Динамический SQL создается с помощью интерфейса уровня вызовов, обсуждению которого посвящается следующий раздел.
Хотя динамический SQL обеспечивает конечным пользователям большую гибкость в построении запросов, он не может сравниться по производительности с сохраненными процедурами, операторы которых оказываются уже подготовленными оптимизатором SQL к немедленному выполнению.
Интерфейс уровня вызововИнтерфейс уровня вызовов (call level interface) используется для внедрения программного кода SQL в главную программу, создаваемую, например, средствами ANSI С Создателям приложений понятие интерфейса уровня вызовов должно быть хорошо знакомым. Это один из методов, с помощью которого программист получает возможность внедрить SQL в программный код некоторых процедурных языков программирования. При использовании интерфейса уровня вызовов текст оператора SQL передается некоторой переменной программы с соблюдением правил соответствующего языка программирования. После этого получившая оператор SQL программа может его выполнить, обработав переменную, которой был передан текст этого оператора.
Типичной командой языка программирования, позволяющей вызвать оператор SQL из программы, является команда EXEC SQL.
ЕХЕС SQL
Вот некоторые из языков программирования, поддерживающих интерфейс уровня вызовов.
• COBOL
• ANSI С
• Pascal
• Fortran
• Ada
По поводу использования опций интерфейса уровня вызовов обратитесь к документации по соответствующему языку программирования
Использование SQL для генерации SQL
Использование SQL для генерации операторов SQL в некоторых случаях значительно экономит время. Предположим, что в вашей базе данных 100 пользователей. Предположим также, что вы создаете новую роль ENABLE (пользовательский объект с соответствующими привилегиями), которую необходимо приписать всем 100 пользователям. Вместо того, чтобы вручную создавать 100 операторов GRANT, можно использовать следующий оператор SQL, который сгенерирует необходимые операторы за вас
SELECT 'GRANT ENABLE TO '|| USERNAME || ';'
FROM SYS.DBA_USERS;
В этом примере используется представление системного каталога Oracle, содержащее информацию о пользователях.
Обратите внимание на то, что здесь фраза GRANT ENABLE TO заключена в кавычки. Использование кавычек заставляет воспринимать все заключенное между ними как буквальное значение. Буквальные значения можно выбирать из таблиц точно так же, как и столбцы. USERNAME является столбцом таблицы SYS. DBA_USERS из системного каталога. Двойная вертикальная черта ( | | ) используется для конкатенации столбцов. Использование двойной вертикальной черты с последующим '; ' добавляет к концу пользовательского имени точку с запятой, означающую завершение оператора.
В результате сгенерированные операторы SQL будут выглядеть примерно так:
GRANT ENABLE TO RRPLEW;
GRANT ENABLE TO RKSTEP;
Результат необходимо сохранить в файле, который можно будет передать базе данных. База данных, в свою очередь, выполнит все операторы SQL из этого файла по очереди, избавляя вас от необходимости долгого печатания команд и тем самым сэкономив вам немало времени.
В следующий раз, когда вам придется при написании операторов SQL повторять однообразные операторы SQL несколько раз подряд, остановитесь и позвольте SQL сделать эту работу за вас.
Прямой вызов SQL и вложенный SQL
Прямое использование SQL означает выполнение операторов SQL с любого интерактивного терминала. Результаты при этом возвращаются обратно на терминал, инициировавший выполнение операторов. В данной книге до сих пор, в основном, рассматривалось прямое использование SQL. Прямое использование SQL называют также интерактивным вызовом или прямым вызовом (direct invocation)
Вюженныи SQL (embedded SQL) представляет собой программный код SQL, используемый в рамках другой программы, созданной, например, средствами Pascal, Fortran, COBOL или С Программный код SQL оказывается фактически встроенным в несущий его язык программирования посредством интерфейса уровня вызовов, как было указано выше. Встроенные в несущий язык программирования операторы SQL обычно предваряются командой ЕХЕС SQL и, как правило, завершаются точкой с запятой. В других же случаях такими завершающими командами могут быть END-EXEC или закрывающая скобка.
Вот пример вложения оператора SQL в вызывающую программу, созданную средствами ANSI С
{операторы вызывающей программы}
ЕХЕС SQL {оператор SQL};
{другие операторы вызывающей программы}
Резюме
В ходе этого урока обсуждались некоторые средства SQL, выходящие за рамки элементарных. Хотя мы и не вдавались в детали, вы должны были получить общее представление о том, как используются обсуждавшиеся здесь возможности. Сначала были рассмотрены курсоры, с помощью которых можно сохранять результаты запросов в памяти. Чтобы объявившая курсор программа могла его использовать, эта программа должна открыть его. После этого содержимое курсора можно передать соответствующей переменной, чтобы программа смогла в нужный момент эти данные использовать. Данные курсора сохраняются в памяти до тех пор, пока курсор не будет закрыт, а занятая им память освобождена.
Затем были рассмотрены сохраняемые процедуры и триггеры. Сохраняемые процедуры состоят, в основном, из групп операторов SQL, хранящихся вместе в базе данных. Такие операторы вместе с другими необходимыми командами компилируются базой данных и сохраняются в виде, готовом для немедленного выполнения в любое время по требованию пользователя базы данных. Триггер тоже представляет собой сохраненную процедуру, но его выполнение инициируется автоматически в ответ на определенные события, происходящие в базе данных. Использование сохраненных процедур оказывается, как правило, более выгодным с точки зрения производительности системы, чем непосредственное использование отдельных операторов SQL.
