コメント
コメントの投稿
※名前とタイトルが入力されていないコメントでは他のコメントとの区別ができません。
入力されていないコメントには返信しませんのであらかじめご了承くださいませ。
ストアドプロシージャ内にてカーソルでループして更新する方法 | SQL Server
- 2017/02/25
- 18:30
今回はストアドプロシージャ（以下SPと呼ぶ）を記述する際に、ループして更新する方法にカーソルがありますが、その記述方法についてご紹介いたします。
前提条件
・Windows 7 professional 以降 / Windows Server 2008 R2 以降
・SQL Server 2005 Developer / Standard / Enterprise 以降
１．カーソルの宣言
DECLARE カーソル名 CURSOR LOCAL FOR SELECT文２．ループのオープン・ループ方法
OPEN カーソル名FETCH NEXT FROM カーソル名
INTO 変数;
WHILE @@FETCH_STATUS = 0
BEGIN
・
・--ループ内の処理
・
FETCH NEXT FROM カーソル名
INTO 変数;
END;
３．カーソルを終了する方法
CLOSE カーソル名 ;DEALLOCATE カーソル名;
４．サンプルコード
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 に関する記事をご紹介しておりますのでご参考頂ければ幸いです。
LinqでDictionary型から条件を指定してフィルターし、ToDictionaryで元の型に変換する方法 | VB.NET ホーム
不要なアプリが勝手にインストールされるのを防ぐ方法 | Windows10
