多相テーブル・ファンクションの概要
多相テーブル・ファンクション(PTF)とは、オペランドに複数の型を指定できるテーブル・ファンクションです。戻り値の型はPTF起動の引数リストによって決定されます。通常は表の型への実際の引数によって行の出力形状が決まりますが、異なる場合もあります。
多相テーブル・ファンクションについて
多相テーブル・ファンクション(PTF)はユーザー定義のファンクションで、SQL問合せブロックのFROM句から起動できます。定義時に行の型が宣言されない表を処理したり、定義時に行の型が必ずしも宣言されない結果表を生成したりできます。多相テーブル・ファンクションは動的SQL機能を利用して、強力かつ複雑なカスタム・ファンクションを作成します。これが便利なのは、アプリケーションのインタフェースに、任意的な入力表または問合せに対応できる汎用的な拡張機能が求められる場合です。
PTF作成者は、表を定義する手続き型メカニズムへのインタフェースを作成します。PTF作成者はPTFの定義、ドキュメント化および実装を行います。
問合せ作成者は、公開されたインタフェースを記述して、問合せ内でPTFを起動することしかできません。
データベースはPTFの指揮者として機能します。これがPTFのコンパイルと実行状態を管理します。データベースおよびPTF作成者は、関連するSQLで起動されるプロシージャのファミリや、呼び出されたPTFコンポーネント・プロシージャ、場合によってはプライベート・データ(変数やカーソルなど)を見ることができます。
多相テーブル・ファンクションの型
-
入力
TABLE引数がRow Semanticsである場合、入力は1行です。 -
入力
TABLE引数がTable Semanticsである場合、入力は行のセットです。Table SemanticsPTFを問合せから呼び出すときに、PARTITION BY句またはORDER BY句のいずれかまたは両方を使用して表引数を拡張できます。
多相テーブル・ファンクションの定義
PTF作成者は多相テーブル・ファンクション(PTF)の定義、ドキュメント化および実装を行います。
PTFは2つの部分で構成されます。
1. PTF実装のクライアント・インタフェースを含むPL/SQLパッケージ。
2. PTFおよび関連付けられている実装パッケージを指定するスタンドアロン・ファンクションまたはパッケージ・ファンクション。
多相テーブル・ファンクションの実装
多相テーブル・ファンクション(PTF)実装のクライアント・インタフェースは固定名を持つサブプログラムのセットであり、すべてのPTFがこれを提供する必要があります。
多相テーブル・ファンクションの実装ステップ
-
DESCRIBEファンクション(必須)と、OPEN、FETCH_ROWSおよびCLOSEプロシージャ(任意)を含む実装パッケージを作成します。 -
PTFを指定したファンクションの仕様部を作成します。パッケージ作成後の最上位レベルでファンクションを作成するか、または実装パッケージ(最初のステップで作成したパッケージ)内でパッケージ・ファンクションとしてファンクションを作成します。多相テーブル・ファンクションにはファンクション定義(
FUNCTIONBODY)がありません。定義は関連付けられている実装パッケージにカプセル化されます。ファンクション定義では次を指定します。
-
多相テーブル・ファンクション(PTF)名
-
TABLE型の仮引数を1つ、および任意の数の非TABLE型の引数 -
PTFの戻り型
TABLE -
PTFファンクションの型(
rowまたはtableセマンティクス) -
PTF実装パッケージ名
-
関連項目:
-
DESCRIBEのみの多相テーブル・ファンクションの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』 -
PTF実装パッケージの指定方法と
DBMS_TFユーティリティの使用方法の詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』 -
スタンドアロンまたはパッケージ多相テーブル・ファンクションを作成する構文およびセマンティクの詳細は、PIPELINED句
多相テーブル・ファンクションの起動
多相テーブル・ファンクションを起動するには、SQL問合せブロックのFROM句でファンクション名を指定し、その後に引数リストを指定します。
PTF引数は通常のテーブル・ファンクションに渡すことができる標準スカラー引数として指定することもできますが、これに加えてPTFでは表引数を取ることができます。表引数は、WITH句問合せまたはFROM句で許可されているスキーマ・レベルのオブジェクト(表、ビューまたはテーブル・ファンクションなど)のいずれかです。
構文
table_argument ::= table [ PARTITION BY column_list ] [ORDER BY order_column_list]
column_list ::= identifier | ( identifier[, identifier…])
order_column_list ::= order_column_name | (order_column_name [, order_column_name…])
order_column_name ::= identifier [ ASC | DESC ][ NULLS FIRST | NULLS LAST ]
セマンティクス
各識別子は対応する表内の1つの列です。
PTFにはTable Semanticsがあります。
必要に応じて、問合せでTable Semantics PTF入力をパーティション化したり順序付けしたりできます。これはRow Semantics PTF入力では許可されません。
多相テーブル・ファンクション(PTF)をDML文のターゲットにすることはできません。PTFの表引数は名前で渡されます。
たとえば、noop PTFは次のような問合せで使用できます。
SELECT * FROM noop(emp);
または
WITH e AS (SELECT * FROM emp NATURAL JOIN dept) SELECT t.* FROM noop(e) t;
入力表引数は、基本的な表名である必要があります。
-
識別子が列名(外側の問合せブロックからの相関列など)に解決されます。
-
識別子が現在の問合せブロック内または一部の外側の問合せブロック内の共通表式(CTE)名に解決されます。CTEは一般に
WITH句として知られています。 -
識別子がスキーマ・レベルの表、ビューまたはテーブル・ファンクション(通常または多相で、スキーマ・レベルまたはパッケージ内で定義される)に解決されます。
表の式の多くのタイプは、FROM句で許可されるとしても、PTFの表引数として直接使用することはできません(ANSI結合、バインド変数、インライン・ビュー、CURSOR演算子、TABLE演算子など)。このような表の式をPTF引数として使用するには、これらの式をCTEでラップし、CTE名をPTFに渡す間接的な方法を使用します。
PTFはFROM句で表参照として使用できるため、ANSI結合やLATERAL構文の一部にすることができます。また、PTFはPIVOTまたはUNPIVOTおよびMATCH_RECOGNIZEのソース表にすることができます。表やビューを対象にした一部の表変更の句(SAMPLING、PARTITION、CONTAINERSなど)は、PTFでは許可されません。
PTFの直接的なファンクション合成は許可されます(ネストされたPTFカーソル式の起動やPTF(TF())ネスティングなど)。ただし、ネストされたPTFは許可されません(PTF(PTF())のようなネスティング)。
PTFのスカラー引数として任意のSQLスカラー式を指定できます。定数のスカラー値はDESCRIBEファンクションにそのまま渡されますが、その他の値はすべてNULLとして渡されます。これらの値が行の形状を決定するものでなければ、通常これがPTF実装で問題になることはありませんが、そうでない場合DESCRIBEファンクションでエラーが発生することがあります。通常、PTF付属のドキュメントに、どれがスカラー・パラメータで(存在する場合)、それゆえNULL以外の定数値を必要とするとの記載があります。問合せの実行中(OPEN、FETCH_ROWS、CLOSEの間)に式が評価され、これらのPTF実行プロシージャに実際の値が渡されます。戻り値の型はPTF起動の引数リストによって決定されます。
問合せ引数はWITH句を使用して、PTFに渡されます。
テーブル・ファンクションの引数リストまたは空のlist ()がある場合、TABLE演算子は省略できます。
可変個引数擬似演算子
可変個引数擬似演算子は、可変数のオペランドで動作します。
Oracle Databaseリリース18c以上では、可変個引数擬似演算子の概念をSQL式言語に導入し、多相テーブル・ファンクション(PTF)をサポートします。擬似演算子を使用すると、識別子のリスト(列名など)をPTFに渡すことができます。疑似演算子は、PTFへの引数としてのみ表示され、他のSQL演算子やPL/SQLファンクションの起動などと同様にSQLコンパイラによって解析されます。疑似演算子の引数は可変数ですが、少なくとも1つは必要です。疑似演算子には関連付けられている実行関数がなく、PTFコンパイルが終了した後にSQLカーソルから完全に削除されます。SQLのコンパイル中に、疑似演算子は対応するDBMS_TF型に変換されてからDESCRIBEメソッドに渡されます。これらの演算子に関連付けられた出力タイプはありません。汎用のSQL式に擬似演算子を埋め込むことはできません。
COLUMNS擬似演算子
COLUMNS擬似演算子を使用して、SQL問合せブロックのFROM句で、多相テーブル・ファンクション(PTF)の起動の引数を指定できます。
COLUMNS疑似演算子の引数は、列名のリスト、または関連付けられたタイプを使用して列名のリストを指定します。
構文
column_operator ::= COLUMNS ( column_list )
column_list ::= column_name_list | column_type_list
column_name_list ::= identifier [, identifier ... ]
column_type_list::= identifier column_type [, identifier column_type…]
セマンティクス
COLUMNS疑似演算子はPTFへの引数としてのみ指定できます。PTF式自体以外のその他のSQL式では使用できません。
column_typeはスカラー型である必要があります。
多相テーブル・ファンクションのコンパイルおよび実行
データベースは多相テーブル・ファンクション(PTF)の指揮者の役割を担います。そのため、PTFのコンパイルから実行、および関連する状態までを管理します。
-
コンパイル状態:
DESCRIBEによって生成される不変の状態で、実行前に必要です。 -
実行状態:
Table semanticsPTFの実行プロシージャによって使用される状態です。
関連項目:
-
データベースがPTFのコンパイルと実行状態を管理する方法の詳細は、Oracle Database PL/SQLパッケージおよびタイプ・リファレンス
多相テーブル・ファンクションの最適化
多相テーブル・ファンクション(PTF)は、データベースの分析機能を拡張するための効率的でスケーラブルなフレームワークを提供します。
主な利点は次のとおりです。
-
最小限のデータ移動: 関心のある列のみがPTFに渡されます。
-
述語/射影/パーティション化が基礎となる表/問合せ(意味的に可能な場合)にプッシュされます。
-
PTF内外へのデータの一括移動
-
並列度は、PTFおよび問合せに指定されたパーティション(ある場合)のタイプに基づきます
Skip_col多相テーブル・ファンクションの例
このPTF例は、Row Semantics、Describeのみ、パッケージ・テーブル・ファンクション、およびオーバーロード機能を示しています。
関連項目:
その他の多相テーブル・ファンクションの(PTF)の例については、Oracle Database PL/SQLパッケージおよびタイプ・リファレンス
例13-37 Skip_col多相テーブル・ファンクションの例
skip_col多相テーブル・ファンクション(PTF)は、PTF入力引数で指定した列を除く、表内のすべての列を返します。skip_col PTFは列名(overload 1)または列データ型(overload 2)に基づいて列をスキップします。
Live SQL:
このサンプルは、18c Skip_col多相テーブル・ファンクションのOracle Live SQLで表示および実行できます
skip_col多相テーブル・ファンクション(PTF)に対して、DESCRIBEファンクションを含むskip_col_pkgという名前の実装パッケージを作成します。PTFによって生成された行の形状を決定するために、DESCRIBEファンクションが起動されます。DBMS_TF.DESCRIBE_T表を戻します。オーバーロードされています。FETCH_ROWSプロシージャは、 指定された行のサブセットに対して関連する新しい列値を生成する必要があるため、必須ではありません。
CREATE PACKAGE skip_col_pkg AS
-- OVERLOAD 1: Skip by name --
FUNCTION skip_col(tab TABLE,
col COLUMNS)
RETURN TABLE PIPELINED ROW POLYMORPHIC USING skip_col_pkg;
FUNCTION describe(tab IN OUT DBMS_TF.TABLE_T,
col DBMS_TF.COLUMNS_T)
RETURN DBMS_TF.DESCRIBE_T;
-- OVERLOAD 2: Skip by type --
FUNCTION skip_col(tab TABLE,
type_name VARCHAR2,
flip VARCHAR2 DEFAULT 'False')
RETURN TABLE PIPELINED ROW POLYMORPHIC USING skip_col_pkg;
FUNCTION describe(tab IN OUT DBMS_TF.TABLE_T,
type_name VARCHAR2,
flip VARCHAR2 DEFAULT 'False')
RETURN DBMS_TF.DESCRIBE_T;
END skip_col_pkg;
多相テーブル・ファンクション定義を含む実装パッケージ本体を作成します。
CREATE PACKAGE BODY skip_col_pkg AS
/* OVERLOAD 1: Skip by name
* Package PTF name: skip_col_pkg.skip_col
* Standalone PTF name: skip_col_by_name
*
* PARAMETERS:
* tab - The input table
* col - The name of the columns to drop from the output
*
* DESCRIPTION:
* This PTF removes all the input columns listed in col from the output
* of the PTF.
*/
FUNCTION describe(tab IN OUT DBMS_TF.TABLE_T,
col DBMS_TF.COLUMNS_T)
RETURN DBMS_TF.DESCRIBE_T
AS
new_cols DBMS_TF.COLUMNS_NEW_T;
col_id PLS_INTEGER := 1;
BEGIN
FOR i IN 1 .. tab.column.count() LOOP
FOR j IN 1 .. col.count() LOOP
tab.column(i).PASS_THROUGH := tab.column(i).DESCRIPTION.NAME != col(j);
EXIT WHEN NOT tab.column(i).PASS_THROUGH;
END LOOP;
END LOOP;
RETURN NULL;
END;
/* OVERLOAD 2: Skip by type
* Package PTF name: skip_col_pkg.skip_col
* Standalone PTF name: skip_col_by_type
*
* PARAMETERS:
* tab - Input table
* type_name - A string representing the type of columns to skip
* flip - 'False' [default] => Match columns with given type_name
* otherwise => Ignore columns with given type_name
*
* DESCRIPTION:
* This PTF removes the given type of columns from the given table.
*/
FUNCTION describe(tab IN OUT DBMS_TF.TABLE_T,
type_name VARCHAR2,
flip VARCHAR2 DEFAULT 'False')
RETURN DBMS_TF.DESCRIBE_T
AS
typ CONSTANT VARCHAR2(1024) := UPPER(TRIM(type_name));
BEGIN
FOR i IN 1 .. tab.column.count() LOOP
tab.column(i).PASS_THROUGH :=
CASE UPPER(SUBSTR(flip,1,1))
WHEN 'F' THEN DBMS_TF.column_type_name(tab.column(i).DESCRIPTION)!=typ
ELSE DBMS_TF.column_type_name(tab.column(i).DESCRIPTION) =typ
END /* case */;
END LOOP;
RETURN NULL;
END;
END skip_col_pkg;
overload 1にskip_col_by_nameという名前のスタンドアロン多相テーブル・ファンクションを作成します。TABLE型の正式引数を1つ指定し、PTFの戻り型をTABLEとして指定し、行セマンティクスPTFタイプを指定し、使用するPTF実装パッケージをskip_col_pkgに指定します。
CREATE FUNCTION skip_col_by_name(tab TABLE,
col COLUMNS)
RETURN TABLE PIPELINED ROW POLYMORPHIC USING skip_col_pkg;
overload 2にskip_col_by_typeという名前のスタンドアロン多相テーブル・ファンクションを作成します。TABLE型の正式引数を1つ指定し、PTFの戻り型をTABLEとして指定し、行セマンティクスPTFタイプを指定し、使用するPTF実装パッケージをskip_col_pkgに指定します。
CREATE FUNCTION skip_col_by_type(tab TABLE,
type_name VARCHAR2,
flip VARCHAR2 DEFAULT 'False')
RETURN TABLE PIPELINED ROW POLYMORPHIC USING skip_col_pkg;
型がNUMBERでない列のみをSCOTT.DEPT表からレポートするために、パッケージskip_col PTF (overload 1)を起動します。
SELECT * FROM skip_col_pkg.skip_col(scott.dept, 'number');DNAME LOC -------------- ------------- ACCOUNTING NEW YORK RESEARCH DALLAS SALES CHICAGO OPERATIONS BOSTON
SCOTT.DEPT表からスタンドアロンskip_col_by_type PTFを起動して、型がNUMBERでない列のみをレポートすると、同じ結果が得られます。
SELECT * FROM skip_col_by_type(scott.dept, 'number');DNAME LOC -------------- ------------- ACCOUNTING NEW YORK RESEARCH DALLAS SALES CHICAGO OPERATIONS BOSTON
SCOTT.DEPT表から型がNUMBERの列のみをレポートするために、パッケージskip_col PTF (overload 2)を起動します。
SELECT * FROM skip_col_pkg.skip_col(scott.dept, 'number', flip => 'True'); DEPTNO
----------
10
20
30
40
SCOTT.DEPT表からスタンドアロンskip_col_by_type PTFを起動して、型がNUMBERの列のみをレポートすると、同じ結果が得られます。
SELECT * FROM skip_col_by_type(scott.dept, 'number', flip => 'True'); DEPTNO
----------
10
20
30
40
パッケージskip_col PTFを起動して、SCOTT.EMP表から部門20のCOMM、HIREDATEおよびMGRを除くすべての従業員をレポートします。
SELECT *
FROM skip_col_pkg.skip_col(scott.emp, COLUMNS(comm, hiredate, mgr))
WHERE deptno = 20; EMPNO ENAME JOB SAL DEPTNO
---------- ---------- --------- ---------- ----------
7369 SMITH CLERK 800 20
7566 JONES MANAGER 2975 20
7788 SCOTT ANALYST 3000 20
7876 ADAMS CLERK 1100 20
7902 FORD ANALYST 3000 20
To_doc多相テーブル・ファンクションの例
to_doc PTFの例では、指定された列のリストを1つのドキュメント列に結合します。
例13-38 To_doc多相テーブル・ファンクションの例
to_doc PTFは、列のリストをJSONオブジェクトのように構築されたドキュメント列に結合します。
Live SQL:
このサンプルは、18c To_doc多相テーブル・ファンクションでOracle Live SQLで表示および実行できます
多相テーブル・ファンクション(PTF)のDESCRIBEファンクションおよびFETCH_ROWSプロシージャを含む実装パッケージto_docを作成します。
-
tab : 入力表(tabパラメータは表記述子レコード型の
DBMS_TF.TABLE_T型です) -
cols (オプション) : ドキュメントを変換する列のリスト。(colsパラメータは、列記述子レコード型の
DBMS_TF.COLUMNS_Tです)
CREATE PACKAGE to_doc_p AS
FUNCTION describe(tab IN OUT DBMS_TF.TABLE_T,
cols IN DBMS_TF.COLUMNS_T DEFAULT NULL)
RETURN DBMS_TF.DESCRIBE_T;
PROCEDURE fetch_rows;
END to_doc_p;
DESCRIBEファンクションおよびFETCH_ROWSプロシージャを含むパッケージを作成します。出力行セットにDOCUMENTという名前の新しい列を作成するには、FETCH_ROWSプロシージャが必要です。DESCRIBEファンクションは、入力表記述子TABLE_Tで注釈を付けることによって読取り列を指定します。指定された読取り列のみがフェッチされるため、FETCH_ROWS時の処理に使用できます。問合せでのPTFの起動は、COLUMNS疑似演算子を使用して、問合せがPTFに読み取らせる列を指定できます。この情報はDESCRIBEファンクションに渡され、このファンクションによってCOLUMN_T.FOR_READブール・フラグが設定されます。読取り列には、スカラーSQLデータ型のみを使用できます。COLUMN_T.PASS_THROUGHブール・フラグは、変更なしでPTFの入力表から出力に渡される列を示します。
CREATE PACKAGE BODY to_doc_p AS
FUNCTION describe(tab IN OUT DBMS_TF.TABLE_T,
cols IN DBMS_TF.COLUMNS_T DEFAULT NULL)
RETURN DBMS_TF.DESCRIBE_T AS
BEGIN
FOR i IN 1 .. tab.column.count LOOP
CONTINUE WHEN NOT DBMS_TF.SUPPORTED_TYPE(tab.column(i).DESCRIPTION.TYPE);
IF cols IS NULL THEN
tab.column(i).FOR_READ := TRUE;
tab.column(i).PASS_THROUGH := FALSE;
CONTINUE;
END IF;
FOR j IN 1 .. cols.count LOOP
IF (tab.column(i).DESCRIPTION.NAME = cols(j)) THEN
tab.column(i).FOR_READ := TRUE;
tab.column(i).PASS_THROUGH := FALSE;
END IF;
END LOOP;
END LOOP;
RETURN DBMS_TF.describe_t(new_columns => DBMS_TF.COLUMNS_NEW_T(1 =>
DBMS_TF.COLUMN_METADATA_T(name =>'DOCUMENT')));
END;
PROCEDURE fetch_rows AS
rst DBMS_TF.ROW_SET_T;
col DBMS_TF.TAB_VARCHAR2_T;
rct PLS_INTEGER;
BEGIN
DBMS_TF.GET_ROW_SET(rst, row_count => rct);
FOR rid IN 1 .. rct LOOP
col(rid) := DBMS_TF.ROW_TO_CHAR(rst, rid);
END LOOP;
DBMS_TF.PUT_COL(1, col);
END;
END to_doc_p;スタンドアロンto_doc PTFを作成します。TABLE型の仮引数を正確に1つ指定し、PTFの戻り型をTABLEとして指定し、行セマンティクスPTF型を指定し、PTF実装パッケージがto_doc_pを使用するように指定します。
CREATE FUNCTION to_doc(
tab TABLE,
cols COLUMNS DEFAULT NULL)
RETURN TABLE
PIPELINED ROW POLYMORPHIC USING to_doc_p;
to_doc PTFを起動して、表SCOTT.DEPTのすべての列を結合された1つのDOCUMENT列として表示します。
SELECT * FROM to_doc(scott.dept);DOCUMENT
--------------------------------------------------
{"DEPTNO":10, "DNAME":"ACCOUNTING", "LOC":"NEW YORK"}
{"DEPTNO":20, "DNAME":"RESEARCH", "LOC":"DALLAS"}
{"DEPTNO":30, "DNAME":"SALES", "LOC":"CHICAGO"}
{"DEPTNO":40, "DNAME":"OPERATIONS", "LOC":"BOSTON"}
部門10および30のすべての従業員について、DEPTNO、ENAMEおよびDOCUMENT列をDEPTNOおよびENAMEの順で表示します。to_doc PTFをCOLUMNS擬似演算子で起動し、表SCOTT.EMPの列EMPNO、JOB、MGR、HIREDATE、SALおよびCOMMを選択します。PTFは、これらの列をDOCUMENT列に結合します。
SELECT deptno, ename, document
FROM to_doc(scott.emp, COLUMNS(empno,job,mgr,hiredate,sal,comm))
WHERE deptno IN (10, 30)
ORDER BY 1, 2;DEPTNO ENAME DOCUMENT
------ ---------- ---------------------------------------------------------------------------------
10 CLARK {"EMPNO":7782, "JOB":"MANAGER", "MGR":7839, "HIREDATE":"09-JUN-81", "SAL":2450}
10 KING {"EMPNO":7839, "JOB":"PRESIDENT", "HIREDATE":"17-NOV-81", "SAL":5000}
10 MILLER {"EMPNO":7934, "JOB":"CLERK", "MGR":7782, "HIREDATE":"23-JAN-82", "SAL":1300}
30 ALLEN {"EMPNO":7499, "JOB":"SALESMAN", "MGR":7698, "HIREDATE":"20-FEB-81", "SAL":1600, "COMM":300}
30 BLAKE {"EMPNO":7698, "JOB":"MANAGER", "MGR":7839, "HIREDATE":"01-MAY-81", "SAL":2850}
30 JAMES {"EMPNO":7900, "JOB":"CLERK", "MGR":7698, "HIREDATE":"03-DEC-81", "SAL":950}
30 MARTIN {"EMPNO":7654, "JOB":"SALESMAN", "MGR":7698, "HIREDATE":"28-SEP-81", "SAL":1250, "COMM":1400}
30 TURNER {"EMPNO":7844, "JOB":"SALESMAN", "MGR":7698, "HIREDATE":"08-SEP-81", "SAL":1500, "COMM":0}
30 WARD {"EMPNO":7521, "JOB":"SALESMAN", "MGR":7698, "HIREDATE":"22-FEB-81", "SAL":1250, "COMM":500}
Eという名前の副問合せを使用して、DOC_ID列およびDOCUMENT列を表示します。すべての事務員従業員、給与、部門および部門の場所をレポートします。to_doc PTFを使用して、NAME、SAL、DEPTNOおよびLOC列をDOCUMENT列に結合します。
WITH e AS (
SELECT ename name, sal, deptno, loc
FROM scott.emp NATURAL JOIN scott.dept
WHERE job = 'CLERK')
SELECT ROWNUM doc_id, t.*
FROM to_doc(e) t; DOC_ID DOCUMENT
---------- -------------------------------------------------------
1 {"NAME":"MILLER", "SAL":1300, "DEPTNO":10, "LOC":"NEW YORK"}
2 {"NAME":"SMITH", "SAL":800, "DEPTNO":20, "LOC":"DALLAS"}
3 {"NAME":"ADAMS", "SAL":1100, "DEPTNO":20, "LOC":"DALLAS"}
4 {"NAME":"JAMES", "SAL":950, "DEPTNO":30, "LOC":"CHICAGO"}
副問合せブロックを使用して、DOCUMENT列に変換されたc1、c2、c3列の値を表示します。
WITH t(c1,c2,c3) AS (
SELECT NULL, NULL, NULL FROM dual
UNION ALL
SELECT 1, NULL, NULL FROM dual
UNION ALL
SELECT NULL, 2, NULL FROM dual
UNION ALL
SELECT 0, NULL, 3 FROM dual)
SELECT *
FROM to_doc(t);DOCUMENT
---------------
{}
{"C1":1}
{"C2":2}
{"C1":0, "C3":3}
部門30のすべての従業員について、プロパティ名ENAMEおよびCOMMを持つメンバーの値を表示します。SCOTT.EMP表のPTF起動レポートにより、JSON_VALUEファンクションへの入力として使用できるDOCUMENT列が生成されます。このファンクションは、一部のJSONデータからスカラー値を選択します。
SELECT JSON_VALUE(document, '$.ENAME') ename,
JSON_VALUE(document, '$.COMM') comm
FROM to_doc(scott.emp)
WHERE JSON_VALUE(document, '$.DEPTNO') = 30;ENAME COMM ---------- ---- ALLEN 300 WARD 500 MARTIN 1400 BLAKE TURNER 0 JAMES
Implicit_echo多相表関数の例
implicit_echo PTFの例では、多相表関数とDESCRIBE関数が同じパッケージ内で定義されている場合、USING句がオプションであることを示しています。
例13-39 Implicit_echo多相表関数の例
implicit_echo PTFは表と列を受け入れ、同じ値で新しい列を作成します。
このPTFは、入力表タブの列を返し、colsにリストされた、列名に"ECHO_"という接頭辞が付いた列を追加します。
DESCRIBEファンクション、implicit_echo多相表関数(PTF)およびFETCH_ROWSプロシージャが含まれている実装パッケージimplicit_echo_packageを作成します。
CREATE PACKAGE implicit_echo_package AS
prefix DBMS_ID := '"ECHO_';
FUNCTION DESCRIBE(tab IN OUT DBMS_TF.TABLE_T,
cols IN DBMS_TF.COLUMNS_T)
RETURN DBMS_TF.DESCRIBE_T;
PROCEDURE FETCH_ROWS;
-- PTF FUNCTION: WITHOUT USING CLAUSE --
FUNCTION implicit_echo(tab TABLE, cols COLUMNS)
RETURN TABLE PIPELINED ROW POLYMORPHIC;
END implicit_echo_package;読み取る入力表パラメータと列パラメータを含むDESCRIBEファンクションが含まれているパッケージを作成します。このファンクションは、多相表関数によって作成される行のタイプを決定する場合に呼び出されます。このファンクションは、表DBMS_TF.DESCRIBE_Tを戻します。FETCH_ROWSプロシージャは、出力行セット内に"ECHO_"という接頭辞付いた新しい列とともに指定された読取り列を生成するために必要になります。implicit_echoはPTF関数であり、2つの引数(tabとcols)が含まれています。それらの値は、問合せによって取得され、この情報がDESCRIBEファンクションに渡されます。RowセマンティクスではPTFタイプを指定しますが、USING句は指定されていません。このファンクションは、SQL問合せから起動されます。
PTF定義を含める実装パッケージ本体implicit_echo_packageを作成します。
CREATE PACKAGE BODY implicit_echo_package AS
FUNCTION DESCRIBE(tab IN OUT DBMS_TF.TABLE_T,
cols IN DBMS_TF.COLUMNS_T)
RETURN DBMS_TF.DESCRIBE_T
AS
new_cols DBMS_TF.COLUMNS_NEW_T;
col_id PLS_INTEGER := 1;
BEGIN
FOR i in 1 .. tab.column.COUNT LOOP
FOR j in 1 .. cols.COUNT LOOP
IF (tab.column(i).description.name = cols(j)) THEN
IF (NOT DBMS_TF.SUPPORTED_TYPE(tab.column(i).description.type)) THEN
RAISE_APPLICATION_ERROR(-20102, 'Unsupported column type['||
tab.column(i).description.type||']');
END IF;
tab.column(i).for_read := TRUE;
new_cols(col_id) := tab.column(i).description;
new_cols(col_id).name := prefix||
REGEXP_REPLACE(tab.column(i).description.name,
'^"|"$');
col_id := col_id + 1;
EXIT;
END IF;
END LOOP;
END LOOP;
/* VERIFY ALL COLUMNS WERE FOUND */
IF (col_id - 1 != cols.COUNT) then
RAISE_APPLICATION_ERROR(-20101,'Column mismatch['||col_id-1||'],
['||cols.COUNT||']');
END IF;
RETURN DBMS_TF.DESCRIBE_T(new_columns => new_cols);
END;
PROCEDURE FETCH_ROWS AS
rowset DBMS_TF.ROW_SET_T;
BEGIN
DBMS_TF.GET_ROW_SET(rowset);
DBMS_TF.PUT_ROW_SET(rowset);
END;
END implicit_echo_package;PTFを起動すると、表SCOTT.EMPのENAME列が表示されます。また、同じ値を持つ別の列ECHO_ENAMEも表示されます。
SELECT ENAME, ECHO_ENAME
FROM implicit_echo_package.implicit_echo(SCOTT.EMP, COLUMNS(SCOTT.ENAME));ENAME ECHO_ENAME ---------- ---------- SMITH SMITH ALLEN ALLEN WARD WARD JONES JONES MARTIN MARTIN BLAKE BLAKE CLARK CLARK SCOTT SCOTT KING KING TURNER TURNER ADAMS ADAMS JAMES JAMES FORD FORD MILLER MILLER