Oracle Table FUNCTIONの作成


おすすめ書籍



テーブル・ファンクションの作成と実行

「ファンクションの作成」では通常のファンクションを作成しましたが、 このページではSELECT文のFROM句にテーブル等の代わりに、ファンクションを用いて実行結果をテーブルとして扱う テーブル・ファンクションについて記します。
ファンクションの結果をテーブルデータとして返す場合には、先ずはテーブルの1行のデータ型としてのレコード型を宣言します。 その後で、そのレコード型を使ってテーブル型というものを宣言します。

このテーブル・ファンクションでは、この宣言したレコード型・テーブル型を利用してテーブルのデータを返す様に振る舞わせます。 以下にレコード型、テーブル型の宣言の例を示します。 

SQL> /* テスト・テーブルのレコード型の作成 */
SQL> CREATE OR REPLACE TYPE TypeTEST IS OBJECT (ID NUMBER, NAME VARCHAR2(32));
  2  /

型が作成されました。

SQL> /* テスト・テーブルのテーブル型の作成 */
SQL> CREATE OR REPLACE TYPE TableTypeTEST IS TABLE OF TypeTEST;
  2  /

型が作成されました。

レコード型の宣言はテーブル作成の方法と同様に、カラム名とそのデータ型をリスト指定で行います。 テーブル型は使用するレコード型を指定します。

このレコード型・テーブル型を使用した簡単なテーブル・ファンクションの例を以下に示します。 

SQL> /* 一括でテーブル内容を返す関数 */
SQL> CREATE OR REPLACE FUNCTION TABLE_FNC1 (NMAX NUMBER)
  2  RETURN TableTypeTEST    /* 戻り値:テーブル型 */
  3  IS
  4      TableArr TableTypeTEST; /* ネストテーブル型宣言 */
  5  BEGIN
  6      TableArr := TableTypeTEST();    /* ネストテーブル型初期化 */
  7      FOR I IN 1..NMAX  LOOP
  8          TableArr.EXTEND;            /* テーブル拡張 */
  9          TableArr(I) := TypeTEST(I, 'TEST...' || TO_CHAR(I));  /* レコード設定 */
 10      END LOOP;
 11      RETURN TableArr;                /* ネストテーブルを返す */
 12  END;
 13  /

ファンクションが作成されました。

このファンクションの使い方は、 SELECT文のFROM句でTABLEと記述語テーブル・ファンクションを呼出す形で指定します。 実行例は以下の様になります。指定した3回までのレコードが生成されて返されるのが分かると思います。 

SQL> SELECT * FROM TABLE(TABLE_FNC1(3));

        ID NAME
---------- ----------------------------------------------------------------
         1 TEST...1
         2 TEST...2
         3 TEST...3

パイプライン・テーブル・ファンクションの作成と実行

上記のテーブル・ファンクションではデータ処理が終わるまではデータが返されませんが、 テーブル・ファンクションにパイプラインの属性を付加することで、 データが順次呼び出し元に返される様になります。 実行例は以下の様になります。指定した3回までのレコードが生成されて返されるのが分かると思います。 

SQL> CREATE OR REPLACE FUNCTION TABLE_FNC1_PIPE (NMAX NUMBER)
  2  RETURN TableTypeTEST PIPELINED  /* 戻り値:テーブル型 */
  3  IS
  4  BEGIN
  5      FOR I IN 1..NMAX  LOOP
  6          PIPE ROW(TypeTEST(I, 'TEST_PIPE...' || TO_CHAR(I)));  /* レコード設定 */
  7      END LOOP;
  8  END;
  9  /

ファンクションが作成されました。

SQL> SELECT * FROM TABLE(TABLE_FNC1_PIPE(3));

        ID NAME
---------- ----------------------------------------------------------------
         1 TEST_PIPE...1
         2 TEST_PIPE...2
         3 TEST_PIPE...3

SQL>

このファンクションで重要なのは PIPE ROW 文のところです。 PIPE ROW 文はファンクションの起動元に ( ) 内の行(表の要素)を戻します。

以下の例からも、ほぼ通常のテーブルの様に扱えます。 

SQL> SELECT ID, '[' || NAME || ']' AS NAMEX FROM TABLE(TABLE_FNC1_PIPE(3))
  2  WHERE ID < 3
  3  ORDER BY ID DESC;

        ID NAMEX
---------- --------------------------------------------------------------------
         2 [TEST_PIPE...2]
         1 [TEST_PIPE...1]



ページのトップへ戻る