Oracle® 非公式 SQL, PL/SQL & DBA's リファレンス

PL/SQL による SQL の繰り返し処理と WHERE CURRENT OF

同じ SQL または 値だけを変更した SQL を繰り返し実行するには PL/SQL による処理が適している。これは アプリケーションによる SQL の実行が 1回ごとにユーザープロセスとサーバーのバックグラウンドプロセス間でラウンドトリップするのに対して、PL/SQL は 1回の通信で PL/SQL ブロック全体を送信してサーバー側ですべて実行することによるものである。PL/SQL ブロック内で複数の SQL を発行している場合により効果的である。

▼DB サーバーと AP サーバーの2台構成にしたらバッチ処理のパフォーマンスが……

パイロットクラスの小規模なシステム構成で DB サーバーと AP サーバーが同一サーバーに存在するとき、ラウンドトリップはループバックやプロセス間通信によって軽微な影響しか与えないかもしれない。しかしシステムが順調に成長しデータも増えて、予算も調達できた。ここでサーバーのグレードアップ！と DB と AP サーバーを最新スペックのマシンに置き換えて計２台にして負荷分散。これでシステムも以前のように快適になるだろうと期待してリプレースも無事完了、オンライン業務でも稼動確認ＯＫでひと安心。ところが月末のバッチ処理に、その隠れていて気づかなかった問題がついに表面化することになる。
同一サーバー上にあったことで気にもならなかったラウンドトリップは比較的重いとされる TCP/IP などのプロトコルによって （ベストエフォートな）共有ネットワーク上の「ある１パケット」 として運ばれる。場合によってはソフトウェア・ファイアウォールを通過しなければならない。ラウンドトリップの回数は大きくなったシステムに比例して右肩上がりに増えている。結果として全体のスループットが明らかに旧サーバーのときよりも遅くなったようだ。現行システムに新たに投資してサーバー増やして負荷分散したことによってオンライン処理は、以前のレスポンス程度にはなったんだけど、、、夜間バッチ処理が朝までに終わりません……これ、どういう事？という業務メールが朝いちにやってきて笑えない事態になったりする。

FOR による SQL (INSERT) の繰り返し処理

ランダムデータを 100 レコード生成するごとに COMMIT を発行して内容を確定する例。
SEED 値として UID を使用しているため、何度プログラム実行をしても同じ内容のランダムな文字列データが再生成される。
この例では単純なデータ生成なので変数に入れる必要はない。より実用的で大量のデータを生成するには コレクション を使って選択値を設定したり、乱数 の生成のコストが高いことで乱数の生成回数を減らすなどの工夫を施したユーザー定義関数などを用意する必要があるだろう。一部のツールには簡易的なテンプレートが付いたテストデータ生成機能もあるので活用すると良い。

SQL> declare
  2     vID     char(8);
  3     vText   varchar2(250);
  4  begin
  5     dbms_random.seed(uid); -- 毎回同じデータになる
  6     for i in 1..1000
  7     loop
  8             vID := to_char(i, 'FM00000000');
  9             vText := dbms_random.string('x', 16);
 10             insert into insert_sample (id, txt) values (vID, vText);
 11             if (mod(i, 100) = 0) then
 12                     commit;
 13             end if;
 14     end loop;
 15     commit;
 16  end;
 17  /
 
PL/SQLプロシージャが正常に完了しました。

SQL> select * from insert_sample where rownum <= 10;
 
ID               TXT
---------------- -------------------------------------
00000001         PGILO4QUSTC5471I
00000002         T8GPUD6NKR9I4TO1
00000003         1X5PXTZF864JZK91
00000004         OKH8OB3A8S4TXFVU
00000005         RIV101Y1FK9UXGP9
00000006         BRQS5SB9PAIU9V9Z
00000007         PZL0L7344R2MRSJJ
00000008         KVL2874GSDW9X815
00000009         KUUJNSDGITEXKVT9
00000010         54XH2EGIDH9IF49P
 
10行が選択されました。

関連事項

• バルクインサート

WHERE CURRENT OF による UPDATE 処理

FOR UPDATE を含む SELECT カーソルによって取得したデータを WHERE CURRENT OF によって更新する例
この例で WHERE CURRENT OF を使用しているため定期的な COMMIT 処理が入ってない。もし大量に更新する場合には、上の例を参考に複数のトランザクションに分割して フェッチ・アクロス・コミット 処理で適度に COMMIT 処理 を追加した方がパフォーマンスがよくなる。

SQL> declare
  2     cursor  cur is select id, txt from insert_sample for update;
  3     vRec    cur%rowtype;
  4  begin
  5     open cur;
  6     loop
  7             fetch cur into vRec;
  8             exit when cur%notfound;
  9             update insert_sample set txt = txt || 'x' where current of cur;
 10     end loop;
 11     commit;
 12  end;
 13  /
 
PL/SQLプロシージャが正常に完了しました。

SQL> select * from insert_sample where rownum <= 10;
 
ID               TXT
---------------- -----------------------------------
00000001         PGILO4QUSTC5471Ix
00000002         T8GPUD6NKR9I4TO1x
00000003         1X5PXTZF864JZK91x
00000004         OKH8OB3A8S4TXFVUx
00000005         RIV101Y1FK9UXGP9x
00000006         BRQS5SB9PAIU9V9Zx
00000007         PZL0L7344R2MRSJJx
00000008         KVL2874GSDW9X815x
00000009         KUUJNSDGITEXKVT9x
00000010         54XH2EGIDH9IF49Px
 
10行が選択されました。

---

関連事項

• 「ラベル」を使って多重ループから抜け出す方法