PL/SQL 効率の良いプログラミング

　今日も見に来てくださって、ありがとうございます。石川さんです。

　今日は、お仕事でオラクルのPL/SQLのストアドプロシージャを作っていて、参考にしたプログラムの書き方が気に入らなかったので、ちょっとそのことを記事にしてみました。どこが気に入らなかったって、大きくは、2点です。カーソルを使ったループの書き方と、%ROWTYPEを使ったINSERT文の書き方です。

カーソルFORループ

　PL/SQLでは、データベースからSELECT文でデータを取ってきて一件ずつ処理をする、ということがよくあります。SELECT文を使うときは、明示的にか暗黙的にかいずれにせよ、カーソルというものを使っています。カーソルは、問い合わせ結果の位置を示すポインタのようなものです。暗黙的なカーソルはPL/SQLでは一行のみ結果を戻すSELECT文や、UPDATE文、DELETE文などで使われています。複数行戻すSELECT文を利用するときには、必ず明示的にカーソルを定義する必要があります。そして、データを取得するために、カーソルをOPENして、FETCHして、FETCHが終わったら、CLOSEする必要があります。

-- カーソル定義
CURSOR c IS SELECT ...;

-- 処理部分
OPEN c;
LOOP
  -- FETCH
  FETCH c INTO 変数1, 変数2, ...;
  -- 終了条件
  EXIT WHEN c%NOTFOUND;
  -- 1件ごとの処理をここに記述します。
END LOOP;
CLOSE c;

　こんな感じです。自分でカーソルをOPENして、終わったらCLOSEする、というのがセオリーです。この場合、個別の終了条件を満たす場合や、例外などの異常が発生したときにもCLOSEを忘れないようにコーディングする必要があります。OPEN、CLOSEを忘れないようにするというわずらわしさから解放してくれる記法として、カーソルFORループという書き方があります。

-- カーソル定義
CURSOR c IS SELECT ...;

-- 処理部分
FOR i IN c LOOP
  -- 1件ごとの処理をここに記述します。
  -- 項目のデータは、「i.カラム名」という風に記載できます。
END LOOP;

　この記法、OPEN、FETCH、CLOSE、終了条件を記載する必要がありません。あ、あと、取得してきたデータを変数に代入する処理も、その変数の定義も記述する必要がありません。とってもエレガントですね。特に理由がない限り、カーソルFORループを使いましょう。ぼくは、1件だけデータを取得するSQLが何度も呼ばれるときにだけ、OPEN、FETCH、CLOSEを利用しています。通常のSELECT文は、データが1件しかなくても2件目を探しに行って、見つからないことがわかって初めてループを終了できるので、若干ですが、処理に時間がかかってしまいます。暗黙カーソルのSELECTの場合もTOO_MANY_ROWS例外を発生させないことを確認するために2件目がないことを確認しています。通常は処理時間はわずかですので、ほとんど気にする必要はありません。ただ、大量データを扱う場合など、パフォーマンスが気になるときには考慮してもよいかもしれませんね。

％ROWTYPEを利用したINSERT文

　オラクルにはテーブル定義を再利用するための記法として、%ROWTYPEという記法があります。宣言部分で以下のように記述します。これだけで、myRecordという変数の中にテーブル定義と同じ項目を宣言できます。

myRecord myTable%ROWTYPE; -- myTableテーブルの項目と同じ項目を持つ変数を定義

　このmyRecord変数を利用して、以下のようにINSERT文を記述することができます。

INSERT INTO myTable VALUES myRecord;

　この記述方法を知らないと、以下のように項目の数の2倍の記述が必要になってしまいます。

INSERT INTO myTable(
  COLUMN_NAME1,
  COLUMN_NAME2,
…
  COLUMN_NAMEn
)VALUES(
  myRecord.COLUMN_NAME1,
  myRecord.COLUMN_NAME2,
...
  myRecord.COLUMN_NAMEn
);

　ちなみに、このmyRecordの初期化は変数にNULLを代入するだけで完了です。すべての項目がNULLにセットされます。

myRecord := NULL;

　テーブルへデータをINSERTするときは、%ROWTYPEを使いましょう。%ROWTYPEを使うことで、項目の増減があっても既存部分はほぼ問題なく動作します。%ROWTYPEを使わなければ、すべての項目を再定義する必要があるため、コーディングの効率がずいぶんと悪くなります。

まとめ

　PL/SQLでデータを取得して繰り返し処理をするときは、カーソルFORループを使う、テーブルへデータをINSERTするときは、%ROWTYPEでテーブル定義を参照した変数を利用する。これだけで、ずいぶんとコーディングの時間が短縮できると思います。