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行データにアクセスして処理が可能
とした。