Transact-SQL カーソル
Transact-SQL カーソルは、主にストアド プロシージャ、トリガ、および Transact-SQL スクリプトの中で使用され、結果セットの内容を他の Transact-SQL ステートメントで使用できるようにします。
ストアド プロシージャやトリガの中で Transact-SQL カーソルを使用する典型的な処理を以下に示します。
カーソルが返すデータを格納する Transact-SQL 変数を宣言します。結果セットの各列に 1 つずつの変数を宣言します。それぞれの変数は、対応する列から返される値を格納できるサイズが確保され、その列のデータ型から暗黙的に変換可能なデータ型で宣言します。
DECLARE CURSOR ステートメントを使用して、Transact-SQL カーソルを SELECT ステートメントに関連付けます。DECLARE CURSOR ステートメントでは、カーソル名や、カーソルを読み取り専用または順方向専用にするかどうかなど、カーソルの特性も定義します。
OPEN ステートメントを使用して SELECT ステートメントを実行し、カーソルを作成します。
FETCH INTO ステートメントを使用して、行を 1 つずつフェッチし、各列のデータを対応する変数に移動します。その後、他の Transact-SQL ステートメントは、これらの変数を参照することにより、フェッチされたデータ値にアクセスできます。Transact-SQL カーソルは、行をブロック単位でフェッチする機能をサポートしていません。
カーソルの使用を終了するときには CLOSE ステートメントを使用します。カーソルを終了すると、そのカーソルの結果セットや現在の行へのロックなど、一部のリソースが解放されます。ただし、OPEN ステートメントを再実行すれば、そのカーソル構造体を引き続き処理に使用できます。この時点ではカーソルはまだ存在しているため、そのカーソル名を再利用できません。DEALLOCATE ステートメントは、カーソル名も含めて、そのカーソルに割り当てられたすべてのリソースを解放します。カーソルの割り当て解除後は、そのカーソルを再構築するために DECLARE ステートメントを実行する必要があります。
Transact-SQL カーソルの利用状況の監視
sp_cursor_list システム ストアド プロシージャを使用すると、現在の接続上で表示されるカーソルの一覧を取得できます。また、sp_describe_cursor、sp_describe_cursor_columns、および sp_describe_cursor_tables を使用すると、カーソルの特性を判定できます。
カーソルを開いた後に、@@CURSOR_ROWS 関数を使用するか、sp_cursor_list または sp_describe_cursor で返される cursor_rows 列を調べることで、カーソル内の行数がわかります。
FETCH ステートメントが終了するごとに、最後に実行したフェッチの状態を反映させるために @@FETCH_STATUS が更新されます。この状態情報は、sp_describe_cursor が返す fetch_status 列からも取得できます。@@FETCH_STATUS は、カーソル内で先頭または末尾の行を超えてフェッチが行われたなどの状態を通知します。@@FETCH_STATUS は使用する接続に対してグローバルです。また、その接続で開かれているカーソルでフェッチが行われるたびにリセットされます。そのため、状態を後で知る必要がある場合、その接続上で他のステートメントを実行する前に、@@FETCH_STATUS をユーザー変数に保存してください。次に実行するステートメントが FETCH ではない場合でも、@@FETCH_STATUS をリセットする FETCH ステートメントが含まれるトリガを起動する INSERT、UPDATE、または DELETE が実行されることがあります。sp_describe_cursor が返す fetch_status 列は指定のカーソルに固有なので、他のカーソルを参照する FETCH ステートメントの影響は受けません。ただし、sp_describe_cursor は、同じカーソルを参照する FETCH ステートメントの影響を受けるので、使用するときには注意が必要です。
FETCH ステートメントが終了すると、カーソルがフェッチされた行に位置付けられます。このフェッチされた行を現在の行と呼びます。カーソルが読み取り専用カーソルとして宣言されていない場合、UPDATE または DELETE ステートメントに WHERE CURRENT OF cursor_name 句を指定して実行すれば、現在の行を変更できます。
DECLARE CURSOR ステートメントで指定する Transact-SQL カーソルの名前は、グローバルにすることもローカルにすることもできます。グローバルなカーソル名は、同じ接続上で実行中のいずれのバッチ、ストアド プロシージャ、またはトリガからも参照できます。ローカルなカーソル名は、そのカーソルが宣言されているバッチ、ストアド プロシージャ、またはトリガの外部からは参照できません。したがって、トリガやストアド プロシージャの内部にあるローカル カーソルは、そのストアド プロシージャやトリガの外部から意図しない参照が行われないように保護されます。
カーソル変数の使用
MicrosoftSQL Server では、cursor データ型の変数もサポートされます。カーソルをカーソル変数に関連付けるには、次の 2 つの方法があります。
/* Use DECLARE @local_variable, DECLARE CURSOR and SET. */
DECLARE @MyVariable CURSOR
DECLARE MyCursor CURSOR FOR
SELECT LastName FROM AdventureWorks.Person.Contact
SET @MyVariable = MyCursor
GO
/* Use DECLARE @local_variable and SET */
DECLARE @MyVariable CURSOR
SET @MyVariable = CURSOR SCROLL KEYSET FOR
SELECT LastName FROM AdventureWorks.Person.Contact;
DEALLOCATE MyCursor;
カーソルをカーソル変数に関連付けると、Transact-SQL カーソル ステートメントではカーソル名の代わりに関連付けられたカーソル変数を使用できます。ストアド プロシージャの出力パラメータに cursor データ型を割り当て、カーソルに関連付けることもできます。これにより、ストアド プロシージャは、管理された方法でローカル カーソルを公開できます。
Transact-SQL カーソルの参照
Transact-SQL カーソル名と変数は、Transact-SQL ステートメントだけが参照できます。OLE DB、ODBC、および ADO の API 関数からは参照できません。たとえば、DECLARE CURSOR と OPEN を使用して Transact-SQL カーソルを開いている場合、ODBC SQLFetch 関数や SQLFetchScroll 関数を使用してこの Transact-SQL カーソルから行をフェッチすることはできません。カーソル処理を必要とし、これらの API 関数を使用しているアプリケーションは、Transact-SQL カーソルの代わりに、データベース API に組み込まれたカーソル サポート機能を使用する必要があります。
アプリケーションで Transact-SQL カーソルを使用するには、FETCH を使用し、FETCH により返された各列をプログラム変数にバインドします。ただし、Transact-SQL FETCH はバッチをサポートしないので、これはデータをアプリケーションに返す最も非効率的な方法です。つまり、行をフェッチするごとに、サーバーとの間で情報のやり取りが必要になります。もっと効率的な方法は、行をバッチでフェッチできる、データベース API に組み込まれたカーソル機能を使用することです。
Transact-SQL カーソルは、ストアド プロシージャやトリガで使用する場合に、非常に効率的です。これは、すべての処理がサーバー上の 1 つの実行プランにコンパイルされるので、行のフェッチに伴うネットワーク トラフィックが発生しないためです。
Transact-SQL カーソルと SET オプション
SQL Server では、カーソルを開いた時点以降に値が変更されている場合に FETCH ステートメントが実行されるとエラーが発生します。このエラーは、次のプランに影響するオプションのいずれか、またはインデックス付きビューや計算列に必要なオプション使用時に発生します。このエラーを回避するには、カーソルが開いている間に SET オプションを変更しないようにします。
プランに影響するオプション |
ARITHABORT NUMERIC_ROUNDABORT FORCEPLAN QUOTED_IDENTIFIER ANSI_NULL_DFLT_ON ANSI_NULL_DFLT_OFF ANSI_WARNINGS ANSI_PADDING ANSI_NULLS CONCAT_NULL_YIELDS_NULL DATEFIRST DATEFORMAT LANGUAGE TEXTSIZE |
インデックス付きビューと計算列 |
ANSI_NULLS ANSI_PADDING ANSI_WARNINGS ARITHABORT (互換性レベルが 80 以下の場合) CONCAT_NULL_YIELDS_NULL QUOTED_IDENTIFIER NUMERIC_ROUNDABORT |
SQL Server 2000 では、ANSI_NULLS と QUOTED_IDENTIFIER を変更してもエラーは発生しませんでしたが、この 2 つ以外ではエラーが発生していました。