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

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

第76回 「テーブル・ファンクションとNO_DATA_NEEDED例外

2013.11.11

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

前回「テーブル・ファンクション」についてご紹介しましたが、テーブル・ファンクションを使う際に、場合によっては注意しなければならないことがあります。
今回はその注意点について簡単に解説します。
それは「WHERE ROWNUM <= n」といった最初のn行だけといった問い合わせの場合です。

例えば、次のような「WHERE ROWNUM <= n」といった条件のあるテーブル・ファンクションの問い合わせを見てください。
このテーブルファンクションは前回(第75回)の説明で使ったものです。ソースコードは前回を参照してください。

1
2
3
4
5
6
SELECT * FROM TABLE (pac1.f1(5)) WHERE ROWNUM <= 2;
 
       COL1 COL2
---------- ----------
          1 ABC1
          2 ABC2

上のテーブル・ファンクション pac1.f1(5)は、1行を返す処理を5回ループ処理するものです。つまり問い合わせ条件が無ければ5行返すテーブル・ファンクションです。しかし、WHERE句の条件が、ROWNUM <= 2 という条件なので、必要なのは最初の2行だけです。
このような場合、テーブル・ファンクションは最初の2行で処理を中断しますので場合により問題が生じることがあります。

では、順を追って確認しましょう。
状況をわかりやすくするために、テーブル・ファンクションにメッセージを出力する処理を追加します。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
CREATE OR REPLACE  PACKAGE BODY pac1
IS
/***********************************************************/
/*  テーブル・ファンクション f1 の定義                    */
/***********************************************************/
    FUNCTION f1(p1 IN NUMBER) RETURN rec_tab_type PIPELINED
    IS
       rec  rec_type;
    BEGIN
       FOR I IN 1..p1  LOOP
           rec.col1 := I;
           rec.col2 := 'ABC' || TO_CHAR(I);
           PIPE ROW(rec);
       END  LOOP;
       DBMS_OUTPUT.PUT_LINE( 'MSG:すべての行を返しました' ); -- 追加した処理
       RETURN ;
    END f1;
END PAC1;
/
 
パッケージ本体が作成されました。

15行目の処理を追加することで、予定通り、最後の行まで返したとき、「MSG:すべての行を返しました」というメッセージを出力しますので、途中で中断せずに、最後まで処理を実行したことを確認できます。

では、メッセージ出力を可能にします。

1
SET SERVEROUTPUT ON

それでは、すべての行を返すように問い合わせ条件なしでテーブルファンクションを問い合わせてみます。p1パラメータの値を5にして、5回ループして5行を返します。

1
2
3
4
5
6
7
8
9
10
11
SELECT * FROM TABLE (pac1.f1(5));
 
       COL1 COL2
---------- ----------
          1 ABC1
          2 ABC2
          3 ABC3
          4 ABC4
          5 ABC5
 
MSG:すべての行を返しました

最後に「MSG:すべての行を返しました」というメッセージが出ていますね。
最後まで処理したわけです。これは予定通りです。

では、「WHERE COL1 = 3」の問い合わせ条件をつけてみましょう。

1
2
3
4
5
6
7
SELECT * FROM TABLE (pac1.f1(5))  WHERE COL1 = 3;
 
       COL1 COL2
---------- ----------
          3 ABC3
 
MSG:すべての行を返しました

問い合わせ条件をつけたので1行のみ行が表示されました。
しかし、この場合も「MSG:すべての行を返しました」のメッセージが出ていますね。予想に反したかもしれませんが、これはこれでいいのです。つまり実際にテーブル・ファンクションは5行を返したのですが、問い合わせの 「 WHERE COL1 = 3 」の条件により、返された5行のうち、条件を満たす1行だけを返したわけです。

しかし、問い合わせ条件が、「 WHERE ROWNUM <= 2 」ではどうでしょうか? 以下をご覧ください。

1
2
3
4
5
6
SELECT * FROM TABLE (pac1.f1(5))  WHERE ROWNUM <= 2;
 
       COL1 COL2
---------- ----------
          1 ABC1
          2 ABC2

この場合は最後に「MSG:すべての行を返しました」というメッセージが表示されておりません。
ということは、このテーブル・ファンクションは途中で処理が終わってしまったのです。
テーブル・ファンクション(正確にはパイプライン・テーブル・ファンクション)は、PIPE ROW処理(ソースコード13行目)で行を返しますが、ROWNUMで指定された最初の行数を越えて、PIPE ROW処理をしようとすると、NO_DATA_NEEDEDという例外が発生します(NO_DATA_FOUND例外ではありません)。

そのため、例外処理部にOTHERSハンドラまたは、NO_DATA_NEEDED例外ハンドラを記述していないと、未処理例外の形で終わってしまいます。このような状況は特にテーブル・ファンクションがUTL_FILEパッケージ(第48回~50回)などで入力ファイルをオープンしてデータを読み込む処理をしている場合に、それをクローズせずに処理が終わることになりますのであきらかに問題です。
そのような状態が蓄積すると、オープンカーソル数の上限に達してエラーが発生します。
ということで、ファイルをオープンするテーブル・ファンクションでは予定通りのクローズ処理の他、NO_DATA_NEEDED例外ハンドラでもクローズ処理を設ける必要があるといえます。
同じように、明示カーソルやカーソル変数をオープンしている場合も、同様の対応をすべきです。

では先ほどの処理に NO_DATA_NEEDED例外ハンドラを追加します。
この処理はファイルやカーソルをオープンしていないのであまり意味はないのですが、とりあえず、 ROWNUMで指定された行数を超えて処理をしようとするとこの例外が発生することだけは確認できます。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
CREATE OR REPLACE  PACKAGE BODY pac1
IS
/***********************************************************/
/*  テーブル・ファンクション f1 の定義                    */
/***********************************************************/
    FUNCTION f1(p1 IN NUMBER) RETURN rec_tab_type PIPELINED
    IS
       rec  rec_type;
    BEGIN
       FOR I IN 1..p1  LOOP
           rec.col1 := I;
           rec.col2 := 'ABC' || TO_CHAR(I);
           PIPE ROW(rec);
       END  LOOP;
       DBMS_OUTPUT.PUT_LINE( 'MSG:すべての行を返しました' );
       RETURN ;
    EXCEPTION                                                 --追加
       WHEN NO_DATA_NEEDED THEN                               --追加
         DBMS_OUTPUT.PUT_LINE( 'MSG:途中で処理をやめました' );  --追加
         RETURN ;                                              --追加
    END f1;
END PAC1;
/
 
パッケージ本体が作成されました。

上記のコードの追加で、 ROWNUM <= n といった条件のとき、指定された行数を越えてPIPE ROW処理をしようとするとNO_DATA_NEEDED例外ハンドラを実行し、「MSG:途中で処理をやめました」のメッセージが表示されます。
実際に行ってみましょう。

1
2
3
4
5
6
7
8
SELECT * FROM TABLE (pac1.f1(5))  WHERE ROWNUM <= 2;
 
       COL1 COL2
---------- ----------
          1 ABC1
          2 ABC2
 
MSG:途中で処理をやめました

きちんと、最後のメッセージ「MSG:途中で処理をやめました」が表示されていますね。
上記のファンクションはカーソルやファイルなどをオープンしていないのであまり意味はありませんが、それらをオープンしているテーブル・ファンクションの場合は、上記の「MSG:すべての行を返しました」「MSG:途中で処理をやめました」といったメッセージ出力処理の部分にカーソルやファイルのクローズ処理を記述しておけばよいのです。

ということで、パイプライン・テーブル・ファンクションは、 「WEHERE ROWNUM <= n 」といった問い合わせ条件で内部的に、「NO_DATA_NEEDED 」例外が発生しますので、ファイルやカーソルをオープンしている場合は通常のクローズ処理の他、NO_DATA_NEEDED例外ハンドラでも、クローズ処理を記述するようにしてください。

では、簡単な例として最後に今回のpac1.f1ファンクションを明示カーソルを使用する内容に書き換えてみます。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
CREATE OR REPLACE  PACKAGE BODY pac1
IS
/*********************************************************************/
/*  テーブル・ファンクション f1 の定義 (部門番号をもとに社員を返す) */
/*********************************************************************/
    FUNCTION f1(p1 IN NUMBER) RETURN rec_tab_type PIPELINED
    IS
       -- 指定された部門番号の社員を返す明示カーソル
       CURSOR c1 is SELECT * FROM EMP WHERE DEPTNO = p1;
       emp_rec  c1%rowtype;    -- カーソルの行レコード変数
       rec  rec_type;          -- テーブル・ファンクションの行レコード変数
    BEGIN
       OPEN C1;                        -- カーソルオープン
       LOOP                            -- 以下の処理を繰り返す
           FETCH c1 into emp_rec;      -- 1行フェッチ
           EXIT WHEN c1%NOTFOUND;      -- フェッチした行が無かったとき、ループを終了
           rec.col1 := emp_rec.empno;  -- テーブルファンクションの行のcol1列に値をセット
           rec.col2 := emp_rec.ename;  -- テーブルファンクションの行のcol2列に値をセット
           PIPE ROW(rec);              -- テーブルファンクションの行をパイプを通して返す
       END LOOP;
       CLOSE c1;                       -- ループが終わったらカーソルクローズ
       RETURN ;                         -- テーブルファンクション終了
    EXCEPTION
       WHEN NO_DATA_NEEDED THEN        -- ROWNUMの条件で処理が中断したとき
         CLOSE c1;                     -- カーソルクローズ
         RETURN ;                       -- テーブルファンクション終了
    END f1;
END PAC1;
/
 
パッケージ本体が作成されました。

pac1.f1ファンクションの引数p1は、社員表(EMP表)を問い合わすときの部門番号(DEPTNO)として用います。
そして、指定された部門の社員の社員番号(EMPNO列)と、社員名(ENAME列)をそれぞれCOL1列、COL2列として返すテーブル・ファンクションとなっています。パッケージの仕様部は変更ありませんので、今までどおり、パッケージ本体のみの修正です。

では実行してみましょう。まずは部門番号30番の社員の問い合わせです。

1
2
3
4
5
6
7
8
9
10
11
12
SELECT * FROM TABLE (pac1.f1(30));
 
       COL1 COL2
---------- ----------
       7499 ALLEN
       7521 WARD
       7654 MARTIN
       7698 BLAKE
       7844 TURNER
       7900 JAMES
 
6行が選択されました。

では次に、この問い合わせに、ROWNUMの条件で最初の2行だけを問い合わせます。

1
2
3
4
5
6
SQL> SELECT * FROM TABLE (pac1.f1(30))  WHERE ROWNUM <= 2;
 
       COL1 COL2
---------- ----------
       7499 ALLEN
       7521 WARD

正しく結果が返りますね。
「WHERE ROWNUM <= n」 の条件がある場合とない場合の両方でこのテーブル・ファンクションはオープンしたカーソルをきちんと明示的にクローズしているわけです。

それでは、今回はここまでにします。また次回、ご期待ください。

先頭へ戻る