Лекция 21: Создание хранимых процедур и управление этими процедурами

Использование локальных переменных внутри хранимой процедуры

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

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

Рассмотрим пример хранимой процедуры, содержащей локальные переменные. Эта процедура вставляет пять строк в таблицу с помощью циклической конструкции WHILE. Сначала мы создадим таблицу с именем mytable для этого примера и затем создадим хранимую процедуру InsertRows. В этой процедуре мы будем использовать локальные переменные @loop_counter и @start_val, которые объявим вместе и разделим запятой. В следующей T-SQL-программе создается таблица и хранимая процедура:

USE MyDB
GO

CREATE TABLE mytable
(
        column1 int,
         column2 char(10)
)
GO

CREATE PROCEDURE InsertRows @start_value int
AS
DECLARE     @loop_counter int, @start_val int
SET             @start_val = @start_value – 1
SET             @loop_counter = 0
WHILE (@loop_counter < 5)
     BEGIN
     INSERT INTO mytable VALUES (@start_val + 1, "new row")
          PRINT (@start_val)
          SET @start_val = @start_val + 1
          SET @loop_counter = @loop_counter + 1
     END
GO

А теперь выполним эту хранимую процедуру с начальным значением 1, как это показано ниже:

EXECUTE InsertRows 1
GO

Вы увидите пять значений, выведенных для @start_val: 0, 1, 2, 3 и 4.

Выберите все строки из таблицы mytable с помощью следующего оператора:

SELECT *
FROM   mytable
GO

После выполнения этого оператора SELECT мы получим следующие выходные результаты:

column1    column2
-----------------------
1           new row
2           new row
3           new row
4           new row
5           new row

После завершения хранимой процедуры переменные @loop_counter и @start_val уже недоступны. Вы получите сообщение об ошибке, если попытаетесь вывести их с помощью следующего оператора T-SQL:

PRINT (@loop_counter)
PRINT (@start_val)
GO

Сообщение об ошибке будет иметь следующую форму:

Msg 137, Level 15, State 2, Server JAMIERE3, Line 1
Must declare the variable '@loop_counter'.
(Должна быть объявлена переменная '@loop_counter')
Msg 137, Level 15, State 2, Server JAMIERE3, Line 2 
Must declare the variable '@start_value'.
(Должна быть объявлена переменная '@start_value')

Те же правила, связанные с областью действия переменной, применимы к выполнению пакетного набора операторов. Как только появляется ключевое слово GO (являющееся признаком конца пакета), локальные переменные, объявленные внутри пакета, становятся недоступны. Область действия локальной переменной находится только в пределах пакета. Чтобы лучше понять эти правила, рассмотрим вызов хранимой процедуры в одном из приведенных выше примеров:

USE Northwind
GO

DECLARE @price money
EXECUTE GetUnitPrice 77, @unit_price = @price OUTPUT
PRINT CONVERT(varchar(6), @price)
GO

PRINT CONVERT(varchar(6), @price)
GO

В первом операторе PRINT выводится значение локальной переменной @price из данного пакета. Во втором операторе сделана попытка вывести его снова вне пакета, но в результате появится сообщение об ошибке в следующей форме:

13.00

Msg 137, Level 15, State 2, Server JAMIERE3, Line 2
Must declare the variable '@price'.
(Должна быть объявлена переменная '@price')

Отметим, что первый оператор PRINT выполнен успешно. (Он вывел значение 13.00.)

Возможно, вам потребуется использовать операторы BEGIN TRANSACTION, COMMIT и ROLLBACK в хранимой процедуре, которая содержит более одного оператора T-SQL. Это позволяет указывать, какие операторы должны быть сгруппированы как одна транзакция. (Об использовании транзакций и этих операторов см. "Транзакции и блокировка транзакций" .)