表関数、パイプライン・テーブルファンクション Oracle 9i

表関数とは、通常の関数(スカラー関数)と異なりレコードセット(表の内容)を戻す。table(table_function_name(..))
特にパイプライン表関数はレコードセットを戻すだけではなく処理の過程をパイプライン化(流れ作業)されて進行される。 そのため、データウェアハウスなどのデータ投入(ETL)での中間のステージング領域が不要になる。 その結果として処理の高速化が可能となる。(処理をパラレル化することも可能)

表関数で指定レコードを持つ DUAL 表(ビュー)を作成する(サンプル1)

10レコードの DUAL 表 もどき? (DUALx10)を作成する。 このような単純な表関数であっても大量のテストデータを作成したい場合に結合対象表*1として利用する価値がある。
レコードの戻り値を順序数値や FROM,TO に変更して拡張すると、さらに活用シーンは増える。

-- DUAL 表の骨組みをタイプで再現
DROP TYPE RIVUS.DUMMY_RSET;
--
CREATE OR REPLACE TYPE RIVUS.DUMMY_T AS OBJECT
(
  DUMMY VARCHAR2(1)
);
/
-- DUAL もどき表のレコードセットを定義
CREATE OR REPLACE TYPE RIVUS.DUMMY_RSET AS TABLE OF DUMMY_T;
/
-- DUAL もどき表を作成する関数の定義
CREATE OR REPLACE FUNCTION RIVUS.X_GENERATOR(P_RECMAX NUMBER)
RETURN DUMMY_RSET PIPELINED
IS
BEGIN
	FOR I IN 1..P_RECMAX LOOP
		-- DUMMY_T に 'X' をセットしてパイプ
		PIPE ROW(DUMMY_T('X'));
	END LOOP;
	RETURN;
END;
/
-- DUAL もどき表関数を ビューで安定化?  DUALx10 の完成
DROP VIEW RIVUS.DUALx10;
CREATE VIEW RIVUS.DUALx10
 AS SELECT * FROM TABLE(X_GENERATOR(10));
-- 完成
SELECT * FROM DUALx10;

表関数を使って半角カタカナをひらがなに変換する (サンプル2)

ETL (Extract Transform Load) 機能への応用例として、外部表から名前を半角カナから平仮名に変換する 表関数の例を作成してみる。 半角カタカナから全角ひらがなに変換するユーザー定義関数(TO_FULL_KANA)を使用する。

表関数を使用した ETL
-- 変換ターゲットの表 ( ID, NAME で構成 ) ※手抜きで外部表ではありません
CREATE TABLE RIVUS.ID_NAME
(
	ID	VARCHAR2(10),
	NAME	VARCHAR2(40)
);
-- 
DROP TYPE RIVUS.ID_NAME_RSET;
-- 表の骨組みをタイプで定義
CREATE OR REPLACE TYPE RIVUS.ID_NAME_T AS OBJECT
(
	ID	VARCHAR2(10)
	,NAME	VARCHAR2(40)
);
/
-- レコードセットを定義
CREATE OR REPLACE TYPE RIVUS.ID_NAME_RSET AS TABLE OF ID_NAME_T;
/
-- ID, NAME 表を作成する関数の定義
CREATE OR REPLACE FUNCTION RIVUS.FUNC_TRANS_ID_NAME(
	P_CUR IN SYS_REFCURSOR
)
RETURN ID_NAME_RSET PIPELINED
IS
	vID		VARCHAR2(10);
	vName		VARCHAR2(40);
--	vRec		ID_NAME%ROWTYPE;
BEGIN
	LOOP
		FETCH P_CUR INTO vID, vName; -- or INTO vRec
		EXIT WHEN P_CUR%NOTFOUND;
 
		vNAME:= TO_FULL_KANA(vName);
		PIPE ROW(ID_NAME_T(vID, vName));
	END LOOP;
	RETURN;
END;
/
-- 表関数を ビューに変換
DROP VIEW RIVUS.ID_NAME_TRANS;
CREATE VIEW RIVUS.ID_NAME_TRANS
 AS SELECT * FROM TABLE(FUNC_TRANS_ID_NAME(CURSOR(SELECT * FROM ID_NAME)));
--
INSERT INTO 〜 SELECT /*+ APPEND */  * FROM ID_NAME_TRANS;

シンプルな表関数

パイプラインしていない簡単な表関数の例

SQL> CREATE OR REPLACE TYPE tNumberList IS TABLE OF NUMBER;
     /
-- または VARRY を用いても良い。(結合配列は TYPE 宣言できない)
--     CREATE OR REPLACE TYPE tNumberList IS VARRAY(100) OF NUMBER;
 
SQL> SELECT * FROM TABLE(tNumberList(100,20,30)) ORDER BY COLUMN_VALUE ASC;
COLUMN_VALUE
------------
          20
          30
         100

COLUMN_VALUE
マニュアルによると表関数を SELECT したときの COLUMN_VALUE という擬似列は

キーワード COLUMN_VALUEは、列または属性名を持たない内部のネストした表の
スカラー値に対してOracleデータベースが生成する名前です。

同じ名前の擬似列に XML 関連の COLUMN_VALUE があるが直接関係ない。



表関数 関連事項

日本オラクル
■ 日本オラクル 株式会社
■ オラクルマスター資格 (オラクルマスターとは
■ オラクルサポートセンター

*1 CATALOG表を使用したりするが件数は不定、効率面でも悪いので常用できるものではない