こんにちは。石川さんです。
最近、PLSQLでストアドプロシージャを作っています。カーソルでSELECT文を定義して、データを一件ずつ取得して、正常に処理できたら処理済みの証として日付を更新する、という処理を書いているのですが、参考にしている元のプロシージャに色々といちゃもんを言いたくなって、書いちゃいました。
カーソルFORループを使おうよ！
SELECTで取得したデータをループで逐次処理するときは、カーソルFORループを使うようにしています。構文が記憶から抜けていたので色々と検索することになりました。ただ、色々と検索したときにあんまり登場してこなかったのですよね。ここにきて世の中的にはあまり認知されていないのかもという気持ちになってきたのですが、カーソルFORループのメリットをお伝えしておこうと思います。
カーソルの使い方は２種類
一つ目、カーソルFORループではないカーソルを使った繰り返し処理の例を示します。
DECLARE
-- カーソルの定義
CURSOR C IS SELECT ... ;
-- FETCHしたときにデータを取得する変数
C_DATA C%ROWTYPE; -- 受け取る変数は％ROWTYPEを使いましょう。取得カラムに対して一個ずつ変数定義しないように！
BEGIN
OPEN C; -- カーソルをオープンします
LOOP
FETCH C INTO C_DATA; -- データを1件フェッチします
EXIT WHEN C%NOTFOUND; -- データがなくなった時にループから抜け出します
-- なにかの処理
END LOOP;
CLOSE C; -- カーソルをクローズします
EXCEPT
WHEN OTHERS THEN -- 想定外のエラーが発生したとき
IF C%ISOPEN THEN -- カーソルがオープンしていたら
CLOSE C; -- カーソルを閉じます
END IF;
RAISE; -- 想定外のエラーを呼び出し元に渡します
END;
/
LOOPから、END LOOPまでの間を繰り返します。
二つ目、カーソルFORループを使用した場合の例を示します。
DECLARE
-- カーソルの定義
CURSOR C IS SELECT ... ;
BEGIN
FOR i IN C LOOP
-- なにかの処理
END LOOP;
EXCEPT
WHEN OTHERS THEN
RAISE;
END;
/
FORから、END LOOPまでの間を繰り返します。そう、圧倒的に手順が少なくなります。メリットをまとめると以下の通り。
- フェッチしたデータを受け取る変数の定義が不要
- 明示的なオープン、クローズが不要
- ループを抜けるための条件判定が不要、データがなくなればループは終了します
- エラー発生したときに、カーソルのオープン判定とクローズ処理が不要
いいことずくめですねぇ。仮に難点があるとすれば、変数「i」がループ終了時には利用できなくなる、ということくらいですが、それは処理に応じて別途必要な変数を用意すればよいので、まったく問題になりませんよね。
個人的に、OPEN、FETCH、CLOSEを使うのは、データが絶対に1件しかないことが分かっている問い合わせのときに限られると思っています。というのもループ処理を終了する条件がNOTFOUNDになっているのですが、これは、2件目を探してデータの最後まで見にいかないとわからないことなのですよね。要は、無駄な検索をしてしまう、ということです。プログラム作成者が2件目がないことを知っているなら、パフォーマンスを改善するために、FETCH後すぐにCLOSEするようにしましょう。無駄な検索が発生しない分、はやくなります。
CURRENT OFカーソルを使って更新する
そう、そもそもなんでこの記事を書こうと思ったかを思い出しました。カーソルFORループ使って欲しいのはそうなのですけど、もうひとつ！
取り出した行を更新するときには、CURRENT OFカーソルを使ってほしいのです。これもパフォーマンスのためです。これを使わないということは、せっかく取得してきたのにそのことを忘れてもう一度検索し直している、ということとほぼ同じことですからね。
で、CURRENT OFの後に指定するのが、カーソルFORループのときは何でしたっけ、というのを調べてもすぐにわからなかったので、スクリプトつくって実験してみました。
知りたかったのは、カーソル名を指定するのか、カーソルの変数を指定するのかどちらでしょうか、ということです。
まずは、作ったスクリプトです。
-- ■CURRENT OF カーソルの使い方 スクリプト
SET NULL NL
CREATE TABLE POI (n NUMBER, v VARCHAR2(20));
INSERT INTO POI(N) SELECT N FROM (
WITH S(N) AS (
SELECT 1 N FROM DUAL
UNION ALL
SELECT N + 1 FROM S
WHERE N < 10)
SELECT N FROM S
); -- このINSERT SELECTで10行作成しています。数字を変えれば好きなだけデータが作れます。
SELECT * FROM POI;
DECLARE
n NUMBER;
CURSOR C IS SELECT N, V FROM POI FOR UPDATE;
BEGIN
FOR i IN C LOOP
UPDATE POI SET V = 'RECORD IS No.'||i.N
WHERE CURRENT OF C;
END LOOP;
END;
/
SELECT * FROM POI;
DROP TABLE POI PURGE;
実行結果です。できました！
-- ■実行結果
SQL> SET NULL NL
SQL> CREATE TABLE POI (n NUMBER, v VARCHAR2(20));
表が作成されました。
経過: 00:00:00.01
SQL> INSERT INTO POI(N) SELECT N FROM (
2 WITH S(N) AS (
3 SELECT 1 N FROM DUAL
4 UNION ALL
5 SELECT N + 1 FROM S
6 WHERE N < 10)
7 SELECT N FROM S
8 );
10行が作成されました。
経過: 00:00:00.00
SQL>
SQL> SELECT * FROM POI;
N V
---------- --------------------
1 NL
2 NL
3 NL
4 NL
5 NL
6 NL
7 NL
8 NL
9 NL
10 NL
10行が選択されました。
経過: 00:00:00.01
SQL>
SQL> DECLARE
2 n NUMBER;
3 CURSOR C IS SELECT N, V FROM POI FOR UPDATE;
4 BEGIN
5 FOR i IN C LOOP
6 UPDATE POI SET V = 'RECORD IS No.'||i.N
7 WHERE CURRENT OF C;
8 END LOOP;
9 END;
10 /
PL/SQLプロシージャが正常に完了しました。
経過: 00:00:00.01
SQL>
SQL> SELECT * FROM POI;
N V
---------- --------------------
1 RECORD IS No.1
2 RECORD IS No.2
3 RECORD IS No.3
4 RECORD IS No.4
5 RECORD IS No.5
6 RECORD IS No.6
7 RECORD IS No.7
8 RECORD IS No.8
9 RECORD IS No.9
10 RECORD IS No.10
10行が選択されました。
経過: 00:00:00.00
SQL>
SQL> DROP TABLE POI PURGE;
表が削除されました。
経過: 00:00:00.04
SQL>
ちなみに、カーソル変数名を指定すると以下の通り、エラーになりました。
-- ■カーソル名ではなく、変数名を指定した場合は、エラーです
SQL> DECLARE
2 n NUMBER;
3 CURSOR C IS SELECT N, V FROM POI FOR UPDATE;
4 BEGIN
5 FOR i IN C LOOP
6 UPDATE POI SET V = 'RECORD IS No.'||i.N
7 WHERE CURRENT OF i;
8 END LOOP;
9 END;
10 /
WHERE CURRENT OF i;
*
行7でエラーが発生しました。:
ORA-06550: 行7、列23:
PLS-00413: CURRENT OF句の識別子はカーソル名ではありません。
ORA-06550: 行7、列23:
PL/SQL: ORA-00904: : 無効な識別子です。
ORA-06550: 行6、列5:
PL/SQL: SQL Statement ignored
経過: 00:00:00.01
SQL>
結果としては、カーソル名を指定すればオッケーということですね！
まとめ
PL/SQLでカーソルをつくってデータを取得するときは、カーソルFORループを使いましょう。取得したデータを更新するときは、CURRENT OF カーソルを使いましょう。