Transact-SQL カーソルの名前のスコープ

[アーティクル]
12/16/2008

Microsoft SQL Server 2005 では、DECLARE CURSOR ステートメントで GLOBAL キーワードと LOCAL キーワードを使用して、カーソル名のスコープを定義できます。GLOBAL は、カーソル名が接続に対してグローバルであることを指定します。LOCAL は、カーソル名が DECLARE CURSOR ステートメントを含むストアドプロシージャ、トリガ、またはバッチに対してローカルであることを指定します。

Microsoft SQL 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 2005 では、データベースオプション自体は、以前のバージョンの 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 カーソルは、プロシージャを呼び出したコード内のカーソルを参照するパラメータや変数がスコープ外になったときに、暗黙的に割り当てを解除されます。

次の方法で共有

Transact-SQL カーソルの名前のスコープ

参照

その他の技術情報

ヘルプおよび情報

その他のリソース