fc2ブログ

記事一覧

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


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


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


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






最後までお読みいただき、ありがとうございます。
他にも SQL Server に関する記事をご紹介しておりますのでご参考頂ければ幸いです。


関連記事

コメント

コメントの投稿

※名前とタイトルが入力されていないコメントでは他のコメントとの区別ができません。

 入力されていないコメントには返信しませんのであらかじめご了承くださいませ。

※ニックネームでも良いので必ずご入力ください。

    

※必ずご入力ください。

    
    

※必ずご入力ください。

※技術的な質問には環境やエラーについて正確かつ詳細にお教えください。

・正確なエラーの内容

・Windowsのバージョン番号

・Visual Studioのバージョン

・機器の型番

・アプリやソフトのバージョン

    

カテゴリ別記事一覧

広告

プロフィール

石河 純


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

IT関連の知識はざっくりとこんな感じです。
【OS関連】
WindowsServer: 2012/2008R2/2003/2000/NT4
Windows: 10/8/7/XP/2000/me/NT4/98
Linux: CentOS RedHatLinux9
Mac: macOS Catalina 10.15 / Mojave 10.14 / High Sierra 10.13 / Sierra 10.12 / OSX Lion 10.7.5 / OSX Snow Leopard 10.6.8
【言語】
VB.net ASP.NET C#.net Java VBA
Xamarin.Forms
【データベース】
Oracle 10g/9i
SQLServer 2016/2008R2/2005/2000
SQLAnywhere 16/11/8
【BI/レポートツール】
Cognos ReportNet (IBM)
Microsoft PowerBI
ActiveReport (GrapeCity)
CrystalReport
【OCX関連】
GrapeCity InputMan SPREAD MultiRow GridView
【ネットワーク関連】
CCNP シスコ技術者認定
Cisco Catalyst シリーズ
Yamaha RTXシリーズ
FireWall関連
【WEB関連】
SEO SEM CSS jQuery IIS6/7 apache2

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