T-SQL カーソルの使い方とサンプル

T-SQL のカーソルについて

カーソルは結果セットを 1 レコードずつループして処理したいような時に便利ですが、パフォーマンスはあまり良くないことが多いです。

まずはカーソルを使わないでやれる方法がないか、考えてみましょう。案外見つかるものです。

他に良い案がない時は、カーソルを使いましょう！ここでは、シンプルなカーソルの使い方とサンプルをご紹介します。

シンプルなカーソルの使い方とサンプル

例えば、ユーザーが定義したテーブルについていて、現在有効化されているトリガーのリストを取得したいとします。

その際、ただ取得するだけではなく、トリガーのリストを次のように改行文字を挟んでつなげて、ひとつの文字列にしたいとします。

一つ目のトリガー名[テーブル名] + 改行文字 + 二つ目のトリガー名[テーブル名] + 改行文字　...

カーソルを使わなくてもできそうですが、、、ここではカーソルを使ってやってみましょう。

こちらが、カーソルのサンプルスクリプトです！

DECLARE @TableName NVARCHAR(128),
        @TriggerName NVARCHAR(128),
        @TriggerNameList NVARCHAR(MAX) = '';

DECLARE crTriggerList CURSOR FOR
   SELECT O.name AS TableName,
          TR.name AS TriggerName
   FROM   sys.triggers AS TR
             INNER JOIN sys.objects AS O
                ON TR.parent_id = O.object_id
   WHERE  TR.is_disabled = 0
          AND O.type = 'U'
   ORDER BY TableName, TriggerName;

OPEN crTriggerList;

FETCH NEXT FROM crTriggerList
INTO @TableName, @TriggerName;

WHILE @@FETCH_STATUS = 0
BEGIN

   SET @TriggerNameList = @TriggerNameList
            + @TriggerName
            + '[' + @TableName + ']'
            + CHAR(13);

   FETCH NEXT FROM crTriggerList
   INTO @TableName, @TriggerName;

END

CLOSE crTriggerList;
DEALLOCATE crTriggerList;

PRINT @TriggerNameList;

では、内容を詳しく見てみましょう。

まず、シンプルなカーソルの使い方の流れは緑の枠の部分で、以下の通りです。

1. カーソルを定義する。 (DECLARE カーソル名 CURSOR FOR)
2. カーソルを OPEN する。
3. カーソルから１行取得する。 (FETCH NEXT FROM カーソル名　INTO ...)
4. @@FETCH_STATUS が 0 (success) の間ループ (WHILE @@FECTH STATUS = 0 BEGIN ... END)
a. カーソルを使って１行１行やりたかったことを書く。（赤い枠の部分）
b. ループの一番最後でカーソルから１行取得する。 (FETCH NEXT FROM カーソル名　INTO ...)
5. カーソルを CLOSE する。
6. カーソルを DEALLOCATE する。

青い枠の部分に、カーソルで 1 行 1 行処理したい結果が返るクエリーを指定します。 ORDER BY で処理したい順番に並べておきましょう。

赤い枠の部分に、カーソルを使って１行１行やりたかったことを書きます。

@@FETCH_STATUS はコネクション内の直前のカーソルの FETCH の結果を返すので 4-b の FETCH は WHILE ループの END の直前で行うようにして下さい。

また、カーソル名のスコープはデフォルト、グローバルなので、実行時に名前がかぶらないように気をつけてください。

カーソル名のスコープをローカルにしたい時は、CURSOR の後に LOCAL オプションを次のように指定します。

また、今回のように CURSOR を使ってデータの更新などを行わず、順にデータを取得していくだけの時は FAST_FORWARD オプションをつけるとパフォーマンスが向上します！

DECLARE crTriggerList CURSOR
    LOCAL FAST_FORWARD 
FOR
   SELECT O.name AS TableName,
   .....