Часть 3. Хранимые процедуры и триггеры.
В предыдущей части были указаны недостатки сложных конструкций SELECT: низкая скорость работы, большая ресурсоемкость при исполнении запроса и т.д. Один из выходов – это создание представлений. Однако, имеется гораздо более мощный и гибкий, а главное, надежный и удобный механизм – это механизм хранимых процедур. Хранимая процедура может выполнять любые дозволенные действия над базой данных.
Хранимой (или предварительно скомпилированной, pre-compiled) процедурой называется именованная совокупность действий, выраженная на языке базы данных, которая может быть в любой момент вызвана клиентом. Хранимая процедура пишется и отлаживается единожды, затем пользователь может к ней обращаться при необходимости.
Хранимая процедура может иметь параметры, а также может сама возвращать целочисленное значение. Максимальный объем хранимой процедуры – 128МБ.
Синтаксис инструкции создания хранимой процедуры таков:
CREATE PROC[EDURE] имя [;номер][ {@параметр тип_параметра} [VARYING] [= по_умолчанию] [OUTPUT]] [,...n][
WITH { RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION }
]
AS предложения SQL [...n]
Имя процедуры должно быть действительным идентификатором SQL. Номер служит для того, чтобы отличать процедуры с одинаковыми именами.
Процедура может иметь параметры. Имена параметров начинаются со значка @ и должны быть действительными идентификаторами SQL. Возможные типы данных перечислены в первой лабораторной работе. Можно задать значения по умолчанию, тогда при вызове параметр можно будет опустить. Параметр VARYING используется в том случае, если содержимое переменной конструируется в самой процедуре и должно быть использовано вызывающим кодом (только тип данных cursor, он же всегда должен быть указан OUTPUT). Параметр OUTPUT указывается, если значение параметра может быть изменено в процедуре и этим значением воспользуется вызывающий код. Процедура может иметь до 1024 параметров.
Параметр RECOMPILE требует от сервера не составлять план выполнения процедуры, в всякий раз при обращении к ней компилировать ее заново. Снижает быстродействие, но требуется иногда, если необходимо сбросить кэш плана выполнения процедуры.
Параметр ENCRYPTION указывает серверу необходимость кодирования текста процедуры. Следует помнить, что после этого невозможно будет увидеть текст процедуры.
Само тело процедуры может содержать почти все команды языка SQL, завершается процедура после выполнения последнего предложения либо по инструкции RETURN, аргументом которой может выступать целочисленное выражение.
Создание процедуры в базе данных
В программе Microsoft SQL Server Management Studio (кнопка New Query) можно набрать текст процедуры (как указано выше) и при исполнении запроса она будет создана на сервере. Дальнейшее изменение процедуры производить не вполне удобно (например, для засылки нового текста следует дать команду DROP PROC). Из программы же Microsoft SQL Server Management Studio в дереве базы данных можно найти ветку «Stored Procedures», находящуюся в свою очередь в ветке «Programmability», в контекстном меню следует выбрать пункт «New Stored Procedure», после чего в окне редактирования процедуры появится шаблон текста хранимой процедуры. После сохранения процедуры на сервере можно редактировать ее либо выбрав пункт «Modify» в ее контекстном меню.
Средства языка Transact-SQL
Сам язык SQL, хоть и не предназначенный для программирования алгоритмов (он ориентирован на работу с множествами), содержит достаточное количество алгоритмических конструкций, позволяющих писать достаточно сложные процедуры.
Локальные переменные:
DECLARE @имя_переменной тип_переменной [,...]
Имена переменных должны начинаться со знака @ и должны быть действительными идентификаторами SQL. Присваивания возможны только в инструкции SELECT, например:
SELECT @sUserName = ‘Ivan’
Циклы:
WHILE логическое_выражение {предложение} [BREAK] {предложение} [CONTINUE]
Логическое выражение – любое допустимое выражение, возвращающее логическое значение True (истина) или False (ложь). Предложение в теле цикла формально может быть только одно, если требуется несколько – следует воспользоваться составным оператором BEGIN..END. Предложение BREAK немедленно завершает работу цикла, предложение CONTINUE досрочно инициирует следующую итерацию.
Комментарий: все, что находится после двух знаков тире ‘--‘, является комментарием и игнорируется сервером.
Строки: строковые константы следует заключать в одинарные кавычки, идентификаторы, содержащие пробелы, следует заключать в квадратные скобки, например [дата выдачи].
Условный оператор:
IF логическое_выражение {предложение}[ELSE {предложение}]
Предложение опять-таки формально должно быть одно, при необходимости следует воспользоваться составным оператором. Конструкции IF могут быть вложенными.
Оператор выбора: работает на уровне строк в запросе и возвращает указанное значение в зависимости от того, что было выбрано. Первая форма, выбирающая значение, равное условию выбора (простой выбор):
CASE условие_выбора
WHEN значение_выбора_1 THEN возвращаемое_значение_1
[...и т.д.]
[
ELSE возвращаемое_значение_по_умолчанию
]
END
Вторая форма, выбирающая первое истинное логическое значение (поисковый выбор):
CASE
WHEN логическое_условие_1 THEN возвращаемое_значение_1
[...и т.д.]
[
ELSE возвращаемое_значение_по_умолчанию
]
END
Оператор распечатки: применяется в том случае, если результат распечатки будет виден (например, с консоли SQL Query Analyzer):
PRINT 'текст' | @переменная | @@функция | строковое_выражение
Агрегатные функции: ориентированы на работу с множеством значений, но возвращают одно значение. Как правило, применяются при выборке из базы данных и принимают обычно имя поля в качестве параметра. ALL – принимают все значение, DISTINCT – только разные, если задано выражение – то только значения выражения.
AVG([ALL|DISTINCT] выражение) – среднее значение выражения в множестве
MAX([ALL|DISTINCT]выражение) – максимальное значение выражения в множестве
MIN([ALL|DISTINCT] выражение) – минимальное значение выражения в множестве
SUM([ALL|DISTINCT] выражение) – суммарное значение выражения в множестве
COUNT({[ALL|DISTINCT]выражение]|*}) – количество выражений во множестве, если задана звездочка «*» (в этом случае задается только она, ничего больше), то подсчитывается все общее количество строк выборки.
Логические операции: AND, OR, NOT – соответственно логическое «И», «ИЛИ», «НЕ». Операторы сравнения: = равно, != (либо <>) не равно, < меньше, > больше, <= меньше либо равно, >= больше либо равно, !< не меньше, !> не больше.
Сюда же относят оператор условного совпадения: выражение [NOT] LIKE маска
Оператор определяет, совпадает ли заданное выражение с маской и возвращает соответственно логическое значение. Если заданное выражение не строковое, оно приводится к таковому. Маска – это регулярное выражение, задаваемое строкой. Символы:
| Символ маски | Описание | Пример маски |
| % | Любое количество любых символов | «%студент%» - любая строка, содержащая слово «студент». |
| _ (подч.) | Один любой символ | «_АБВ» - строка из четырех символов, первый любой, остальные как указано. Т.е., подходят «1АБВ», «ЮАБВ» и т.д. |
| [] | Один символ из указанного множества | «[A-Z]%» - любая строка, начинающаяся с заглавной буквы. |
| [^] | Один символ не из указанного множества | «[^0-9]%» - любая строка, не начинающаяся с цифры. |
Оператор LIKE гораздо удобнее, нежели конструкция типа «равно» или «не равно», ибо имеет гораздо больше возможностей.
Пример простой хранимой процедуры (это реальная процедура из работающей базы данных). Данная процедура удаляет связанные записи из нескольких таблиц: _lists, _listItems, _listGroups. Параметр @i задает идентификатор записи в главной таблице. Организована транзакция для сохранения семантической целостности базы данных при возможных сбоях во время выполнения процедуры. Все идентификаторы взяты в квадратные скобки для наглядности.
CREATE PROCEDURE [_DeleteFromList]
@i INT
AS
IF NOT EXISTS (SELECT [ID] FROM [_lists] WHERE [ID] = @i) RETURN -3
BEGIN TRANSACTION
-- Delete items of list
DELETE FROM [_listItems] WHERE [ListID] = @i
-- Delete groups of list
DELETE FROM [_listGroups] WHERE [ListID] = @i
-- Delete list header
DELETE FROM [_lists] WHERE [ID] = @i
COMMIT
RETURN 0
Вызов сохраненных процедур
Сохраненная процедура может быть вызвана как непосредственно, так и из другой процедуры. Для вызова служит инструкция EXECUTE
[[EXEC[UTE]] { [@переменная_возврата =] {имя_процедуры [;номер] | @переменная_с_именем } [[@параметр =] {значение | @переменная [OUTPUT] | [DEFAULT]] [,...n][WITH RECOMPILE]
Переменная возврата – это переменная, которая получит значение, возвращаемое процедурой. Имя процедуры и номер уже описаны, имя процедуры может также быть указано в переменной. Тип переменной может быть char, varchar, nchar, nvarchar.
Затем перечисляются через запятую отсылаемые в процедуру параметры. Если требуется взять значение по умолчанию, можно указать слово DEFAULT либо опустить и имя параметра (если он не последний, просто ставится очередная запятая).
Пример. Пусть имеется процедура _myProcedure, принимающая параметры @p1, @p2, @p3. Следующие вызовы эквивалентны, второй параметр взят по умолчанию:
EXEC @j = _myProcedure 'Parameter1', , 50
EXEC @j = _myProcedure @p1 = 'Parameter1', @p2 = DEFAULT, @p3= 50
Триггеры
Триггер – особый вид хранимой процедуры, связанной с определенной таблицей (или представлением) базы данных. Триггер выполняется автоматически при попытке изменения информации в этой таблице. С помощью триггеров можно задавать дополнительные ограничения целостности информации в базе данных и правила поведения данных.
Синтаксис оператора создания триггера:
CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]
[NOT FOR REPLICATION]
{
{ {FOR | INSTEAD OF } { [ INSERT ] [ ,] [ UPDATE ] [ ,] [DELETE] }
AS
[ { IF UPDATE (column )
[ { AND | OR } UPDATE (column )]
[ ...n ]
} ]
sql_statement [...n ]
}
}
Аргументы этого оператора:
WITH ENCRYPTION – указывает необходимость шифрования описания триггера в системной таблице syscomments
INSTEAD OF – при использовании триггера данного типа действия, вызвавшие его срабатывание не оказывают влияния на данные в таблице
{ [DELETE] [,] [INSERT] [,] [UPDATE] } – указывают одно или несколько действий по изменению данных в таблице, которые будут вызывать срабатывание триггера.
NOT FOR REPLICATION – указывает что триггер не будет срабатывать в процессе репликации данных
AS – ключевое слово, указывающее начало тела триггера.
sql_statement – операторы Transact-SQL, составляющие тело триггера и реализующие проверку условий и действия, заложенные в этот триггер.
IF UPDATE(column) –оператор IF, проверяющий изменение значения указанного столбца таблицы оператором, вызвавшим срабатывание триггера. Можно одновременно проверять несколько столбцов на изменение, объединяя несколько UPDATE(column)с помощью логических условий. Сама конструкция UPDATE(column) может быть использована где угодно в теле триггера. Если триггер сработал от оператора INSERT, UPDATE(column) будет истиной в любом случае, так как для столбцов, в которые не были переданы новые значения, будут добавлены NULL значения. При срабатывании от оператора удаления данных конструкция UPDATE(column) не используется.
В триггерах используются две логические (реально не существующие) таблицы – inserted и deleted. По структуре они аналогичны таблице, на которую задан триггер, но существуют лишь в кэше сервера и предназначены для хранения удаляемых или добавляемых данных во время выполнения триггера. Например, если необходимо выбрать все добавленные записи используется SELECT * FROM inserted. Допускается объединять эти виртуальные таблицы с другими таблицами базы данных в запросах.
Свойства триггеров
Триггер является частью транзакции того оператора SQL, который вызвал его срабатывание, и запись данных непосредственно в базу данных произойдет только после завершения работы триггера. Поэтому использование в триггере оператора ROLLBACK TRANSACTION приведет к отмене всех действий, выполненных транзакцией, в которой вызвано его срабатывание. Все действия внутри этой транзакции или же в блоке совместно выполняемых операторов после оператора, вызвавшего срабатывание такого триггера, выполнены не будут. Если после оператора ROLLBACK TRANSACTION в теле триггера есть еще операторы, они также выполнятся. Для их разделения, нужно использовать оператор RETURN.
Для выполнения действий, заложенных в триггере пользователь, чей оператор вызвал его срабатывание должен иметь соответствующие привилегии.
При одновременном изменении, добавлении или удалении нескольких записей триггер срабатывает только один раз. В этом случае вы можете либо выполнять одни и те же действия над всеми этими записями (при этом все они должны удовлетворять проверяемым условиям), либо выполнять различные действия в зависимости от количества записей и информации в каждой записи.
В отличие от ограничений, задаваемых на таблицы, триггеры срабатывают после выполнения действий, вызвавших его срабатывание (за исключением триггера INSTEAD OF). Если на таблицу, для которой создан триггер задано ограничение, то оно проверяются до выполнения оператора модификации данных и если ограничение нарушено, ни действие, ни триггер выполнены не будут. Триггер INSTEAD OF срабатывает до проверки ограничений и в случае их нарушения действия, выполненные триггером, откатываются.
На каждую таблицу может быть задано несколько триггеров каждый из которых может срабатывать либо на какое-то определенное, либо сразу на несколько действий по изменению информации в таблице.
Несмотря на то, что триггер задается только к одной таблице, в теле триггера можно выполнять обращение к данным и изменение данных в других таблицах, даже из других баз данных
Любые изменения настроек сервера и смена текущей базы данных применяются только в течение работы триггера.
Срабатывание триггеров может быть вложенным. Например, один триггер может изменять данные в другой таблице, что вызывает срабатывание триггера, заданного для нее. Допускается 32 уровня вложенности.
Триггеры могут быть рекурсивными. Триггер может изменять данные в таблице, на которую он задан, что приведет к его повторному срабатыванию и так далее. Допускается также 32 уровня рекурсивной вложенности. Рекурсия может быть не только прямой, но и опосредованной, например, когда триггер изменяет данные в другой таблице, что вызывает срабатывание триггера, заданного для нее, который, в свою очередь изменяет данные в первой таблице и вызывает повторное срабатывание первого триггера. В случае превышения допустимого уровня вложенности выполнение триггера прерывается, а все действия отменяются.
Дата добавления: 2018-08-06; просмотров: 407; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!