今日は、データベースのカーソルについて勉強していきます。
試行はOracle19cにて行っています。
もくじ
１．カーソルとは
２．カーソルの基本の使い方
３．カーソル利用時に注意すべきこと
１．カーソルとは
DB検索結果レコードを1行ずつ処理するためのしくみ。
DBデータは、通常は一括して処理することが多い。
一括してデータを取得したり、WHEREで示した条件にあう全レコードをUPDATEしたり、といったイメージである。
が、時には取得してきた複数のレコードに対して、それぞれの処理内容を個別に決定したいこともある。
レコードによって、より細かく処理の内容を指定したいときに、
検索レコードを1件ずつ処理するためのカーソル機能を利用することができる。
ちなみに、レコードを1件ずつ取得することは「フェッチする」と呼ばれる。
２．カーソルの基本の使い方
カーソルを利用するときは、以下のような流れとなる。
①カーソルの宣言（カーソル処理したいレコードの取得）
②カーソルを開く
③カーソルを利用した処理の実行
④カーソルを閉じる
具体例としては、以下のようなコードとなる。
なおカーソルの処理記述は、OracleならたいていPL/SQL内で利用されるはずなので、
ここでもPL/SQL内のクエリとして示す。
/* DECLARE： PL/SQLの変数宣言場所 */
DECLARE
/* ①カーソルの宣言 */
CURSOR csr IS SELECT ID FROM FRUITS_TABLE WHERE SHOP_ID = 2;
fruits csr%ROWTYPE;
/* BEGIN～END： PL/SQLの処理記述場所 */
BEGIN
/* ②カーソルを開く */
OPEN csr;
/* ③カーソルを利用した処理の実行 */
LOOP
FETCH csr INTO fruits;
EXIT WHEN csr%NOTFOUND;
UPDATE FRUITS_TABLE
SET SHOP_ID = 9
WHERE fruits.id = 3;
END LOOP;
/* ④カーソルを閉じる */
CLOSE csr;
END;
/ ← ※PL/SQLのファイルの最後には「/」が必要。
黄緑で注釈を入れた、DECLARE、BEGIN、ENDは、いずれもPL/SQLの記述ルールであり、
DECLAREは変数宣言部分、BEGIN～ENDでくくられている箇所が処理記述箇所となる。
（PL/SQLについては別途記事にする予定）
オレンジの注釈部分が、カーソル記述に関する内容となる。
②および④は、いわゆる「おまじない」の記述とも言えるが、特にクローズ書き忘れには気を付けないといけない。
というのも、カーソルはメモリを利用した仕組みのため、クローズし忘れると余計なメモリが食われてしまうからである。
①カーソルの宣言について
カーソルを利用するためには、まずはどんなカーソルとしたいか、定義（宣言）する必要がある。
以下のルールで、カーソルを定義している。
CURSOR カーソル名 IS （カーソル処理したいレコードの取得クエリ）;
また、例では、カーソルが保持する各レコードの内容を変数化して扱いやすくするために、
変数fruitsも定義した。
fruits csr%ROWTYPE;
③カーソルを利用した処理の実行について
ここは任意の処理を記述する箇所だが、多くの場合、定義したカーソルから
1件ずつ処理するため、ループをまわすことが一般的だと思う。
例に示した処理部分を抜き出して解説を加えると、以下のようになる。
LOOP /* ループ開始 */
FETCH csr INTO fruits; /* カーソルから1件を取り出し、変数fruitsに格納する */
EXIT WHEN csr%NOTFOUND; /* ループ終了条件 */
UPDATE FRUITS_TABLE /* 処理内容 */
SET SHOP_ID = 9
WHERE fruits.id = 3;
END LOOP; /* ループ終了 */
オレンジにした箇所で、カーソルから1件を取り出す＝フェッチを行っている。
また、ループ終了条件で「カーソル名%NOTFOUND」としているが、これは
「次のカーソルが見つからなかった時（全てを処理し終えた時）」という意味になる。
３．カーソル利用時に注意すべきこと
カーソルで取得したレコード毎に処理をしている間に、意図しない更新が可能性が発生することには、注意する必要がある。
というのも、取得したレコードに処理をしている最中に、
別の全く関係ない処理が平行に実行され、結果として最初に取得したレコードが、対象から外れてしまうかもしれないためである。
従って、本来更新すべきでないレコードに対し、UPDATEやDELETEなどをかけてしまう危険を避ける必要がある。
具体的な方法としては、ロックをかけることが必要。
とはいえ、なんでもかんでもロックをかければよいわけではなく、
・デッドロックの可能性
・ロック待ちによる処理時間の長期化
・ロックされていたことによって別の処理が失敗するという、失敗数の増加
にも考慮したうえで、ロックをどうするかは総合的に判断・設計する必要がある。（ムズカシイ…(-_-;)）
参考サイト様
・atmarkIT 「カーソル」を理解する
https://www.atmarkit.co.jp/ait/articles/1703/01/news193.html
・テックスコア SQL 12章 カーソル
https://www.techscore.com/tech/sql/SQL12/12_02.html
そもそもPL/SQLの実行方法をあんまり分かっていなくて手間取ったので、
どこかでPL/SQLをテーマにして勉強しようかな…。
ひとまず今回は以上！