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

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

第75回 「テーブル・ファンクション ~表のように問い合わせできるファンクション~」

2013.10.28

こんにちは。インストラクターの蓑島です。
早いものでもうすぐ11月ですね。1年は本当に短いです。

さて、今回から2回で新しいテーマ「テーブル・ファンクション」について解説します。

皆さんは、SELECT文のFROM句に表やビューの代わりに、ファンクションを直接指定してファンクションの実行結果を自由に問い合わすことができれば便利だろうなぁ・・と思ったことはありませんか?

もしそれが可能なら、大量データを返すファンクションの結果を取得するためにわざわざPL/SQLプログラムを書かなくても、SQL*PlusやSQL*Developerから簡単に問い合わせて結果を表示したりできますね。あるいはプログラムの中であっても、必要に応じて、テーブル・ファンクションと他の表を結合して問い合わせるとか、あるいはカーソル処理と組合せるとか、様々な可能性があるわけです。
つまり、ファンクションを表と同様に問い合わせの対象とできれば、アイディア次第でその応用の仕方は大きく広がってきます。

今回解説するテーブル・ファンクションは、まさにそのようなことを可能にします。

では、早速以下の簡単な例をご覧ください。

まずテーブル・ファンクション関連の宣言を含むパッケージ仕様部を作成します。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATE OR REPLACE  PACKAGE pac1
IS
/**********************************************************/
/** レコード型の宣言 (テーブル・ファンクションの行の型)     */
/**********************************************************/
    TYPE rec_type  is record
      ( col1   NUMBER,
        col2   VARCHAR2(10)
     );
/**********************************************************/
/** そのレコード型のコレクションの型                     **/
/**********************************************************/
    TYPE rec_tab_type IS TABLE OF rec_type;
--
/************************************************************/
/** そのコレクション型を返すテーブル・ファンクションの宣言 **/
/************************************************************/
    FUNCTION f1(p1 IN NUMBER) RETURN rec_tab_type PIPELINED;
END PAC1;
/
 
パッケージが作成されました。

まず最初にパッケージpac1の仕様部を作成しましたが、テーブル・ファンクションはいくつか関連するレコード型やそのコレクションの型の宣言が必要になりますので、パッケージ化するのが一般的と思います。

6行目から9行目で、レコード型(rec_type)の宣言がありますが、これがテーブル・ファンクションの返す1行の型です。
つまり、これから作成するテーブル・ファンクションの行には、col1列、col2列という2列があるわけです。
それらの列のデータ型はそれぞれ、NUMBER型とVARCHAR2(10)型です。

次に、13行目でそのレコードのコレクション型(rec_tab_type)を宣言しています。
つまりそのレコード(行)の集合(コレクション)の型です。
ここで注目していただきたいのは、13行目の宣言に「INDEX BY BINARY_INTEGER」などの記述がないことです。

一般に、コレクションの定義をするときに、「INDEX BY ~」といった記述で添え字のデータ型を記述しますが、この記述をすると、コレクションは「索引付き表」(あるいは、結合配列、PL/SQL表ともいう)という種類となります。

索引付き表は、PL/SQLプログラムの変数としては、もっとも一般的なコレクションといえますが、テーブル・ファンクションの型には使えません。
上記(13行目)のように「INDEX BY ~」という記述をしなければ「ネストした表」という種類のコレクションとなりテーブル・ファンクションの型として可能です。
このほか「VARRAY」という型もテーブル・ファンクションの型として可能です。いずれにしろ、「INDEX BY ~」という記述をしないことに注意してください。

最後に18行目で、そのコレクション型を返す、テーブル・ファンクション(f1)の宣言をしています。
ここで「PIPELINED」というキーワードですが、このキーワードは「パイプライン・テーブル・ファンクション」としての定義で必要です。
詳しい解説は割愛しますが、「パイプライン・テーブル・ファンクション」はパフォーマンスがよく、一般的なものです。「パイプライン」とは、データをためずに、パイプのようにどんどんと流すといった意味あいです。もし非パイプラインのテーブル・ファンクションだと、返すべき行の集合(コレクション)全体が出来上がるまで、結果を返せません。しかし、「パイプライン」だと、1行ができるたびにどんどんとパイプに流して行を返せるのでパフォーマンスがよいわけです。

では、次にパッケージの本体を作成します。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE OR REPLACE  PACKAGE BODY pac1
IS
/***********************************************************/
/*  テーブル・ファンクション f1 の定義                    */
/***********************************************************/
    FUNCTION f1(p1 IN NUMBER) RETURN rec_tab_type PIPELINED
    IS
       rec  rec_type;    -- 1行のレコード変数
    BEGIN
       FOR I IN 1..p1  LOOP    -- 1~P1までのループ
           rec.col1 := I;     -- col1列に値をセット
           rec.col2 := 'ABC' || TO_CHAR(I);   -- col2列に値をセット
           PIPE ROW(rec);      -- その行をパイプに流して呼び出し元に返す
       END  LOOP;
       RETURN ;    -- よって、RETURN文は値を返さない(形式的に必要)
    END f1;
END PAC1;
/
 
パッケージ本体が作成されました。

では本体の解説です。
本体でテーブル・ファンクション f1が完全に定義されますが、ここでは、パラメータp1の回数だけループ処理で行を生成します。

ここで最も大事な記述は、13行目の「PIPE ROW(rec);」です。
これにより1行(rec)をパイプに流して返しているわけです。ですから行レコード(rec)が用意できたらコレクションにためずにそのまま返しています。

したがって、ループが終了した後の 15行目のRETURN文は値を返しません。
通常、ファンクションのRETURN文は返すべき値を指定します(RETURN 値;)。
しかし、ご覧のようにパイプライン・テーブル・ファンクションでは行は「PIPE ROW」の処理で、1行ずつすでに返しています。ファンクションとしての形式で、一番最後にRETURN文を実行する必要があるので、形式的に実行しているわけであり、行を返しているわけではありません。
一番最後のRETURN文では返す値を指定しないことに注目してください。

ではこのように、テーブル・ファンクションができましたので、さっそく問い合わせて見ましょう。

1
2
3
4
5
6
7
8
9
SELECT * FROM  TABLE (pac1.f1(5));
 
       COL1 COL2
---------- ----------
          1 ABC1
          2 ABC2
          3 ABC3
          4 ABC4
          5 ABC5

ご覧のようにFROM句で、「TABLE(テーブル・ファンクション名(パラメータ))」 のように記述します。
ここでは、pac1.f1(5)ということで、pac1パッケージのf1ファンクションを引数5で問い合わせています。それにより5行の結果が返ってきたわけですね。
ご覧のように、「COL1」列、「COL2」列があります。
文字の位置(右揃え、左揃え)からCOL1列はNUMBERであり、COL2列は文字型(VARCHAR2など)であることがわかります。
レコード型の定義が反映されていますね。

もちろん、SELECT句はアスタリスク(*)である必要はなく自由に列や式を記述できます。
WHERE句やGROUP BY句、ORDER BY 句も自由に可能です。普通の表に対する問い合わせとまったく同じです。

いかがですか? 結構簡単そうですね。
アイディア次第で応用範囲はとても広いです。

では今回はここまでとし、次回テーブル・ファンクションについての細かい注意点を解説します。
ご期待ください。

先頭へ戻る