Управление хранимыми процедурами

Хранимые процедуры управляются посредством инструкций языка определения данных (DDL) CREATE, ALTER и DROP.

Общий синтаксис T-SQL кода для создания хранимой процедуры имеет следующий вид:

CREATE PROC | PROCEDURE <procedure_name>

[ <@parameter> <data_type> [ = <default> ] [ OUT | OUTPUT ] ] [,...n ]

AS

[ BEGIN ] <sql_statements> [ END ]

<procedure_option>::=

[ ENCRYPTION ]

[ RECOMPILE ]

[ EXECUTE_AS_Clause ]

Структура этого оператора соответствует основному синтаксису CREATE <Object Туре> <Object Name>, лежащему в основе любого оператора CREATE. Единственная отличительная особенность состоит в том, что в нем допускается использовать ключевое слово PROCEDURE или PROC. Оба эти варианта являются допустимыми: PROC является лишь сокращением от PROCEDURE.

Каждая процедура должна иметь уникальное в рамках базы данных имя (procedure_name), соответствующее правилам для идентификаторов объектов.

Процедуры могут иметь любое число входных параметров (@parametr) заданного типа данных (data_type), которые используются внутри процедуры как локальные переменные. При выполнении процедуры для каждого из объявленных формальных параметров должны быть переданы фактические значения. Или же для входного параметра может быть определено значение по умолчанию (default), которое должно быть константой или равняться NULL. В этом случае процедуру можно выполнить без указания значения соответствующего аргумента. Применение входных параметров необязательно.

Можно также указать выходные параметры (помеченные как OUTPUT), позволяющие хранимой процедуре вернуть одно или несколько скалярных значений в подпрограмму, из которой она была вызвана. При создании процедур можно задать три параметра. При создании процедуры с параметром ENCRYPTION SQL Server шифрует определение процедуры. При задании параметра RECOMPILE SQL Server перекомпилирует хранимую процедуру при каждом ее запуске. Параметр EXECUTE AS определяет контекст безопасности для процедуры.

В конце определения хранимой процедуры вслед за ключевым словом AS должно быть приведено непосредственно тело процедуры (sql_statements) в виде кода из одной или нескольких инструкций языка T-SQL.

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

Пример хранимой процедуры без параметров.

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

 

CREATE PROCEDURE spr_getOrders

AS

SELECT IdOrd, IdCust, OrdDate

FROM [Order]

WHERE (OrdDate >= '01.01.2010')

RETURN

 

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

EXEC spr_getOrders

Команда EXECUTE или сокращенно EXEC выполняет указанную хранимую процедуру.

В данном случае хранимая процедура вернет все строки из таблицы Order, в которых значение поля OrdDate больше 1 января 2010 года, в соответствии с содержащимся в нем запросом на выборку. Применение входных параметров. Хранимая процедура предоставляет определенные процедурные возможности (а если она применяется в инфраструктуре.NET, такие возможности становятся весьма значительными), а также обеспечивает повышение производительности, но в большинстве обстоятельств хранимая процедура не позволяет добиться многого, если не предусмотрена возможность передать ей некоторые данные, указывающие на то, какие действия должны быть выполнены с ее помощью. В частности основная проблема, связанная с предыдущей хранимой процедурой (spr_getOrders), состоит в ее статичности. Если пользователям потребуется информация о заказах за другой период времени, то эта процедура им не поможет. Поэтому необходимо предусмотреть возможность передачи в нее соответствующих входных параметров, которые позволили бы динамически изменять период выборки.

Параметры, передаваемые хранимой процедуре, перечисляются через запятую в инструкции CREATE (ALTER) PROCEDURE непосредственно после ее имени. При объявлении входного параметра необходимо указать имя параметра, тип данных и возможно значение по умолчанию. В общем случае объявление входного параметра имеет следующий вид:

@ parameter _ name [ AS ] datatype [= default | NULL ]

Правила определения входных параметров во многом аналогичны объявлению локальных переменных. Каждый из параметров должен начинаться с символа @. Для хранимой процедуры он является локальной переменной. Как и все локальные переменные, параметры должны объявляться с допустимыми встроенными или определяемыми пользователями типами данных СУБД SQL Server.

Значительные различия между объявлениями параметров хранимых процедур и объявлениями переменных начинают впервые обнаруживаться, когда дело касается значений, заданных по умолчанию. Прежде всего, при инициализации переменным всегда присваиваются NULL-значения, а на параметры это правило не распространяется. В действительности, если в объявлении параметра не предусмотрено заданное по умолчанию значение, то подразумевается, что этот параметр должен быть обязательным и что при вызове хранимой процедуры должно быть указано его начальное значение. Чтобы задать предусмотренное по умолчанию значение, необходимо добавить знак равенства (=) после обозначения типа данных, а затем указать применяемое по умолчанию значение. Благодаря этому пользователи получают возможность при вызове хранимой процедуры принимать решение о том, следует ли задать другое значение параметра или воспользоваться значением, предусмотренным по умолчанию.

В следующем примере хранимая процедура spr_getOrders дополняется двумя входными параметрами, позволяющими явно указать период выборки.

ALTER PROCEDURE [dbo].[spr_getOrders]

@dateBegin datetime,

@dateEnd datetime

AS