Создание хранимой процедуры

Для создания хранимой процедуры в среде SQL Server используется оператор CREATE PROCEDURE, синтаксис которого имеет следующий вид:

В приведенном выше синтаксисе оператора создания хранимой процедуры вместо элемента имя процедуры нужно указать имя хранимой процедуры.

Кроме того, в приведенном выше синтаксисе элемент номер означает необязательное целое число, которое можно использовать для группировки хранимых процедур под одним именем, чтобы их можно было удалить с помощью одного оператора. Нетрудно догадаться, что синтаксическая единица параметр означает возможность передачи в хранимую процедуру необязательных параметров. При использовании опции WITH RECOMPILE план запроса хранимой процедуры не хранится в процедурном кэше. Вместо этого при каждом выполнении процедуры генерируется новый план запроса. Опции FOR REPLICATION и WITH RECOMPILE совместно использовать нельзя.

При использовании опции WITH RECOMPILE теряется преимущество плана скомпилированного запроса. Однако скомпилированная процедура выполняется все же быстрее обычного запроса, поскольку текст SQL уже проанализирован и дерево запроса построено. Но более важно то, что в случае применения опции WITH RECOMPILE вы теряете меньше времени (которое уходит на создание плана запроса), чем при использовании некорректного плана запроса, на обработку которого могут уйти минуты или даже часы.

Под элементом Оператора SQL понимаются операторы SQL или операторы управления программой, составляющие хранимую процедуру.

Пример 1. Создание хранимой процедуры с именем usp_show_authors, которая выбирает все столбцы и строки из таблицы authors.

CREATE PROCEDURE usp show authors

AS

SELECT * FROM authors

Хранимые процедуры создаются в той базе данных, в которой была выполнена команда CREATE PROCEDURE. Исключением являются временные хранимые процедуры, которые создаются в базе данных tempdb.

Для запуска хранимой процедуры наберите ее имя в окне запросов программы Microsoft SQL ServerQueryAnalyzer и щелкните на зеленой стрелке. Если же хранимая процедура не является первым оператором в строке запроса, для ее запуска используйте оператор EXECUTE.

Пример 2. Выполнение хранимой процедуры

EXECUTE uspshowauthors

А вот сокращенный формат, который, как правило, и используется:

ЕХЕС uspshowauthors

Операторы управления

 GОТО метка

 BEGIN...END

 IF...ELSE

 WAITFOR

 RETURN

 WHILE

 BREAK

 CONTINUE

 Оператор DECLARE

 Оператор PRINT

 RAISERROR

Оператор DECLARE

С помощью оператора DECLARE можно создавать переменные, используя стандартные типы данных SQL Server. Переменные, определяемые оператором DECLARE, должны начинаться с символа @. В одном операторе DECLARE можно объявить сразу несколько переменных, разделяя их запятыми. Синтаксис оператора DECLARE имеет следующий вид:

DECLARE @переменная 1 тип данных

[,@переменная 2 тип данных...]

Пример 3. Создание трех переменных различных типов данных.

DECLARE @count INT, @current date DATETIME

DECLARE @My_Hessage VARCHAR(255)

Чтобы инициализировать или установить значение переменной, создаваемой с помощью оператора DECLARE, используйте ключевое слово SELECT.

Пример 4. Устанавливание значения переменной @count равного 100

Select @count = 100

Пример 5. Устанавливание значения переменной @count равного общему числу строк в таблице authors, принадлежащей базе данных pubs

SELECT @count = count(*) FROM pubs.authors

Оператор GOTO

Оператор GOTO выполняет ту же самую функцию, что и во многих других языках программирования, таких как С и VisualBasic. Он передает управление соответствующей метке хранимой процедуры, с которой и будет продолжено выполнение операторов.

Оператор GOTO имеет следующий синтаксис:

GOTO метка

Пример 6. Оператор GOTO пропускает оператор SELECT и выполняет оператор UPDATE:

GOTO do_update SELECT * from authors

do_update:

UPDATE authors

SET au_Iname = "Spenik"

WHERE state = 'VA'

При определении метки помните, что ее имя должно оканчиваться двоеточием. А при ссылке на метку через GOTO двоеточие опускается.

Оператор BEGIN...END

Операторы BEGIN и END считаются блочными, поскольку они объединяют набор операторов SQL и операторов языка управления программой в единое целое. Операторы BEGIN и END часто используются с блоками IF...ELSE.

Синтаксис операторов BEGIN и END имеет следующий вид:

BEGIN

{Операторы SQL | блок операторов}

END

Оператор IF...ELSE

С помощью операторов IF...ELSE можно проверять условия и в зависимости от результатов проверки выполнять соответствующие операторы SQL. Оператор IF проверяет выражение типа BOOLEAN, которое возвращает значение TRUE (ИСТИНА) или FALSE (ЛОЖЬ). Если возвращаемое значение равно TRUE, выполняется блок операторов или единственный оператор, который следует сразу за оператором IF. Если же возвращаемое значение равно FALSE, выполняется необязательный оператор ELSE.

Синтаксис оператора IF...ELSE выглядит следующим образом:

IF Выражение типа BOOLEAN

{Операторы SQL | блок операторов}

(ELSE [ Выражение_ типа_ВООLEAN

{Операторы SQL | блок операторов}]

Пример 7. Использование операторов IF и IF...ELSE как одиночным оператором SQL, так и с блоком операторов.

IF ecount = 0

SELECT * FROM authors

ELSE

SELECT * FROM titles

IF @Total!= 0

BEGIN

SELECT count(*) FROM authors

SELECT count(*) FROM titles

END

ELSE

BEGIN

SELECT * FROM authors

SELECT * FROM titles

END

Оператор WAITFOR

Оператор WAITFOR формирует задержку, в течение которой хранимая процедура находится в состоянии ожидания. Величина задержки определяется либо заданным интервалом времени (с помощью опции DELAY), либо интервалом до наступления заданного времени суток (с помощью опции TIME). По истечении времени задержки выполнение хранимой процедуры продолжается.

Синтаксис оператора WAITFOR имеет следующий вид:

WAITFOR {DELAY 'интервал времени' | TIME 'время суток'}

Максимальное время задержки, формируемое оператором WAITFOR, составляет 24 часа. В опциях TIME и DELAY используется формат чч:мм:сс.

Пример 8. формирование 10-секундной задержки.

WAITFOR DELAY '00:00:10'

Пример 9. формирование состояния ожидания до 11 часов утра.

WAITFOR TIME '11:00:00'

Оператор RETURN

Оператор RETURN выполняет выход из хранимой процедуры и обеспечивает возврат в вызывающую процедуру или приложение. Синтаксис оператора RETURN выглядит следующим образом:

RETURN [Целое значение]

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

exec @status = имя_процедуря

Пример 10. Представление хранимой процедуры uspreturn, которая возвращает целое значение в вызывающую процедуру или приложение и хранимой процедуры usp_call, которая вызывает хранимую процедуру uspreturn и проверяет возвращаемое значение.

CREATE PROCEDURE usp_return

AS

DECLARE @gret_val INT

SELECT @gret_val = 0

RETURN @gret_val

go

CREATE PROCEDURE usp_call

AS

DECLARE @Status INT

EXEC @Status = usp_returnif(@Status = 0)

PRINT "Возвращаемое значение равно нулю"

go

При написании хранимых процедур в среде SQL Server нулевое значение (0) используется для индикации факта успешного завершения. Отрицательные значения свидетельствуют о возникновении ошибки. Коды возврата, находящиеся в диапазоне от -1 до -99, зарезервированы для SQL Server.

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

Операторы WHILE, BREAK и CONTINUE

С помощью оператора WHILE можно циклически выполнять один оператор SQL или блок операторов до тех пор, пока соблюдается заданное условие.

Оператор BREAK вызывает выход из цикла WHILE, а оператор CONTINUE — безусловный переход к началу цикла, пропуская все операторы, стоящие за оператором CONTINUE.

Эти операторы имеют следующий формат:

WHILE Выражение типа BOOLEAN [оператор SQL | блок операторов} [BREAK | CONTINUE]

Оператор PRINT

С помощью оператора PRINT можно возвратить сообщение вобработчик сообщений клиента. Сообщение может иметь длину до 1 024 символов. Оператор PRINT имеет следующий синтаксис:

PRINT 'любой ASCII-текст' | локальная переменная | @@FUNCTION | строковое выражение

Пример 11. Вывод на печать значение локальной или глобальной переменной, преобразованной сначала в тип CHAR или VARCHAR.

DECLARE @msg VARCHAR(255), @count INT

SELECT @count = 0

PRINT "Начало процедуры"

WHILE fcount< 5

BEGIN

SELECT @count = gcount + 1

SELECT @msg = "Значение переменной @count равно " + STR(@count)

PRINT @msg

END

SELECT @msg = "Процедура подходит к концу. "

SELECT @msg = @msg + "Значение переменной @count равно " + STR(@count)

PRINT @msg

GO

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

Оператор RAISERROR

Оператор RAISERROR устанавливает системный признак SQL Server, чтобы обозначить факт возникновения ошибки, и посылает сообщение об ошибке клиенту. Синтаксис оператора RAISERROR имеет следующий вид:

RAISERROR ({Номер_ сообщения | Строка_сообщения}, серьезность, состояние [,аргуыент1[,аргумент 2]]) [WITH (LOG | NOWAIT | SETERROR)]

Опцию WITH LOG используйте для записи сообщения об ошибке в журнал ошибок SQL Server и журнал приложений Windows NT.

Комментарии

Комментарии в хранимой процедуре соответствуют стандарту языка программирования С или стилю ANSI.

Комментарии начинаются с сочетания символов /* и заканчиваются теми же символами, но в обратном порядке: */.

Все, что находится между парами символов /* и */, относится к комментариям. Комментарии, которые начинаются с символов –, соответствуют стандарту ANSI.

Пример 12. Примеры комментариев.

/* Это одна строка комментария */

/* Этот

комментарий

занимает

несколько строк */

/*

** Это тоже комментарий; я думаю, что звездочки

** облегчают его чтение.

*/

*Это комментарий в стиле ANSI.

20. Понятие и применение триггеров

Ответ:

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

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

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

SQL Server поддерживает два различных типа триггеров:

· триггеры AFTER

· триггеры INSTEAD OF.

Триггеры AFTER вызываются после выполнения команды, которой они назначены, а триггеры INSTEAD OF вызываются вместо команды.

Триггеры AFTER вы можете использовать для команд INSERT, UPDATE и DELETE. Триггеры AFTER можно создавать только для таблиц, но не для представлений. Для каждой из этих трех команд могут быть установлены несколько триггеров. С другой стороны, один триггер может быть применен для любой комбинации этих трех команд.

Триггер AFTER вызывается после того, как выполнены все операции по обработке ограничений низкого уровня, и не будут вызваны в случае нарушения ограничения.

Триггеры INSTEAD OF заменяют команду, для которой они объявлены. Подобно триггерам AFTER, можно определять триггеры INSTEAD OF для команд INSERT, UPDATE или DELETE. Один триггер может быть применен к нескольким командам.

Однако, в отличие от триггеров AFTER, можно создавать триггеры INSTEAD OF как для таблиц, так и для представлений, но для каждого действия над этой таблицей или представлением может быть создан только один триггер INSTEAD OF.

Триггеры INSTEAD OF несовместимы с каскадными изменениями связанных данных. Вы не можете объявить триггер INSTEAD OF DELETE или INSTEAD OF UPDATE для таблицы, внешний ключ которой затрагивается действиями удаления (DELETE) или модификации (UPDATE).

Когда триггер срабатывает и начинает выполняться, во время его выполнения существуют две специальные таблицы - INSERTED и DELETED. В них находятся записи, соответственно добавляемые или удаляемые. Если триггер вызывается из команды DELETE, таблица удаления будет содержать строки, которые были удалены из таблицы. При вызове из команды INSERT таблица вставки будет содержать копию новых (вставляемых) строк. Физически оператор UPDATE представляет собой последовательное выполнение команды удаления DELETE и вставки INSERT, так что таблица удаления будет содержать старые значения, а таблица вставки – новые значения. Вы можете обращаться к содержимому этих таблиц из триггера, но вы не можете изменять их.

Глобальная переменная @@ROWCOUNT указывает на число записей, участвующих в операциях с данными.

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

Даже, если операция реально не затрагивает ни одной записи, как, например, в случае DELETE FROM PAYS WHERE 1=2 триггер все равно сработает, @@ROWCOUNT будет равен нулю, а таблица DELETED пуста.

Формирование триггера выполняется с помощью команды CREATE TRIGGER. Синтаксискомандыприведенниже:

CREATE TRIGGER [owner.]trigger_name

ON [owner.]table_name

FOR | AFTER | INSTEAD OF {INSERT, UPDATE, DELETE}

[WITH ENCRYPTION]

AS sql_statements

Использованиефункции UPDATE

SQL Server предоставляет специальную функцию, UPDATE, которая может быть использована в триггере для определения, подвергся ли изменению определенный столбец в строке. Функция UPDATE имеет следующий синтаксис:

UPDATE (имя_столбца)

Функция UPDATE будет возвращать TRUE, если значения данных для указанного столбца были изменены командой INSERT или командойUPDATE.

Пример: Создадим триггер на обновление, добавление записей в таблицу Student, который помещает в поле lastEdit дату последнего редактирования (добавления) записи.

CREATE TRIGGER StudentLastEdit

ON Student

FOR INSERT, UPDATE

AS

UPDATE Student

SET lastEdit=getDate()

FROM Student, inserted

WHERE Student.idSt= inserted.idSt

print 'Дата последнего редактирования изменена'

Проверим работу триггера, выполним вставку данных в таблицу:

insert into Student (nameSt, sNameSt) values ('Анна', 'Иванова')

Результат выполнения запроса на вставку:

(строк обработано: 1)

Дата последнего редактирования изменена

(строк обработано: 1)

Пример: Создадим триггер на удаление, который запретит удалять студентов, уже имеющих номер зачетной книжки. В этом случае создается триггер INSTEAD OF, в теле триггера описаны действия, которые выполняются при попытке удалить строки из таблицы Student.

CREATE TRIGGER [dbo].[StudentDel]

ON [dbo].[Student]

INSTEAD OF DELETE

AS

DELETE FROM Student

WHERE idSt in (SELECT idSt FROM deleted WHERE numZach IS NULL)

Пример: Создадим триггер на изменение данных, который запрещает изменять дату зачисления.

CREATE TRIGGER StudUpdate

ON Student

AFTER UPDATE

AS

BEGIN

SET NOCOUNT ON;

IF UPDATE(dateZach)

BEGIN

RAISERROR('Нельзяизменятьдатузачисления',10,1)

ROLLBACK

END

END

GO

SET NOCOUNT { ON | OFF } - Запрещает вывод количества строк, на которые влияет инструкция Transact-SQL или хранимая процедура, в составе результирующего набора

RAISERROR(‘Сообщение’, степень_серьезности, состояние) – формирование сообщений об ошибках.

Параметр “Сообщение” – это текст, содержащий описание ошибки

Параметр “Уровень серьезности” – уровень серъезности ошибки. Значение от 0 до 18 может указать любой пользователь. Уровни серьезности от 19 до 25 могут быть указаны только членами предопределенной роли сервера sysadmin и пользователями с разрешениями ALTER TRACE. Для уровней серьезности от 19 до 25 требуется параметр WITH LOG..

Параметр «Состояние» - целое число от 0 до 255.