Хранимые процедуры создаются при помощи оператора create procedure, который имеет следующий синтаксис:

create proc[edure]   [schema_name.]proc_name

[{{Qparaml} typel [ varying]   [= default!]   [output])]  (,   ...} [with { recompile | encryption | execute as   'user_name' }} [for replication]

as batch i external name method_name;

Здесь schema_name- имя схемы, которой назначается создаваемая хранимая процедура; ргос_  - имя новой хранимой процедуры; Qparami - это имя параметра, a typel задает его тип данных. Параметр в хранимой процедуре имеет тот же самый логический смысл, что и локальная переменная в пакете. Параметры являются значениями, передаваемыми от вызвавшего объекта хранимой процедуре и используемыми в хранимой процедуре. Параметр defauiti задает необязательное значение по умолчанию соответствующего параметра. (Значением по умолчанию может быть также и null.)

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

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

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

По умолчанию оператор create procedure могут использовать только участники с фиксированной серверной ролью sysadmin и участники с фиксированными ролями базы данных dbowner и dbddladmin. Однако пользователи с этими ролями могут назначать эти привилегии другим пользователям с помощью оператора grant create procedure. (Обсуждение полномочий пользователя, фиксированных серверных ролей и фиксированных ролей базы данных.)

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

project.

 

 

Хранимая процедура increase_budget увеличивает бюджеты всех проектов на заданный процент, который указывается с использованием параметра @percent. В процедуре также определяется значение по умолчанию (5), которое будет использовано, если во время выполнения процедуры ей не передается никакой аргумент.

В отличие от «базовых» хранимых процедур, которые размещаются в текущей базе данных, возможно создание и временных хранимых процедур, которые всегда помещаются во временную системную базу данных tempdb. Вы можете создавать временную хранимую процедуру для исключения повторного выполнения группы операторов при соединении с базой данных. Вы можете создавать локальные или глобальные временные процедуры, задав перед именем процедуры одиночный символ # (#proc_name) для локальной временной процедуры и два символа # (например, ##proc_name) для глобальной временной процедуры. Локальная временная хранимая процедура может быть выполнена только пользователем, ее создавшим, и только во время того же соединения. Глобальная временная процедура может выполняться всеми пользователями, но только пока не завершилось последнее соединение, в котором она выполняется (обычно создателем процедуры).

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

[[exec[ute]]   [@return_status = ] [proc__name i  @proc_name_var\ {[[Qparameterl = ] value

i   [Qparameterl = ]  ^variable [output]]   |  default}... [with recompile];

Все опции оператора execute, за исключением return_status, имеют то же логическое значение, что и опции с теми же именами в операторе create procedure. Опция return_status является необязательной целочисленной переменной, которая хранит возвращаемый статус процедуры. Значение параметру может быть присвоено с использованием либо значения (value), либо локальной переменной (^variable). Порядок задания параметров безразличен, если используются именованные параметры, однако, если параметры не имеют имен, значения параметров должны быть представлены точно в том порядке, в каком они были указаны в операторе create procedure.

Предложение default указывает на значение по умолчанию для параметра, которое было определено в процедуре. Если процедура ожидает получение параметра, для которого не определено значение по умолчанию, а параметр отсутствует или указано предложение default, то возникает ошибка.

В примере 8.6 показано использование оператора execute.

 

 

Оператор execute в примере 8.6 выполняет хранимую процедуру increase_ budget (см. пример 8.5) и увеличивает бюджеты всех проектов на 10%.

В примере 8.7 показано создание процедуры, которая ссылается на таблицы

employee и works_on.

 

 

Процедура modifyempno в примере 8.7 демонстрирует использование хранимых процедур в виде части средств поддержки ссылочной целостности данных (в данном случае между таблицами employee и workson). Подобная хранимая процедура может быть использована внутри определения триггера, который фактически обеспечивает ссылочную целостность (см. пример 14.3).

В примере 8.8 показано использование предложения output.

 

 

Эта хранимая процедура может быть выполнена с использованием следующих операторов:

DECLARE @quantity INT

EXECUTE delete_emp @employee_no = 28559,  @counter = @quantity OUTPUT

Предыдущий пример содержит операторы создания процедуры deleteemp и ее выполнения. Эта процедура вычисляет количество проектов, над которыми работает служащий (с номером служащего @employee_no). Вычисленное значение затем присваивается параметру @counter. После удаления всех строк с указанным номером служащего из таблиц employee и workson полученное значение будет присвоено переменной @quantity.