Transact-SQL カーソルの名前のスコープ
MicrosoftSQL Server では、DECLARE CURSOR ステートメントで GLOBAL キーワードと LOCAL キーワードを使用して、カーソル名のスコープを定義できます。GLOBAL は、カーソル名が接続に対してグローバルであることを指定します。LOCAL は、カーソル名が DECLARE CURSOR ステートメントを含むストアド プロシージャ、トリガ、またはバッチに対してローカルであることを指定します。
MicrosoftSQL Server Version 7.0 より前のバージョンでは、Transact-SQL カーソルの名前は接続に対してグローバルでした。そのため、次のように、カーソルを作成する 1 つのストアド プロシージャを実行して、そのカーソルから行をフェッチする別のストアド プロシージャを呼び出すことができました。
USE AdventureWorks;
GO
CREATE PROCEDURE OpenCrsr AS
DECLARE SampleCrsr CURSOR FOR
SELECT TOP (20)LastName
FROM Person.Contact
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
ローカル カーソルは、ストアド プロシージャやトリガで実装されたカーソルを保護するために重要です。グローバル カーソルは、カーソルが宣言されているストアド プロシージャまたはトリガの外部から参照できます。したがって、グローバル カーソルは、ストアド プロシージャやトリガ外部のステートメントで誤って変更される可能性があります。ローカル カーソルは、cursor 出力パラメータとして呼び出し側に意図的に渡されない限り、ストアド プロシージャ外部からは参照できないので、グローバル カーソルより高いセキュリティ保護を実現できます。
グローバル カーソルはストアド プロシージャやトリガ外部から参照できるので、予想外の副作用が生じ、他のステートメントに影響を与えることがあります。たとえば、ストアド プロシージャでは、xyz という名前のグローバル カーソルを作成し、プロシージャの完了時にカーソルを開かれたままの状態にしておくことができます。この場合、ストアド プロシージャの完了後、xyz という名前の別のグローバル カーソルを宣言しようとすると、名前の重複によりエラーが発生します。
グローバル カーソルとローカル カーソルは名前空間が分離されているので、同じ名前のグローバル カーソルとローカル カーソルが同時に存在することもありえます。カーソル名をパラメータとして受け取る Transact-SQL ステートメントでも、名前のスコープを識別する GLOBAL キーワードがサポートされます。GLOBAL を指定せず、カーソル名のパラメータで指定された名前のローカル カーソルとグローバル カーソルがどちらも存在する場合は、ローカル カーソルが参照されます。
LOCAL も GLOBAL も指定しなかった場合、DECLARE CURSOR ステートメントでどちらのカーソルを既定値として使用するかは、データベース オプション CURSOR_DEFAULT (ALTER DATABASE を使用して設定) によって制御されます。このデータベース オプションの現在値は、sys.databases カタログ ビューに格納されます。sys.databases カタログ ビューで local_cursor_default 列の値が true の場合、Transact-SQL カーソルは既定でローカルになります。このオプションが false の場合、Transact-SQL カーソルは既定でグローバルになります。SQL Server では、データベース オプション自体は、以前のバージョンの SQL Server の動作と一致するように、既定で FALSE (GLOBAL) に設定されます。
ローカル カーソルを宣言して開くストアド プロシージャでは、呼び出し側のストアド プロシージャ、トリガ、またはバッチで使用するために、そのローカル カーソルを渡すことができます。そのためには、新しい CURSOR VARYING データ型で定義した OUTPUT パラメータを使用します。カーソル変数は OUTPUT パラメータとしてのみ使用できます。入力パラメータとしては使用できません。ストアド プロシージャが完了して OUTPUT パラメータに渡されるとき、カーソルは開かれている必要があります。また、ローカル カーソルへの参照を保持するために、新しい CURSOR データ型でローカル変数を宣言することもできます。
USE AdventureWorks;
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.Contact
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 では、ストアド プロシージャで cursor 出力パラメータがサポートされていません。そのため、cursor 出力パラメータを含むストアド プロシージャをデータベース API 関数から直接実行することはできません。このようなストアド プロシージャは、別のストアド プロシージャ、トリガ、または Transact-SQL のバッチやスクリプトからのみ実行できます。
GLOBAL カーソルは、明示的に割り当てを解除されるか、接続が閉じられるまで使用できます。LOCAL カーソルは、LOCAL カーソルが作成されたストアド プロシージャ、トリガ、またはバッチが終了するときに、暗黙的に割り当てを解除されます。ただし、カーソルがパラメータとして渡された場合を除きます。また、LOCAL カーソルは、プロシージャを呼び出したコード内のカーソルを参照するパラメータや変数がスコープ外になったときに、暗黙的に割り当てを解除されます。