e-learning、オラクル研修、LMS(学習管理システム)のiStudy

e-learning、オラクル研修、LMS(学習管理システム)のiStudy

第63回 「バルク・バインド(FETCH BULK COLLECT INTO 文のLIMIT句)」

2013.06.24

こんにちは。インストラクターの蓑島です。

前回は、FETCH BULK COLLECT INTO文で、明示カーソルの結果セット全体を一回の操作でバルク・フェッチできることを紹介しました。
いわば一度の操作で結果セット全体を配列に移し替えるような操作ですね。
結果セットは、フェッチ済みの行を再利用できませんが、配列であれば、添え字により自由にアクセスできるメリットがあります。
前回、取得した行を逆の順番で表示したように、配列を使うことで、結果セットではできないパターンの処理でも可能になるわけです。

では、前回の予告の通り、今回は LIMIT句について解説します。

LIMIT句はFETCH BULK COLLECT INTO文のオプションの句です。
LIMIT句なしでバルク・フェッチすると、結果セット全体を一度にすべてフェッチし配列変数に格納します。
したがって結果セットのサイズが大きいと、大量のデータを一度に配列に抱え込むことになるので、十分なメモリがないとパフォーマンスに影響します。

そこで、LIMIT句を用いると、一度にバルク・フェッチする行数を指定できますので、大量データを配列に抱え込む必要がありません。

では、前回のバルク・フェッチ処理をLIMIT句を使った処理に書き換えたいと思います。
前回同様、10行のデータを持つTEST01表を使いますが、今回は行の順番がわかりやすいように以下のようなデータに変えました。

SQL> SELECT * FROM TEST01 ORDER BY A;

         A         B
---------- ----------
         1 BB001
         2 BB002
         3 BB003
         4 BB004
         5 BB005
         6 BB006
         7 BB007
         8 BB008
         9 BB009
        10 BB010

10行が選択されました。

B列の値に連続番号が含まれているので、後でB列を表示するとき順番がわかりやすくなります。

では、LIMIT句の値を4にして、前回の処理を書き換えてみましょう。

画面表示を有効にして、

SQL> SET SERVEROUTPUT ON

実行します。

SQL> DECLARE  --●宣言部
  2        -- カーソルの宣言
  3    CURSOR  C1 IS SELECT * FROM TEST01 ORDER BY A;
  4        -- レコード型変数の配列の型を宣言
  5    TYPE RECTAB_TYPE IS TABLE OF C1%ROWTYPE INDEX BY BINARY_INTEGER;
  6        -- その型でレコード型の配列を宣言
  7    RECTAB     RECTAB_TYPE;
  8    -- レコード型配列を逆の順番に表示するプロシージャを宣言
  9    PROCEDURE  PROC_REVERSE_DISP( PTAB IN  RECTAB_TYPE)
 10    IS
 11        J   BINARY_INTEGER;  -- 配列用の変数
 12    BEGIN
 13        J := PTAB.LAST;    -- 一番大きな添え字
 14        LOOP
 15            DBMS_OUTPUT.PUT_LINE(PTAB(J).B);   -- 画面表示
 16            EXIT WHEN  J = PTAB.FIRST;      -- 一番小さな添え字でループを終える
 17            J := PTAB.PRIOR(J);   -- 一つ前の添え字を取得を取得
 18        END LOOP;
 19    END  PROC_REVERSE_DISP;
 20  BEGIN  -- ●実行部
 21    OPEN C1;   -- カーソルオープン(結果セットの生成)
 22    LOOP        -- 繰り返し処理
 23       FETCH C1 BULK COLLECT INTO RECTAB LIMIT 4;   -- 4行バルク・フェッチして
 24       PROC_REVERSE_DISP(RECTAB);  -- プロシージャで配列を逆の順に表示
 25       EXIT WHEN  C1%NOTFOUND;
 26    END LOOP;
 27    CLOSE C1;
 28  END;
 29  /
BB004
BB003
BB002
BB001
BB008
BB007
BB006
BB005
BB010
BB009

PL/SQLプロシージャが正常に完了しました。

では解説します。

今回はLIMIT句の値が4なので、4行ずつバルク・フェッチします。
しかし全体の行数は10行なので全体の行数をカバーするには、4行ずつバルク・フェッチする処理をループしなければなりません。
それにより配列のデータを逆の順番に表示する処理もループに伴い何度も実行することになります。
今回はループ構造をわかりやすくするために、その処理を宣言部でプロシージャ化しました(9-19行目)。
そのプロシージャは内部的にPTABという名前で配列を受け取り(9行目のPTABパラメータの宣言)、前回と同じロジックで配列のB列を逆の順番に画面表示するように定義しています。

実行部(20行目から)では、カーソルオープン後、4行バルク・フェッチして(23行)、その配列を上記のプロシージャに渡し配列の値を逆の順番に表示することを繰り返します(22行~26行)

そうすると、全体で10行のデータを4行ずつバルク・フェッチすることを繰り返すので、最後にバルク・フェッチで取得される行数は2行となります。
実はこの時に、カーソル名%NOTFOUNDがTRUEとなりますので注意してください。
普通に1行ずつFETCH INTO 文でフェッチする場合は、取り出し行がなかったときにカーソル名%NOTFOUNDがTRUEとなりますが、LIMIT句のあるFETCH BULK COLLEC INTO文においては、たとえ取り出し行があっても、それがLIMIT句の行数に満たない場合(つまり最後の半端な行数の場合)にカーソル名%NOTFOUNDがTRUEとなるわけです。

21行目の 「EXIT WHEN C1%NOTFOUND;」の記述が23行目のFETCH文の直後でないのは、そのためです。
つまりカーソル名%NOTFOUNDがTRUEでも、最後の半端な行数の取り出し行がある可能性がありますので、その処理を終えてからループを終えているわけです。

表示されるB列の値の順番にも注目してください。
4行ずつのまとまりが逆の順番で表示されていることがわかりますね。

では今回はここまでにいたします。
次回もバルク・バインド(FORALLで疎コレクションを処理する)について解説します。
ご期待ください。

先頭へ戻る