DECLARE CURSOR (Transact-SQL)
適用対象: SQL Server
Azure SQL Database
Azure SQL Managed Instance
Transact-SQL サーバー カーソルの属性を定義します。これには、スクロール動作や、カーソルが操作する結果セットを作成するクエリなどが含まれます。 DECLARE CURSOR
は、ISO 標準に基づく構文と、Transact-SQL の拡張機能のセットを使用する構文の両方で指定できます。
構文
ISO 構文:
DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR
FOR select_statement
[ FOR { READ_ONLY | UPDATE [ OF column_name [ , ...n ] ] } ]
[ ; ]
Transact-SQL 拡張構文:
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 ] ] ]
[ ; ]
Note
SQL Server 2014 (12.x) 以前のバージョンの Transact-SQL 構文を確認するには、以前のバージョンのドキュメントを参照してください。
引数
cursor_name
定義されている Transact-SQL サーバー カーソルの名前。 cursor_name は識別子の規則に準拠している必要があります。
INSENSITIVE
データの一時コピーを作成するためのカーソルを定義します。作成されるコピーは、カーソルで使用されます。 カーソルに対するすべての要求は、 tempdb
のこの一時テーブルから応答されます。 したがって、ベース テーブルの変更は、このカーソルに対して行われたフェッチによって返されるデータには反映されず、このカーソルでは変更が許可されません。 ISO 構文で INSENSITIVE
を指定しない場合は、任意のユーザーによって基になるテーブルに加えられた削除および更新がコミットされると、以降のフェッチで反映されます。
SCROLL
すべてのフェッチ オプション (FIRST
、LAST
、PRIOR
、NEXT
、RELATIVE
、ABSOLUTE
) を使用可能に指定します。 DECLARE CURSOR
で指定されていない場合は、NEXT
が唯一のフェッチ オプションとしてサポートされます。 SCROLL
FAST_FORWARD
も指定されている場合は指定できません。 SCROLL
が指定されていない場合は、NEXT
フェッチ オプションのみが使用でき、カーソルはFORWARD_ONLY
になります。
select_statement
カーソルの結果セットを定義する標準の SELECT
ステートメント。 キーワード FOR BROWSE
、および INTO
は、カーソル宣言の select_statement 内では許可されません。
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
カーソルのスコープは、接続に対してグローバルです。 カーソル名は、その接続によって実行されるストアド プロシージャやバッチの中で参照できます。 カーソルは、切断のときだけ暗黙的に割り当てを解除されます。
Note
GLOBAL
も LOCAL
も指定しない場合は、default to local cursor データベース オプションの設定によって既定の動作が決まります。
FORWARD_ONLY
カーソルは前方にだけ移動でき、先頭行から最終行までスクロールできることを指定します。 FETCH NEXT
は、サポートされている唯一のフェッチ オプションです。 結果セット内の行に影響を与える現在のユーザー (または他のユーザーによってコミットされた) によって行われた挿入、更新、および削除のすべてのステートメントは、行がフェッチされると表示されます。 ただし、カーソルを後方にスクロールすることはできないため、行がフェッチされた後にデータベース内の行に加えられた変更は、カーソルを通じて表示されません。 順方向専用カーソルは既定では動的であり、現在の行が処理されるとすべての変更が検出されることを意味します。 これにより、カーソルを開く時間が短縮され、基になるテーブルに対して行われた更新を結果セットで表示できるようになります。 前方スクロールのみのカーソルは後方スクロールをサポートしていませんが、アプリケーションはカーソルを閉じて再度開くことで、結果セットの先頭に戻ることができます。
STATIC
、KEYSET
、または DYNAMIC
キーワードなしで FORWARD_ONLY
を指定した場合、カーソルは動的カーソルとして動作します。 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
カーソルを開くときに、カーソル内の行の構成要素と順序が固定されることを指定します。 行を一意に識別するキーのセットは、keyset と呼ばれるtempdb
のテーブルに組み込まれます。 このカーソルでは、変更を検出する機能において、静的カーソルと動的カーソルの中間の機能が提供されます。 静的カーソルと同様に、結果セットのメンバーシップと順序の変更が常に検出されるとは限りません。 動的カーソルと同様に、結果セット内の行の値に対する変更は検出されます。
キーセット ドリブン カーソルは、キーセットという一意の識別子 (キー) のセットにより制御されます。 これらのキーは、結果セットの行を一意に識別する列のセットから構築されます。 キーセットは、クエリ ステートメントによって返されるすべての行からのキー値のセットです。 キーセット ドリブン カーソルでは、カーソルの行ごとにキーが作成されて保存され、クライアント ワークステーションまたはサーバーに格納されます。 各行にアクセスすると、格納されているキーを使用して、データ ソースから現在のデータ値がフェッチされます。 キーセット ドリブン カーソルでは、キーセットが完全に作成された時点で、結果セットのメンバーシップは凍結されます。 その後、メンバーシップに影響を与える追加または更新は、再度開くまで結果セットの一部ではありません。
(キーセットの所有者または他のプロセスによって行われた) データ値に対する変更は、ユーザーが結果セットをスクロールすると表示されます。
行が削除されると、削除された行が結果セットのギャップとして表示されるため、行をフェッチしようとすると
-2
の@@FETCH_STATUS
が返されます。 行に対するキーはキーセット内に存在しますが、結果セットには行は存在しなくなっています。(他のプロセスによって) カーソルの外部で行われた挿入は、カーソルを閉じて再度開いた場合にのみ表示されます。 カーソルの内部から行われた挿入は、結果セットの末尾に表示されます。
カーソル外部からキー値を更新すると、古い行を削除した後で新しい行を挿入した場合と同様に、 新しい値を持つ行は表示されず、古い値を持つ行をフェッチしようとすると、
-2
の@@FETCH_STATUS
が返されます。 新しい値は、WHERE CURRENT OF
句を指定してカーソルから更新が行われた場合に表示されます。
Note
クエリが一意なインデックスのないテーブルを少なくとも 1 つ参照する場合、このキーセット カーソルは静的カーソルに変換されます。
DYNAMIC
変更がカーソル内またはカーソル外の他のユーザーのどちらによって行われたのかに関係なく、カーソルをスクロールして新しいレコードをフェッチすると、結果内の行に対して行われたすべてのデータ変更が反映されるカーソルを定義します。 したがって、すべてのユーザーによって行われたすべての挿入、更新、削除ステートメントが、カーソルによって表示されます。 行のデータ値、順序、メンバーシップは、各フェッチ操作で変化する可能性があります。 ABSOLUTE
フェッチ オプションは、動的カーソルではサポートされていません。 カーソルの外部で行われた更新は、コミットされるまで表示されません (カーソル トランザクション分離レベルが UNCOMMITTED
に設定されていない場合)。
たとえば、動的カーソルが 2 つの行をフェッチし、別のアプリケーションがそれらの行の 1 つを更新し、もう 1 つの行を削除するとします。 動的カーソルがそれらの行をフェッチすると、削除された行は見つかりませんが、更新された行の新しい値が表示されます。
FAST_FORWARD
パフォーマンスの最適化が有効に設定された FORWARD_ONLY
、READ_ONLY
カーソルを指定します。 FAST_FORWARD
SCROLL
またはFOR_UPDATE
も指定されている場合は、指定できません。 この種類のカーソルでは、カーソル内からデータを変更することはできません。
Note
FAST_FORWARD
と FORWARD_ONLY
の両方を同じ DECLARE CURSOR
ステートメントで使用できます。
READ_ONLY
このカーソルによる更新を禁止します。 UPDATE
またはDELETE
ステートメントのWHERE CURRENT OF
句でカーソルを参照することはできません。 このオプションは、更新対象のカーソルの既定の機能をオーバーライドします。
SCROLL_LOCKS
カーソルによって行われる位置指定更新または位置指定削除の成功が保証されることを指定します。 SQL Server はカーソルに読み取られた行をロックし、後で変更できることを保証します。 SCROLL_LOCKS
FAST_FORWARD
またはSTATIC
も指定されている場合は、指定できません。
OPTIMISTIC
行がカーソルに読み込まれた後に更新された場合に、カーソルを介して行われた位置指定された更新または削除が成功しないことを指定します。 SQL Server では、行がカーソルに読み取られるとき、その行はロックされません。 代わりに timestamp 列の値を比較するか、テーブルに timestamp 列がない場合はチェックサム値を使用して、行がカーソルに読み込まれてから変更されたかどうかが判別されます。
行が変更されている場合、位置指定更新または位置指定削除の試行は失敗します。 OPTIMISTIC
FAST_FORWARD
も指定されている場合は指定できません。
STATIC
がOPTIMISTIC
カーソル引数と共に指定されている場合、2 つの組み合わせは、STATIC
引数とREAD_ONLY
引数の組み合わせ、またはSTATIC
引数とFORWARD_ONLY
引数の組み合わせに暗黙的に変換されます。
TYPE_WARNING
カーソルの種類が、要求されたものから別のものに暗黙的に変換された場合、クライアントに警告メッセージが送信されることを指定します。
OPTIMISTIC
カーソル引数とSTATIC
カーソル引数の組み合わせが使用され、カーソルが暗黙的にSTATIC READ_ONLY
カーソルまたはSTATIC FORWARD_ONLY
カーソルに変換される場合、警告はクライアントに送信されません。 READ_ONLY
への変換は、クライアントの観点からFAST_FORWARD
およびREAD_ONLY
カーソルに変わります。
select_statement
カーソルの結果セットを定義する標準の SELECT
ステートメント。 キーワード COMPUTE
、 COMPUTE BY
、 FOR BROWSE
、および INTO
は、カーソル宣言の select_statement 内では使用できません。
Note
カーソル宣言内でクエリ ヒントを使用できます。 ただし、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
ステートメントは、ISO 構文を使用してカーソルの動作を宣言します。 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
ステートメントでのみフェッチできます。
カーソルが宣言された後、これらのシステム ストアド プロシージャを使用してカーソルの特性を判断できます。
システム ストアド プロシージャ | 説明 |
---|---|
sp_cursor_list | 現在接続時に可視であるカーソルとその属性の一覧を返します。 |
sp_describe_cursor | カーソルの属性 (前方専用カーソルかスクロール カーソルかなど) について説明します。 |
sp_describe_cursor_columns | カーソル結果セット内の列の属性を記述します。 |
sp_describe_cursor_tables | カーソルがアクセスするベース テーブルを記述します。 |
変数は、カーソルを宣言する select_statement の一部として使用できます。 カーソル変数の値は、カーソルが宣言された後は変更されません。
アクセス許可
DECLARE CURSOR
権限は、特に指定のない限りカーソル内で使用されるビュー、テーブル、および列の SELECT
権限を持つユーザーに与えられます。
制限事項
クラスター化列ストア インデックスを使用しているテーブルでは、カーソルやトリガーは使用できません。 この制限は非クラスター化列ストア インデックスには適用されません。 非クラスター化列ストア インデックスを使用しているテーブルでは、カーソルとトリガーを使用できます。
例
A. 基本的なカーソルと構文を使用する
このカーソルのオープン時に作成された結果セットには、テーブルに存在するすべての行と列が含まれています。 このカーソルは更新することができ、すべての更新結果および削除結果は、このカーソルに対して行ったフェッチに反映されます。 FETCH NEXT
は、 SCROLL
オプションが指定されていないため、使用可能な唯一のフェッチです。
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;