DECLARE CURSOR (Transact-SQL)

[アーティクル]
04/07/2024

適用対象:SQL Server Azure SQL Database Azure SQL Managed Instance

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

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) を使用可能に指定します。 ISO DECLARE CURSORで指定されていない場合SCROLLは、NEXTサポートされている唯一のフェッチオプションです。 SCROLL も指定されている場合 FAST_FORWARD は指定できません。指定されていない場合 SCROLL は、フェッチオプション NEXT のみが使用でき、カーソル FORWARD_ONLYは .

select_statement

カーソルの結果セットを定義する標準 SELECT ステートメント。キーワード (keyword)FOR BROWSEINTO。カーソル宣言のselect_statement内では使用できません。

select_statement 内の句が、要求されたカーソルの種類の機能と矛盾する場合、SQL Server によってカーソルが別の種類に暗黙的に変換されます。

READ ONLY

このカーソルによる更新を禁止します。カーソルは、or DELETE ステートメント内のWHERE CURRENT OF句でUPDATE参照できません。このオプションは、更新対象のカーソルの既定の機能をオーバーライドします。

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キーワード (keyword)KEYSETSTATIC、またはSCROLL指定されていない場合、またはDYNAMIC指定FORWARD_ONLYされていない場合は既定値です。 STATIC、KEYSET、および DYNAMIC の各カーソルは既定で SCROLL になります。 ODBC や ADO などのデータベース API と異なり、FORWARD_ONLY は、STATIC、KEYSET、DYNAMIC の Transact-SQL カーソルでサポートされます。

STATIC

カーソルが最初に開かれた時点での結果セットを常に表示し、カーソルによって使用されるデータの一時的なコピーを作成することを指定します。カーソルに対するすべての要求は、次の一時テーブル tempdbから応答されます。したがって、ベーステーブルに対して行われた挿入、更新、および削除は、このカーソルに対して行われたフェッチによって返されるデータには反映されず、カーソルを開いた後に結果セットのメンバーシップ、順序、または値に加えられた変更は、このカーソルで検出されません。静的カーソルは、独自の更新、削除、挿入を検出する場合がありますが、これを行う必要はありません。

たとえば、静的カーソルが行をフェッチした後で、別のアプリケーションによってその行が更新されるものとします。アプリケーションで静的カーソルから行を再フェッチした場合、他のアプリケーションによって変更が行われたにも関わらず、認識される値は変更されていません。すべての種類のスクロールがサポートされています。

KEYSET

カーソルを開くときに、カーソル内の行の構成要素と順序が固定されることを指定します。行を一意に識別するキーのセットは、キーセットと呼ばれるテーブル tempdb に 組み込まれます。このカーソルでは、変更を検出する機能において、静的カーソルと動的カーソルの中間の機能が提供されます。静的カーソルと同様に、結果セットのメンバーシップと順序の変更が常に検出されるとは限りません。動的カーソルと同様に、結果セット内の行の値に対する変更は検出されます。

キーセットドリブンカーソルは、キーセットという一意の識別子 (キー) のセットにより制御されます。これらのキーは、結果セットの行を一意に識別する列のセットから構築されます。キーセットは、クエリステートメントによって返されるすべての行からのキー値のセットです。キーセットドリブンカーソルでは、カーソルの行ごとにキーが作成されて保存され、クライアントワークステーションまたはサーバーに格納されます。各行にアクセスすると、格納されているキーを使用して、データソースから現在のデータ値がフェッチされます。キーセットドリブンカーソルでは、キーセットが完全に作成された時点で、結果セットのメンバーシップは凍結されます。その後、メンバーシップに影響を与える追加または更新は、再度開くまで結果セットの一部ではありません。

(キーセットの所有者または他のプロセスによって行われた) データ値に対する変更は、ユーザーが結果セットをスクロールすると表示されます。

行が削除された場合、削除された行が結果セットのギャップとして表示されるため、行をフェッチしようとすると"の値が返@@FETCH_STATUS-2されます。行に対するキーはキーセット内に存在しますが、結果セットには行は存在しなくなっています。
(他のプロセスによって) カーソルの外部で行われた挿入は、カーソルを閉じて再度開いた場合にのみ表示されます。カーソルの内部から行われた挿入は、結果セットの末尾に表示されます。
カーソル外部からキー値を更新すると、古い行を削除した後で新しい行を挿入した場合と同様に、新しい値を持つ行は表示されず、古い値を持つ行をフェッチしようとすると、 @@FETCH_STATUS 次の -2値が返されます。新しい値は、WHERE CURRENT OF 句を指定してカーソルから更新が行われた場合に表示されます。

Note

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

DYNAMIC

変更がカーソル内またはカーソル外の他のユーザーのどちらによって行われたのかに関係なく、カーソルをスクロールして新しいレコードをフェッチすると、結果内の行に対して行われたすべてのデータ変更が反映されるカーソルを定義します。したがって、すべてのユーザーによって行われたすべての挿入、更新、削除ステートメントが、カーソルによって表示されます。行のデータ値、順序、メンバーシップは、各フェッチ操作で変化する可能性があります。フェッチオプションは ABSOLUTE 、動的カーソルではサポートされていません。カーソルの外部で行われた更新は、コミットされるまで表示されません (カーソルトランザクション分離レベルが設定UNCOMMITTEDされていない限り)。

たとえば、動的カーソルが 2 つの行をフェッチし、別のアプリケーションがそれらの行の 1 つを更新し、もう 1 つの行を削除するとします。動的カーソルがそれらの行をフェッチすると、削除された行は見つかりませんが、更新された行の新しい値が表示されます。

FAST_FORWARD

パフォーマンスの最適化が有効に設定された FORWARD_ONLY、READ_ONLY カーソルを指定します。 FAST_FORWARDを指定できない場合、またはFOR_UPDATE指定されている場合SCROLLは指定できません。この種類のカーソルでは、カーソル内からデータを変更することはできません。

Note

FAST_FORWARD と FORWARD_ONLY の両方を同じ DECLARE CURSOR ステートメントで使用できます。

READ_ONLY

SCROLL_LOCKS

カーソルによって行われる位置指定更新または位置指定削除の成功が保証されることを指定します。 SQL Server はカーソルに読み取られた行をロックし、後で変更できることを保証します。 SCROLL_LOCKSを指定できない場合、またはSTATIC指定されている場合FAST_FORWARDは指定できません。

OPTIMISTIC

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

TYPE_WARNING

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

select_statement

カーソルの結果セットを定義する標準 SELECT ステートメント。キーワード (keyword)、COMPUTE、 COMPUTE BYINTOFOR BROWSEカーソル宣言のselect_statement内では使用できません。

Note

カーソル宣言内でクエリヒントを使用できます。ただし、句も使用するFOR UPDATE OF場合は、after 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 つのフォームを混在することはできません。キーワード (keyword)のSCROLL前にキーワード (keyword)INSENSITIVEを指定した場合、キーワード (keyword)間のキーワード (keyword)はCURSORFOR <select_statement> 使用CURSORできません。とキーワード (keyword)の間にCURSORFOR <select_statement> キーワード (keyword)を指定した場合、キーワード (keyword)のCURSOR前にINSENSITIVE指定SCROLLすることはできません。

DECLARE CURSOR Using Transact-SQL 構文で指定READ_ONLYOPTIMISTICされていない場合、既定値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 (Transact-SQL)	現在接続時に可視であるカーソルとその属性の一覧を返します。
sp_describe_cursor (Transact-SQL)	カーソルの属性 (前方専用カーソルかスクロールカーソルかなど) について説明します。
sp_describe_cursor_columns (Transact-SQL)	カーソル結果セット内の列の属性を記述します。
sp_describe_cursor_tables (Transact-SQL)	カーソルがアクセスするベーステーブルを記述します。

変数は、カーソルを宣言する 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;

DECLARE CURSOR (Transact-SQL)

構文

引数

cursor_name

INSENSITIVE

SCROLL

select_statement

READ ONLY

UPDATE [ OF column_name [ ,...n ]

cursor_name

LOCAL

GLOBAL

FORWARD_ONLY

STATIC

KEYSET

DYNAMIC

FAST_FORWARD

READ_ONLY

SCROLL_LOCKS

OPTIMISTIC

TYPE_WARNING

select_statement

FOR UPDATE [ OF column_name [ ,...n ]

注釈

アクセス許可

制限事項

例

A. 基本的なカーソルと構文を使用する

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

フィードバック

フィードバック

その他のリソース

DECLARE CURSOR (Transact-SQL)

構文

引数

cursor_name

INSENSITIVE

SCROLL

select_statement

READ ONLY

UPDATE [ OF column_name [ ,...n ]

cursor_name

LOCAL

GLOBAL

FORWARD_ONLY

STATIC

KEYSET

DYNAMIC

FAST_FORWARD

READ_ONLY

SCROLL_LOCKS

OPTIMISTIC

TYPE_WARNING

select_statement

FOR UPDATE [ OF column_name [ ,...n ]

注釈

アクセス許可

制限事項

例

A. 基本的なカーソルと構文を使用する

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

関連するコンテンツ

フィードバック

フィードバック

その他のリソース