100日でSQLの達人になる@LeetCode! Day58 <要復習：CURSOR, FETCHの使い方>

CURSORの使い方について理解する

前回に引き続きLeetCodeの問題はお休み。
しばらくLeetCodeの方はお休みしましたが、そろそろ問題の方に戻ろうと思います。
今回のテーマはCURSORの使い方です。ただ、テーマが重いので今回はサラッと学習してまた復習したいと思います。

前回同様にdb<>fiddleを使います。

今回、まずは理屈よりも基本的な使い方の例を先に示すことにします。

下記が実行例です。

-------------------------------
-------------------------------
-- オリジナルデータの準備
CREATE TABLE tbl1 (id INT)
DECLARE @num INT =1

WHILE @num <= 10
 BEGIN
  INSERT INTO tbl1 (id) VALUES(@num)
     SET @num=@num+1
 END
-- ここまでで合計10行の行番号のみ入ったテーブルが出来ています。

SELECT id, 
ABS(CHECKSUM(RAND(id)) % 3) + 1 AS rnd
INTO tbl2
FROM tbl1
-- rndには1,2,3の値のどれかがランダムに入ります
-------------------------------
-------------------------------

BEGIN TRANSACTION
BEGIN TRY

-- 新しくテーブルを作成するための準備
CREATE TABLE tbl3 (id INT, originalid INT, num INT)

-- カーソルの値を取得する変数を宣言
DECLARE @nid int =1
DECLARE @id int
DECLARE @rnd int

-- カーソルを定義
DECLARE cur SCROLL CURSOR  --SCROLLを指定しないとFETCHでNEXTのみしか扱えない
FOR SELECT id, rnd FROM tbl2

-- カーソルのオープン
OPEN cur

-- 最初の1行を取得し変数へ値をセットする
FETCH NEXT FROM cur INTO @id, @rnd

-- データの行数分ループ処理を実行する　@@FETCH_STATUSは最終行まで来て読み込めなくなると-2になる。
WHILE @@FETCH_STATUS=0
BEGIN

-- 実際の処理はここから
INSERT INTO tbl3 VALUES(@nid, @id, @rnd)
FETCH ABSOLUTE @nid FROM cur INTO @id, @rnd　 -- FETCH RELATIVEを実行するとカーソル位置も変わるためリセット
-- 実際の処理はここまで

-- あまり良い例では無いが、現在のカーソルの注目している行のrndの値を見て、その分だけ進んだ行のデータを取ってくる
FETCH RELATIVE @rnd FROM cur INTO @id, @rnd　
SET @nid = @nid + 1
END

-- カーソルのクローズ
CLOSE cur
DEALLOCATE cur
END TRY

-- エラーをキャッチした場合
BEGIN CATCH
CLOSE cur
DEALLOCATE cur

ROLLBACK TRANSACTION
SELECT  ERROR_NUMBER()    AS errornumber
       ,ERROR_SEVERITY()  AS errorseverity
       ,ERROR_STATE()     AS errorstate
       ,ERROR_PROCEDURE() AS errorprocedure
       ,ERROR_LINE()      AS errorline
       ,ERROR_MESSAGE()   AS errormessage;
RETURN
END CATCH

COMMIT TRANSACTION

-- もともとのテーブルを表示
SELECT * FROM tbl2

-- 今回のCURSORを使った結果作成できた表の表示
SELECT * FROM tbl3

GO

id | rnd
-: | --:
 1 |   2
 2 |   2
 3 |   3
 4 |   3
 5 |   1
 6 |   2
 7 |   1
 8 |   1
 9 |   3
10 |   3

id | originalid | num
-: | ---------: | --:
 1 |          1 |   2
 2 |          3 |   3
 3 |          4 |   3
 4 |          6 |   2
 5 |          7 |   1
 6 |          6 |   2
 7 |          8 |   1
 8 |          8 |   1
 9 |          9 |   3

db<>fiddle here

今日のポイントはCURSORを使うと1行1行データにアクセスして処理が可能とした。