FC2ブログ

記事一覧

複数のカーソルを開く方法 -ヘッダーと明細で複数のカーソルが必要な場合の記述方法- | SQL Server


今回は SQL Server で複数のカーソルを続けて開く方法を覚え書きします。
例えばヘッダーと明細でヘッダーの番号を元に明細のレコードをフェッチするなどの記述が必要なケースが多々あると思います。
明細のカーソルの記述場所がヘッダーのカーソルの内部に定義しないと正しく動作しませんので、少しハマりどころだと感じました。



sqlserver_cursor_01.png



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



1.実装方法

以下のようにストアドプロシージャを構成します。
テーブルは一時テーブルを使用していますが、あくまでわかりやすいようにしています。実際のテーブル構造にならって書き直してください。
通常のカーソルは先頭部分に定義を記述し、FETCHでループしていきますが、明細のカーソルではヘッダーのキーを元に明細をループさせます。そのため、ヘッダーのキーを明細カーソルの条件に指定しますが、明細カーソルの定義を先頭部分に記述すると正しく動作しません。必ずヘッダーのカーソルをFETCHしてループする内部に記述するようにします。

USE testDB
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
DROP PROCEDURE IF EXISTS dbo.usp_CursorTest
GO
CREATE PROCEDURE dbo.usp_CursorTest
@RetCode INT OUTPUT --エラーコード
AS
BEGIN
SET NOCOUNT ON;

CREATE TABLE #Order
(
OrderCode INT,
OrderPrice MONEY
);
CREATE TABLE #OrderDetails
(
OrderCode INT,
Seq INT,
ProductCode VARCHAR(10),
ProductName NVARCHAR(100),
UnitPrice MONEY,
Qty MONEY
);

--------------------------------------------------------------------
--変数宣言
--------------------------------------------------------------------
DECLARE @OrderCode INT;
DECLARE @Seq INT;
DECLARE @OrderPrice MONEY;
DECLARE @UnitPrice MONEY
DECLARE @Qty MONEY;

--------------------------------------------------------------------
--カーソル宣言
--------------------------------------------------------------------
DECLARE cur_Order CURSOR LOCAL FOR
SELECT OrderCode
FROM #Order
;

--初期化
SET @RetCode = 1;

BEGIN TRY

BEGIN TRANSACTION

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

--初期化
SET @OrderPrice = 0;

--2つ目のカーソルは1つ目のカーソルの注文番号を条件にしている為、ここに記述します。
--明細のカーソルを宣言
DECLARE cur_OrderDetails CURSOR LOCAL FOR
SELECT Seq
,UnitPrice
,Qty
FROM #OrderDetails
WHERE OrderCode = @OrderCode
;

--カーソルオープン
OPEN cur_OrderDetails
FETCH NEXT FROM cur_OrderDetails
--最初の行[0番目]の値の格納
INTO @Seq
,@UnitPrice
,@Qty
;
--@@FETCH_STATUS:最後の行を超えたときは-2が設定される。
WHILE @@FETCH_STATUS = 0
BEGIN

--明細の金額を計算して合計に加算する
SET @OrderPrice += @UnitPrice * @Qty;

FETCH NEXT FROM cur_OrderDetails
--次の行の値の格納
INTO @Seq
,@UnitPrice
,@Qty
;
END;

--カーソル終了
IF CURSOR_STATUS('local', 'cur_OrderDetails') > 0
BEGIN
CLOSE cur_OrderDetails;
DEALLOCATE cur_OrderDetails;
END;

--注文番号毎に合計値を更新
UPDATE #Order
SET OrderPrice = @OrderPrice
WHERE OrderCode = @OrderCode
;

FETCH NEXT FROM cur_Order
--次の行の値の格納
INTO @OrderCode;
END;

COMMIT TRANSACTION;

SET @RetCode = 0;

END TRY
BEGIN CATCH
--エラーの場合ロールバックして終了
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION;
END;
END CATCH;

--------------------------------------------------------------------
--終了処理
--------------------------------------------------------------------
IF CURSOR_STATUS('local', 'cur_Order') > 0
BEGIN
CLOSE cur_Order ;
DEALLOCATE cur_Order;
END;

IF CURSOR_STATUS('local', 'cur_OrderDetails') > 0
BEGIN
CLOSE cur_OrderDetails ;
DEALLOCATE cur_OrderDetails;
END;
END
GO





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


関連記事

コメント

コメントの投稿

カテゴリ別記事一覧

広告

プロフィール

石河 純


著者名 :石河 純
自己紹介:素人上がりの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

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