DECLARE CURSOR (Transact-SQL)

適用対象: 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 ] ] ]
[ ; ]

引数

cursor_name

定義されている Transact-SQL サーバー カーソルの名前。 cursor_name は識別子の規則に準拠している必要があります。

INSENSITIVE

データの一時コピーを作成するためのカーソルを定義します。作成されるコピーは、カーソルで使用されます。 カーソルに対するすべての要求は、 tempdbのこの一時テーブルから応答されます。 したがって、ベース テーブルの変更は、このカーソルに対して行われたフェッチによって返されるデータには反映されず、このカーソルでは変更が許可されません。 ISO 構文で INSENSITIVE を指定しない場合は、任意のユーザーによって基になるテーブルに加えられた削除および更新がコミットされると、以降のフェッチで反映されます。

SCROLL

すべてのフェッチ オプション (FIRST、LAST、PRIOR、NEXT、RELATIVE、ABSOLUTE) を使用可能に指定します。 が ISO DECLARE CURSORで指定されていない場合は、NEXTが唯一のフェッチ オプションとしてサポートされます。 SCROLLFAST_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_FORWARDSCROLLまたはFOR_UPDATEも指定されている場合は、指定できません。 この種類のカーソルでは、カーソル内からデータを変更することはできません。

Note

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

READ_ONLY

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

SCROLL_LOCKS

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

OPTIMISTIC

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

行が変更されている場合、位置指定更新または位置指定削除の試行は失敗します。 OPTIMISTICFAST_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;

関連するコンテンツ

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