Область имен курсоров языка Transact-SQL

Microsoft SQL Server поддерживает в инструкции DECLARE CURSOR ключевые слова GLOBAL и LOCAL для определения области имени курсора. Ключевое слово GLOBAL указывает на то, что имя курсора является глобальным для соединения. Ключевое слово LOCAL указывает на то, что имя курсора будет локальным для хранимой процедуры, триггера или пакета, содержащих инструкцию DECLARE CURSOR.

До Microsoft SQL Server версии 7.0 имена курсоров языка Transact-SQL были глобальными для соединения. Можно было выполнять одну хранимую процедуру, создающую курсор, и затем вызывать другую, которая выбирала строки из этого курсора:

USE AdventureWorks2008R2;
GO
CREATE PROCEDURE OpenCrsr AS

DECLARE SampleCrsr CURSOR FOR
SELECT TOP (20)LastName
FROM Person.Person
WHERE LastName LIKE 'S%';

OPEN SampleCrsr;
GO

CREATE PROCEDURE ReadCrsr AS
FETCH NEXT FROM SampleCrsr
WHILE (@@FETCH_STATUS <> -1)
BEGIN
   FETCH NEXT FROM SampleCrsr
END
GO

EXEC OpenCrsr; /* DECLARES and OPENS SampleCrsr. */
GO
EXEC ReadCrsr; /* Fetches the rows from SampleCrsr. */
GO
CLOSE SampleCrsr;
GO
DEALLOCATE SampleCrsr;
GO

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

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

Глобальные и локальные курсоры имеют отдельные пространства имен, поэтому возможно существование как глобального, так и локального курсоров с одним и тем же именем в одно и то же время. Инструкции языка Transact-SQL, принимающие в качестве параметров имена курсоров, также поддерживают ключевое слово GLOBAL для определения области имени. Если ключевое слово GLOBAL не указано, и существуют локальный и глобальный курсор с указанным в параметре именем курсора, используется локальный курсор.

Параметр базы данных CURSOR_DEFAULT, устанавливаемый инструкцией ALTER DATABASE, управляет значением по умолчанию, которое берется инструкцией DECLARE CURSOR, если ключевые слова LOCAL и GLOBAL не указаны. Текущее значение этого параметра базы данных хранится в представлении каталога sys.databases. Если значение в столбце local_cursor_default в представлении каталога sys.databases равно true, по умолчанию создается локальный курсор языка Transact-SQL. Если этот параметр имеет значение false, курсоры языка Transact-SQL по умолчанию создаются глобальными. В SQL Server параметр базы данных по умолчанию имеет значение FALSE (GLOBAL) для совместимости с поведением более ранних версий SQL Server.

Хранимые процедуры, выполняющие инструкции DECLARE и OPEN для локальных курсоров, могут возвращать эти курсоры для использования в вызывающей хранимой процедуре, триггере или пакете. Это достигается использованием параметра OUTPUT, определенного с новым типом данных CURSOR VARYING. Переменные курсоров могут использоваться только в качестве параметров OUTPUT. Они не могут использоваться во входных параметрах. Курсор должен быть открытым во время завершения выполнения хранимой процедуры, чтобы иметь возможность быть переданным в параметр OUTPUT. Локальные переменные также могут быть объявлены как имеющие тип данных CURSOR для хранения ссылки на локальный курсор.

USE AdventureWorks2008R2;
GO
/* Create a procedure with a cursor output parameter. */
CREATE PROCEDURE OpenCrsr @OutCrsr CURSOR VARYING OUTPUT AS

SET @OutCrsr = CURSOR FOR
SELECT TOP (20) LastName
FROM Person.Person
WHERE LastName LIKE 'S%';

OPEN @OutCrsr;
GO

/* Allocate a cursor variable. */
DECLARE @CrsrVar CURSOR;

/* Execute the procedure created earlier to fill
  the variable. */
EXEC OpenCrsr @OutCrsr = @CrsrVar OUTPUT;

/* Use the variable to fetch the rows from the cursor. */
FETCH NEXT FROM @CrsrVar
WHILE (@@FETCH_STATUS <> -1)
BEGIN
   FETCH NEXT FROM @CrsrVar
END;

CLOSE @CrsrVar;

DEALLOCATE @CrsrVar;
GO

API базы данных не поддерживает выходные параметры-курсоры в хранимых процедурах. Хранимая процедура, которая содержит выходной параметр-курсор, не может быть выполнена напрямую из функции API базы данных. Эти хранимые процедуры могут быть выполнены только из другой хранимой процедуры, триггера, пакета или скрипта языка Transact-SQL.

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