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

Как и большинство объектов баз данных SQL Server, хранимую процедуру можно создать тремя способами:

- с помощью средств Transact-SQL;

- с помощью Enterprise Manager;

- с помощью мастера Create Stored Procedure Wizard.

Мы будем рассматривать создание хранимых процедур только средствами Transact-SQL, точнее, с помощью команды CREATE PROCEDURE. Рассмотрим синтаксис этой команды:

CREATE PROCEDURE procedure_name [: number ]

[ { @parameter data_type }

[ VARYING ] [ = default ] [.OUTPUT ]

WITH

{ RECOMPILE|ENCRYPTION|RECOMPILE. ENCRYPTION } ]

[ FOR REPLICATION ]

AS

sql_statement.

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

Рассмотрим параметры, используемые при создании хранимой процедуры.

- procedure_name – имя создаваемой процедуры. Используя префиксы sp_, # и ##, можно определить создаваемую процедуру соответственно как системную или временную. Как видно из синтаксиса команды, не допускается указывать имя владельца, которому будет принадлежать создаваемая процедура, а также имя базы данных, в которой должна быть размещена процедура. Таким образом, чтобы разместить создаваемую хранимую процедуру в конкретной базе дан­ных, необходимо выполнять команду CREATE PROCEDURE в контексте этой базы данных. Напомним, что переключить текущую базу данных можно с помо­щью команды USE.

При обращении из тела хранимой процедуры к объектам той базы данных, в которой расположена процедура, можно использовать укороченные имена, то есть без ука­зания имени базы данных. Когда же требуется обратиться к объектам, расположен­ным в других базах данных, то указание имени базы данных обязательно.

- number – идентификационный номер хранимой процедуры, однозначно опре­деляющий ее в группе процедур.

- @parameter – имя параметра, который будет использоваться создаваемой хра­нимой процедурой для передачи входных или выходных данных. Как и при работе с локальными переменными, имена параметров хранимой процедуры должны начинаться с символа @. В одной хранимой процедуре можно исполь­зовать множество параметров (до 1024). Для этого достаточно разделить их запятыми. Параметры, определяемые при создании хранимой процедуры, являются своего рода локальными переменными, поэтому различные хранимые процедуры могут иметь абсо­лютно идентичные параметры. Однако, в хранимой процедуре не разре­шается использование переменных с именами, совпадающими с именами параметров.

- data_type – тип данных, который будет иметь соответствующий параметр хра­нимой процедуры. Для определения параметров можно использовать любые типы данных SQL Server, включая text, ntext и image, а также пользователь­ские типы данных.

- OUTPUT – наличие этого ключевого слова будет означать, что соответствующий параметр предназначается для возвращения данных из хранимой процедуры. Однако это вовсе не означает, что параметр не может быть использован для передачи значений в хранимую процедуру. Указание ключевого слова OUTPUT предписывает серверу при выходе из хранимой процедуры присвоить теку­щее значение параметра локальной переменной, которая была указана при вызове процедуры в качестве значения параметра. Отметим, что при наличии ключевого слова OUTPUT значение соответствующего параметра при вызове про­цедуры может быть задано только с помощью локальной переменной. Не раз­решается использование любых выражений или констант, допустимое для обычных параметров.

- VARYING – ключевое слово, которое используется совместно с параметром OUTPUT, имеющим тип данных cursor. Ключевое слово VARYING определяет, что в качестве выходного параметра будет представлено результирующее множество.

- default – значение, которое будет принимать соответствующий параметр по умолчанию. Таким образом, при вызове процедуры можно будет не указывать явно значение соответствующего параметра. В этом случае будет использовано значение, заданное с помощью параметра default.

- RECOMPILE – как мы говорили в начале урока, сервер кэширует план исполнения запроса и компилированный код. Так что при последующем вызове процедуры будут использоваться уже готовые значения. Однако в некоторых случаях все же требуется выполнять перекомпиляцию кода процедуры. Указание данного ключевого слова предписывает системе создавать план выполнения хранимой процедуры при каждом ее вызове.

- FOR REPLICATION – это ключевое слово используется при репликации данных и включении создаваемой хранимой процедуры в качестве статьи в публикацию. Когда процедура, созданная с параметром FOR REPLICATION, копируется механизмами репликации на подписчиков, она не может быть выполнена стандартными средствами. Ее вызов осуществляется только подсистемой репликации. Параметр FOR REPLICATION не может быть использован совместно с параметром RECOMPILE.

- ENCRYPTON – использование этого ключевого слова при создании хранимой процедуры предписывает серверу выполнить кодирование кода хранимой процедуры. Эта возможность позволяет обеспечить защиту от плагиата авторских алгоритмов, реализованных в работе хранимой процедуры.

- AS – это ключевое слово свидетельствует о начале собственно тела хранимой процедуры, то есть набора команд Transact-SQL, с помощью которых и будет реализовываться то или иное действие. В теле процедуры могут использоваться практически все команды Transact-SQL, объявляться транзакции, устанавливаться блокировки и производиться вызовы других хранимых процедур. Для выхода из хранимой процедуры можно использовать команду RETURN.

Рассмотрим пример создания хранимой процедуры, возвращающей в переменной @i среднюю длину имени студента:

Пример 4.

CREATE PROCEDURE SimpleMine1

AS

SELECT StName, StSurname, Birth from Grooup

Это пример простой хранимой процедуры.

Пример 5.

CREATE PROCEDURE SimpleMine2

@x char(50)= ‘Вася’

AS

SELECT StName, StSurname FROM GROOUP

WHERE StName=@x.

Это пример хранимой процедуры со значением по умолчанию.

 

Пример 6.

CREATE PROCEDURE SimpleMine3

@x char(50)

AS

SELECT StName, StSurname FROM GROOUP

WHERE StName=@x.

Создаем хранимую процедуру с входным параметром.

 

Пример 7.

CREATE PROCEDURE SimpleMine4

@x char(50)output

AS

set @x='МИРУ МИР'.

Создаем хранимую процедуру с выходным параметром.

Пример 8.

CREATE PROCEDURE ErrorSP

AS

DECLARE @returnCode int

SELECT StName,StSurname from Grooup

SET @returnCode=@@ERROR

RETURN(@returnCode).

Вызов хранимой процедуры

После того как процедура была создана, ее можно запускать.

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

- указание только имени процедуры;

- использование команды EXECUTE.

Первый вариант используется, когда вызов хранимой процедуры является единственной командой передаваемого на исполнение пакета. Однако когда вызов хранимой процедуры не является единственной командой в пакете, то требуется обязательное указание команды EXECUTE. Более того, указание этой команды требуется и при вызове процедуры из тела другой процедуры.

Упрощенный синтак­сис команды EXECUTE следующий:

EXEC procedure_name

[ [ @parameter = ] { value | @variable [ OUTPUT ] | [ DEFAULT ] ] [,...n ].

Указывая ключевое слово OUTPUT, вы тем самым предписываете присвоить соответствующей локальной переменной (внешней по отношению к процедуре) при завершении хранимой процедуры значение соответствующего параметра. Отметим, что значения параметров могут свободно изменяться в ходе работы процедуры. Использование слова OUTPUT при вызове процедуры разрешается только для параметров, которые были объявлены при создании процедуры с ключевым словом OUTPUT.

Когда при вызове процедуры для параметра указывается ключевое слово DEFAULT, то для этого параметра будет использовано значение по умолчанию. Естественно, указание слова DEFAULT разрешается только для тех параметров, для которых определено значение по умолчанию.

Рассмотрим примеры:

Пример 9.

Для выполнения процедуры из первого примера:

EXECUTE SimpleMine1

Точно также и в примере 5, процедура SimpleMine2.

Пример 10.

Чтобы выполнить процедуру SimpleMine3 введите:

EXECUTE SimpleMine3 ‘Вася’

Пример 11.

DECLARE @myOutput char(6)

EXECUTE SimpleMine4 @myOutput OUTPUT

SELECT @myOutput.

 

⇐ Предыдущая12345678Следующая ⇒

Поделиться:

Воспользуйтесь поиском по сайту: