SQL Serverでカーソルを使う時の備忘録 | cloud.config Tech Blog

こんにちは、あおいです。
今年の夏も猛暑ですが、水分補給をしっかりして熱中症対策をしましょう、、、マスクが暑すぎる、、、

さて、T-SQLのカーソルとは、SELECT 文で抽出されたレコードを1行ずつフェッチ(取得)して、複雑な処理をしたい場合に有効です。

ただし、場合によっては処理のパフォーマンスを落ちる可能性もあるので、カーソルの使用には検討の必要があります…

今回はSQL Serverでカーソルの使い方を備忘録として記事に残します。

いつもどうやって書くんだっけ？となって忘れちゃうので(忘れるな)、この記事見れば大丈夫(のはず)。

OldCustomerテーブルのデータをNewCustomerテーブルにインサートする、という超シンプルなロジックでカーソル処理を試してみます。

まず、以下のスクリプトを実行します。

DROP TABLE IF EXISTS OldCustomer
DROP TABLE IF EXISTS NewCustomer

CREATE TABLE OldCustomer(
	CustomerID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
	Name NVARCHAR(20),
	Job NVARCHAR(20),
	InvestmentHistory INT
)

CREATE TABLE NewCustomer(
	CustomerID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
	Name NVARCHAR(20),
	Job NVARCHAR(20),
	InvestmentHistory INT
)

INSERT INTO OldCustomer
	(Name, Job, InvestmentHistory)
VALUES
(N'佐藤一郎', N'公務員', 1),
(N'鈴木二郎', N'会社員', 3),
(N'高橋三郎', N'大学生', 0),
(N'田中四郎', N'エンジニア', 5),
(N'伊藤五郎', N'経営者', 20)

①カーソルで使用する変数を宣言

DECLARE @Name NVARCHAR(20),
		@Job  NVARCHAR(20),
		@InvestmentHistory INT

②カーソルを宣言する

DECLARE cs CURSOR 
	LOCAL FAST_FORWARD
FOR
	SELECT Name, Job, InvestmentHistory
	FROM Customer

③カーソルを開く

OPEN cs

④ループでレコードを1行ずつフェッチ(取得)して処理

FETCH NEXT FROM cs 
INTO @Name, @Job, @InvestmentHistory

WHILE @@FETCH_STATUS = 0
		BEGIN
	
			INSERT INTO NewCustomer
			VALUES
			(@Name, @Job, @InvestmentHistory)

			FETCH NEXT FROM cs
			INTO @Name, @Job, @InvestmentHistory

		END

⑤カーソルを閉じる

CLOSE cs

⑥リソースを開放する

DEALLOCATE cs

また、トランザクション制御と例外処理も頻繁に使う大事な操作なので、セットとして覚えておきましょう。

トランザクション制御の基本構文は以下の通りです。

BEGIN TRANSACTION -- トランザクションの開始
［一連の処理］
COMMIT TRANSACTION -- トランザクションを終了して、変更を確定する
ROLLBACK TRANSACTION -- トランザクションを終了して、変更を取り消す

単純に、ロールバックでトランザクションを打ち消したいだけの場合、XACT_ABORTをONにすると良いです。XACT_ABORT を ON にすると、エラーが起こるとトランザクションの開始までロールバックしてくれるので、ROLLBACK TRANSACTION を使う必要はありません。

SET XACT_ABORT ON
BEGIN TRANSACTION
［一連の処理］
COMMIT TRANSACTION

例外処理の基本構文は以下の通りです。

BEGIN TRY
［例外が発生しうる処理］
END TRY

BEGIN CATCH
［例外発生時の処理］
END CATCH

全てを踏まえて記述すると、以下の通りになります。

-- カーソルで使用する変数を宣言
DECLARE @Name NVARCHAR(20),
		@Job  NVARCHAR(20),
		@InvestmentHistory INT

-- カーソルを宣言する
DECLARE cs CURSOR
  -- 順次にデータを取得するだけの場合、FAST_FOWARDオプションを付けるとパフォーマンス向上
	LOCAL FAST_FORWARD
FOR
	SELECT Name, Job, InvestmentHistory
	FROM Customer

-- カーソルを開く
OPEN cs

-- ループでレコードを1行ずつ取得して処理
FETCH NEXT FROM cs 
INTO @Name, @Job, @InvestmentHistory

BEGIN TRY
	BEGIN TRANSACTION -- トランザクションの開始
		WHILE @@FETCH_STATUS = 0
		BEGIN
	
			INSERT INTO NewCustomer
			VALUES
			(@Name, @Job, @InvestmentHistory)

			-- 次の行を取得する
			FETCH NEXT FROM cs
			INTO @Name, @Job, @InvestmentHistory

		END
	COMMIT TRANSACTION --トランザクションを確定
END TRY

-- 例外処理
BEGIN CATCH
	ROLLBACK TRANSACTION  -- トランザクションの打ち消し
	PRINT ERROR_MESSAGE() -- エラー内容を出力
END CATCH

-- カーソルを閉じる
CLOSE cs
-- リソースを解放する
DEALLOCATE cs

今回はSQL Serverでカーソルを使う時の備忘録をただ書き記しました。備忘録なのでお許しを。

参考記事
カーソル(Transact-SQL) | Microsoft Docs
トランザクション(Transact-SQL) | Microsoft Docs
TRY...CATCH(Transact-SQL) | Microsoft Docs

【補足】

今回の場合、OldCustomerテーブルのデータをNewCustomerテーブルにそのまま挿入している処理なので、カーソルを使わずに INSERT INTO ... SELECT の記述で同様の処理が可能です。
ただ、INSERT INTO ... SELECT での記述はインサート先のテーブルが既に存在していることが前提です。

INSERT INTO NewCustomer(
	Name,
	Job,
	InvestmentHistory
)
SELECT Name,
	Job,
	InvestmentHistory
FROM OldCustomer

もし、インサート対象のテーブルを用意するのが面倒であれば、SELECT INTO ... FROM で処理が可能です。
ただ、SELECT INTO ... FROMでの記述はインサート先のテーブルが存在していないことが前提です。

SELECT Name,
	   Job,
	   InvestmentHistory
INTO NewCutomer
FROM OldCustomer