Oracle® 非公式 SQL, PL/SQL & DBA's リファレンス

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

表関数とは、通常の関数（スカラー関数）と異なりレコードセット（表の内容）を戻す。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)を使用する。

-- 変換ターゲットの表 ( 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 があるが直接関係ない。

---

表関数 関連事項

• 外部表の定義方法
• ネストした表、および、VARRY （コレクション型）
• 表関数によるコレクションのネスト解除