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 に関する記事をご紹介しておりますのでご参考頂ければ幸いです。


関連記事

コメント

コメントの投稿

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

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

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

    

※必ずご入力ください。

    
    

※必ずご入力ください。

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

・正確なエラーの内容

・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

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