複数変換用のパイプライン・テーブル・ファンクションの連鎖

パイプライン・テーブル・ファンクションの連鎖は、複数のデータ変換を実行する場合に効率的な方法です。

ノート:

パイプライン・テーブル・ファンクションは、データベース・リンクを介して実行することはできません。パイプライン・テーブル・ファンクションの戻り型がユーザー定義のSQL型であり、単一データベース内でのみ使用可能であるためです(『Oracle Databaseオブジェクト・リレーショナル開発者ガイド』を参照)。パイプライン・テーブル・ファンクションの戻り型がPL/SQL型のように見える場合でも、そのPL/SQL型は実際にはデータベースによって対応するユーザー定義のSQL型に変換されます。

ここでのトピック

テーブル・ファンクションの概要

テーブル・ファンクションは、行のコレクション(連想配列、ネストした表またはVARRAY)を戻すユーザー定義のPL/SQLファンクションです。

SELECT文のTABLE句の内部でテーブル・ファンクションを起動することで、データベース表のようにこのコレクションから要素を選択できます。TABLE演算子は省略可能です。

たとえば:

SELECT * FROM TABLE(table_function_name(parameter_list))

次のように、同等の問合せをTABLE演算子なしで記述できます。

SELECT * FROM table_function_name(parameter_list)

テーブル・ファンクションは、入力として行のコレクションを使用することができます(入力パラメータとしてネストした表、VARRAYまたはカーソル変数を含めることができます)。したがって、テーブル・ファンクションtf1からの出力をテーブル・ファンクションtf2に入力したり、tf2からの出力をテーブル・ファンクションtf3に入力することなどができます。

テーブル・ファンクションのパフォーマンスを向上するには、次の操作を実行します。

PARALLEL_ENABLEオプションを使用して、ファンクションのパラレル実行を有効にします。

パラレル実行が有効化されたファンクションは、同時に実行できます。
Oracle Streamsを使用して、次のプロセスにファンクションの結果をストリーム化して直接送ります。

ストリームによって、プロセス間の中間的なステージングが排除されます。
PIPELINEDオプションを使用して、ファンクションの結果をパイプライン化します。

パイプライン・テーブル・ファンクションは、行を処理した直後に起動元に行を戻し、行の処理を継続します。問合せで1つの結果行を戻す前にコレクション全体を組み立ててサーバーに戻す必要がないため、応答時間が短縮されます。(オブジェクト・キャッシュでコレクション全体をマテリアライズする必要がないため、ファンクションのメモリー消費量も減少します。)

注意:

パイプライン・テーブル・ファンクションは、常にデータの現在の状態を参照します。コレクションに対するカーソルのオープン後にコレクションのデータが変更されると、カーソルにその変更が反映されます。PL/SQL変数は単一セッション内でのみ有効であり、トランザクション対応ではありません。このため、表データへの適用性がよく知られている読取り一貫性は、PL/SQLコレクション変数には適用されません。

関連項目:

パイプライン・テーブル・ファンクションの連鎖
SELECT文のTABLE句の詳細は、『Oracle Database SQL言語リファレンス』を参照してください
パイプライン・テーブル・ファンクションおよびパラレル・テーブル・ファンクションを使用する方法の詳細は、『Oracle Databaseデータ・カートリッジ開発者ガイド』を参照してください。

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

パイプライン・テーブル・ファンクションは、スタンドアロン・ファンクションまたはパッケージ・ファンクションのいずれかである必要があります。

PIPELINEDオプション(必須)

スタンドアロン・ファンクションでは、CREATE FUNCTION文にPIPELINEDオプションを指定します(構文は、「CREATE FUNCTION文」を参照)。パッケージ・ファンクションでは、ファンクション宣言とファンクション定義の両方にPIPELINEDオプションを指定します(構文は、「ファンクションの宣言および定義」を参照してください)。

PARALLEL_ENABLEオプション(推奨)

パフォーマンスを向上するには、PARALLEL_ENABLEオプションを指定して、パイプライン・テーブル・ファンクションのパラレル実行を有効にします。

AUTONOMOUS_TRANSACTIONプラグマ

パイプライン・テーブル・ファンクションでDML文を実行する場合、AUTONOMOUS_TRANSACTIONプラグマ(「AUTONOMOUS_TRANSACTIONプラグマ」を参照)を使用すると、ファンクションが自律型になります。その後、パラレル実行中に、ファンクションの各インスタンスが独立したトランザクションを作成します。

DETERMINISTICオプション(推奨)

パイプライン・テーブル・ファンクションを同じ問合せまたは別の問合せで複数回起動すると、基礎となる実装が複数回実行されます。ファンクションが決定的である場合、DETERMINISTICオプションを指定します(DETERMINISTIC句を参照)。

パラメータ

通常、パイプライン・テーブル・ファンクションには、1つ以上のカーソル変数パラメータが含まれます。ファンクション・パラメータとしてのカーソル変数の詳細は、「サブプログラム・パラメータとしてのカーソル変数」を参照してください。

関連項目:

カーソル変数の一般情報は、「カーソル変数」を参照してください
サブプログラムのパラメータの詳細は、「サブプログラムのパラメータ」を参照してください

RETURNデータ型

パイプライン・テーブル・ファンクションが戻す値のデータ型は、スキーマ・レベルまたはパッケージ内で定義されたコレクション型である必要があります(そのため、結合配列型にはできません)。コレクション型の要素は、PL/SQLによってのみサポートされるデータ型(PLS_INTEGERなど)でなく、SQLデータ型にする必要があります。コレクション型の詳細は、「コレクション型」を参照してください。SQLデータ型の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。

SQLデータ型のANYTYPE、ANYDATAおよびANYDATASETを使用して、オブジェクト型やコレクション型などの他のSQL型の型記述、データ・インスタンスおよびデータ・インスタンス・セットを動的にカプセル化してアクセスできます。また、これらの型を使用すると、匿名コレクション型などの名前を持たない型を作成できます。これらの型の詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。

PIPE ROW文

パイプライン・テーブル・ファンクションの内部でPIPE ROW文を使用すると、制御を起動元に戻すことなく、起動元にコレクション要素を戻すことができます。構文およびセマンティクスの詳細は、「PIPE ROW文」を参照してください。

RETURN文

他のすべてのファンクションと同様に、パイプライン・テーブル・ファンクションのすべての実行パスは、RETURN文に導かれ、それによって制御が起動元に戻される必要があります。ただし、パイプライン・テーブル・ファンクションでは、RETURN文で起動元に値を戻す必要はありません。その構文およびセマンティクスは、「RETURN文」を参照してください。

例

例13-30 パイプライン・テーブル・ファンクションの作成および起動

この例では、パイプライン・テーブル・ファンクションf1を含むパッケージを作成し、f1が戻す行のコレクションから要素を選択します。

CREATE OR REPLACE PACKAGE pkg1 AUTHID DEFINER AS
  TYPE numset_t IS TABLE OF NUMBER;
  FUNCTION f1(x NUMBER) RETURN numset_t PIPELINED;
END pkg1;
/

要素(1,2,3,... x)のコレクションを戻すパイプライン・テーブル・ファンクションf1を作成します。

CREATE OR REPLACE PACKAGE BODY pkg1 AS
  FUNCTION f1(x NUMBER) RETURN numset_t PIPELINED IS
  BEGIN
    FOR i IN 1..x LOOP
      PIPE ROW(i);
    END LOOP;
    RETURN;
  END f1;
END pkg1;
/

SELECT * FROM TABLE(pkg1.f1(5));

結果:

COLUMN_VALUE
------------
           1
           2
           3
           4
           5
 
5 rows selected.

SELECT * FROM pkg1.f1(2);

結果:

COLUMN_VALUE
------------
           1
           2

変換ファンクションとしてのパイプライン・テーブル・ファンクション

カーソル変数パラメータを持つパイプライン・テーブル・ファンクションは、変換ファンクションとして使用できます。ファンクションは、カーソル変数を使用して入力行をフェッチします。ファンクションは、PIPE ROW文を使用して、変換された1つ以上の行を起動元にパイプします。FETCH文およびPIPE ROW文がLOOP文の内側にある場合、ファンクションで複数の入力行を変換できます。

例13-31では、パイプライン・テーブル・ファンクションによって、employees表の各選択行がネストした表の2つの行に変換され、起動元のSELECT文にパイプされます。仮カーソル変数パラメータに対応する実パラメータは、CURSOR式です(詳細は、「パイプライン・テーブル・ファンクションへのCURSOR式の引渡し」を参照してください)。

例13-31 パイプライン・テーブル・ファンクションによる各行の2つの行への変換

CREATE OR REPLACE PACKAGE refcur_pkg AUTHID DEFINER IS
  TYPE refcur_t IS REF CURSOR RETURN employees%ROWTYPE;
  TYPE outrec_typ IS RECORD (
    var_num    NUMBER(6),
    var_char1  VARCHAR2(30),
    var_char2  VARCHAR2(30)
  );
  TYPE outrecset IS TABLE OF outrec_typ;
  FUNCTION f_trans (p refcur_t) RETURN outrecset PIPELINED;
END refcur_pkg;
/

CREATE OR REPLACE PACKAGE BODY refcur_pkg IS
  FUNCTION f_trans (p refcur_t) RETURN outrecset PIPELINED IS
    out_rec outrec_typ;
    in_rec  p%ROWTYPE;
  BEGIN
    LOOP
      FETCH p INTO in_rec;  -- input row
      EXIT WHEN p%NOTFOUND;

      out_rec.var_num := in_rec.employee_id;
      out_rec.var_char1 := in_rec.first_name;
      out_rec.var_char2 := in_rec.last_name;
      PIPE ROW(out_rec);  -- first transformed output row

      out_rec.var_char1 := in_rec.email;
      out_rec.var_char2 := in_rec.phone_number;
      PIPE ROW(out_rec);  -- second transformed output row
    END LOOP;
    CLOSE p;
    RETURN;
  END f_trans;
END refcur_pkg;
/

SELECT * FROM TABLE (
  refcur_pkg.f_trans (
    CURSOR (SELECT * FROM employees WHERE department_id = 60)
  )
);

結果:

   VAR_NUM VAR_CHAR1                      VAR_CHAR2
---------- ------------------------------ ------------------------------
       103 Alexander                      James
       103 AJAMES                         1.590.555.0103
       104 Bruce                          Miller
       104 BMILLER                        1.590.555.0104
       105 David                          Williams
       105 DWILLIAMS                      1.590.555.0105
       106 Valli                          Jackson
       106 VJACKSON                       1.590.555.0106
       107 Diana                          Nguyen
       107 DNGUYEN                        1.590.555.0107

10 rows selected.

パイプライン・テーブル・ファンクションの連鎖

パイプライン・テーブル・ファンクションのtf1とtf2を連鎖するには、tf1の出力をtf2の入力にします。たとえば:

SELECT * FROM TABLE(tf2(CURSOR(SELECT * FROM TABLE(tf1()))));

tf1によってパイプ出力される行は、tf2の仮入力パラメータと互換性のある実パラメータである必要があります。

連鎖されたパイプライン・テーブル・ファンクションでパラレル実行が有効化されていると、各ファンクションは異なるプロセス(またはプロセスのセット)で実行されます。

関連項目:

「パイプライン・テーブル・ファンクションへのCURSOR式の引渡し」

パイプライン・テーブル・ファンクションの結果からのフェッチ

名前付きカーソルは、パイプライン・テーブル・ファンクションを起動する問合せに関連付けることができます。このようなカーソルに特別なフェッチ・セマンティクスはなく、また、このようなカーソル変数に特別な代入セマンティクスはありません。

ただし、SQLオプティマイザでは、PL/SQL文にまたがる最適化は行われません。したがって、例13-32で、2番目のPL/SQL文には2つのSQL文を実行するオーバーヘッドがありますが、1番目のPL/SQL文は2番目の文より遅くなり、この結果は、1番目のPL/SQL文の2つのSQL文の間でファンクション結果がパイプされる場合でも変わりません。

例13-32で、fおよびgがパイプライン・テーブル・ファンクションであり、各ファンクションでカーソル変数パラメータを受け入れるとします。1番目のPL/SQL文は、カーソル変数rを、fを起動する問合せに関連付けて、rをgに渡します。2番目のPL/SQL文は、CURSOR式をfとgの両方に渡します。

関連項目:

「サブプログラム・パラメータとしてのカーソル変数」

例13-32 パイプライン・テーブル・ファンクションの結果からのフェッチ

DECLARE
  r SYS_REFCURSOR;
  ...
  -- First PL/SQL statement (slower):
BEGIN
  OPEN r FOR SELECT * FROM TABLE(f(CURSOR(SELECT * FROM tab)));
  SELECT * BULK COLLECT INTO rec_tab FROM TABLE(g(r));

  -- NOTE: When g completes, it closes r.
END;

-- Second PL/SQL statement (faster):

SELECT * FROM TABLE(g(CURSOR(SELECT * FROM
  TABLE(f(CURSOR(SELECT * FROM tab))))));
/

パイプライン・テーブル・ファンクションへのCURSOR式の引渡し

例13-32のように、パイプライン・テーブル・ファンクションのカーソル変数パラメータの実パラメータには、カーソル変数またはCURSOR式を使用できますが、後者の方が効率的です。

ノート:

SQLのSELECT文でCURSOR式をファンクションに渡す場合、参照先のカーソルは、ファンクションの実行開始時にオープンされ、ファンクションの完了時にクローズされます。

関連項目:

CURSOR式の一般情報は、「CURSOR式」を参照してください

例13-33では、2つのカーソル変数パラメータがあるパイプライン・テーブル・ファンクションを含むパッケージを作成し、実パラメータにCURSOR式を使用してSELECT文のファンクションを起動します。

例13-34では、パイプライン・テーブル・ファンクションを集計ファンクションとして使用し、入力行のセットを取得して1つの結果を戻します。ファンクションの結果は、SELECT文で選択します。(疑似列COLUMN_VALUEの詳細は、『Oracle Database SQL言語リファレンス』を参照してください。)

例13-33 2つのカーソル変数パラメータを使用するパイプライン・テーブル・ファンクション

CREATE OR REPLACE PACKAGE refcur_pkg AUTHID DEFINER IS
  TYPE refcur_t1 IS REF CURSOR RETURN employees%ROWTYPE;
  TYPE refcur_t2 IS REF CURSOR RETURN departments%ROWTYPE;
  TYPE outrec_typ IS RECORD (
    var_num    NUMBER(6),
    var_char1  VARCHAR2(30),
    var_char2  VARCHAR2(30)
  );
  TYPE outrecset IS TABLE OF outrec_typ;
  FUNCTION g_trans (p1 refcur_t1, p2 refcur_t2) RETURN outrecset PIPELINED;
END refcur_pkg;
/


CREATE OR REPLACE PACKAGE BODY refcur_pkg IS
  FUNCTION g_trans (
    p1 refcur_t1,
    p2 refcur_t2
  ) RETURN outrecset PIPELINED
  IS
    out_rec outrec_typ;
    in_rec1 p1%ROWTYPE;
    in_rec2 p2%ROWTYPE;
  BEGIN
    LOOP
      FETCH p2 INTO in_rec2;
      EXIT WHEN p2%NOTFOUND;
    END LOOP;
    CLOSE p2;
    LOOP
      FETCH p1 INTO in_rec1;
      EXIT WHEN p1%NOTFOUND;
      -- first row
      out_rec.var_num := in_rec1.employee_id;
      out_rec.var_char1 := in_rec1.first_name;
      out_rec.var_char2 := in_rec1.last_name;
      PIPE ROW(out_rec);
      -- second row
      out_rec.var_num := in_rec2.department_id;
      out_rec.var_char1 := in_rec2.department_name;
      out_rec.var_char2 := TO_CHAR(in_rec2.location_id);
      PIPE ROW(out_rec);
    END LOOP;
    CLOSE p1;
    RETURN;
  END g_trans;
END refcur_pkg;
/


SELECT * FROM TABLE (
  refcur_pkg.g_trans (
    CURSOR (SELECT * FROM employees WHERE department_id = 60),
    CURSOR (SELECT * FROM departments WHERE department_id = 60)
  )
);

結果:

   VAR_NUM VAR_CHAR1                      VAR_CHAR2
---------- ------------------------------ ------------------------------
       103 Alexander                      James
        60 IT                             1400
       104 Bruce                          Miller
        60 IT                             1400
       105 David                          Williams
        60 IT                             1400
       106 Valli                          Jackson
        60 IT                             1400
       107 Diana                          Nguyen
        60 IT                             1400
 
10 rows selected.

例13-34 集計ファンクションとしてのパイプライン・テーブル・ファンクション

DROP TABLE gradereport;
CREATE TABLE gradereport (
  student VARCHAR2(30),
  subject VARCHAR2(30),
  weight NUMBER,
  grade NUMBER
);

INSERT INTO gradereport (student, subject, weight, grade)
VALUES ('Mark', 'Physics', 4, 4);
 
INSERT INTO gradereport (student, subject, weight, grade) 
VALUES ('Mark','Chemistry', 4, 3);
 
INSERT INTO gradereport (student, subject, weight, grade) 
VALUES ('Mark','Maths', 3, 3);
 
INSERT INTO gradereport (student, subject, weight, grade) 
VALUES ('Mark','Economics', 3, 4);

CREATE OR REPLACE PACKAGE pkg_gpa AUTHID DEFINER IS
  TYPE gpa IS TABLE OF NUMBER;
  FUNCTION weighted_average(input_values SYS_REFCURSOR)
    RETURN gpa PIPELINED;
END pkg_gpa;
/

CREATE OR REPLACE PACKAGE BODY pkg_gpa IS
  FUNCTION weighted_average (input_values SYS_REFCURSOR)
    RETURN gpa PIPELINED
  IS
    grade         NUMBER;
    total         NUMBER := 0;
    total_weight  NUMBER := 0;
    weight        NUMBER := 0;
  BEGIN
    LOOP
      FETCH input_values INTO weight, grade;
      EXIT WHEN input_values%NOTFOUND;
      total_weight := total_weight + weight;  -- Accumulate weighted average
      total := total + grade*weight;
    END LOOP;
    PIPE ROW (total / total_weight);
    RETURN; -- returns single result
  END weighted_average;
END pkg_gpa;
/

この問合せは、テーブル・ファンクションをTABLE演算子なしで起動する方法を示しています。

SELECT w.column_value "weighted result" 
FROM pkg_gpa.weighted_average (
    CURSOR (SELECT weight, grade FROM gradereport)
  ) w;

結果:

weighted result
---------------
            3.5
 
1 row selected.

パイプライン・テーブル・ファンクションの結果に対するDML文

パイプライン・テーブル・ファンクションが戻す表を、DELETE文、INSERT文、UPDATE文またはMERGEのターゲット表にすることはできません。ただし、このような表のビューを作成し、そのビューに対してINSTEAD OFトリガーを作成できます。INSTEAD OFトリガーの詳細は、「INSTEAD OF DMLトリガー」を参照してください。

関連項目:

CREATE VIEW文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。

NO_DATA_NEEDED例外

事前定義の例外NO_DATA_NEEDEDを理解する必要があるのは、次の2つの場合です。

PIPE ROW文が存在するブロックにOTHERS例外ハンドラを含める場合
PIPE ROW文を使用するコードの次に、クリーンアップ・プロシージャを続ける必要がある場合

通常、クリーンアップ・プロシージャは、コードで必要とされなくなったリソースを解放します。

パイプライン・テーブル・ファンクションの起動元がファンクションからの行をそれ以上必要としなくなると、PIPE ROW文によってNO_DATA_NEEDEDが呼び出されます。パイプライン・テーブル・ファンクションでNO_DATA_NEEDEDを処理しない場合、例13-35に示すとおり、ファンクションの起動は終了しますが、起動元の文は終了しません。パイプライン・テーブル・ファンクションでNO_DATA_NEEDEDを処理する場合、例13-36に示すとおり、例外ハンドラによって不要になったリソースを解放できます。

例13-35では、パイプライン・テーブル・ファンクションpipe_rowsでNO_DATA_NEEDED例外を処理しません。pipe_rowsを起動するSELECT文では、4つの行のみを必要とします。そのため、pipe_rowsの5回目の起動時に、PIPE ROW文によって例外NO_DATA_NEEDEDが呼び出されます。pipe_rowsの5回目の起動は終了しますが、SELECT文は終了しません。

PIPE ROW文が存在するブロックの例外処理部に、予期しない例外を処理するためのOTHERS例外ハンドラを含める場合、その例外処理部に予期されるNO_DATA_NEEDED例外のための例外ハンドラも含める必要があります。そうしない場合、NO_DATA_NEEDED例外は、OTHERS例外ハンドラで処理され、予期しないエラーとして扱われます。次の例外ハンドラでは、リカバリ不可能なエラーとして扱われるかわりに、NO_DATA_NEEDED例外が再度呼び出されます。

EXCEPTION
  WHEN NO_DATA_NEEDED THEN
    RAISE;
  WHEN OTHERS THEN
    -- (Put error-logging code here)
    RAISE_APPLICATION_ERROR(-20000, 'Irrecoverable error.');
END;

例13-36では、External_Sourceパッケージに次のパブリック項目が含まれると想定します。

プロシージャInit: Next_Rowが必要とするリソースの割当てと初期化を行います。
ファンクションNext_Row: 特定の外部ソースのデータを戻し、外部ソースのデータがなくなるとユーザー定義の例外Done(同じくパッケージ内のパブリック項目)を呼び出します。
プロシージャClean_Up: Initで割り当てたリソースを解放します。

パイプライン・テーブル・ファンクションget_external_source_dataは、External_Source.Next_Rowを起動して、次のいずれかの状態になるまで外部ソースから行をパイプします。

外部ソースの行がなくなった場合。

この場合、External_Source.Next_Rowファンクションによってユーザー定義の例外External_Source.Doneが呼び出されます。
get_external_source_dataで行を必要としなくなった場合

この場合、get_external_source_dataのPIPE ROW文によってNO_DATA_NEEDED例外が呼び出されます。

どちらの場合でも、get_external_source_dataのブロックbにある例外ハンドラによって、Next_Rowが使用していたリソースを解放するExternal_Source.Clean_Upが起動されます。

例13-35 パイプライン・テーブル・ファンクションで処理されないNO_DATA_NEEDED

CREATE TYPE t IS TABLE OF NUMBER
/
CREATE OR REPLACE FUNCTION pipe_rows RETURN t PIPELINED AUTHID DEFINER IS
  n NUMBER := 0;
BEGIN
  LOOP
    n := n + 1;
    PIPE ROW (n);
  END LOOP;
END pipe_rows;
/
SELECT COLUMN_VALUE
  FROM TABLE(pipe_rows())
  WHERE ROWNUM < 5
/

結果:

COLUMN_VALUE
------------
           1
           2
           3
           4

4 rows selected.

例13-36 パイプライン・テーブル・ファンクションで処理されるNO_DATA_NEEDED

CREATE OR REPLACE FUNCTION get_external_source_data
  RETURN t PIPELINED AUTHID DEFINER IS
BEGIN
  External_Source.Init();           -- Initialize.
  <<b>> BEGIN
    LOOP                            -- Pipe rows from external source.
      PIPE ROW (External_Source.Next_Row());
    END LOOP;
  EXCEPTION
    WHEN External_Source.Done THEN  -- When no more rows are available,
      External_Source.Clean_Up();   --  clean up.
    WHEN NO_DATA_NEEDED THEN        -- When no more rows are needed,
      External_Source.Clean_Up();   --  clean up.
      RAISE NO_DATA_NEEDED;           -- Optional, equivalent to RETURN.
  END b;
END get_external_source_data;
/