Создание хранимых процедур

Создание хранимой процедуры осуществляется вызовом команды 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

VARYING

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

default

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.

• ⇐ Назад
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 262728
• 29
• 30
• 31
• 32
• 33
• 34
• Далее ⇒