173 DBMS_TF
DBMS_TFパッケージには、多相表関数(PTF)実装のためのユーティリティが含まれています。DBMS_TFサブプログラムを使用してデータを消費および作成し、その実行環境に関する情報を取得できます。
多相表関数(PTF)の概念、構文およびセマンティクスを十分に理解している必要があります。
参照:
-
多相表関数(PTF)の概念の概要は、Oracle Database PL/SQL言語リファレンスを参照してください
-
CREATEFUNCTIONPIPELINED句の構文とセマンティクスの詳細は、Oracle Database PL/SQL言語リファレンスを参照してください
173.1 DBMS_TFの概要
DBMS_TFパッケージには、多相表関数(PTF)で使用できるタイプ、定数およびサブプログラムが含まれています。
多相表関数(PTF)には、その機能を実装するためにデータベースからの様々なサービスが必要です。たとえば、PTFには、データベースから行を取得し、新しい行を返送するメカニズムが必要です。DBMS_TFパッケージは、これらのサーバーおよびクライアント・インタフェース・ユーティリティを提供します。
173.3 DBMS_TFの定数
このトピックでは、DBMS_TFパッケージで定義されている便利な定数について説明しています。
DBMS_TFパッケージは、パラメータ値またはタイプの指定に使用するいくつかの列挙定数を定義します。列挙定数にはパッケージ名を接頭辞として付加する必要があります(DBMS_TF.TYPE_DATEなど)。
表173-1 DBMS_TFでサポートされているタイプ
| 名前 | 説明 |
|---|---|
TYPE_BINARY_DOUBLE |
|
TYPE_BINARY_FLOAT |
|
TYPE_BLOB |
|
TYPE_CHAR |
|
TYPE_CLOB |
|
TYPE_DATE |
|
TYPE_INTERVAL_DS |
|
TYPE_INTERVAL_YM |
|
TYPE_NUMBER |
|
TYPE_ROWID |
|
TYPE_RAW |
|
TYPE_TIMESTAMP |
|
TYPE_TIMESTAMP_TZ |
|
TYPE_VARCHAR2 |
|
追加の定数は、特定のサブプログラムで使用するために定義されます。
参照:
-
CSTOREに関連する定数の詳細は、表173-3を参照してください
-
事前定義済のPTFメソッド名の詳細は、表173-4を参照してください
-
XSTOREに関連する定数の詳細は、表173-6を参照してください
-
サポートされているタイプの事前定義済のコレクションの詳細は、サポートされるタイプ・コレクションを参照してください
173.4 DBMS_TFの操作上のノート
これらの操作上のノートでは、クライアント側とサーバー側のインタフェース、および多相表関数(PTF)のコンパイルと実行文の管理の詳細について説明しています。
173.4.1 PTFクライアント・インタフェース
多相表関数(PTF)実装のクライアント・インタフェースは、すべてのPTFが指定する必要のある固定名を持つ一連のサブプログラムです。
PTFクライアント・インタフェースには、次の最大4つのサブプログラムを含めることができます。
-
DESCRIBEファンクション(必須) -
OPENプロシージャ(オプション) -
FETCH_ROWSプロシージャ(オプション) -
CLOSEプロシージャ(オプション)
DESCRIBEファンクションはSQLカーソル・コンパイル時に呼び出されます。
プロシージャOPEN、FETCH_ROWSおよびCLOSEは問合せ実行時に呼び出されます。
実装ファンクションへの引数は、次のような変更を含むPTFファンクションと一致する必要があります。
-
タイプが
TABLEおよびCOLUMNSの引数は、実行プロシージャOPEN、FETCH_ROWSおよびCLOSEではスキップされます。 -
TABLEおよびCOLUMNS引数には、DESCRIBEファンクションの記述子タイプが含まれています。 -
コンパイル時に使用できないスカラー引数はNULL値として渡されます(バインド変数を使用する場合など)。実行中、実際の値が渡されます。
DESCRIBEファンクション
DESCRIBEファンクションは、多相表関数(PTF)によって作成される行(行形式)のタイプを決定する場合に呼び出されます。DBMS_TF.DESCRIBE_T表を戻します。
SQL問合せがPTFを参照する場合は、DESCRIBEファンクションがSQLカーソル・コンパイル時に呼び出されます。SQLコンパイラは、PTF実装パッケージで定義されているDESCRIBEファンクションを特定します。PTFをコールする問合せからすべての引数値がDESCRIBEファンクションに渡されます。PLSQLファンクションと同様に、DESCRIBEファンクションはオーバーロードされる場合があり、引数のデフォルト値を持つ場合があります。
PTFファンクションとDESCRIBEファンクションの引数は一致する必要がありますが、TABLE引数のタイプはDBMS_TF.TABLE_T記述子タイプで置換され、COLUMNS引数のタイプはDBMS_TF.COLUMN_T記述子で置換されます。
DESCRIBEファンクションは、データベースに保存され、PTF出力(パススルー列)として変更されずに渡される必要のある列を示します。また、DESCRIBEファンクションは、PTFが計算に使用する入力列を示します(読取り列)。
最後に、DESCRIBEファンクションは、PTFがDBMS_TF.DESCRIBE_T記述子を使用して作成する新しい列のリスト(新しい列が作成されていない場合はNULL)を戻します。
OPENプロシージャ
OPENプロシージャの目的は、実行に固有の状態を初期化し、割り当てることです。OPENプロシージャは、表セマンティクスPTFを実装する場合に最も有用です。このファンクションは通常、GET_XIDファンクションをコールして、実行状態を管理するための一意のIDを取得します。
通常、OPENプロシージャはFETCH_ROWSプロシージャをコールする前に呼び出されます。
FETCH_ROWSプロシージャ
FETCH_ROWSプロシージャは、データベースに送信する出力行セットを作成します。このファンクションの呼出し回数および各行セットのサイズは、データによって異なり、問合せ実行時に決定されます。
CLOSEプロシージャ
CLOSEプロシージャは、PTF実行の終了時にコールされます。このプロシージャは、PTF実行状態に関連付けられているリソースを解放します。
例173-1 noop多相表関数の例
この例では、noopと呼ばれるPTFが作成されます。このPTFは、入力行を変更またはフィルタ処理なしで出力行として戻します。noopは、作成可能な最小PTFの1つです。
Live SQL:
この例は、Oracle Live SQLのnoop多相表関数で表示および実行できます。
noop PTFを実装するには、まず実装パッケージnoop_packageを作成します。
CREATE PACKAGE noop_package AS
FUNCTION describe(t IN OUT DBMS_TF.TABLE_T)
RETURN DBMS_TF.DESCRIBE_T;
PROCEDURE fetch_rows;
END noop_package;
DESCRIBEファンクションによって、新しい列が作成されないため、NULLが戻されます。FETCH_ROWSを実行しても、NULLが戻されます。
CREATE PACKAGE BODY noop_package AS
FUNCTION describe(t IN OUT DBMS_TF.TABLE_T)
RETURN DBMS_TF.DESCRIBE_T AS
BEGIN
RETURN NULL;
END;
PROCEDURE fetch_rows AS
BEGIN
RETURN;
END;
END noop_package;noop PTFは、呼び出されたときにnoop_packageを実行するように定義されています。
CREATE FUNCTION noop (t TABLE)
RETURN TABLE PIPELINED ROW POLYMORPHIC USING noop_package;PTFは、問合せで呼び出すことができます。次に例を示します。
SELECT *
FROM NOOP(emp)
WHERE deptno = 10; 7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7934 MILLER CLERK 7782 23-JAN-82 1300 10
WITH e
AS (SELECT *
FROM emp
NATURAL JOIN dept
WHERE dname = 'SALES')
SELECT t.*
FROM NOOP(e) t; 30 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 SALES CHICAGO 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 SALES CHICAGO 30 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 SALES CHICAGO 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 SALES CHICAGO 30 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 SALES CHICAGO 30 7900 JAMES CLERK 7698 03-DEC-81 950 SALES CHICAGO
173.4.2 PTFサーバー側インタフェース
DBMS_TFパッケージは、多相表関数(PTF)の実装に必要なサーバー側インタフェースを提供し、データベース内の情報の読取りおよび書込みを実行します。
このトピックでは、PTFサーバー側の実装に使用するタイプおよびサブプログラムのリストの一部を示します。
表173-2 PTFサーバー側インタフェースで一般的に使用されるタイプおよびサブプログラムの要約
| 名前 | 説明 |
|---|---|
COLUMN_METADATA_T |
列メタデータ・レコード |
COLUMN_T |
列記述子レコード |
TABLE_T |
表記述子レコード |
COLUMNS_T |
列名を含むコレクション |
COLUMNS_NEW_T |
新しい列のコレクション |
TAB_<typ>_T |
サポートされているタイプごとのコレクション。<typ>はサポートされるタイプ・コレクションで説明されています |
ROW_SET_T
|
行セットのレコードのデータ |
GET_COLプロシージャ
|
指定された(入力)列のデータをフェッチします |
PUT_COLプロシージャ
|
指定された(新規の)列のデータを戻します |
GET_ROW_SETプロシージャ
|
列の値の入力行セットをフェッチします |
PUT_ROW_SETプロシージャ
|
ALL (新規)列のデータを戻します |
SUPPORTED_TYPEファンクション
|
タイプがDBMS_TFサブプログラムでサポートされているかどうかを検証します
|
GET_XIDファンクション
|
セッション内のPTF状態に索引付けするための一意の実行IDを戻します |
参照:
-
タイプの完全なリストは、DBMS_TFのデータ構造を参照してください
-
サブプログラムの完全なリストは、DBMS_TFサブプログラムの要約を参照してください
173.4.3 読取り列
読取り列は、FETCH_ROWSプロシージャの実行時に多相表関数(PTF)によって処理される表の列のセットです。
PTFは、入力表記述子TABLE_Tで注釈を付けることによってDESCRIBE内の読取り列を指定します。指定された読取り列のみがフェッチされるため、FETCH_ROWS時の処理に使用できます。
問合せでのPTFの起動は、通常、COLUMNS演算子を使用して、問合せがPTFに読み取らせる列を指定します。この情報はDESCRIBEファンクションに渡され、このファンクションによってCOLUMN_T.FOR_READブール・フラグが設定されます。
読取り列には、スカラーSQLデータ型のみを使用できます。
echo多相表関数の例では、表および列のリストを使用して、同じ値で新しい列を作成します。
173.4.4 パススルー列
パススルー列は、変更されずに多相表関数(PTF)の入力表から出力に渡されます。
DESCRIBEファンクションは、入力表記述子DBMS_TF.TABLE_TにCOLUMN_T.PASS_THROUGHブール・フラグを設定することで、パススルー列を示します。
Row Semantics PTF内のすべての列は、デフォルトではパススルーとしてマークされます。Table Semantics PTFの場合、パススルーのデフォルト値はfalseに設定されます。Table Semantics PTFでは、パーティション化列が常にパススルーであり、これはDESCRIBEファンクションで変更できません。
パススルー・オプションと読取りの概念には依存関係がなく、列をどちらか一方として示したとしても他方には影響がありません。
173.4.5 状態管理
データベースは、多相表関数(PTF)のコンパイルおよび実行状態を管理します。
データベースは、PTF実行者ロールを満たしています。したがって、データベースがPTFコンパイルの状態および実行状態を管理します。
-
コンパイル状態: 実行前に必要な
DESCRIBEによって生成される不変の状態です。 -
実行状態:
Table semanticsPTFの実行時プロシージャ(OPEN、FETCH_ROWSおよびCLOSE)で使用される状態です。
コンパイル状態の最も一般的な用途は、読み取られる列および作成される新しい列の追跡です。PTFサーバー・インタフェースには、これを実現するために使用できるファンクションであるGET_ENVおよびGET_ROW_SETが用意されています。PTFを定義、記述および実装するPTF作成者は、データベースを使用してPTF状態を管理できます。PTF作成者は、セッション状態(PL/SQLパッケージ・グローバル変数など)を使用してコンパイル状態を格納できません。指定されたセッションではPTFを使用するすべてのカーソルが状態を共有し、PTFカーソルを実行するその他のセッションでは元のコンパイル状態を確認できないため、問題が発生することがあります。
実行状態のセッションおよびカーソル固有であるため、Table Semantics PTFは実行状態の格納にグローバルなパッケージを使用できますが、PTFがデータベース提供の一意の実行IDを使用して、状態を識別するという規定があります。GET_XIDファンクションによって、PTFの実行プロシージャの一意の実行IDが提供されます。このIDはPTFのすべての実行ファンクションで一定のままとなります。
173.4.5.1 CSTOREコンパイル状態管理
CSTOREは、PTFコンパイル状態管理インタフェースです。
CSTOREは、多相表関数(PTF)によってSQLカーソルのコンパイル状態を格納できるようにします。
CSTOREインタフェースを使用して、DBMS_TF.DESCRIBE_Tレコードによるカーソル・コンパイル時にキー/値ペアを格納します。
コンパイル状態情報は、OPEN、FETCH_ROWS、CLOSEなどの実行プロシージャ時に取得されます。
CSTOREサブプログラム
CSTOREインタフェースは、次のサブプログラムで構成されています。
| 名前 | 説明 |
|
|
指定されたタイプの項目をフェッチします。見つからない場合は、OUT値は変更されません。 |
|
|
指定したキーを持つ項目がCSTORE内に存在する場合、このファンクションはTRUEを戻します。 |
CSTOREでサポートされているタイプ
DBMS_TF.DESCRIBE_Tでは、スカラー・タイプ(VARCHAR2、NUMBER、DATE、BOOLEAN)のキー/値ペアを指定できます。
表173-3 DBMS_TF CSTOREスカラーでサポートされているタイプ
| 名前 | 説明 |
|---|---|
CSTORE_TYPE_VARCHAR2 |
CSTORE |
CSTORE_TYPE_NUMBER |
CSTORE |
CSTORE_TYPE_DATE |
CSTORE |
CSTORE_TYPE_BOOLEAN |
CSTORE |
コンパイル格納のコレクション
次の事前定義済コレクション・タイプがコンパイル状態管理に使用されます。
TYPE CSTORE_CHR_T IS TABLE OF VARCHAR2(32767) INDEX BY VARCHAR2(32767); TYPE CSTORE_NUM_T IS TABLE OF NUMBER INDEX BY VARCHAR2(32767); TYPE CSTORE_BOL_T IS TABLE OF BOOLEAN INDEX BY VARCHAR2(32767); TYPE CSTORE_DAT_T IS TABLE OF DATE INDEX BY VARCHAR2(32767);
DBMS_TFメソッド名
メソッド名もDBMS_TF.DESCRIBE_Tレコードに格納されます。PTF作成者は、メソッド名のこれらの事前定義済値をカスタマイズできます。
デフォルトのメソッド名の変更の詳細は、メソッド名の上書きを参照してください
表173-4 DBMS_TFメソッド名の定数
| 名前 | タイプ | 値 | 説明 |
|---|---|---|---|
|
|
|
'CLOSE' |
CLOSEという名前のメソッドの事前定義済索引値 |
|
|
|
'FETCH_ROWS' |
FETCH_ROWSという名前のメソッドの事前定義済索引値 |
|
|
|
'OPEN' |
OPENというメソッドの事前定義済索引値 |
173.4.5.2 XSTORE実行状態管理
XSTOREは、PTF実行状態管理インタフェースです。
キーが文字列であり、値が一般的に使用されるスカラー・タイプである場合は、XSTOREキー/値インタフェースによって、自動状態管理機能が提供され、Table Semantics PTFの実装が簡略化されます。
データベースでは、このインタフェースを使用して、割り当てられたすべての実行状態の削除が自動的に管理されます。
XSTOREサブプログラム
実行状態管理インタフェースは、次のサブプログラムで構成されています。
表173-5 DBMS_TF XSTOREサブプログラム
| 名前 | 説明 |
|---|---|
|
|
XSTORE実行状態からすべてのキー/値ペアを削除します |
|
|
特定のキーを持つ項目がXSTORE内に存在する場合にTRUEを戻します |
|
|
XSTOREに格納されている指定されたキーに関連付けられた値を取得します |
|
|
指定されたキーおよびkey_typeに関連付けられている項目を削除します |
|
|
PTF実行状態管理の指定されたキーの値を設定します |
XSTORE事前定義タイプ
XSTOREでは、スカラー・タイプ(VARCHAR2、NUMBER、DATEおよびBOOLEAN)のキー/値ペアを指定できます。
表173-6 DBMS_TF XSTOREスカラーでサポートされているタイプ
| 名前 | 説明 |
|---|---|
|
|
XSTORE |
|
|
XSTORE |
|
|
XSTORE |
|
|
XSTORE |
173.4.6 メソッド名の上書き
複数の多相表関数(PTF)実装が同じパッケージ内にある場合は、デフォルトのランタイム・メソッド名(OPEN、FETCH_ROWSおよびCLOSE)をPTFに固有の名前で上書きできます。
メソッド名を上書きするには、アプリケーションはDBMS_TF METHOD_NAMESコレクションを使用して新規メソッド名を指定します(DESCRIBE_Tレコード・タイプを参照)。
参照:
表173-4例173-2 DBMS_TFメソッド名の上書き
この例では、noop_p PTF fetch_rowsメソッドのデフォルトのメソッド名をnoop_fetchに変更する方法を示しています。
Live SQL:
この例は、Oracle Live SQLのDBMS_TFメソッド名の上書きで表示および実行できます。
PTF実装パッケージnoop_pを作成します。
CREATE PACKAGE noop_p AS
FUNCTION describe(tab IN OUT DBMS_TF.table_t)
RETURN DBMS_TF.describe_t;
PROCEDURE noop_fetch;
END noop_p;
メソッド名の上書きを指定するには、DBMS_TF.Method_Namesコレクションを使用して新しいメソッド名を指定できます。FETCH_ROWSメソッド名は'Noop_Fetch'に変更されます。このメソッドを実装するプロシージャnoop_fetchは、パッケージに定義されています。
CREATE OR replace PACKAGE BODY noop_p
AS
FUNCTION describe(tab IN OUT DBMS_TF.table_t)
RETURN DBMS_TF.describe_t AS
methods DBMS_TF.methods_t := DBMS_TF.methods_t(DBMS_TF.fetch_rows => 'Noop_Fetch');
BEGIN
RETURN DBMS_TF.describe_t(method_names => methods);
END;
PROCEDURE noop_fetch AS
BEGIN
RETURN;
END;
END noop_p;
noop PTFは、呼び出されたときにnoop_pを実行するように定義されています。
CREATE FUNCTION noop (t TABLE) RETURN TABLE PIPELINED ROW POLYMORPHIC USING noop_p; PTFは、問合せブロックのFROM句で起動されます。
SELECT *
FROM noop(scott.emp)
WHERE deptno =10;173.4.7 COLUMNS疑似演算子の使用方法
COLUMNS疑似演算子は、SQL式の言語の追加です。
COLUMNS疑似演算子を使用して、FROM句で多相表関数(PTF)を起動する際に引数を指定します。COLUMNS疑似演算子の引数は、列名のリスト、または関連付けられたタイプを使用して列名のリストを指定します。
参照:
COLUMNS疑似演算子の構文とセマンティクスの詳細は、Oracle Database PL/SQL言語リファレンスを参照してください
173.4.8 問合せ変換
述語、予測およびパーティション化について説明します。
Row Semantics PTFのパススルー列およびTable Semantics PTFのPARTITION BYキー列は、予測および述語のプッシュダウンに使用できます。
例173-3 問合せ変換
次の例は、Row Semantics PTFの述語および予測のプッシュダウンを示しています。
SELECT empno, ename, sal, comm, echo_sal
FROM echo(emp, COLUMNS(sal,comm))
WHERE deptno = 30
AND echo_sal > 1000; EMPNO ENAME SAL COMM ECHO_SAL
---------- ---------- ---------- ---------- ----------
7499 ALLEN 1600 300 1600
7521 WARD 1250 500 1250
7654 MARTIN 1250 1400 1250
7698 BLAKE 2850 2850
7844 TURNER 1500 0 1500
概念上は、この問合せは次のようにリライトされます。WITH t AS (SELECT empno, ename, sal, comm
FROM emp
WHERE deptno=30)
SELECT empno, ename, sal, comm, echo_sal
FROM echo(t, COLUMNS(sal, comm))
WHERE echo_sal > 1000;173.4.9 パラレル実行
多相表関数(PTF)の主な利点は、その実行をパラレル化できることです。
行および表のセマンティックPTFは、パラレルで別の方法で実行されます。
行セマンティクスPTF
Row Semantics PTFでは、PTFが存在しない場合と同じ並列度(DOP)でパラレル問合せが実行されます。DOPが子行ソースによって決定されます。
次に、emp表でDOPが5に設定されている場合のパラレル化の例を示します。
EXPLAIN PLAN FOR
SELECT * FROM echo(emp, COLUMNS(ename, job))
WHERE deptno != 20;
---------------------------------------------------
| Id | Operation | Name |
---------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | PX COORDINATOR | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 |
| 3 | POLYMORPHIC TABLE FUNCTION | ECHO |
| 6 | PX BLOCK ITERATOR | |
|* 7 | TABLE ACCESS FULL | EMP |
---------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("EMP"."DEPTNO"<>20)表セマンティクスPTF
Table Semantics PTFでは、入力表の行が、PARTITION BYキーを使用して再分散される必要があります。パラレル実行は、問合せに指定されているPARTITION BY句によって決定されます。
173.5 DBMS_TFの実行フロー
多相表関数(PTF)を呼び出す問合せの実行は、この実行モデルおよびデータ転送フローに従います。
PTF実行プロシージャ(OPEN、FETCH_ROWSおよびCLOSE)は問合せ実行時にデータベースによってコールされます。
-
OPEN(存在する場合) -
FETCH_ROWS(複数回起動される可能性があります) -
CLOSE(存在する場合)
FETCH_ROWSプロシージャは、行セット(行のコレクション)のデータを読み取り、出力行セットを作成します。
FETCH_ROWSの各コールは、PTFによって処理されることが予期される、入力行のデータ・コレクションである行セットに関連付けられます。
GET_ROW_SETまたはGET_COLを使用して、入力行セットを読み取ります。
PUT_ROW_SETまたはPUT_COLを使用して、出力行セットを作成します。出力行セットは、再度データベースに書き込まれます。
PUT_ROW_SETを使用して、1回のコールですべての新しい列を設定します。
ROWSET_Tレコードは、複数の列のデータを保持します。PTFアルゴリズムのほうが一度に1つの出力列を作成するのに適している場合は、PUT_COLプロシージャを使用して1つの列を作成できます。指定された列は、FETCH_ROWSへのコール内で1回のみ作成できます。
Row Semantics PTFの場合、FETCH_ROWSプロシージャは、データベースに戻る前にPTFサーバー・インタフェースを使用して新しい行を戻します。
173.6 DBMS_TFの制限事項
これらの制限事項は、多相表関数(PTF)およびDBMS_TFパッケージの使用に適用されます。
タイプの制限事項
多相表関数(PTF)は、任意のSQLタイプの列を含む表を操作できます。ただし、読取り列および新しい列はスカラー・タイプに制限されています。読取り列および新しい列は、PUT_ROW_SET、PUT_COL、GET_ROW_SETおよびGET_COLプロシージャで使用されます。すべてのSQLタイプは、パススルー列で使用できます。DESCRIBEファンクションはDBMS_TF.SUPPORTED_TYPEファンクションを使用して、サポートされるタイプを決定できます。
PTFの起動と実行の制限事項
多相表関数は、問合せのFROM句内にネストできません。PTFのネストは、WITH句を使用する場合にのみ許可されます。
多相表関数でのテーブル・ファンクションのネストは、CURSOR式を使用する場合にのみ許可されます。PTFは、テーブル・ファンクションの引数として指定することはできません。
多相表関数(PTF)からROWIDを選択することはできません。
PARTITION BYおよびORDER BY句は、Table Semantics PTFの引数にのみ指定できます。
PTF実行メソッドOPEN、FETCH_ROWSおよびCLOSEは、多相表関数の実行コンテキストのみで起動する必要があります。
DESCRIBEメソッドを直接起動することはできません。
次の例は、PTFでネストされた10個の起動を示しています。
WITH t0
AS (SELECT /*+ parallel */ *
FROM noop(dept)),
t1
AS (SELECT *
FROM noop(t0)),
t2
AS (SELECT *
FROM noop(t1)),
t3
AS (SELECT *
FROM noop(t2)),
t4
AS (SELECT *
FROM noop(t3)),
t5
AS (SELECT *
FROM noop(t4)),
t6
AS (SELECT *
FROM noop(t5)),
t7
AS (SELECT *
FROM noop(t6)),
t8
AS (SELECT *
FROM noop(t7)),
t9
AS (SELECT *
FROM noop(t8))
SELECT *
FROM noop(t9)
WHERE deptno = 10; 10 ACCOUNTING NEW YORK
173.7 DBMS_TFの例
これらの例では、DBMS_TFサブプログラムを使用します。
DBMS_TFの例の要約
次の例は不完全であり、デモンストレーション専用です。
-
例173-1 noop多相表関数の例
-
例173-2 DBMS_TFメソッド名の上書き
-
例173-3 問合せ変換
-
例173-5 DBMS_TF.COLUMN_TYPE_NAMEの例
-
例173-6 DBMS_TF.COL_TO_CHARの例
-
例173-7 DBMS_TF.CSTORE_EXISTSの例
-
例173-8 DBMS_TF.GET_COLの例
-
例173-9 DBMS_TF.GET_ENVの例
-
例173-10 DBMS_TF.GET_ROW_SETの例
-
例173-12 DBMS_TF.GET_XIDの例
-
rand_col多相表関数の例(DBMS_TF.PUT_COLの例)
-
split多相表関数の例(DBMS_TF.GET_ROW_SETおよびPUT_ROW_SETの例)
-
例173-14 DBMS_TF.PUT_ROW_SETの例
-
例173-16 Replicate: DBMS_TF.ROW_REPLICATIONの例
-
例173-17 DBMS_TF.ROW_TO_CHARの例
-
例173-18 DBMS_TF.TRACEの例
-
row_num多相表関数の例(DBMS_TF.XSTORE_GETおよびXSTORE_SETの例)
-
Oracle PL/SQL言語リファレンスのskip_col多相表関数の例
Oracle PL/SQL言語リファレンスのto_doc多相表関数の例
173.7.1 echo多相表関数の例
echo PTFは、表および列のリストを受け入れて同じ値で新しい列を作成します。
このPTFは、入力表タブのすべての列を戻し、colsにリストされた、列名にECHO_という接頭辞が付いた列を追加します。
Live SQL:
この例は、Oracle Live SQLのecho多相表関数で表示および実行できます。
echo PTFは、問合せのFROM句に使用できます。COLUMNS演算子を使用して、次のように列を指定します。
SELECT *
FROM echo(scott.dept, COLUMNS(dname, loc));
DEPTNO DNAME LOC ECHO_DNAME ECHO_LOC
---------- -------------- ------------- -------------- -------------
10 ACCOUNTING NEW YORK ACCOUNTING NEW YORK
20 RESEARCH DALLAS RESEARCH DALLAS
30 SALES CHICAGO SALES CHICAGO
40 OPERATIONS BOSTON OPERATIONS BOSTON
-
PTF実装パッケージ仕様部: 仕様部には、DESCRIBEメソッドが必要です。OPEN、FETCH_ROWSおよびCLOSEメソッドはオプションです。
-
PTF実装パッケージ本体: DESCRIBEメソッドには、新しい列パラメータ(このPTFによって作成された追加の列)を含めることができます。この後に、PTFファンクションのパラメータが続きます。
-
PTFファンクション: PTFファンクションには、実装パッケージへの参照があります。
echo_packageパッケージ仕様部は、DESCRIBEおよびFETCH_ROWSメソッドを定義します。
CREATE PACKAGE 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;
END echo_package;echo_packageパッケージ本体には、PTF実装が含まれています。
CREATE PACKAGE BODY 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 || 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 echo_package;PTF echoは実装パッケージecho_packageを参照しています。
CREATE FUNCTION echo(tab TABLE,
cols COLUMNS)
RETURN TABLE
PIPELINED ROW POLYMORPHIC USING echo_package;例173-4 問合せでのecho PTFの使用方法
次の例では、部門20に所属する全従業員を選択します。結果の行には、3つの新しい列ECHO_ENAME、ECHO_HIREDATEおよびECHO_SALが含まれます。
SELECT *
FROM echo(scott.emp, COLUMNS(ename, sal, hiredate))
WHERE deptno = 20; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ECHO_ENAME ECHO_HIRE ECHO_SAL ----- ----- -------- ---- --------- ---- ---- ------ ---------- --------- -------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 SMITH 17-DEC-80 800 7566 JONES MANAGER 7839 02-APR-81 2975 20 JONES 02-APR-81 2975 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 SCOTT 19-APR-87 3000 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 ADAMS 23-MAY-87 1100 7902 FORD ANALYST 7566 03-DEC-81 3000 20 FORD 03-DEC-81 3000
副問合せWを使用して、部門30で給与が1000を超えているすべての従業員のENAME列、ECHO_LOC列およびDNAME列を表示します。
WITH w
AS (SELECT e.*,
dname,
loc
FROM scott.emp e,
scott.dept d
WHERE e.deptno = d.deptno)
SELECT ename,
echo_loc,
dname
FROM echo(w, COLUMNS(sal, dname, loc, hiredate))
WHERE deptno = 30
AND echo_sal > 1000; ENAME ECHO_LOC DNAME ---------- --------------- -------- ALLEN CHICAGO SALES WARD CHICAGO SALES MARTIN CHICAGO SALES BLAKE CHICAGO SALES TURNER CHICAGO SALES
副問合せWを使用して、給与が1000を超えているすべての従業員のENAME列およびDNAME列を表示します。
WITH w
AS (SELECT e.*,
dname,
loc
FROM scott.emp e,
scott.dept d
WHERE e.deptno = d.deptno)
SELECT echo_ename,
dname
FROM echo(w, COLUMNS(loc, deptno, dname, ename)) e
WHERE ename IN (SELECT echo_ename
FROM echo(scott.emp, COLUMNS(sal, deptno, ename, hiredate))
WHERE deptno = e.echo_deptno
AND sal > 1000); ECHO_ENAME DNAME ---------- ---------- ALLEN SALES MILLER ACCOUNTING CLARK ACCOUNTING WARD SALES ADAMS RESEARCH TURNER SALES SCOTT RESEARCH BLAKE SALES JONES RESEARCH KING ACCOUNTING FORD RESEARCH MARTIN SALES
173.8 DBMS_TFのデータ構造
DBMS_TFパッケージは、次のレコード・タイプ、表タイプおよびサブタイプを定義します。
レコード・タイプ
表タイプ
タイプ
173.8.1 サポートされるタイプ・コレクション
サポートされているタイプごとに、対応する事前定義済のコレクションが定義されています。
構文
TYPE TAB_BOOLEAN_T IS TABLE OF BOOLEAN INDEX BY PLS_INTEGER; TYPE TAB_BINARY_FLOAT_T IS TABLE OF BINARY_FLOAT INDEX BY PLS_INTEGER; TYPE TAB_BINARY_DOUBLE_T IS TABLE OF BINARY_DOUBLE INDEX BY PLS_INTEGER; TYPE TAB_BLOB_T IS TABLE OF BLOB INDEX BY PLS_INTEGER; TYPE TAB_CHAR_T IS TABLE OF CHAR(32767) INDEX BY PLS_INTEGER; TYPE TAB_CLOB_T IS TABLE OF CLOB INDEX BY PLS_INTEGER; TYPE TAB_DATE_T IS TABLE OF DATE INDEX BY PLS_INTEGER; TYPE TAB_INTERVAL_YM_T IS TABLE OF YMINTERVAL_UNCONSTRAINED INDEX BY PLS_INTEGER; TYPE TAB_INTERVAL_DS_T IS TABLE OF DSINTERVAL_UNCONSTRAINED INDEX BY PLS_INTEGER; TYPE TAB_NATURALN_T IS TABLE OF NATURALN INDEX BY PLS_INTEGER; TYPE TAB_NUMBER_T IS TABLE OF NUMBER INDEX BY PLS_INTEGER; TYPE TAB_RAW_T IS TABLE OF RAW(32767) INDEX BY PLS_INTEGER; TYPE TAB_ROWID_T IS TABLE OF ROWID INDEX BY PLS_INTEGER; TYPE TAB_VARCHAR2_T IS TABLE OF VARCHAR2(32767) INDEX BY PLS_INTEGER; TYPE TAB_TIMESTAMP_T IS TABLE OF TIMESTAMP_UNCONSTRAINED INDEX BY PLS_INTEGER; TYPE TAB_TIMESTAMP_TZ_T IS TABLE OF TIMESTAMP_TZ_UNCONSTRAINED INDEX BY PLS_INTEGER; TYPE TAB_TIMESTAMP_LTZ_T IS TABLE OF TIMESTAMP_LTZ_UNCONSTRAINED INDEX BY PLS_INTEGER;
参照:
DBMS_TFでサポートされるタイプの詳細は、表173-1を参照してください173.8.2 COLUMN_DATA_Tレコード・タイプ
単一の列のデータ(変数レコード)。
レコードでアクティブな変数フィールドは1つのみです。descriptionにはアクティブな列タイプに関する情報が含まれます。
サポートされているタイプのリストは、表173-1を参照してください。
構文
TYPE COLUMN_DATA_T IS RECORD
( description COLUMN_METADATA_T,
tab_varchar2 TAB_VARCHAR2_T,
tab_number TAB_NUMBER_T,
tab_date TAB_DATE_T,
tab_binary_float TAB_BINARY_FLOAT_T,
tab_binary_double TAB_BINARY_DOUBLE_T,
tab_raw TAB_RAW_T,
tab_char TAB_CHAR_T,
tab_clob TAB_CLOB_T,
tab_blob TAB_BLOB_T,
tab_timestamp TAB_TIMESTAMP_T,
tab_timestamp_tz TAB_TIMESTAMP_TZ_T,
tab_interval_ym TAB_INTERVAL_YM_T,
tab_interval_ds TAB_INTERVAL_DS_T,
tab_timestamp_ltz TAB_TIMESTAMP_LTZ_T,
tab_rowid TAB_ROWID_T);フィールド
表173-7 COLUMN_DATA_Tのフィールド
| フィールド | 説明 |
|---|---|
|
|
タグは、どの変数フィールドがアクティブかを示す、列のメタデータを定義します。 |
|
|
変数フィールド |
|
|
変数フィールド |
|
|
変数フィールド |
|
|
変数フィールド |
|
|
変数フィールド |
|
|
変数フィールド |
|
|
変数フィールド |
|
|
変数フィールド |
|
|
変数フィールド |
|
|
変数フィールド |
|
|
変数フィールド |
|
|
変数フィールド |
|
|
変数フィールド |
|
|
変数フィールド |
|
|
変数フィールド |
173.8.3 COLUMN_METADATA_Tレコード・タイプ
このタイプには、既存の表の列またはPTFで作成された新しい列に関するメタデータが含まれます。
構文
TYPE COLUMN_METADATA_T IS RECORD
( type PLS_INTEGER,
max_len PLS_integer DEFAULT -1,
name VARCHAR2(32767),
name_len PLS_INTEGER,
precision PLS_INTEGER,
scale PLS_INTEGER,
charsetid PLS_INTEGER,
charsetform PLS_INTEGER,
collation PLS_INTEGER ); フィールド
表173-8 COLUMN_METADATA_Tのフィールド
| フィールド | 説明 |
|---|---|
|
|
列のタイプの内部Oracle typecode |
|
|
列の最大長。許可されている最大長未満である場合はその値が使用され、NULLまたはゼロの場合はゼロが使用されます。ゼロ未満の場合は、最大許容長が使用されます。タイプ(date、floatなど)に長さが関連しない場合、この値は無視されます。 |
|
|
列の名前 |
|
|
名前の長さ |
|
|
精度または最大有効桁数(数値データ型の場合) |
|
|
スケールまたは小数点から最下位有効数字までの桁数(数値データ型の場合) |
|
|
文字セットID (内部Oracleコード。文字列タイプに適用されます) |
|
|
文字セット形式(内部Oracleコード。文字列タイプに適用されます) |
|
|
照合ID (内部Oracleコード。文字列タイプに適用されます) |
173.8.4 COLUMN_Tレコード・タイプ
PTFに固有の属性を含むタイプCOLUMN_METADATA_Tの列記述子レコード。
構文
TYPE column_t IS RECORD (
description COLUMN_METADATA_T,
pass_through BOOLEAN,
for_read BOOLEAN);フィールド
表173-9 COLUMN_Tのフィールド
| フィールド | 説明 |
|---|---|
|
|
列メタデータ |
|
|
パススルー列 |
|
|
PTFによって読み取られる列 |
173.8.5 DESCRIBE_Tレコード・タイプ
PTFのDESCRIBEメソッドからの戻り型。
構文
TYPE DESCRIBE_T IS RECORD
( NEW_COLUMNS COLUMNS_NEW_T DEFAULT COLUMNS_NEW_T(),
CSTORE_CHR CSTORE_CHR_T DEFAULT CSTORE_CHR_T(),
CSTORE_NUM CSTORE_NUM_T DEFAULT CSTORE_NUM_T(),
CSTORE_BOL CSTORE_BOL_T DEFAULT CSTORE_BOL_T(),
CSTORE_DAT CSTORE_DAT_T DEFAULT CSTORE_DAT_T(),
METHOD_NAMES METHODS_T DEFAULT METHODS_T());フィールド
表173-10 DESCRIBE_Tのフィールド
| フィールド | 説明 |
|---|---|
|
|
PTFによって作成される新しい列の説明 |
|
|
CStore配列キー・タイプ: |
|
|
CStore配列キー・タイプ: |
|
|
CStore配列キー・タイプ: |
|
|
CStore配列キー・タイプ: |
|
|
ユーザーが |
173.8.6 ENV_Tレコード・タイプ
このレコードには、多相表関数の実行状態に関するメタデータが含まれます。
構文
TYPE ENV_T IS RECORD ( get_columns TABLE_METADATA_T, put_columns TABLE_METADATA_T, ref_put_col REFERENCED_COLS_T, parallel_env PARALLEL_ENV_T, query_optim BOOLEAN, row_count PLS_INTEGER, row_replication BOOLEAN, row_insertion BOOLEAN);
フィールド
表173-11 ENV_Tのフィールド
| フィールド | 説明 |
|---|---|
|
|
PTFの |
|
|
|
|
|
put列が問合せで参照される場合はTRUE |
|
|
パラレル実行情報(問合せがパラレルで実行される場合) |
|
|
この実行は、問合せ最適化用ですか。 問合せがオプティマイザのかわりに実行されていた場合はTRUE |
|
|
現在の行セット内の行の数 |
|
|
行のレプリケーションは有効ですか。 |
|
|
行の挿入は有効ですか。 |
173.8.7 PARALLEL_ENV_Tレコード・タイプ
このレコードには、多相表関数のパラレル実行に固有のメタデータが含まれます。
構文
TYPE PARALLEL_ENV_T IS RECORD
( instance_id PLS_INTEGER,
session_id PLS_INTEGER,
slave_svr_grp PLS_INTEGER,
slave_set_no PLS_INTEGER,
no_slocal_slaves PLS_INTEGER,
global_slave_no PLS_INTEGER,
no_local_slaves PLS_INTEGER,
local_slave_no PLS_INTEGER );
フィールド
表173-12 PARALLEL_ENV_Tのフィールド
| フィールド | 説明 |
|---|---|
|
|
QCインスタンスID |
|
|
QCセッションID |
|
|
スレーブ・サーバー・グループ |
|
|
スレーブ・サーバー・セット番号 |
|
|
兄弟スレーブの数(自身を含む) |
|
|
グローバル・スレーブ数(ベース0) |
|
|
インスタンスで実行されている兄弟スレーブの数 |
|
|
ローカル・スレーブ数(ベース0) |
173.8.8 TABLE_Tレコード・タイプ
DESCRIBEファンクションの入力表記述子の引数は、TABLE_Tレコード・タイプです。
構文
TYPE TABLE_T IS RECORD( column TABLE_COLUMNS_T, schema_name DBMS_id, package_name DBMS_id, ptf_name DBMS_id);
フィールド
表173-13 TABLE_Tのフィールド
| フィールド | 説明 |
|---|---|
|
|
列情報 |
|
|
PTFスキーマ名 |
|
|
PTF実装パッケージ名 |
|
|
起動されたPTF名 |
173.8.9 COLUMNS_NEW_T表タイプ
新しい列のコレクション
構文
TYPE COLUMNS_NEW_T IS TABLE OF COLUMN_METADATA_T INDEX BY PLS_INTEGER;
173.9 DBMS_TFサブプログラムの要約
この要約では、DBMS_TFパッケージのサブプログラムについて簡単に説明します。
表173-14 DBMS_TFサブプログラム
| サブプログラム | 説明 |
|---|---|
|
|
指定された列タイプのタイプ名を戻します |
|
|
指定された列の文字列表現を戻します |
|
|
PTFコンパイル状態管理ストア内に特定のキーを持つ項目が存在する場合にTRUEを戻します |
|
|
PTFコンパイル状態管理ストアから指定されたタイプの項目を取得します |
|
|
読取り列値を取得します |
|
|
PTFランタイム環境に関する情報を戻します |
|
|
コレクション内の列値の読取りセットを取得します |
|
|
カーソル実行に固有のランタイム状態を索引付けするため、PTFで使用できる一意の実行IDを戻します |
|
|
データベースの列の値を設定します |
|
|
データベースの列値のコレクション読取りセットを設定します |
|
|
行のレプリケーション係数を設定します |
|
|
行セットの行の文字列表現を戻します |
|
|
指定されたタイプがPTFインフラストラクチャでサポートされている場合は、TRUEを戻します |
|
|
開発および問題の診断を支援するためのデータ構造を出力します |
|
|
すべてのキー/値ペアをXStoreから削除します |
|
|
キーに関連付けられた値がある場合は、TRUEを戻します |
|
|
PTF実行状態管理のキーと値のストアを取得します |
|
|
指定されたキーに関連付けられた値をすべて削除します |
|
|
PTF実行状態管理の指定されたキー・ストアの値を設定します |
173.9.1 COLUMN_TYPE_NAMEファンクション
指定された列タイプのタイプ名を戻します。
構文
FUNCTION COLUMN_TYPE_NAME( col COLUMN_METADATA_T) RETURN VARCHAR2;
パラメータ
表173-15 DBMS_TF.COLUMN_TYPE_NAMEファンクションのパラメータ
| パラメータ | 説明 |
|---|---|
|
|
列メタデータ。COLUMN_METADATA_Tレコード・タイプを参照してください |
戻り値
テキストとして変換された列タイプを戻します。
例173-5 DBMS_TF.COLUMN_TYPE_NAMEの例
次の例は、COLUMN_TYPE_NAMEを起動して列タイプを比較し、列タイプがVARCHAR2ではない場合にアプリケーション・エラーが発生するアプリケーション・タイプ・チェックを示しています。
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.count LOOP FOR j IN 1 .. cols.count LOOP IF (tab(i).description.name = cols(j)) THEN IF (DBMS_TF.column_type_name(tab(i).description.type) != 'VARCHAR2') THEN raise_application_error(-20102, 'Unsupported column type ['||tab(i).description.type||']'); END IF; tab(i).for_read := true; new_cols(col_id) := tab(i).description; new_cols(col_id).name := 'ECHO_'|| tab(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;
173.9.2 COL_TO_CHARファンクション
指定された列の文字列表現を戻します。
構文
FUNCTION COL_TO_CHAR( col COLUMN_DATA_T, rid PLS_INTEGER, quote VARCHAR2 DEFAULT '"') RETURN VARCHAR2;
パラメータ
表173-16 DBMS_TF.COL_TO_CHARファンクションのパラメータ
| パラメータ | 説明 |
|---|---|
|
|
値が変換される列 |
|
|
行番号 |
|
|
数値以外の値に使用する引用符 |
戻り値
列データの値の文字列表現。
例173-6 DBMS_TF.COL_TO_CHARの例
PROCEDURE Fetch_Rows AS
rowset DBMS_TF.rROW_SET_T;
str VARCHAR2(32000);
BEGIN
DBMS_TF.GET_ROW_SET(rowset);
str := DBMS_TF.COL_TO_CHAR(rowset(1), 1)
END;173.9.3 CSTORE_EXISTSファンクション
PTFコンパイル状態ストアに指定されたキーを持つ項目が存在する場合はTRUEを戻します。
構文
FUNCTION CSTORE_EXISTS (key IN VARCHAR2, key_type IN PLS_INTEGER default NULL) return BOOLEAN;
パラメータ
表173-17 CSTORE_EXISTSファンクションのパラメータ
| パラメータ | 説明 |
|---|---|
|
|
一意の文字キー |
|
|
キーのタイプ(オプション)。デフォルト: NULL |
戻り値
キーに関連付けられた値がある場合は、TRUEを戻します。key_typeがNULL (デフォルト)の場合、キーにサポートされているタイプの関連付けられている値がある場合は、TRUEを戻します。
key_typeパラメータ値が渡されるときに、キーおよびキーの指定されたタイプに関連付けられている値がある場合は、TRUEを戻します。それ以外の場合はFALSEを返します。
例173-7 DBMS_TF.CSTORE_EXISTSの例
次のコードの抜粋は、コンパイル・ストアから読み取る前にキーを持つ項目が存在するかどうかをチェックします。
IF (DBMS_TF.CSTORE_EXISTS('min'||j)) THEN
DBMS_TF.CSTORE_GET('min'||j, min_col);
END IF;173.9.4 CSTORE_GETプロシージャ
CSTORE_GETプロシージャを使用して、PTFコンパイル状態の格納されている指定されたキーに関連付けられた値を取得できます。
CSTOREは、PTFコンパイル状態管理インタフェースです。CSTOREインタフェースを使用して、DBMS_TF.DESCRIBEファンクションによるカーソル・コンパイル時にキー/値ペアを設定し、格納できます。
PTFコンパイル状態は、OPEN、FETCH_ROWS、CLOSEなどのランタイム・プロシージャの実行時に取得できます。
このプロシージャはオーバーロードされています。DESCRIBE_Tでは、スカラー・タイプ(VARCHAR2、NUMBER、DATE、BOOLEAN)のキー/値ペアを指定できます。
詳細は、表173-3を参照してください。
構文
値出力変数のキーに関連付けられた値を取得します。返される値のタイプは、サポートされているスカラー・タイプの1つです。
PROCEDURE CSTORE_GET( key IN VARCHAR2, value IN OUT VARCHAR2);
PROCEDURE CSTORE_GET( key IN VARCHAR2, value IN OUT NUMBER);
PROCEDURE CSTORE_GET( key IN VARCHAR2, value IN OUT DATE);
PROCEDURE CSTORE_GET( key IN VARCHAR2, value IN OUT BOOLEAN);
特定のキーが入力パラメータとして渡されない場合、CSTOREに存在する、そのタイプのキー値のコレクション全体が戻されます。
PROCEDURE CSTORE_GET(key_value OUT CSTORE_CHR_T); PROCEDURE CSTORE_GET(key_value OUT CSTORE_NUM_T); PROCEDURE CSTORE_GET(key_value OUT CSTORE_BOL_T); PROCEDURE CSTORE_GET(key_value OUT CSTORE_DAT_T);
パラメータ
表173-18 DBMS_TF.CSTORE_GETプロシージャのパラメータ
| パラメータ | 説明 |
|---|---|
|
|
一意の文字キー |
|
|
サポートされているタイプのキーに対応する値 |
|
|
キー値 |
173.9.5 GET_COLプロシージャ
読取り列値を取得します
構文
PROCEDURE GET_COL( columnId NUMBER, collection IN OUT NOCOPY <datatype>);
<datatype>は、次のサポートされるタイプのいずれかになります。
サポートされているタイプのリストは、表173-1を参照してください。パラメータ
表173-19 GET_COLプロシージャのパラメータ
| パラメータ | 説明 |
|---|---|
|
|
列のID |
|
|
列のデータ |
使用上のノート
このプロシージャを使用して、スカラー・タイプのコレクションで読取り列値を取得します。
列番号は、PTFのDESCRIBEメソッドで作成された列取得の順序になっています。
ColumnIdが同じ場合、GET_COLおよびPUT_COLは別の列に対応することがあります。
例173-8 DBMS_TF.GET_COLの例
次の例は、PTF実装パッケージで定義されているfetch_rowsプロシージャの抜粋です。
PROCEDURE fetch_rows
IS
col1 DBMS_TF.TAB_CLOB_T;
col2 DBMS_TF.TAB_CLOB_T;
out1 DBMS_TF.TAB_CLOB_T;
out2 DBMS_TF.TAB_CLOB_T;
BEGIN
DBMS_TF.GET_COL(1, col1);
DBMS_TF.GET_COL(2, col2);
FOR I IN 1 .. col1.COUNT LOOP
out1(i) := 'ECHO-' || col1(i);
END LOOP;
FOR I IN 1 .. col2.COUNT LOOP
out2(i) := 'ECHO-' || col2(i);
END LOOP;
DBMS_TF.PUT_COL(1, out1);
DBMS_TF.PUT_COL(2, out2);
END;
DBMS_TFのAPIを直接起動することはできません。コンテキスト外で前述のプロシージャを実行しようとすると、エラーが発生します。
exec fetch_rows ERROR at line 1: ORA-62562: The API Get_Col can be called only during execution time of a polymorphic table function.
173.9.6 GET_ENVファンクション
PTFランタイム環境に関する情報を戻します
構文
FUNCTION GET_ENV
RETURN ENV_T;戻り値
PTFランタイム環境に関する情報を戻します。
例173-9 DBMS_TF.GET_ENVの例
次の行は、FETCH_ROWS実装プロシージャのPTF実行情報を持つENV_Tタイプのローカル変数envを初期化する方法を示しています。
env DBMS_TF.ENV_T := DBMS_TF.GET_ENV();173.9.7 GET_ROW_SETプロシージャ
読取り列値を取得します
FETCH_ROWプロシージャはGET_ROW_SETプロシージャをコールして、サポートされるスカラー・タイプのコレクションの列値の入力行セットを読み取ることができます。このプロシージャはオーバーロードされています。
構文
PROCEDURE GET_ROW_SET( rowset OUT NOCOPY ROW_SET_T);
PROCEDURE GET_ROW_SET( rowset OUT NOCOPY ROW_SET_T, row_count OUT PLS_INTEGER);
PROCEDURE GET_ROW_SET( rowset OUT NOCOPY ROW_SET_T, row_count OUT PLS_INTEGER, col_count OUT PLS_INTEGER);
パラメータ
表173-20 GET_ROW_SETプロシージャのパラメータ
| パラメータ | 説明 |
|---|---|
|
|
データおよびメタデータのコレクション |
|
|
列内の行数 |
|
|
列数 |
例173-10 DBMS_TF.GET_ROW_SETの例
次の例は、説明を目的としたPTF実装パッケージの抜粋です。
PROCEDURE fetch_rows(new_name IN VARCHAR2 DEFAULT 'PTF_CONCATENATE')
AS
rowset DBMS_TF.ROW_SET_T;
accumulator DBMS_TF.TAB_VARCHAR2_T;
row_count PLS_INTEGER;
FUNCTION get_value(col PLS_INTEGER,
ROW PLS_INTEGER)
RETURN VARCHAR2
AS
col_type PLS_INTEGER := rowset(col).description.TYPE;
BEGIN
CASE col_type
WHEN DBMS_TF.TYPE_VARCHAR2 THEN
RETURN NVL(rowset(col).TAB_VARCHAR2 (ROW), 'empty');
ELSE
RAISE_APPLICATION_ERROR(-20201, 'Non-Varchar Type='||col_type);
END CASE;
END;
BEGIN
DBMS_TF.GET_ROW_SET(rowset, row_count);
IF ( rowset.count = 0 ) THEN
RETURN;
END IF;
FOR row_num IN 1 .. row_count LOOP
accumulator(row_num) := 'empty';
END LOOP;
FOR col_num IN 1 .. rowset.count LOOP
FOR row_num IN 1 .. row_count LOOP
accumulator(row_num) := accumulator(row_num) || get_value(col_num, row_num);
END LOOP;
END LOOP;
-- Pushout the accumulator
DBMS_TF.PUT_COL(1, accumulator);
END;
173.9.7.1 stack多相表関数の例
stack PTFの例では、各列の値を新しい行に変換することによって、指定した数値列のNULL以外の値をアンピボットします。
例173-11 stack多相表関数の例
Live SQL:
この例は、Oracle Live SQLのstack多相表関数で表示および実行できます。
PTF実装パッケージstack_pを作成します。
-
tab - 入力表
-
col - スタックする数値(入力)表の列の名前
CREATE PACKAGE stack_p AS
FUNCTION describe(tab IN OUT dbms_tf.table_t,
col dbms_tf.columns_t)
RETURN dbms_tf.describe_t;
PROCEDURE fetch_rows;
END stack_p;
PTF実装パッケージ本体stack_pを作成します。
次のPTFでは、2つの新しい列(COLUMN_NAMEとCOLUMN_VALUE)が作成されます。COLUMN_NAMEには、アンピボットされる列の名前が格納され、COLUMN_VALUEには、その列の数値が含まれます。また、アンピボットされた列がPTFの出力から削除されます。
CREATE PACKAGE BODY stack_p AS
FUNCTION describe(tab IN OUT dbms_tf.table_t,
col dbms_tf.columns_t)
RETURN dbms_tf.describe_t AS
BEGIN
FOR i IN 1 .. tab.column.count LOOP
FOR j IN 1 .. col.count LOOP
IF (tab.column(i).description.name = col(j) AND
tab.column(i).description.TYPE = dbms_tf.type_number) THEN
tab.column(i).pass_through := false;
tab.column(i).for_read := true;
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 => 'COLUMN_NAME',
TYPE => dbms_tf.type_varchar2),
2 => dbms_tf.column_metadata_t(name => 'COLUMN_VALUE',
TYPE => dbms_tf.type_number)),
row_replication => true);
END;
PROCEDURE fetch_rows AS
env dbms_tf.env_t := dbms_tf.get_env();
rowset dbms_tf.row_set_t;
colcnt PLS_INTEGER;
rowcnt PLS_INTEGER;
repfac dbms_tf.tab_naturaln_t;
namcol dbms_tf.tab_varchar2_t;
valcol dbms_tf.tab_number_t;
BEGIN
dbms_tf.get_row_set(rowset, rowcnt, colcnt);
FOR i IN 1 .. rowcnt LOOP
repfac(i) := 0;
END LOOP;
FOR r IN 1 .. rowcnt LOOP
FOR c IN 1 .. colcnt LOOP
IF rowset(c).tab_number(r) IS NOT NULL THEN
repfac(r) := repfac(r) + 1;
namcol(nvl(namcol.last+1,1)) :=
INITCAP(regexp_replace(env.get_columns(c).name, '^"|"$'));
valcol(NVL(valcol.last+1,1)) := rowset(c).tab_number(r);
END IF;
END LOOP;
END LOOP;
dbms_tf.row_replication(replication_factor => repfac);
dbms_tf.put_col(1, namcol);
dbms_tf.put_col(2, valcol);
END;
END stack_p;
stackという名前のスタンドアロンPTFを作成します。TABLEタイプの正式引数を1つ指定し、PTFの戻り型をTABLEとして指定し、行セマンティクスPTFタイプを指定し、使用するPTF実装パッケージがstack_pであることを指定します。
CREATE FUNCTION stack(tab TABLE,
col columns)
RETURN TABLE
PIPELINED ROW POLYMORPHIC USING stack_p;
部門10と30のすべての従業員について、MGR、SALおよびCOMM列の値を部門番号と従業員名で順序付けてレポートします。
SELECT deptno, ename, column_name, column_value
FROM stack(scott.emp, COLUMNS(mgr, sal, comm))
WHERE deptno IN (10, 30)
ORDER BY deptno, ename; DEPTNO ENAME COLUMN_NAME COLUMN_VALUE
---------- ---------- --------------- ------------
10 CLARK Mgr 7839
10 CLARK Sal 2450
10 KING Sal 5000
10 MILLER Sal 1300
10 MILLER Mgr 7782
30 ALLEN Comm 300
30 ALLEN Mgr 7698
30 ALLEN Sal 1600
30 BLAKE Mgr 7839
30 BLAKE Sal 2850
30 JAMES Sal 950
30 JAMES Mgr 7698
30 MARTIN Comm 1400
30 MARTIN Mgr 7698
30 MARTIN Sal 1250
30 TURNER Comm 0
30 TURNER Sal 1500
30 TURNER Mgr 7698
30 WARD Comm 500
30 WARD Mgr 7698
30 WARD Sal 1250
173.9.8 GET_XIDファンクション
カーソル実行に固有のランタイム状態を索引付けするため、PTFで使用できる一意の実行IDを戻します。
構文
FUNCTION GET_XID
RETURN XID_T;
戻り値
カーソル実行に固有のランタイム状態を索引付けするため、PTFで使用できる一意の実行ID。
例173-12 DBMS_TF.GET_XIDの例
次は、実行IDを使用してゼロ値に索引付けされたローカル変数を初期化するGET_XIDの起動を示すコードの抜粋です。
PROCEDURE open IS
BEGIN
xst(DBMS_TF.GET_XID()) := 0;
END;173.9.9 PUT_COLプロシージャ
列値を設定します
構文
PROCEDURE PUT_COL( columnid NUMBER, collection IN <datatype>);
<datatype>は、次のサポートされるタイプのいずれかになります。
サポートされているタイプのリストは、表173-1を参照してください。パラメータ
表173-21 PUT_COLプロシージャのパラメータ
| パラメータ | 説明 |
|---|---|
|
|
列のID |
|
|
列のデータ |
使用上のノート
このプロシージャを使用して、スカラー・タイプのコレクションで読取り列値を設定します。
スカラー・タイプのコレクションが、サポートされているタイプである必要があります。
列番号は、PTFのDESCRIBEメソッドで作成された列取得の順序になっています。
columnidが同じ場合、GET_COLおよびPUT_COLは別の列に対応することがあります。
173.9.9.1 rand_col多相表関数の例
rand_col PTFでは、出力に指定された数のランダム値の列を追加します。
例173-13 rand_col多相表関数の例
Live SQL:
この例は、Oracle Live SQLのrand_col多相表関数で表示および実行できます。
このrand_col PTFの例では、col_count数のランダム値の列が出力に追加されます。オプションで、[low, high]を指定して、ランダム値を数値範囲に限定できます。新しい列にRAND_<n>という名前が付けられます
PTF実装パッケージrand_col_pを作成します。
パラメータは次のとおりです。
-
tab: 入力表
-
col_count (オプション): 生成するランダム値の列の数[デフォルト=1]
-
low (オプション): ランダム数の下限[デフォルト=Null]
-
high (オプション): ランダム数の上限[デフォルト=Null]
CREATE PACKAGE rand_col_p AS
FUNCTION describe(tab IN OUT DBMS_TF.table_t,
col_count NATURALN DEFAULT 1,
low NUMBER DEFAULT NULL,
high NUMBER DEFAULT NULL)
RETURN DBMS_TF.describe_t;
PROCEDURE fetch_rows(col_count NATURALN DEFAULT 1,
low NUMBER DEFAULT NULL,
high NUMBER DEFAULT NULL);
END rand_col_p; PTF実装パッケージ本体rand_col_pを作成します。
パラメータcol_countは形式決定パラメータであるため、定数(バインド、相関または式なし)である必要があります。col_countのタイプを暗黙的なNOT NULL制約を持つNATURALNに定義することで、このパラメータの非定数値を持つカーソルがコンパイル・エラーを取得することが保証されます。
CREATE PACKAGE BODY rand_col_p AS
col_name_prefix CONSTANT dbms_id := 'RAND_';
FUNCTION describe(tab IN OUT DBMS_TF.table_t,
col_count NATURALN DEFAULT 1,
low NUMBER DEFAULT NULL,
high NUMBER DEFAULT NULL)
RETURN DBMS_TF.describe_t
AS
cols DBMS_TF.columns_new_t;
BEGIN
FOR i IN 1 .. col_count LOOP
cols(i):= DBMS_TF.column_metadata_t(name=>col_name_prefix||i, TYPE=>DBMS_TF.type_number);
END LOOP;
RETURN DBMS_TF.describe_t(new_columns => cols);
END;
PROCEDURE fetch_rows(col_count NATURALN DEFAULT 1,
low NUMBER DEFAULT NULL,
high NUMBER DEFAULT NULL)
AS
row_count CONSTANT PLS_INTEGER := DBMS_TF.get_env().row_count;
col DBMS_TF.tab_number_t;
BEGIN
FOR c IN 1 .. col_count LOOP
FOR i IN 1 .. row_count LOOP
col(i) := CASE WHEN (low IS NULL OR high IS NULL)
THEN dbms_random.VALUE
ELSE dbms_random.VALUE(low, high)
END;
END LOOP;
DBMS_TF.put_col(c, col);
END LOOP;
END;
END rand_col_p; スタンドアロンrand_col PTFを作成します。TABLEタイプの正式引数を1つ指定し、PTFの戻り型をTABLEとして指定し、行セマンティクスPTFタイプを指定し、使用するPTF実装パッケージがrand_col_pであることを指定します。
CREATE FUNCTION rand_col(tab TABLE,
col_count NATURALN DEFAULT 1,
low NUMBER DEFAULT NULL,
high NUMBER DEFAULT NULL)
RETURN TABLE
PIPELINED ROW POLYMORPHIC USING rand_col_p;rand_col PTFを起動して、生成済のRAND_1列を1つ持つ表SCOTT.DEPTの列をすべて表示します。
SELECT *
FROM rand_col(scott.dept); DEPTNO DNAME LOC RAND_1
---------- -------------- ------------- ----------
10 ACCOUNTING NEW YORK .738666262
20 RESEARCH DALLAS .093256312
30 SALES CHICAGO .992944835
40 OPERATIONS BOSTON .397948124
rand_col PTFを起動して、生成済のRAND_1とRAND_2の2つの列を持つ表SCOTT.DEPTの列をすべて表示します。
SELECT *
FROM rand_col(scott.dept, col_count => 2); DEPTNO DNAME LOC RAND_1 RAND_2
---------- -------------- ------------- ---------- ----------
10 ACCOUNTING NEW YORK .976521361 .209802028
20 RESEARCH DALLAS .899577891 .10050334
30 SALES CHICAGO .277238362 .110736583
40 OPERATIONS BOSTON .989839995 .164822363
ジョブがSALESMANでないすべての従業員について、従業員名、ジョブを表示し、—10と10の間のランダム値を生成する3つのRAND列を生成します。
SELECT ename, job, rand_1, rand_2, rand_3
FROM rand_col(scott.emp, col_count => 3, low => -10, high => +10)
WHERE job != 'SALESMAN';
ENAME JOB RAND_1 RAND_2 RAND_3 ---------- --------- ---------- ---------- ---------- SMITH CLERK 8.91760464 6.67366638 -9.2789076 JONES MANAGER 6.78612961 -1.8617958 6.5282227 BLAKE MANAGER 7.59545803 5.22269017 -2.7966401 CLARK MANAGER -6.4747304 -7.3650276 3.28388872 SCOTT ANALYST 6.80492435 -3.2271045 -.97099797 KING PRESIDENT -9.3161177 6.27762154 -1.8184785 ADAMS CLERK -1.6618848 3.13119089 8.06363075 JAMES CLERK 2.86918245 -3.5187936 -.72913809 FORD ANALYST 6.67038328 -7.4989893 1.99072598 MILLER CLERK -2.1574578 -8.5082989 -.56046716
173.9.10 PUT_ROW_SETプロシージャ
データベースに新しい列値のコレクションを書き込みます。
このプロシージャを使用して、データベース内の行のコレクション内ですべての新しい列を書き込むことができます。
このプロシージャはオーバーロードされています。行は、デフォルトではレプリケートされません。ROW_REPLICATIONプロシージャを使用して、レプリケーション係数を設定できます。
構文
次の構文は、行がレプリケートされない場合に使用されます。
PROCEDURE PUT_ROW_SET( rowset IN ROW_SET_T);
次の構文は、レプリケーション係数が定数である場合に使用されます。
PROCEDURE PUT_ROW_SET( rowset IN ROW_SET_T, replication_factor IN NATURALN);
次の構文は、レプリケーション係数が複数の値を含む配列として指定されている場合に使用されます。
PROCEDURE PUT_ROW_SET( rowset IN ROW_SET_T, replication_factor IN TAB_NATURALN_T);
パラメータ
表173-22 PUT_ROW_SETプロシージャのパラメータ
| パラメータ | 説明 |
|---|---|
|
|
データおよびメタデータのコレクション |
|
|
行ごとのレプリケーション係数 |
例173-14 DBMS_TF.PUT_ROW_SETの例
次のコードの抜粋は、行のコレクションをフェッチし、処理のないデータベースに新しい列をすべて書込みます。
PROCEDURE fetch_rows
AS
rowset DBMS_TF.ROW_SET_T;
BEGIN
DBMS_TF.GET_ROW_SET(rowset);
DBMS_TF.PUT_ROW_SET(rowset);
END;
173.9.10.1 split多相表関数の例
split PTFの例では、入力表の各行を指定された断片に分割します。
例173-15 split多相表関数の例
次のPTFの例では、入力表の各行を分割列の値で割ってcnt個に分割します。
Live SQL:
この例は、Oracle Live SQLのsplit多相表関数で表示および実行できます。
PTF実装パッケージsplit_pを作成します。
-
tab - 入力表
-
col - 分割する数値(入力)表の列の名前
-
cnt - 各入力行が分割される回数
CREATE PACKAGE split_p AS
FUNCTION describe(tab IN OUT DBMS_TF.table_t,
col DBMS_TF.columns_t,
cnt NATURALN)
RETURN DBMS_TF.describe_t;
PROCEDURE fetch_rows(cnt NATURALN);
END split_p;
PTF実装パッケージ本体split_pを作成します。入力表の各行は、分割列の値で割ってcnt個に分割されます。
CREATE PACKAGE BODY split_p AS
FUNCTION describe(tab IN OUT DBMS_TF.Table_t,
col DBMS_TF.Columns_t,
cnt NATURALN)
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
IF (tab.column(i).description.name = col(j) AND
tab.column(i).description.TYPE = DBMS_TF.type_number) THEN
tab.column(i).pass_through := FALSE;
tab.column(i).for_read := TRUE;
new_cols(col_id) := tab.column(i).description;
col_id := col_id + 1;
END IF;
END LOOP;
END LOOP;
RETURN DBMS_TF.describe_t(new_columns=>new_cols, row_replication=>true);
END;
PROCEDURE fetch_rows(cnt NATURALN)
AS
inp_rs DBMS_TF.row_set_t;
out_rs DBMS_TF.row_set_t;
rows PLS_INTEGER;
BEGIN
DBMS_TF.get_row_set(inp_rs, rows);
FOR c IN 1 .. inp_rs.count() LOOP
FOR r IN 1 .. rows LOOP
FOR i IN 1 .. cnt LOOP
out_rs(c).tab_number((r-1)*cnt+i) := inp_rs(c).tab_number(r)/cnt;
END LOOP;
END LOOP;
END LOOP;
DBMS_TF.put_row_set(out_rs, replication_factor => cnt);
END;
END split_p;
splitという名前のスタンドアロンPTFを作成します。TABLEタイプの正式引数を1つ指定し、PTFの戻り型をTABLEとして指定し、行セマンティクスPTFタイプを指定し、使用するPTF実装パッケージがsplit_pであることを指定します。
CREATE FUNCTION split(tab TABLE, col columns, cnt NATURALN)
RETURN TABLE
PIPELINED ROW POLYMORPHIC USING split_p;部門30のすべての従業員について、ENAME、SALおよびCOMM列を表示します。split PTFをCOLUMNS疑似演算子で起動し、問合せによって返されるレプリケートされた行ごとにSALおよびCOMMの値を2で除算します。各行は2回レプリケートされます。
SELECT ename, sal, comm
FROM split(scott.emp, COLUMNS(sal, comm), cnt => 2)
WHERE deptno=30;ENAME SAL COMM ---------- ---------- ------------------- ALLEN 800 150 ALLEN 800 150 WARD 625 250 WARD 625 250 MARTIN 625 700 MARTIN 625 700 BLAKE 1425 BLAKE 1425 TURNER 750 0 TURNER 750 0 JAMES 475 JAMES 475
173.9.11 ROW_REPLICATIONプロシージャ
行のレプリケーション係数を固定値または行ごとの値として設定します。
このプロシージャはオーバーロードされています。Row Semantics多相表関数は、指定された入力行ごとに1つの出力行(1対1)または指定された入力行に複数の出力行(1対多)を作成するか、出力行を作成しません(1対なし)。
構文
行のレプリケーション係数を固定値として設定します。
PROCEDURE ROW_REPLICATION( replication_factor IN NATURALN);
行のレプリケーション係数を行ごとの値として設定します。
PROCEDURE ROW_REPLICATION( replication_factor IN TAB_NATURALN_T);
パラメータ
表173-23 ROW_REPLICATIONプロシージャのパラメータ
| パラメータ | 説明 |
|---|---|
|
|
行ごとのレプリケーション係数 |
例173-16 replicate多相表関数の例
次の例では、パラメータとして指定されたreplication_factorによって各入力行をレプリケートするPTFを作成します。
Live SQL:
この例は、Oracle Live SQLのreplicate多相表関数で表示および実行できます。
PTF実装パッケージreplicate_pを作成します。
CREATE PACKAGE replicate_p
AS
FUNCTION Describe(tab IN OUT DBMS_TF.TABLE_T,
replication_factor NATURAL)
RETURN DBMS_TF.describe_t;
PROCEDURE Fetch_Rows(replication_factor NATURALN);
END replicate_p;
PTF実装パッケージ本体replicate_pを作成します。PTFは、パラメータとして指定されたreplication_factorによって各入力行をレプリケートします。
CREATE PACKAGE body replicate_p
AS
FUNCTION Describe(tab IN OUT DBMS_TF.Table_t
, replication_factor NATURAL)
RETURN DBMS_TF.describe_t AS
BEGIN
RETURN DBMS_TF.describe_t(row_replication => True);
END;
PROCEDURE Fetch_Rows(replication_factor NATURALN)
AS
BEGIN
DBMS_TF.ROW_REPLICATION(replication_factor);
END;
END replicate_p;
replicateという名前のスタンドアロンPTFを作成します。TABLEタイプの正式引数を1つ指定し、PTFの戻り型をTABLEとして指定し、行セマンティクスPTFタイプを指定し、使用するPTF実装パッケージがreplicate_pであることを指定します。
CREATE FUNCTION replicate(tab TABLE,
replication_factor NATURAL)
RETURN TABLE PIPELINED ROW POLYMORPHIC USING replicate_p;
次の例では、replication_factorを2に設定します。これにより、行の数が2倍になります。
SELECT *
FROM replicate(dept, replication_factor => 2);
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
20 RESEARCH DALLAS
30 SALES CHICAGO
30 SALES CHICAGO
40 OPERATIONS BOSTON
40 OPERATIONS BOSTON
次の例では、replication_factorをゼロに設定します。
SELECT *
FROM replicate(dept, replication_factor => 0);
no rows selected
各部門の従業員数を数えます。SCOTT.EMP表からレポートするreplicate PTFを起動し、replication_factorを1000000に設定します。
SELECT deptno, COUNT(*)
FROM replicate(scott.emp, 1e6)
GROUP BY deptno;
DEPTNO COUNT(*)
---------- ----------
30 6000000
10 3000000
20 5000000
これで、replication_factorが1000000000に設定されます。
SELECT COUNT(*)
FROM replicate(dual, 1e9);
COUNT(*) ---------- 1000000000
173.9.12 ROW_TO_CHARファンクション
ROW_TO_CHARファンクションは、行のデータ値を文字列表現に変換します。
構文
FUNCTION ROW_TO_CHAR( rowset ROW_SET_T, rid PLS_INTEGER, format PLS_INTEGER DEFAULT FORMAT_JSON) RETURN VARCHAR2;
パラメータ
表173-24 DBMS_TF.ROW_TO_CHARファンクションのパラメータ
| パラメータ | 説明 |
|---|---|
|
|
値が変換される行セット |
|
|
行番号 |
|
|
文字列形式(デフォルトはFORMAT_JSON) |
使用上のノート
JSON形式のみがサポートされます。
戻り値
JSON形式の文字列表現。
例173-17 DBMS_TF.ROW_TO_CHARの例
PROCEDURE Fetch_Rows as rowset DBMS_TF.ROW_SET_T; str VARCHAR2(32000); BEGIN DBMS_TF.GET_ROW_SET(rowset); str := DBMS_TF.ROW_TO_CHAR(rowset, 1) END;
173.9.13 SUPPORTED_TYPEファンクション
このファンクションは、指定されたタイプが多相表関数でサポートされているかどうかをテストします。
構文
FUNCTION SUPPORTED_TYPE( type_id PLS_INTEGER) RETURN BOOLEAN;
パラメータ
表173-25 DBMS_TF.SUPPORTED_TYPEファンクションのパラメータ
| パラメータ | 説明 |
|---|---|
|
|
タイプ |
戻り値
type_idがPUT_COLおよびGET_COLでサポートされているスカラーである場合は、TRUEを戻します。
参照:
DBMS_TF.SUPPORTED_TYPEの使用例は、echo多相表関数の例を参照してください。173.9.14 TRACEプロシージャ
開発および問題の診断を支援するためのデータ構造を出力します。
このプロシージャはオーバーロードされています。
構文
PROCEDURE TRACE( msg VARCHAR2, with_id BOOLEAN DEFAULT FALSE, separator VARCHAR2 DEFAULT NULL, prefix VARCHAR2 DEFAULT NULL); PROCEDURE TRACE( rowset IN ROW_SET_T); PROCEDURE TRACE( env IN ENV_T); PROCEDURE TRACE( columns_new IN COLUMNS_NEW_T); PROCEDURE trace( cols IN COLUMNS_T); PROCEDURE trace( columns_with_type IN COLUMNS_WITH_TYPE_T); PROCEDURE trace( tab IN TABLE_T); PROCEDURE trace( col IN COLUMN_METADATA_T);
パラメータ
表173-26 TRACEプロシージャのパラメータ
| パラメータ | 説明 |
|---|---|
|
|
カスタム・ユーザー・トレース・メッセージ |
|
|
トレース内に一意の実行IDを含めますか。 |
|
|
値を区切るために使用する文字列を指定します |
|
|
実際の値の接頭辞となる文字列を指定します |
|
|
行セットのデータ |
|
|
多相表関数の実行状態に関するメタデータ |
|
|
新しい列のコレクション |
|
|
列名を含むコレクション |
|
|
列メタデータを含むコレクション |
|
|
表記述子 |
|
|
既存の表の列または作成された新しい列に関するメタデータ |
例173-18 DBMS_TF.TRACEの例
次の例では、トレースがfetch_rowsプロシージャに追加されます。
PROCEDURE fetch_rows
AS
rowset DBMS_TF.ROW_SET_T;
BEGIN
DBMS_TF.TRACE('IDENTITY_PACKAGE.Fetch_Rows()', with_id => TRUE);
DBMS_TF.TRACE(rowset);
DBMS_TF.GET_ROW_SET(rowset);
DBMS_TF.TRACE(rowset);
DBMS_TF.PUT_ROW_SET(rowset);
DBMS_TF.TRACE(DBMS_TF.GET_ENV);
END;
173.9.16 XSTORE_EXISTSファンクション
特定のキーを持つ項目がXSTORE内に存在する場合にTRUEを戻します。
構文
FUNCTION XSTORE_EXISTS( key IN VARCHAR2, key_type IN PLS_INTEGER DEFAULT NULL) RETURN BOOLEAN;
パラメータ
表173-27 DBMS_TF.XSTORE_EXISTSファンクションのパラメータ
| パラメータ | 説明 |
|---|---|
|
|
一意の文字キー |
|
|
キーのタイプ(オプション)。デフォルト: NULL |
戻り値
キーに関連付けられた値がある場合は、TRUEを戻します。key_typeがNULL (デフォルト)の場合、キーにサポートされているタイプの関連付けられている値がある場合は、TRUEを戻します。
key_typeパラメータ値が渡されるときに、キーおよびキーの指定されたタイプに関連付けられている値がある場合は、TRUEを戻します。それ以外の場合はFALSEを返します。
参照:
サポートされるキー・タイプの詳細は、表173-6を参照してください。
173.9.17 XSTORE_GETプロシージャ
XSTORE_GETプロシージャを使用して、PTF実行状態管理の格納されている指定されたキーに関連付けられた値を取得できます。
XStoreは、PTF実行状態管理インタフェースです。XStoreインタフェースは、PTF実行時にキー/値ペアの設定および格納に使用されます。
このプロシージャはオーバーロードされています。XStoreでは、スカラー・タイプ(VARCHAR2、NUMBER、DATEおよびBOOLEAN)のキー/値ペアを指定できます。
サポートされるキー・タイプの詳細は、表173-6を参照してください。
構文
PROCEDURE XSTORE_GET( key IN VARCHAR2, value IN OUT VARCHAR2); PROCEDURE XSTORE_GET( key IN VARCHAR2, value IN OUT NUMBER); PROCEDURE XSTORE_GET( key IN VARCHAR2, value IN OUT DATE); PROCEDURE XSTORE_GET( key IN VARCHAR2, value IN OUT BOOLEAN);
パラメータ
表173-28 DBMS_TF.XSTORE_GETプロシージャのパラメータ
| パラメータ | 説明 |
|---|---|
|
|
一意の文字キー |
|
|
サポートされているタイプのキーに対応する値 |
使用上のノート
キーが見つからない場合、値は変更されません。
173.9.17.1 row_num多相表関数の例
row_num PTFの例では、表に順序列が追加されます。
例173-19 row_num多相表関数の例
Live SQL:
この例は、Oracle Live SQLのrow_num多相表関数で表示および実行できます。
PTF実装パッケージrow_num_pを作成します。
-
tab - 入力表
-
ini - 初期値(デフォルト=1)
-
inc - 増分する金額(デフォルト=1)
CREATE PACKAGE row_num_p IS
FUNCTION describe(tab IN OUT dbms_tf.table_t,
ini NUMBER DEFAULT 1,
inc NUMBER DEFAULT 1)
RETURN dbms_tf.describe_t;
PROCEDURE fetch_rows(ini NUMBER DEFAULT 1, inc NUMBER DEFAULT 1);
END;このPTFは、入力表を受け入れ、順序列ROW_IDを表に追加します。順序値は指定された値(ini)から開始し、各時間は指定された値(inc)で増分されます。
CREATE PACKAGE BODY row_num_p IS
FUNCTION describe(tab IN OUT dbms_tf.table_t,
ini NUMBER DEFAULT 1,
inc NUMBER DEFAULT 1)
RETURN dbms_tf.describe_t AS
BEGIN
RETURN dbms_tf.describe_t(new_columns =>
dbms_tf.columns_new_t(1 =>
dbms_tf.column_metadata_t(name => 'ROW_ID',
TYPE => dbms_tf.type_number)));
END;
PROCEDURE fetch_rows(ini NUMBER DEFAULT 1, inc NUMBER DEFAULT 1) IS
row_cnt CONSTANT PLS_INTEGER := dbms_tf.get_env().row_count;
rid NUMBER := ini;
col dbms_tf.tab_number_t;
BEGIN
dbms_tf.xstore_get('rid', rid);
FOR i IN 1 .. row_cnt LOOP col(i) := rid + inc*(i-1); END LOOP;
dbms_tf.put_col(1, col);
dbms_tf.xstore_set('rid', rid + inc*row_cnt);
END;
END;row_numという名前のスタンドアロン多相表関数を作成します。TABLEタイプの正式引数を1つ指定し、PTFの戻り型をTABLEとして指定し、表セマンティクスPTFタイプを指定し、使用するPTF実装パッケージがrow_num_pであることを指定します。
CREATE FUNCTION row_num(tab TABLE,
ini NUMBER DEFAULT 1,
inc NUMBER DEFAULT 1)
RETURN TABLE
PIPELINED TABLE POLYMORPHIC USING row_num_p;SCOTT.DEPT表からレポートするrow_num PTFの起動により、新しい列ROW_IDが生成されます。値は1で始まり、行セットで1ずつ増分されます。
SELECT * FROM row_num(scott.dept);DEPTNO DNAME LOC ROW_ID
------ -------------- ------------- ----------
10 ACCOUNTING NEW YORK 1
20 RESEARCH DALLAS 2
30 SALES CHICAGO 3
40 OPERATIONS BOSTON 4
SCOTT.DEPT表からレポートするrow_num PTFの起動により、新しい列ROW_IDが生成されます。値は100で始まり、行セットで1ずつ増分されます。
SELECT * FROM row_num(scott.dept, 100);DEPTNO DNAME LOC ROW_ID
------ -------------- ------------- ----------
10 ACCOUNTING NEW YORK 100
20 RESEARCH DALLAS 101
30 SALES CHICAGO 102
40 OPERATIONS BOSTON 103
SCOTT.DEPT表からレポートするrow_num PTFの起動により、新しい列ROW_IDが生成されます。値は0で始まり、行セットで1ずつ減分されます。
SELECT * FROM row_num(scott.dept, ini => 0, inc => -1);DEPTNO DNAME LOC ROW_ID
------ -------------- ------------- ----------
10 ACCOUNTING NEW YORK 0
20 RESEARCH DALLAS -1
30 SALES CHICAGO -2
40 OPERATIONS BOSTON -3
SCOTT.EMP表からレポートするrow_num PTFの起動により、新しい列ROW_IDが生成されます。値は0で始まり、部門番号でパーティション化され従業員名で順序付けられた行セットで0.25ずつ増分されます。
SELECT deptno, ename, job, sal, row_id
FROM row_num(scott.emp PARTITION BY deptno ORDER BY ename, ini => 0, inc => 0.25)
WHERE deptno IN (10, 30);
DEPTNO ENAME JOB SAL ROW_ID
------ ---------- --------- ---------- ----------
10 CLARK MANAGER 2450 0
10 KING PRESIDENT 5000 .25
10 MILLER CLERK 1300 .5
30 ALLEN SALESMAN 1600 0
30 BLAKE MANAGER 2850 .25
30 JAMES CLERK 950 .5
30 MARTIN SALESMAN 1250 .75
30 TURNER SALESMAN 1500 1
30 WARD SALESMAN 1250 1.25
173.9.18 XSTORE_REMOVEプロシージャ
指定されたキーおよびkey_typeに関連付けられている項目を削除します。
構文
PROCEDURE XSTORE_REMOVE( key IN VARCHAR2, key_type IN PLS_INTEGER DEFAULT NULL);
パラメータ
表173-29 DBMS_TF.XSTORE_REMOVEファンクションのパラメータ
| パラメータ | 説明 |
|---|---|
|
|
一意の文字キー |
|
|
削除するキーのタイプ(オプション) |
使用上のノート
key_typeパラメータ値は、渡されるときに、キーの関連項目および指定したタイプのキーを削除します。
173.9.19 XSTORE_SETプロシージャ
PTF実行状態管理の指定されたキーの値を設定します。
このプロシージャを使用して、XStoreで項目のキー/値ペアを格納できます。このプロシージャはオーバーロードされています。XStoreでは、スカラー・タイプ(VARCHAR2、NUMBER、DATEおよびBOOLEAN)のキー/値ペアを指定できます。
構文
PROCEDURE XSTORE_SET ( key IN VARCHAR2, value IN VARCHAR2); PROCEDURE XSTORE_SET ( key IN VARCHAR2, value IN NUMBER); PROCEDURE XSTORE_SET ( key IN VARCHAR2, value IN DATE); PROCEDURE XSTORE_SET ( key IN VARCHAR2, value IN BOOLEAN);
パラメータ
表173-30 DBMS_TF.XSTORE_SETプロシージャのパラメータ
| パラメータ | 説明 |
|---|---|
|
|
一意の文字キー |
|
|
サポートされているタイプのキーに対応する値 |
使用上のノート
指定されたキーの項目がすでに存在する場合、値は置換されます。