記事一覧

ストアドプロシージャ内にてカーソルでループして更新する方法 | SQL Server


今回はストアドプロシージャ(以下SPと呼ぶ)を記述する際に、ループして更新する方法にカーソルがありますが、その記述方法についてご紹介いたします。


前提条件
Windows 7 professional 以降 / Windows Server 2008 R2 以降
SQL Server 2005 Developer / Standard 以上


SQL_Cursor_00.png



1.カーソルの宣言

DECLARE カーソル名 CURSOR LOCAL FOR SELECT文



2.ループのオープン・ループ方法

OPEN カーソル名
FETCH NEXT FROM カーソル名
INTO 変数;
WHILE @@FETCH_STATUS = 0
BEGIN
    ・
    ・--ループ内の処理
    ・
    FETCH NEXT FROM カーソル名
    INTO 変数;
END;



3.カーソルを終了する方法

CLOSE  カーソル名 ;
DEALLOCATE カーソル名;



4.サンプルコード

USE [tsetDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_UpdateTotalPriceIncludeTax]
@RetCode     INT OUTPUT    --エラーコード
AS
BEGIN
--------------------------------------------------------------------
    --変数宣言
--------------------------------------------------------------------
    DECLARE @CntRecord     INT;              --レコードカウント
    DECLARE @OrderCode             NUMERIC(18, 0);   --受注番号
    DECLARE @TotalPriceIncludeTax  MONEY;            --税込合計金額

    --------------------------------------------------------------------
    --カーソル宣言
--------------------------------------------------------------------
    DECLARE Cur_CustomerOrder CURSOR LOCAL FOR
      SELECT  OrderCode
      FROM    ES30_CustomerOrder AS ES30
      WHERE   ES30.Deleted = 0
      ;

--------------------------------------------------------------------
--更新処理
--------------------------------------------------------------------
    BEGIN TRANSACTION   
    BEGIN TRY       
        --カーソルオープン
        OPEN Cur_CustomerOrder   
        FETCH NEXT FROM Cur_CustomerOrder
        --最初の行[0番目]の値の格納
        INTO @OrderCode;
        --@@FETCH_STATUS:最後の行を超えたときは-2が設定される。
        WHILE @@FETCH_STATUS = 0
        BEGIN

            --受注番号ごとに税込合計金額の取得
            SELECT  @TotalPriceIncludeTax =
dbo.usp_RoundDown(SUM(ES31.Price), 0) +
dbo.usp_RoundDown(SUM(ES31.Tax), 0)
            FROM    ES30_CustomerOrder AS ES30
            LEFT JOIN
                    ES31_CustomerOrder_Details AS ES31
            ON      ES30.OrderCode = ES31.OrderCode
            WHERE   ES30.OrderCode = @OrderCode
            AND     ES30.Deleted = 0
            AND     ES31.Deleted = 0
            ;
            --取得した合計金額で受注を更新
            UPDATE  ES30_CustomerOrder
            SET      TotalPriceIncludeTax = @TotalPriceIncludeTax
            WHERE    OrderCode = @OrderCode
            AND      Deleted = 0
            ;
           
            --更新レコード数取得
            SET @CntRecord = @@ROWCOUNT;
            IF @CntRecord = 1
                --成功時の処理
                BEGIN
                    SET @RetCode = 0;
                END;
            ELSE
                BEGIN
                    SET @RetCode = 1;
                    --エラーの場合ロールバックして終了
                    ROLLBACK TRANSACTION;
                    --カーソル処理の終了宣言
                    CLOSE Cur_CustomerOrder ;
                    DEALLOCATE Cur_CustomerOrder;
                    RETURN;
                END;   
           
            FETCH NEXT FROM Cur_CustomerOrder
            --次の行の値の格納
            INTO @OrderCode;
        END;
       
    END TRY
    BEGIN CATCH
        SET @RetCode = 1;
        --エラーの場合ロールバックして終了
        ROLLBACK TRANSACTION;
        --カーソル処理の終了宣言
        CLOSE Cur_CustomerOrder ;
        DEALLOCATE Cur_CustomerOrder;
        RETURN;
    END CATCH;

--------------------------------------------------------------------
    --終了処理
--------------------------------------------------------------------
    CLOSE Cur_CustomerOrder ;
    DEALLOCATE Cur_CustomerOrder;
       
    IF @RetCode = 0
        --成功時の処理
        BEGIN
            --全て成功ならコミット
            COMMIT TRANSACTION;
        END;
    ELSE
        BEGIN
            SET @RetCode = 1 ;
            --エラーの場合ロールバックして終了
            ROLLBACK TRANSACTION;
            RETURN;
        END;   
END;
GO



関連記事

コメント

コメントの投稿

広告

プロフィール

石河 純


著者名 :石河 純
自己紹介:素人上がりのIT技術者。趣味は卓球・車・ボウリング

IT関連の知識はざっくりとこんな感じです。
【OS関連】
WindowsServer: 2012/2008R2/2003/2000/NT4
Windows: 10/8/7/XP/2000/me/NT4/98
Linux: CentOS RedHatLinux9
Mac: macOS Sierra 10.12 / OSX Lion 10.7.5 / OSX Snow Leopard 10.6.8
【言語・データベース】
VB.net ASP.NET C#.net Java VBA
Xamarin.Forms
Oracle10g SQLServer2008R2 SQLAnywhere8/11/16
ActiveReport CrystalReport ReportNet(IBM)
【ネットワーク関連】
CCNP シスコ技術者認定
Cisco Catalyst シリーズ
Yamaha RTXシリーズ
FireWall関連
【WEB関連】
SEO SEM CSS IIS6/7 apache2

休みの日は卓球をやっています。
現在、卓球用品通販ショップは休業中です。