SQL SERVER でカーソル についての覚書

データベースで単純なクエリを書くだけでは解決できないような処理をしたいときがあります。他の言語であれば簡単にできそうな処理も、SQL では何かと面倒なことも多いですよね。

そんなときは、もしかするとカーソルを使うと解決できるかもしれません。

 

 

ある程度SQLを書けるようになってくると、少し複雑な処理をしたくなってきます。例えば一行一行取り出して、値によって処理を分岐させたり、テーブルに挿入したり更新したり・・。

そんなときは、カーソルを使うと思い通りの処理ができるようになるかもしれません。

SQL SERVER でカーソルを利用する

カーソルは、SELECT 文を使って抽出されたレコードを一行ずつフェッチして、複雑な処理をしたい場合に有効です。大抵そういう処理は、SELECT 文を駆使しても記述できないような内容だと思います。

ただし、SELECT文と比べると、処理のパフォーマンスを落ちるので、検討するときには SELECT で代用できないかどうか熟考する必要があると思います。

 

どのようにカーソルを使うか

SQL Serverでカーソルを利用する場合は、次のような順序になります。

1. 変数リストを宣言
2. カーソルを宣言
3. カーソルを開く
4. レコードをフェッチしてループ処理
5. カーソルを閉じて、リソースを開放する

 

カーソルで使用する変数を宣言

カーソルでフェッチしたフィールドの値を収める変数を宣言します。変数は、フェッチされるフィールドの分だけ必要になります。また、フィールドの型に合わせて変数の型を定義しています。

DECLARE @shopCd SMALLINT
DECLARE @rackId varchar(5)
DECLARE @rackSeq SMALLINT
DECLARE @rackBranch SMALLINT
DECLARE @rackStatus SMALLINT

 

カーソルを宣言

次にカーソルを宣言します。ここで SELECT で抽出されるフィールドが先程の変数に対応しています。

DECLARE invCur CURSOR
FOR
SELECT shop_cd
    ,rack_id
    ,rack_seq
    ,rack_branch
    ,rack_status
FROM inventory

 

カーソルを開く

カーソルを利用するには、まずカーソルをオープンします。

OPEN invCur

 

ループでレコードをフェッチして処理

カーソルを使用して、レコードをフェッチし、WHILE で、@@FETCH_STATUS = 0 の間だけループさせます。またフェッチされたフィールドは、最初に宣言した変数に代入されます。

FETCH NEXT FROM invCur
INTO @shopCd
    ,@rackUpper
    ,@rackSeq
    ,@rackBranch
    ,@rackStatus;

WHILE @@FETCH_STATUS = 0
BEGIN
    --ここに処理を

    -- 次のレコードをフェッチ
    FETCH NEXT FROM invCur
    INTO @shopCd
        ,@rackUpper
        ,@rackSeq
        ,@rackBranch
        ,@rackStatus;
END

または、WHILE 1 = 1 として無限ループを作成するパターンは以下のようになります。

この場合は、FETCH 文は WHILE ループの中に入ります。@@FETCH_STATUS != 0 になったら BREAK でループを抜けます。

WHILE 1 = 1
BEGIN
    FETCH
    FROM invCur
    INTO @shopCd
        ,@rackId
        ,@rackSeq
        ,@rackBranch
        ,@rackStatus

    IF @@FETCH_STATUS != 0
    BEGIN
        BREAK;
    END
    -- ここに処理を
END

 

カーソルを閉じてリソースを開放する

カーソルを使った処理が完了したら、忘れないようにカーソルをクローズし、カーソルのリソースを開放します。

CLOSE invCur
DEALLOCATE invCur

 

エラー処理の方法

実際のカーソルの処理では、途中でエラーが発生して中断してしまうことがあります。その時、カーソルが閉じられずに残ってしまうので、エラーが発生してもカーソルを閉じ忘れることがないように処理する必要があります。

 

TRY ～ CATCH でエラーをキャッチ

カーソルでの処理中は、エラーが発生することがあるかもしれません。そのため、TRY 構文でエラーを捉えて、CATCH 構文にてエラーに対する処理を行います。

カーソル処理の流れに、TRY ～ CATCH 構文を入れると次のようになります。

--変数宣言
--カーソル宣言

BEGIN TRY
--カーソルを開く
--レコードをフェッチしてループ処理
--カーソルを閉じて、リソースを開放する
END TRY

-- エラーをキャッチした場合
BEGIN CATCH
--カーソルを閉じてリソースを開放する
END CATCH

TRY 文の中に、カーソルを開く～ループ処理～カーソルを閉じて、リソースを開放する 処理を記述します。もしこの処理の途中にエラーが発生した場合は、CATCH 文に処理が移りますので、そちらで忘れずにカーソルを閉じる処理を記述します。

ただ、この例のようにすると、エラー発生時に、エラーの内容等を表示することもなく終了してしまうので、実際には発生したエラーに応じて何らかの処理をすることになると思います。

単純にエラーの内容を表示させる場合は次のようにしてエラーの内容を表示させることができます。

-- エラーをキャッチした場合
BEGIN CATCH
--カーソルを閉じてリソースを開放する
--エラーを表示
    SELECT 'Error while insert ttms_man into wrk_ttms_man' AS ErrorWhile
        ,ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;
END CATCH

 

TRANSACTION でデータの一貫性を保つ

また、エラー発生時、データのの整合性が保つために、トランザクションを使って処理することも多いと思います。エラーを補足した場合は、トランザクションをロールバックするようにします。

--変数宣言
--カーソル宣言
BEGIN TRANSACTION
BEGIN TRY
    --カーソルを開く
    --レコードをフェッチしてループ処理
    --カーソルを閉じて、リソースを開放する
END TRY

-- エラーをキャッチした場合
BEGIN CATCH
    --カーソルを閉じてリソースを開放する
    --トランザクションをロールバック
    ROLLBACK TRANSACTION
    --エラーに関する処理
    --処理を抜ける
    RETURN
END CATCH

-- トランザクションをコミット
COMMIT TRANSACTION

CATCH構文内の最後に、RETURN とありますが、この RETURN 文が入っていないと、CATCH構文を抜けた後の COMMIT TRANSACTION を実行してしまいエラーになります。

ここまでを踏まえて、コードを完成させると以下のようになります。

--変数宣言
DECLARE @shopCd SMALLINT
DECLARE @rackId varchar(5)
DECLARE @rackSeq SMALLINT
DECLARE @rackBranch SMALLINT
DECLARE @rackStatus SMALLINT

--カーソル宣言
DECLARE invCur CURSOR
FOR
SELECT shop_cd
    ,rack_id
    ,rack_seq
    ,rack_branch
    ,rack_status
FROM inventory

BEGIN TRANSACTION
BEGIN TRY
    --カーソルを開く
    OPEN invCur
    --レコードをフェッチしてループ処理
    FETCH NEXT FROM invCur
    INTO @shopCd
        ,@rackId
        ,@rackSeq
        ,@rackBranch
        ,@rackStatus;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        --ここに処理を
        --次のレコードをフェッチ
        FETCH NEXT FROM invCur
        INTO @shopCd
            ,@rackId
            ,@rackSeq
            ,@rackBranch
            ,@rackStatus;
    END

    --カーソルを閉じて、リソースを開放する
    CLOSE invCur
    DEALLOCATE invCur
END TRY

-- エラーをキャッチした場合
BEGIN CATCH
    --カーソルを閉じてリソースを開放する
    CLOSE invCur
    DEALLOCATE invCur
    -- トランザクションをロールバック
    ROLLBACK TRANSACTION
    -- エラーに関する処理
    SELECT 'Error while insert ttms_man into wrk_ttms_man' AS ErrorWhile
        ,ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;
    -- 処理を終了する
    RETURN
END CATCH

-- トランザクションをコミット
COMMIT TRANSACTION

なんだか複雑に見えますが、カーソル処理を実際に行う際にはこういったエラー発生時の処理は必須だと思います。

まとめ

このようにカーソルを使うと、フェッチしたレコードごとに判断して処理させることが可能です。

ただ、パフォーマンスはあまり良くないとも聞くので、カーソルを使わずに実現できないかをよく吟味して使用してください。