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

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

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

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

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. Вы увидите пять значений, выведенных для @start_val: 0, 1, 2, 3 и 4. Выберите все строки из таблицы mytable с помощью оператора 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 (являющееся признаком конца пакета), локальные переменные, объявленные внутри пакета, становятся недоступны. Область действия локальной переменной находится только в пределах пакета. Чтобы лучше понять эти правила, рассмотрим вызов хранимой процедуры в одном из приведенных выше примеров:

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. Это позволяет указывать, какие операторы должны быть сгруппированы как одна транзакция.