DECLARE CURSOR (Transact-SQL)

更新 : 2005 年 12 月 5 日

Transact-SQL サーバー カーソルの属性を定義します。これには、スクロール動作や、カーソルが操作する結果セットを作成するクエリなどが含まれます。DECLARE CURSOR は、SQL-92 標準に基づく構文と、Transact-SQL の拡張機能のセットを使用する構文の両方で指定できます。

Transact-SQL 構文表記規則

構文

SQL 92 Syntax
DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR 
     FOR select_statement 
     [ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]
[;]
Transact-SQL Extended Syntax
DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ] 
     [ FORWARD_ONLY | SCROLL ] 
     [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] 
     [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] 
     [ TYPE_WARNING ] 
     FOR select_statement 
     [ FOR UPDATE [ OF column_name [ ,...n ] ] ]
[;]

引数

• cursor_name
  定義する Transact-SQL サーバー カーソルの名前です。cursor_name は、識別子の規則に従っている必要があります。識別子のルールの詳細については、「オブジェクト名としての識別子の使用」を参照してください。

• INSENSITIVE
  カーソルで使うデータの、一時的コピーを作成するためのカーソルを定義します。このカーソルに対する要求の応答は、すべて tempdb 内のこの一時テーブルから得られます。したがって、ベース テーブルへの修正は、このカーソルで取り出したデータには反映されません。また、このカーソルで修正を行うこともできません。SQL-92 構文で INSENSITIVE を指定しない場合は、任意のユーザーによって基になるテーブルに加えられた削除および更新がコミットされると、以降のフェッチで反映されます。

• SCROLL
  すべてのフェッチ オプション (FIRST、LAST、PRIOR、NEXT、RELATIVE、ABSOLUTE) が利用できることを指定します。SQL-92 形式の DECLARE CURSOR ステートメントに SCROLL を指定しない場合は、NEXT オプションだけがサポートされます。FAST_FORWARD も指定した場合は、SCROLL を指定できません。

• select_statement
  カーソルの結果セットを定義する標準の SELECT ステートメントです。COMPUTE、COMPUTE BY、FOR BROWSE、および INTO キーワードは、カーソル宣言の select_statement で使用することはできません。

  Microsoft select_statement 内の句が、要求されたカーソルの種類の機能と矛盾する場合、SQL Server によってカーソルは別の種類に暗黙的に変換されます。詳細については、「暗黙のカーソル変換の使用」を参照してください。

• READ ONLY
  このカーソルによる更新を禁止します。UPDATE または DELETE ステートメントの WHERE CURRENT OF 句で、このカーソルを参照することはできません。このオプションは、更新対象のカーソルの既定の機能よりも優先されます。

• UPDATE [OF column_name [,...n]]
  カーソル内で更新できる列を定義します。OF column_name [,...n] を指定した場合は、指定した列に対してのみ更新ができます。列リストを伴わずに UPDATE を指定した場合は、すべての列を更新できます。

• cursor_name
  定義する Transact-SQL サーバー カーソルの名前です。cursor_name は、識別子の規則に従っている必要があります。識別子のルールの詳細については、「オブジェクト名としての識別子の使用」を参照してください。

• LOCAL
  カーソルのスコープが、カーソルが作成されたバッチ、ストアド プロシージャ、またはトリガに対してローカルであることを指定します。カーソル名は、そのスコープの中でだけ有効です。カーソルは、バッチ、ストアド プロシージャ、トリガ内のローカル カーソル変数によって、またはストアド プロシージャの OUTPUT パラメータによって参照できます。OUTPUT パラメータは、呼び出し側のバッチ、ストアド プロシージャ、トリガにローカル カーソルを戻すのに使用されます。呼び出し側はパラメータをカーソル変数に割り当て、ストアド プロシージャが終了した後でカーソルを参照できます。カーソルは、OUTPUT パラメータで戻された場合を除いて、バッチ、ストアド プロシージャ、またはトリガが終了するときに暗黙的に割り当てを解除されます。OUTPUT パラメータで戻された場合は、カーソルを参照している最後の変数の割り当てが解除されるか、そのスコープが失われたときに、カーソルの割り当てが解除されます。

• GLOBAL
  カーソルのスコープが接続に対してグローバルであることを指定します。カーソル名は、接続によって実行されるストアド プロシージャやバッチの中で参照できます。カーソルは、切断のときだけ暗黙的に割り当てを解除されます。

  メモ :
  GLOBAL も LOCAL も指定しない場合は、default to local cursor データベース オプションの設定によって既定の動作が決まります。SQL Server Version 7.0 の場合、このオプションの既定値は FALSE で、すべてのカーソルがグローバルであるという、以前のバージョンの SQL Server と一致します。ただし、このオプションの既定値は、SQL Server の将来のバージョンで変更する可能性があります。詳細については、「データベース オプションの設定」を参照してください。

• FORWARD_ONLY
  カーソルが先頭行から最終行に向かってだけスクロールできることを指定します。FETCH NEXT は、サポートされている唯一のフェッチ オプションです。STATIC、KEYSET、または DYNAMIC キーワードを伴わずに FORWARD_ONLY を指定した場合、カーソルは DYNAMIC カーソルとして動作します。FORWARD_ONLY も SCROLL も指定しなかった場合は、STATIC、KEYSET、または DYNAMIC キーワードを指定しない限り、FORWARD_ONLY が有効になります。STATIC、KEYSET、および DYNAMIC カーソルは、特に指定のない限り SCROLL に設定されます。ODBC や ADO などのデータベース API と異なり、FORWARD_ONLY は、STATIC、KEYSET、および DYNAMIC Transact-SQL カーソルでサポートされます。

• STATIC
  カーソルで使うデータの、一時的コピーを作成するためのカーソルを定義します。このカーソルに対する要求の応答は、すべて tempdb 内のこの一時テーブルから得られます。したがって、ベース テーブルへの修正は、このカーソルで取り出したデータには反映されません。また、このカーソルで修正を行うこともできません。

• KEYSET
  カーソルをオープンするときに、カーソル内の行の構成要素と順序が固定されることを指定します。行を一意に識別するキーのセットは、tempdb 内の keyset というテーブルに組み込まれています。

  メモ :
  クエリが一意なインデックスのないテーブルを少なくとも 1 つ参照する場合、このキーセット カーソルは静的カーソルに変換されます。

  ベース テーブル内の非キー値がカーソル所有者によって変更されるか、変更が他のユーザーによってコミットされると、その変更は、所有者がカーソルの周囲をスクロールするときに表示されます。他のユーザーによって行われた挿入は表示されません。Transact-SQL サーバー カーソルを介して、挿入を行うことはできません。行が削除された場合に、行をフェッチしようとすると、@@FETCH_STATUS が -2 に設定されて返されます。カーソル外部からキー値を更新すると、古い行を削除した後で新しい行を挿入した場合と同様に、新しい値の行は表示されず、古い値の行をフェッチしようとすると @@FETCH_STATUS が -2 に設定されて返されます。新しい値は、WHERE CURRENT OF 句を指定してカーソルから更新を終了した場合に表示されます。

• DYNAMIC
  カーソルの周囲をスクロールするときに、結果セット内の行に対して行ったすべてのデータ変更を反映するカーソルを定義します。行のデータ値、順序、構成要素は、各フェッチで変化する可能性があります。ABSOLUTE フェッチ オプションは、動的カーソルではサポートされません。

• FAST_FORWARD
  パフォーマンスの最適化が有効に設定された FORWARD_ONLY、READ_ONLY カーソルを指定します。SCROLL または FOR_UPDATE も指定した場合は、FAST_FORWARD を指定できません。

  メモ :
  SQL Server 2000 では、FAST_FORWARD と FORWARD_ONLY カーソル オプションは同時に指定できません。両方を指定した場合、エラーが発生します。SQL Server 2005 では、同一の DECLARE CURSOR ステートメントの中で、両方のキーワードを使用できます。

• READ_ONLY
  このカーソルによる更新を禁止します。UPDATE または DELETE ステートメントの WHERE CURRENT OF 句で、このカーソルを参照することはできません。このオプションは、更新対象のカーソルの既定の機能よりも優先されます。

• SCROLL_LOCKS
  カーソルによって行われる位置指定更新または位置指定削除は、確実に成功します。Microsoft SQL Server では、後続の変更で行を使用できるようにするために、カーソルに読み取られるときに行がロックされます。FAST_FORWARD または STATIC も指定した場合は、SCROLL_LOCKS を指定できません。

• OPTIMISTIC
  行がカーソルに読み取られた後、その行が更新されている場合に、カーソルによって行われる位置指定更新または位置指定削除を失敗するように指定します。SQL Server は、行をカーソルに読み取るときに、その行をロックしません。代わりに、timestamp 列の値を比較して、または timestamp 列がない場合はチェックサム値を使用して、行がカーソルに読み込まれてから変更されたかどうかを判断します。行が変更されている場合には、位置指定更新または位置指定削除の試みは失敗します。FAST_FORWARD を指定した場合は、OPTIMISTIC を指定できません。

• TYPE_WARNING
  カーソルが要求された種類から別の種類に暗黙的に変換された場合、クライアントに警告メッセージを送信することを指定します。

• select_statement
  カーソルの結果セットを定義する標準の SELECT ステートメントです。COMPUTE、COMPUTE BY、FOR BROWSE、および INTO キーワードは、カーソル宣言の select_statement で使用することはできません。

  メモ :
  カーソル宣言内ではクエリ ヒントを使用できますが、FOR UPDATE OF 句も使用する場合は、FOR UPDATE OF の後に OPTION (query_hint) を指定する必要があります。

  select_statement 内の句が、要求されたカーソルの種類の機能と矛盾する場合、SQL Server によってカーソルが別の種類に暗黙的に変換されます。詳細については、「暗黙的なカーソル変換」を参照してください。

• FOR UPDATE [OF column_name [,...n]]
  カーソル内で更新できる列を定義します。OF column_name [,...n] を指定した場合は、指定した列に対してのみ更新ができます。列リストを伴わずに UPDATE を指定した場合は、すべての列を更新できます。ただし、READ_ONLY 同時実行オプションを指定した場合は除きます。

解説

DECLARE CURSOR は、Transact-SQL サーバー カーソルの属性を定義します。これには、スクロール動作や、カーソルが操作する結果セットを作成するクエリなどが含まれます。OPEN ステートメントは結果セットを設定し、FETCH ステートメントは結果セットから行を返します。CLOSE ステートメントは、カーソルに関係付けられた現在の結果セットを解放します。DEALLOCATE ステートメントは、カーソルが使用するリソースを解放します。

最初の形式の DECLARE CURSOR ステートメントは、SQL-92 構文を使用してカーソルの動作を宣言します。2 番目の形式の DECLARE CURSOR は、Transact-SQL の拡張機能を使用します。これによって、ODBC または ADO のデータベース API カーソル関数で使用されるカーソルの種類と同じカーソルの種類を使用して、カーソルを定義できます。

2 つの形式を混在することはできません。CURSOR キーワードの前に SCROLL または INSENSITIVE キーワードを指定した場合、CURSOR キーワードと FOR select_statement キーワードの間で一切のキーワードを使用できません。CURSOR キーワードと FOR select_statement キーワードの間にキーワードを指定した場合は、CURSOR キーワードの前に SCROLL も INSENSITIVE も指定できません。

Transact-SQL 構文を使用する DECLARE CURSOR が READ_ONLY、OPTIMISTIC、または SCROLL_LOCKS を指定しない場合、既定値は次のとおりです。

• 権限が十分でなかったり、更新をサポートしないリモート テーブルにアクセスしているなどの理由で、SELECT ステートメントが更新をサポートしない場合、カーソルは READ_ONLY です。
• STATIC および FAST_FORWARD カーソルの既定値は READ_ONLY です。
• DYNAMIC および KEYSET カーソルの既定値は OPTIMISTIC です。

カーソル名は、他の Transact-SQL ステートメントでのみ参照できます。データベース API 関数でカーソル名を参照することはできません。たとえば、カーソルの宣言後、OLE DB、ODBC、または ADO 関数やメソッドからカーソル名を参照することはできません。カーソル行は、フェッチ関数や API のメソッドでフェッチすることはできません。Transact-SQL FETCH ステートメントによってのみフェッチできます。

カーソルの宣言後、次に示すシステム ストアド プロシージャを使用すると、カーソルの特性を判断できます。

カーソルを宣言する select_statement の一部として、変数を使用できます。カーソルが宣言された後、カーソル変数の値は変更されません。SQL Server のバージョン 6.5 以前では、カーソルが再オープンされるたびに変数の値が更新されます。

権限

DECLARE CURSOR 権限は、特に指定のない限りカーソル内で使用されるビュー、テーブル、および列の SELECT 権限を持つユーザーに与えられます。

例

A. 単純なカーソルと構文を使用する

このカーソルのオープン時に作成された結果セットには、テーブルに存在するすべての行と列が含まれています。このカーソルは更新することができ、すべての更新結果および削除結果は、このカーソルに対して行ったフェッチに反映されます。ただし、この例では SCROLL オプションを指定していないため、フェッチに使えるのは FETCH NEXT のみです。

DECLARE vend_cursor CURSOR
FOR SELECT * FROM Purchasing.Vendor
OPEN vend_cursor
FETCH NEXT FROM vend_cursor

B. 入れ子になったカーソルを使用してレポート出力を作成する

この例では、カーソルを入れ子にして複雑なレポートを作成する方法を示します。内部のカーソルは、各製造元に対して宣言されます。

SET NOCOUNT ON

DECLARE @vendor_id int, @vendor_name nvarchar(50),
@message varchar(80), @product nvarchar(50)

PRINT '-------- Vendor Products Report --------'

DECLARE vendor_cursor CURSOR FOR 
SELECT VendorID, Name
FROM Purchasing.Vendor
WHERE PreferredVendorStatus = 1
ORDER BY VendorID

OPEN vendor_cursor

FETCH NEXT FROM vendor_cursor 
INTO @vendor_id, @vendor_name

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ' '
SELECT @message = '----- Products From Vendor: ' + 
@vendor_name

PRINT @message

-- Declare an inner cursor based   
-- on vendor_id from the outer cursor.

DECLARE product_cursor CURSOR FOR 
SELECT v.Name
FROM Purchasing.ProductVendor pv, Production.Product v
WHERE pv.ProductID = v.ProductID AND
pv.VendorID = @vendor_id-- Variable value from the outer cursor

OPEN product_cursor
FETCH NEXT FROM product_cursor INTO @product

IF @@FETCH_STATUS <> 0 
PRINT '         <<None>>'     

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @message = '         ' + @product
PRINT @message
FETCH NEXT FROM product_cursor INTO @product

END

CLOSE product_cursor
DEALLOCATE product_cursor

-- Get the next vendor.
FETCH NEXT FROM vendor_cursor 
INTO @vendor_id, @vendor_name
END 
CLOSE vendor_cursor
DEALLOCATE vendor_cursor

参照

関連項目

@@FETCH_STATUS (Transact-SQL)
CLOSE (Transact-SQL)
カーソル (Transact-SQL)
DEALLOCATE (Transact-SQL)
FETCH (Transact-SQL)
SELECT (Transact-SQL)
sp_configure (Transact-SQL)

ヘルプおよび情報

SQL Server 2005 の参考資料の入手

変更履歴