НОВЫЙ КУРС БД 2013

4.3Хранимые процедуры

4.3.1Общие сведения о хранимых процедурах.

Хранимая процедура – это специальный пакет, который хранится в базе данных, в специальном месте. Хранимая процедура представляет собой аналог функции в языках программирования, т.е. она может иметь входные и выходные параметры, а также возвращаемое значение. Синтаксис создания хранимой процедуры приведен ниже:

CREATE PROCEDURE | PROC <имя процедуры> [@<параметр 1 > <тип данных> [= значение по умолчанию 1], [@<параметр 2 > <тип данных> [= значение по умолчанию 2],

…

[@<параметр N > <тип данных> [= значение по умолчанию N] WITH

RECOMPILE | ENCRIPTION | EXCEUTE AS {CALLER | SELF | OWNER | <’имя пользователя’>}

]

[FOR REPLICATION] AS

Код процедуры

[RETURN]

Давайте разберем этот, довольно большой код. Создание любого объекта базы данных как всегда предзнаменует оператор CREATE, далее идет тип создаваемого объекта – в нашем случае это PROCEDURE или просто PROC, опять же какой вариант использовать зависит от предпочтений пользователя. Далее идет список параметров (всего их может быть не больше 2100 штук), которые будут передаваться в процедуру, указываются они как список переменных с указанием типа, затем идет не обязательное значение по умолчанию и слово OUT (или OUTPUT), которое указывает, что параметр процедуры является выходным. Рассмотрим следующие параметры создания процедуры:

RECOMPILE. Показывает, что компонент Database Engine не кэширует план выполнения процедуры, и что процедура компилируется во время выполнения. Этот параметр нельзя использовать, если указан аргумент FOR REPLICATION.

ENCRYPTION. Показывает, что SQL Server выполнит шифрование исходного текста инструкции CREATE PROCEDURE. Результат шифрования не виден непосредственно ни в одном представлении каталога SQL Server. Пользователи, не имеющие доступа к системным таблицам или файлам баз данных, не смогут получить скрытый текст. Однако этот текст будет доступен привилегированным пользователям, которые либо смогут обращаться к системным таблицам через выделенное административное соединение (DAC), либо будут иметь непосредственный доступ к файлам баз данных. Кроме того, пользователи, имеющие право на подключение отладчика к серверному процессу, могут получить дешифрованный текст процедуры из памяти во время выполнения.

EXECUTE AS. Определяет контекст безопасности, в котором должна быть выполнена хранимая процедура из указанного списка.

o CALLER. Указывает, что инструкции, содержащиеся в модуле, выполняются в контексте пользователя, вызывающего этот модуль. Пользователь, выполняющий модуль, должен иметь соответствующие разрешения не только на сам модуль, но также и на объекты базы данных, на которые имеются ссылки из этого модуля.

o SELF эквивалентно EXECUTE AS user_name, где указанный пользователь — это тот, кто создает или изменяет модуль. Фактический идентификатор пользователя, создающего или

изменяющего	модуль, хранится	в	столбце execute_as_principal_id	в	представлении
каталога sys.sql_modules или sys.service_queues.

81

o OWNER. Указывает, что инструкции, содержащиеся в модуле, выполняются в контексте текущего владельца этого модуля. Если для модуля не определен владелец, то подразумевается владелец схемы модуля.

o ’ИМЯ ОЛЬЗОВАТЕЛЯ’. Указывает, что инструкции, содержащиеся в модуле, выполняются в контексте пользователя, указываемого аргументом user_name. Разрешения на объекты, на которые ссылается модуль, проверяются для пользователя user_name.

Пользователь должен присутствовать в текущей базе данных и не должен относиться к учетной записи группы. В качестве аргумента ’имя пользователя’ нельзя указывать роль, сертификат, ключ или встроенную учетную запись (например, NT AUTHORITY\LocalService, NT AUTHORITY\NetworkService или NT AUTHORITY\LocalSystem). Идентификатор пользователя контекста выполнения хранится в

метаданных, его можно получить из столбца	execute_as_principal_id	представления
каталога sys.sql_modules или sys.assembly_modules.

FOR REPLICATION. Указывает, что хранимые процедуры, созданные для репликации, не могут выполняться на подписчике. Хранимая процедура, созданная с параметром FOR REPLICATION, используется как процедура-фильтр и выполняется только во время процедуры репликации. Если указан аргумент FOR REPLICATION, параметры не могут быть объявлены. Параметр RECOMPILE не учитывается для процедур, созданных с аргументом FOR REPLICATION. Процедура с параметром FOR REPLICATION будет иметь в представлении sys.objects и sys.procedures объектный тип RF.

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

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

CREATE PROCEDURE spSelectMan

AS

SELECT * FROM Mans

Эта процедура не имеет параметров и просто возвращает все строки таблицы Mans. Давайте посмотрим на имя процедуры – spSelectMan, префикс sp – это общепринятое сокращение, которое обозначает Stored Procedure, т.е. хранимая процедура. Мы можем присваивать любое имя для процедур, но лучше пользоваться префиксом sp для обозначения имен процедур. Но вернемся к нашей процедуре, как теперь ее использовать? Для этого нам нужно вспомнить оператор EXEC, который исполняет инструкции, подаваемые в него в качестве параметра. Так вот в качестве параметра нам просто нужно указать имя нашей процедуры:

EXEC spSelectMan

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

ALTER PROCEURE spSelectMan @id int

AS

SELECT Name FROM Mans WHERE id = @id

82

Вызов процедуры может выглядеть так:

Exec spSelectMan 1

или

spSelectMan 1

или даже так spSelectMan @id=1

SQL Server предоставляет множество возможностей по синтаксису использования хранимых процедур, какой вариант использовать – дело вкуса. Поясним немного что мы сделали: изменили код процедуры, используя оператор ALTER. При этом добавили параметр с именем @id, типом данных int, а затем мы изменили и сам код процедуры таким образом, чтобы проверять столбец id на соответствие передаваемому параметру @id.

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

ALTER PROC spInsertMan @N varchar(30),

@DF datetime = Null AS

INSERT INTO Mans

(Name, InsertDate) Values

(@N,@DF)

EXEC spInsertMan ‘Адам’

--при этом в поле InsertDate будет вставлено значение Null

--если мы вызовем с такими параметрами как ниже:

EXEC spInsertMan ‘Ева’ ’01.01.01’

--то в поле дата будет вставлена указанная дата

Однако при использовании параметров в хранимых процедурах есть и подводные камни, например, если мы захотим вместо значения Null использовать текущую и дату, используя функцию GETDATE, и напишем следующий код:

CREATE PROC spInsertMan @N varchar(30),

@DF datetime = GETDATE AS

INSERT INTO Mans

(Name, InsertDate) Values

(@N,@DF)

Хотя синтаксически написано все верно, СУБД все же выдает такое сообщение об ошибке:

Server: Msg 241, Level 16, State 1, Procedure spInsertMan, Line 0

Syntax error converting datetime from character string.

83

Причина этой ошибки в том, что значение параметра по умолчанию можно задавать только константами! Запрещается использовать функции и другие процедуры. Возникает вопрос, - как тогда задать изменяющееся по умолчанию значение? Ответ довольно прост, описать логику этого изменения внутри самой процедуры:

CREATE PROC spInsertMan @N varchar(30),

@DF datetime = Null AS

INSERT INTO Mans

(Name, InsertDate) Values

(@N,ISNULL(@DF,GETDATE))

Поясним смысл созданного нами кода: по умолчанию дата принимает значение Null, затем при вставке в таблицу проверяется указал ли пользователь параметр @DF, т.е. если он равен Null, то дата указана не была и в таблицу вставится текущая дата, посредством GETDATE.

Помимо того, что мы можем передавать параметры процедуре мы можем их еще и получать. Кроме того процедуры позволяют принимать в качестве параметров переменные. Эти два факта дают множество возможностей при создании процедур. Например:

CREATE PROC spInsertMan @N varchar(30),

@DF datetime = Null AS

INSERT INTO Mans

(Name, InsertDate) Values

(@N,ISNULL(@DF,GETDATE))

GO

DECLARE @D datetime SET @D = GETDATE

EXEC spInsertMan ‘Ева’ @D

Давайте теперь рассмотрим, как применять параметры с пометкой OUT, т.е. выходные параметры. Выходные параметры, как правило, используется в тех случаях, когда нужно отследить результат какой-нибудь процедуры, т.е. после выполнения этой процедуры считать значения параметра и по этому значению сделать какие-либо действия. Например, пусть после вставки в таблицу Mans, нам нужно знать id вставленной строки. Конечно мы можем использовать системную переменную @@Identity, однако как уже было сказано выше она способна хранить только одно значение – последнее вставленное в таблицу. Поэтому мы будем использовать выходные параметры:

ALTER PROC spInsertMan @N varchar(30),

@DF datetime = Null, @InsertedID int OUT AS

INSERT INTO Mans ([Name], InsertDate) Values (@N,ISNULL(@DF,GETDATE())) SET @InsertedID = @@Identity

SELECT @InsertedID as 'ВставленныйИдентификатор'

GO

84

DECLARE @InsId int

EXEC spInsertMan @N = 'Ева', @InsertedID = @InsId OUT

SELECT * FROM Mans where id = @InsId

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

EXEC spInsertMan @N = 'Ева', @InsertedID = @InsId OUT

Если мы забудем это слово, СУБД не сообщит об ошибке, но результат выполнения будет совершенно иным! Параметр будет передан как не выходной, и в результате мы получим пустой результат, поскольку переменная @InsId меняться не будет. Этот момент является ключевым! НЕЛЬЗЯ ЗАБЫВАТЬ СТАВИТЬ OUT ВО ВРЕМЯ ВЫЗОВА ПРОЦЕДУРЫ!

Оператор Return

Помимо выходных параметров, указываемых пользователем, у процедуры есть еще один выходной параметр – это возвращаемое значение. Так называемое возвращаемое значение (Return Value) возвратить ноль, если процедура выполнилась верно. Однако пользователь может сам указывать какое значение вернуть при помощи оператора Return:

Return <целое число>

Если во время вызова процедуры, она будет приравнена какой-либо переменной, то эта переменная будет хранить возвращаемое значение этой процедуры. Давайте рассмотрим пример, чтобы было белее понятно:

CREATE PROC spInsertMan @N varchar(30),

@DF datetime = Null AS

INSERT INTO Mans (Name, InsertDate) Values (@N,ISNULL(@DF,GETDATE)) Return 100

GO

DECLARE @D datetime, @RV int

SET @D = GETDATE

EXEC @RV = spInsertMan ‘Ева’, @D PRINT @RV

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

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

CREATE PROC spNewMan @N varchar(30) AS

INSERT INTO Mans (Name, InsertDate) Values (@N,NULL)

85

Return 0

--код указанный ниже оператора Return никогда не выполнится

INSERT INTO Mans (Name, InsertDate) Values (@N,NULL) PRINT ‘это сообщение никогда не будет показано’

GO

DECLARE @RV int

EXEC @RV = spInsertMan ‘Ева’

На этом общее описание процедур мы завершим и рассмотрим их особенности использования при обработке ошибок, защите данных, а также рассмотрим вопросы повышения производительности за счет использования хранимых процедур.

4.3.2 Обработка ошибок в процедурах. Операторы Try…Catch

Вернемся к операторам Try…Catch, которые мы рассматривали в пункте динамическое программирование. Мы рассматривали простой пример обработки ошибок:

USE TEST_DB

BEGIN TRY

INSERT INTO Mans(name, job) VALUES (‘Неизвестный’, 999)

END TRY

BEGIN CATCH

PRINT ‘ роизошла ошибка во время вставки в таблицу Mans’

END CATCH

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

Итак, начнем с того, что не все ошибки, возникающие в блоке Try, отлавливаются (т.е. передается управление в блок Catch). Отлавливаются только ошибки, имеющие степень серьезности от 11 – 19. Информация об уровнях серьезности приведена ниже:

Степень
серьезности		Описание
ошибки
1-10		Информационные сообщения, содержащие код ошибки. Не прерывают выполнение
		процедуры и не активизируют ошибку в клиентской программе.
11-16		При получении этих сообщений выполнение процедуры завершается и в клиентской
		программе возникает ошибка.
17		Эта ошибка указывает на недостаток ресурсов, например, если не хватает памяти,
		чтобы выполнить запрос.
18-19		Эти степени уже представляют проблему. При возникновении таких ошибок, как
		правило, требуется вмешательство системного администратора. При этом в журнал
		событий Windows будет записано сообщение об этой ошибке.
20-25		Ошибки выше 20 степени являются критическими, они вызывают крах соединения с
		базой. Ошибки этого уровня являются неисправимыми. При их возникновении также
		записывается лог-файл в системе Windows.
Для того, чтобы определить степень серьезности ошибки нужно вызвать специальную
функцию –	ERROE_SEVERITY(). Ниже приведена информация по функциям, применяемым для
	86

получения информации об ошибках (таблица из книги Программирование баз данных MS SQL Server 2005. Роберт Виейра).

Кроме того, информация о номере ошибки храниться в системной переменной @@ERROR. Информацию, о том, что значит, конкретный номер ошибки можно найти в системной таблице sysmessages в базе данных master. Например, номер ошибки 547 означает, что нарушено требование ограничения. О чем в таблице sysmessages в столбце description есть запись - %ls statement conflicted with %ls %ls constraint '%.*ls'. The conflict occurred in database '%.*ls', table '%.*ls'%ls%.*ls%ls. Именно это сообщение выводит SQL, подставляя соответствующие данные вместо флагов ls. Вроде бы мы определились с тем, как и в каких случаях осуществляется перехват ошибок. Давайте напишем процедуру, которая будет осуществлять перехват ошибок:

USE TEST_DB

CREATE PROC spInsertManE @N varchar(30),

@DF datetime = Nul,l @InsertedNum int OUT AS

BEGIN TRY

87

INSERT INTO Mans

(Name, InsertDate) Values

(@N,ISNULL(@DF,GETDATE)) END TRY

BEGIN CATCH DECLARE @ErrNumber int, @ErrState int, @ErrLine int, @ErrSeverity int,

@ErrMsg varchar(4000) SET

@ErrNumber = ERROR_NUMBER(),

@ErrState	= ERROR_STATE(),
@ErrLine	= ERROR_LINE(),
@ErrSeverity = ERROR_SEVERITY(),
@ErrMsg	= ERROR_MESSAGE()

IF @ErrNum = 547 BEGIN

Print ‘Ошибка вставки данных’

Print ‘Сработало ограничение в таблице Mans ’

Print ‘Скорее всего дата или имя человека лежит вне диапазона переменных’

END

ELSE

BEGIN

Print ‘Неизвестная ошибка’

Print ‘Ошибка имеет код - ’ + CAST(@ErrNumber as varchar) + ‘ степень ошибки ’ + CAST(@ErrSeverity as varchar)

Print ‘ роверьте правильность вводимых данных’

END

Return @ ErrNum END CATCH

SET @InsertedNum = @@IDENTITY RETURN 0

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

USE TEST_DB

DECLARE @WasErr int, -- будет хранить код ошибки

@Insert

EXEC @WasErr = spInsertManE ‘Адам’,’01.01.01’,@Insert OUT IF @WasErr <> 0

BEGIN

Select ‘Ошибка’ = case @WasErr when 100 then ‘Ошибка номер 100’ when 200 then ‘Ошибка номер 200’ else ‘Ошибка неизвестна’

END

Наверное, вы уже поняли, насколько мощна система обработки ошибок в SQL Server. Однако и это еще не все. Мы можем сами сгенерировать свой механизм обработки ошибок, задав

88

логику, номер ошибки, уровень серьезности и прочие параметры. Такая необходимость возникает довольно часто в приложениях, где с точки зрения СУБД ошибки нет, но такой ситуации по условиям предметной области быть не должно. Например, остаток на счете не может быть меньше 100 рублей. Стандартными средствами такую ошибку не отловить. Однако в SQL есть средство, которое позволяет инициировать процесс обработки ошибок вручную:

RAISERROR (<номер ошибки> | <строка сообщения>, <уровень серьезности>, <статус>

[аргумент1,… аргументN] [WITH option [опции]])

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

RAISERROR (‘Это сообщение об ошибке’, 1, 1)

При выполнении данного кода SQL выдаст такое сообщение:

Msg 50000, Level 1, State 50000

Это сообщение об ошибке

Это означает, что система сама присвоила номер нашей ошибке равный 50000. Это по умолчанию присваиваемое значение. Его можно изменить указав в качестве аргумента WITH option – WITH SETERROR. Однако прежде чем указывать конкретный номер ошибки ее нужно поместить в таблицу ошибок. Т.е. нам нужно зарегистрировать нашу ошибку за этим номером в таблице sysmassage базы master. Для того чтобы это сделать нужно вызвать процедуру:

sp_addmassage

[@msgnum=] <идентификатор ошибки>, [@severity=] <степень ошибки>,

[@msgtext=] <текст ошибки>, [@lang=] <язык программирования>, [@with_log=] <TRUE | FALSE>,

[@replace=] <текст ошибки при замене существующей ошибки>

Создать свое сообщение об ошибке можно вызвав указанную выше процедуру с подобными параметрами:

sp_addmassage @msgnum= 60000, @severity= 5,

@msgtext= ‘Текст ошибки’

После регистрации можно вызвать ее таким образом:

RAISERROR (60000, 1, 1) WITH SETERROR

Удалить ошибку из базы можно вызвав процедуру sp_dropmessage <идентификатор ошибки>

Параметр With Option, может принимать всего 3 значения, одно из которых мы уже рассмотрели – WITH SETERROR. Другие два возможных значения – WITH LOG, которое говорит СУБД, что сообщение должно быть внесено в журнал ошибок СУБД и системы Windows. Второй параметр – WITH NOWAIT означает немедленно уведомить клиентское приложение о возникновении ошибки.

89

4.3.3 Дополнительные сведения о процедурах.

Мы многое сказали о хранимых процедурах, однако упустили пару важных моментов. Момент первый – в коде хранимой процедуры мы можем вызывать на выполнение другую хранимую процедуру. В версии 2005 имеется возможность делать 32-кратные вложения. Однако я рекомендую ограничиваться максимум 2-3х кратным вложением, поскольку читабельность кода падает очень сильно.

Второй момент касается вопроса производительности. Хранимые процедуры выполняются на сервере, значит, по сетевым каналам передается минимум информации. Так как процедура находится на сервере, ее код уже скомпилирован и оптимизирован, по сравнению с динамическими запросами, которые мы будем посылать с клиента. Однако не все так просто как кажется на первый взгляд. Разница при использовании хранимой процедуры вместо динамического запроса с точно таким же кодом как в процедуре, составляет около секунды. На больших объемах данных эта разница становится особенно заметной. Теперь ложка дегтя – если внутри хранимой процедуры идет создание каких либо объектов или используется динамическое программирование посредством вызова процедуры exec. То план выполнения этой процедуры строится, еще до ее выполнения! Т.е. еще до создания объектов, которые в ней описаны, а это значит, что план выполнения будет не оптимальным и может занять даже больше времени, нежели динамический запрос. Именно для таких процедур придумана опция WITH RECOMPILE, рассмотренная в начале описания хранимых процедур. Она производит план выполнения запроса два раза – 1 раз при первом срабатывании, второй раз после создания всех объектов этой процедурой. Мы, конечно, теряем часть преимущества в скорости при этом, но на 3-ий раз план выполнения будет уже в кэше и он будет оптимальным!

Третий момент, в связи с появление платформы .NET и включением поддержки языков этой платформы в SQL Server (начиная с версии 2005), писать процедуры теперь можно на любом NET-совместимом языке. Раньше для решения определенного круга задач, приходилось писать отдельные динамически подключаемые библиотеки на низкоуровневом языке, таком как «Си». С появлением же платформы .NET надобность в этом отпала. И в силу большой интеграции платформы NET с операционной системой и другими продуктами от Microsoft необходимость в отдельно подключаемых хранимых процедурах снизилась практически до нуля.

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

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

4.4 Пользовательские функции

Пользовательские функции во многом напоминают хранимые процедуры, т.е. представляют собой последовательность операторов TSQL, хранятся как отдельные объекты в базе, заранее оптимизированы и откомпилированы. Разница с хранимыми процедурами в том, что пользовательские функции по-другому осуществляют возврат результатов и по-другому работают с параметрами. А именно в пользовательских функциях запрещено использовать выходные значения параметров, т.е. использовать слово OUT. Возвращаться может только одно значение, но при этом в отличии от хранимых процедур, это значение может быть практически любого типа, а не только типа int. Еще больше возможностей дают пользовательские функции поскольку позволяют возвращать набор данных в виде таблицы, с которой можно выполнять различные

90