Oracle Table FUNCTIONの作成
おすすめ書籍
本書用のWebアプリを使ってSQLを実行し学習していきます。初心者の方に分かりやすく、図表を多く使って解説されています。 学習の最後には200問越えのドリルが付いていてSQLのスキルが試せる様になっています。
テーブル・ファンクションの作成と実行
「ファンクションの作成」では通常のファンクションを作成しましたが、
このページではSELECT文のFROM句にテーブル等の代わりに、ファンクションを用いて実行結果をテーブルとして扱う
テーブル・ファンクションについて記します。
ファンクションの結果をテーブルデータとして返す場合には、先ずはテーブルの1行のデータ型としてのレコード型を宣言します。
その後で、そのレコード型を使ってテーブル型というものを宣言します。
このテーブル・ファンクションでは、この宣言したレコード型・テーブル型を利用してテーブルのデータを返す様に振る舞わせます。
以下にレコード型、テーブル型の宣言の例を示します。
SQL> /* テスト・テーブルのレコード型の作成 */ |
SQL> CREATE OR REPLACE TYPE TypeTEST IS OBJECT (ID NUMBER, NAME VARCHAR2(32)); |
SQL> /* テスト・テーブルのテーブル型の作成 */ |
SQL> CREATE OR REPLACE TYPE TableTypeTEST IS TABLE OF TypeTEST; |
レコード型の宣言はテーブル作成の方法と同様に、カラム名とそのデータ型をリスト指定で行います。
テーブル型は使用するレコード型を指定します。
このレコード型・テーブル型を使用した簡単なテーブル・ファンクションの例を以下に示します。
SQL> /* 一括でテーブル内容を返す関数 */ |
SQL> CREATE OR REPLACE FUNCTION TABLE_FNC1 (NMAX NUMBER) |
2 RETURN TableTypeTEST /* 戻り値:テーブル型 */ |
4 TableArr TableTypeTEST; /* ネストテーブル型宣言 */ |
6 TableArr := TableTypeTEST(); /* ネストテーブル型初期化 */ |
8 TableArr.EXTEND; /* テーブル拡張 */ |
9 TableArr(I) := TypeTEST(I, 'TEST...' || TO_CHAR(I)); /* レコード設定 */ |
11 RETURN TableArr; /* ネストテーブルを返す */ |
このファンクションの使い方は、
SELECT文のFROM句でTABLEと記述語テーブル・ファンクションを呼出す形で指定します。
実行例は以下の様になります。指定した3回までのレコードが生成されて返されるのが分かると思います。
SQL> SELECT * FROM TABLE(TABLE_FNC1(3)); |
パイプライン・テーブル・ファンクションの作成と実行
上記のテーブル・ファンクションではデータ処理が終わるまではデータが返されませんが、
テーブル・ファンクションにパイプラインの属性を付加することで、
データが順次呼び出し元に返される様になります。
実行例は以下の様になります。指定した3回までのレコードが生成されて返されるのが分かると思います。
SQL> CREATE OR REPLACE FUNCTION TABLE_FNC1_PIPE (NMAX NUMBER) |
2 RETURN TableTypeTEST PIPELINED /* 戻り値:テーブル型 */ |
6 PIPE ROW(TypeTEST(I, 'TEST_PIPE...' || TO_CHAR(I))); /* レコード設定 */ |
SQL> SELECT * FROM TABLE(TABLE_FNC1_PIPE(3)); |
このファンクションで重要なのは PIPE ROW 文のところです。
PIPE ROW 文はファンクションの起動元に ( ) 内の行(表の要素)を戻します。
以下の例からも、ほぼ通常のテーブルの様に扱えます。
SQL> SELECT ID, '[' || NAME || ']' AS NAMEX FROM TABLE(TABLE_FNC1_PIPE(3)) |
