10. Лекция: Хранимые процедуры и функции. Типы параметров, синтаксис описания формальных параметров

SQL Server

Хранимая процедура - это подпрограмма, состоящая изSQL операторов и команд T-SQL. Хранимая процедурасохраняется на сервере и выполняется по команде пользователя или вызывается из блока T-SQL. План выполнения процедуры подготавливается во время запуска процедуры, поэтому собственно выполнениепроцедуры происходит очень быстро. Хранимая процедура может:

• Содержать параметры (аргументы);

• Вызывать другие процедуры;

• Возвращать свой статус вызывающей процедуре или модулю T_SQL, указывающий на успешное окончание или ошибку, и в случае ошибки на ее причину;

• Возвращать значения параметров вызывающей процедуреили модулю T-SQL ;

Синтаксис для создания хранимой процедуры:

create procedure [владелец.]название_процедуры[;номер] [

[(] @название_параметра тип_данных [= default] [output]

[, @название_параметра тип_данных [= default] [output]]...[)]] [with

recompile]

as sql_операторы

Оператор execute:

[execute] [@return_status =]

[[[сервер.]база_данных.]владелец.]название_процедуры[;номер]

[[@название_параметра = ] значение |

[@название_параметра = ] @переменная [output]

[,[@название_параметра = ] значение |

[@название_параметра = ] @переменная [output]...]]

[with recompile]

Параметры

Параметр - это аргумент хранимой процедуры. Один или несколько параметров могут быть объявлены в операторе создания процедуры. Значение каждого параметра, объявленного воператоре create procedure, должно указываться пользователем в момент вызова процедуры.

Названиям параметров должен предшествовать символ "@", а сами эти названия должны соответствовать правилам, установленным дляидентификаторов. Для всех параметров должен быть указан системный или пользовательский тип данных, и если необходимо длина этоготипа данных. Названия параметров являются локальными по отношению к содержащей их процедуре; такие же названия можно использовать для параметров в другой процедуре.

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

CREATE proc dbo.tran_Begin /*

* Инициализация транзакции

* Если процедура выполняется внутри транзакции, то создается savepoint

* Иначе инициализируется транзакция

* На выходе передается null для транзакции и имя точки сохранения для savepoint

*/

(@trName DObjectName output, @explicitSavepointNameFlag DLogical = 0)

as

declare @d Datetime

begin

-- return 0

if @@trancount = 0

begin

select @trName = null

begin transaction

end

else

begin

-- если не задано имя и не установлен флаг использования переданного имени, то генерим имя

if @explicitSavepointNameFlag = 0 or @trName is null

begin

select @d = getdate()

select @trName = 'tr_'+ISNULL(convert(varchar, @@nestlevel), '')+'_'+ISNULL(convert(varchar(50),

rand(datepart(mm, @d)*100000+datepart(ss, @d)*1000+datepart(ms, @d))), '')

end

save tran @trName

end

if @@error <> 0

begin

-- ошибка создания транзакции

return 5

end

return 0

end

CREATE proc dbo.tran_Commit /*

* Подтверждение транзакции

* Работает в паре с tran_Begin

* на вход передается имя точки сохранения (транзакции), возвращенное tran_Begin

* Если вызов идет из транзакции и @trName равно null, то выполняется commit,

* иначе ничего не делается

*/

(@trName DObjectName)

as

begin

if @@trancount > 0 and @trName is null

begin

commit

if @@error <> 0

begin

-- ошибка подтверждения транзакции

return 6

end

end

return 0

end

CREATE proc dbo.tran_Rollback /*

* Откат транзакции

* Работает в паре с tran_Begin

* на вход передается имя точки сохранения (транзакции), возвращенное tran_Begin

* Если вызов идет из транзакции и @trName равно null, то выполняется откат всей транзакции,

* иначе выполняется откат до точки сохранения @trName

*/

(@trName DObjectName)

as

begin

if @@trancount > 0

begin

if @trName is null

rollback tran

else

rollback tran @trName

if @@error <> 0

begin

-- ошибка отката транзакции

return 7

end

end

return 0

end

Листинг 10.1. (html, txt)