Создание хранимых процедур
Создание хранимой процедуры осуществляется вызовом команды CREATE PROCEDURE.
При этом можно создавать хранимые процедуры, ссылающиеся на еще не существующие объекты (таблицы, представления и т.д.). Во время создания хранимой процедуры выполняется только проверка синтаксиса. Сама хранимая процедура не компилируется до первого обращения к ней. Ссылки на все используемые в хранимой процедуре объекты разрешаются только во время компиляции. Таким образом, ничто не мешает создать синтаксически корректную хранимую процедуру, ссылающуюся на несуществующие таблицы, однако если эти таблицы будут отсутствовать во время выполнения хранимой процедуры, она завершится с ошибкой.
CREATE { PROC | PROCEDURE } procedure_name
[
{ @parameter data_type }
[ VARYING ] [ = default ] [ OUT | OUTPUT ] [READONLY]
] [ ,...n ]
[ WITH [ ENCRYPTION ] [ RECOMPILE ] [ EXECUTE AS ]]
AS
[ BEGIN ] <statements> [ END ]
procedure_name
Имя новой хранимой процедуры. Bмя хранимой процедуры не может иметь длину более 128 символов.
@parameter
Параметр процедуры. Имя параметра всегда должно начинаться со знака @. В одной инструкции CREATE PROCEDURE можно объявить один или более параметров. При выполнении процедуры значение каждого из объявленных параметров должно быть указано пользователем, если для параметра не определено значение по умолчанию или значение не задано равным другому параметру. Хранимая процедура может иметь не более 2 100 параметров.
Параметры являются локальными в пределах процедуры; в разных процедурах могут быть использованы одинаковые имена параметров. По умолчанию параметры могут использоваться только в качестве константных выражений; они не могут быть использованы вместо имен таблиц, столбцов или других объектов базы данных.
data_type
Тип данных параметра. Все типы данных Transact-SQL могут использоваться в качестве параметра хранимой процедуры. Также можно использовать определяемый пользователем табличный тип, чтобы объявить входной параметр хранимой процедуры Transact-SQL. Параметры табличного типа могут быть только входными и должны сопровождаться ключевым словом READONLY. Тип данных cursor можно использовать только в качестве выходного параметра. При указании типа данных cursor нужно также указать ключевые слова VARYING и OUTPUT. Выходных параметров типа cursor может быть несколько.
VARYING
Указывает результирующий набор, поддерживаемый в качестве выходного параметра. Этот параметр динамически формируется хранимой процедурой, и его содержимое может различаться. Применяется только к параметрам типа cursor.
default
Значение по умолчанию для параметра. Если значение default определено, процедуру можно выполнить без указания значения соответствующего параметра. Значение по умолчанию должно быть константой или может равняться NULL. Если в процедуре используется параметр с ключевым словом LIKE, он может включать символы-шаблоны %, _, [] и [^].
OUTPUT
Показывает, что параметр процедуры является выходным. Значение этого параметра можно получить после вызова хранимой процедуры. Выходные параметры используются для возврата значений коду, вызвавшему процедуру. Определяемый пользователем табличный тип не может быть указан в качестве выходного параметра хранимой процедуры.
READONLY
Указывает, что параметр не может быть обновлен или изменен в тексте процедуры. Если тип параметра является определяемым пользователем табличным типом, ключевое слово READONLY должно быть указано.
RECOMPILE
Показывает, что компонент Database Engine не кэширует план выполнения процедуры и что процедура компилируется во время выполнения.
ENCRYPTION
Показывает, что SQL Server выполнит затемнение исходного текста инструкции CREATE PROCEDURE. Результат затемнения не виден непосредственно ни в одном из представлений каталогов SQL Server. Пользователи, не имеющие доступа к системным таблицам или файлам баз данных, не смогут получить скрытый текст. Однако этот текст будет доступен привилегированным пользователям.
EXECUTE AS
Определяет контекст безопасности, в котором должна быть выполнена хранимая процедура, т.е. какую учетную запись компонент Database Engine использует при проверке разрешений на объекты, на которые ссылается хранимая процедура. Это повышает гибкость и безопасность управления разрешениями на цепочки владения между пользовательскими хранимыми процедурами и объектами, на которые они ссылаются.
Пользователям необходимо будет предоставлять только разрешения на саму хранимую процедуру, без выдачи явных разрешений на объекты, на которые он ссылается. Только пользователь, от имени которого выполняется модуль, должен будет иметь разрешения на объекты, к которым этот модуль обращается.
Допустимые значения EXECUTE AS { CALLER | SELF | OWNER | 'user_name' }.
CALLER
Является значением по умолчанию и указывает, что инструкции, содержащиеся в хранимой процедуре, выполняются в контексте пользователя, ее вызывающего. Пользователь, выполняющий хранимую процедуру, должен иметь соответствующие разрешения не только на саму хранимую процедуру, но также и на объекты базы данных, на которые имеются ссылки из нее.
SELF
EXECUTE AS SELF эквивалентно EXECUTE AS user_name, где указанный пользователь - это тот, кто создает или изменяет модуль.
OWNER
Указывает, что инструкции, содержащиеся в хранимой процедуре, выполняются в контексте текущего владельца этой хранимой процедуре.
'user_name'
Указывает, что инструкции, содержащиеся в хранимой процедуре, выполняются в контексте пользователя, указываемого аргументом user_name. Разрешения на объекты, на которые ссылается хранимая процедура, проверяются для пользователя user_name.
Пользователь user_name должен присутствовать в текущей базе данных и не должен относиться к учетной записи группы. В качестве аргумента user_name нельзя указывать роль, сертификат, ключ или встроенную учетную запись (например, NT AUTHORITY\LocalService, NT AUTHORITY\NetworkService или NT AUTHORITY\LocalSystem).
<statements>
Одна или несколько инструкций языка Transact-SQL, которые будут включены в состав процедуры.
Инструкцию CREATE PROCEDURE нельзя объединять с другими инструкциями Transact-SQL в одном пакете.
По умолчанию параметры могут принимать значения NULL. Если параметр, имеющий значение NULL, используется в инструкции CREATE TABLE или ALTER TABLE при обращении к столбцу, не поддерживающему значения NULL, то компонент Database Engine возвращает ошибку. Чтобы предотвратить передачу значений NULL столбцу, который их не поддерживает, следует реализовать в процедуре соответствующую логику или передать столбцу значение по умолчанию при помощи ключевого слова DEFAULT инструкции CREATE TABLE или ALTER TABLE.
Каждая хранимая процедура имеет возвращаемое значение типа int. Возвращаемое значение можно передать вызывающему коду при помощи конструкции RETURN value. Возвращаемое значение можно использовать совместно с выходными параметрами, например, для индикации успешности вызова процедуры.
При создании или изменении хранимой процедуры Transact-SQL компонент Database Engine сохраняет значения SET QUOTED_IDENTIFIER и SET ANSI_NULLS. Эти первоначальные значения используются при выполнении хранимой процедуры. Таким образом, пока хранимая процедура выполняется, любые значения SET QUOTED_IDENTIFIER и SET ANSI_NULLS, задаваемые во время клиентского сеанса, не учитываются. Другие параметры SET, такие как SET ARITHABORT или SET ANSI_PADDINGS, при создании или изменении хранимой процедуры не сохраняются. Если логика хранимой процедуры зависит от конкретного значения параметра, следует включить инструкцию SET в начало процедуры, чтобы гарантировать нужное значение. Если инструкция SET выполняется из хранимой процедуры, устанавливаемое ею значение действует только до завершения хранимой процедуры. После этого оно принимает значение, которое имело место при вызове хранимой процедуры. Это позволяет клиентам задавать нужные им параметры без влияния на логику хранимой процедуры.
Вызов хранимых процедур
Выполнение созданной хранимой процедуры осуществляется вызовом команды EXEC с указанием хранимой процедуры в качестве аргумента:
EXEC procedure_name
Если в хранимой процедуре предусмотрена обработка параметров, можно указать значения аргументов.
Предоставленное значение должно быть константой или переменной; нельзя указывать имя функции в качестве значения аргумента. Переменные могут быть пользовательскими или системными, например @@spid.
EXEC procedure_name value1, value2 ...
Примеры:
В следующих примерах демонстрируется передача значений аргументов хранимой процедуре uspSaleProduct. Процедура ожидает значений для двух входных аргументов: кода продукта и даты.
Пример 1:
Передача констант в качестве значений параметров
EXEC dbo.uspSaleProduct 819, '20050225';
Пример 2:
Передача переменных в качестве значений параметров
DECLARE @ProductID int, @SaleDate datetime;
SET @ProductID = 819;
SET @SaleDate = '20050225';
EXEC dbo.uspSaleProduct @ProductID, @SaleDate;
Пример 3:
Передача имени функции в качестве значений параметров. Данный вызов порождает ошибку, поскольку имя функции нельзя указывать в качестве значения аргумента
EXEC dbo.uspSaleProduct 819, GETDATE();
Пример 3:
Передача результата вызова функции в качестве значения параметра, используя переменные
DECLARE @SaleDate datetime;
SET @SaleDate = GETDATE();
EXEC dbo.uspSaleProduct 819, @SaleDate;
Если необходимо указывать параметры в порядке, отличном от определенного в хранимой процедуре, их необходимо именовать, то есть указывать в виде @аргумент = значение. Можно также опустить аргументы, для которых представлены значения по умолчанию. При указании одного аргумента в виде @аргумент =value необходимо предоставить все последующие аргументы тем же способом.
EXEC procedure_name @arg1=value1, @arg2= value2 ...
При выполнении хранимой процедуры сервер отвергает любые аргументы, не включенные в список аргументов в процессе создания процедуры. Любой аргумент, переданный ссылкой (явно передающей имя аргумента) не принимается, если имя аргумента не совпадает.
Примеры:
В следующих примерах демонстрируется передача значений аргументов хранимой процедуре uspSaleProduct в виде @аргумент =value. Процедура ожидает значений для двух входных аргументов: кода продукта и даты.
Пример 1:
Передача параметров в порядке следования в командеCREATE PROCEDURE
EXEC dbo.uspSaleProduct @SaleDate='20050225', @ProductID=819;
Пример 2:
Передача параметров в обратном порядке
EXEC dbo.uspSaleProduct @ProductID=819, @SaleDate='20050225';
Хотя разрешается опустить аргументы, для которых предоставлены значения по умолчанию, список аргументов можно лишь подвергнуть усечению (т.е. не передавать значения параметров идущих в конце списка параметров и имеющих DEFAULT значение). Например, когда в хранимой процедуре присутствует пять аргументов, можно опустить четвертый и пятый, но нельзя пропустить четвертый и включить пятый, если только аргументы не были предоставлены в формате @аргумент = value.
Значение по умолчанию, определенное для аргумента в хранимой процедуре, используется, когда:
· не указано значение для аргумента при выполнении хранимой процедуры;
· в качестве значения для аргумента указывается ключевое слово DEFAULT.