Как и большинство объектов баз данных 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.
Это пример хранимой процедуры со значением по умолчанию.
