【Oracle】汎用的に使えるファンクション関数を作る

TYPEを使用したパイプラインファンクション
Oracleの上級テクニックになるのかな？
さらにこれよりも複雑なパラレルパイプラインファンクションというのがあるけど、
汎用的に使えるものを作るのであれば、これくらいでいいかな思っています。

まずはTYPE（オブジェクト型）を決める。
汎用的に使うので、なるべく分かりやすい列名にして、30フィールドを作ってみる。

CREATE TYPE STD_VALUE30 AS OBJECT (
    STR_VALUE001   VARCHAR2(500),
    STR_VALUE002   VARCHAR2(500),
    STR_VALUE003   VARCHAR2(500),
    STR_VALUE004   VARCHAR2(500),
    STR_VALUE005   VARCHAR2(500),
    STR_VALUE006   VARCHAR2(500),
    STR_VALUE007   VARCHAR2(500),
    STR_VALUE008   VARCHAR2(500),
    STR_VALUE009   VARCHAR2(500),
    STR_VALUE010   VARCHAR2(500),
    STR_VALUE011   VARCHAR2(500),
    STR_VALUE012   VARCHAR2(500),
    STR_VALUE013   VARCHAR2(500),
    STR_VALUE014   VARCHAR2(500),
    STR_VALUE015   VARCHAR2(500),
    STR_VALUE016   VARCHAR2(500),
    STR_VALUE017   VARCHAR2(500),
    STR_VALUE018   VARCHAR2(500),
    STR_VALUE019   VARCHAR2(500),
    STR_VALUE020   VARCHAR2(500),
    STR_VALUE021   VARCHAR2(500),
    STR_VALUE022   VARCHAR2(500),
    STR_VALUE023   VARCHAR2(500),
    STR_VALUE024   VARCHAR2(500),
    STR_VALUE025   VARCHAR2(500),
    STR_VALUE026   VARCHAR2(500),
    STR_VALUE027   VARCHAR2(500),
    STR_VALUE028   VARCHAR2(500),
    STR_VALUE029   VARCHAR2(500),
    STR_VALUE030   VARCHAR2(500)
);

続いて、オブジェクト型の配列を作る。
これは1行だけで良いので簡単。

CREATE TYPE STD_VALUE30_TABLE IS TABLE OF STD_VALUE30;

とりあえず、サンプルで参照するテーブルを作ってデータを入れる。

CREATE TABLE TABLE1 
(	"A1" VARCHAR2(500 BYTE), 
	"A2" VARCHAR2(500 BYTE), 
	"A3" VARCHAR2(500 BYTE), 
	"A4" VARCHAR2(500 BYTE), 
	"A5" VARCHAR2(500 BYTE), 
	"A6" VARCHAR2(500 BYTE), 
	"A7" VARCHAR2(500 BYTE), 
	"A8" VARCHAR2(500 BYTE), 
	"A9" VARCHAR2(500 BYTE), 
	"A10" VARCHAR2(500 BYTE), 
	"A11" VARCHAR2(500 BYTE), 
	"A12" VARCHAR2(500 BYTE)
)

材料はそろったのでパイプラインファンクションを作ってみる。
TYPEで上記の定義した配列はBULK COLLECT INTOで、一気にデータを入れ込むことができる。
そうすると、余計なループ処理が省ける。

CREATE OR REPLACE FUNCTION FUNC_STD_VALUE30_TABLE
RETURN STD_VALUE30_TABLE
PIPELINED
AS
    
    /*レコード型定義*/
    STD_REC   STD_VALUE30;
    
    /*物理テーブル型定義*/
    TYPE TBL_REC_TYPE IS
        TABLE OF TABLE1%ROWTYPE;
    TBL_REC   TBL_REC_TYPE;
BEGIN

    /*物理TBL型初期化*/
    TBL_REC := TBL_REC_TYPE ();

    /*テーブルのデータをそのまま配列として入れてしまう*/
    SELECT
        *
    BULK COLLECT
    INTO TBL_REC
    FROM
        TABLE1;

    /*取得データをループで処理する*/
    FOR INDX IN 1..TBL_REC.COUNT LOOP
        /*配列初期化 30項目に半角スペースを初期値として入れておく*/
        STD_REC := STD_VALUE30(' ',' ',' ',' ',' '
                          ,' ',' ',' ',' ',' '
                          ,' ',' ',' ',' ',' '
                          ,' ',' ',' ',' ',' '
                          ,' ',' ',' ',' ',' '
                          ,' ',' ',' ',' ',' ');
        
        /*値の代入*/
        STD_REC.STR_VALUE001 := TBL_REC(INDX).A1;
        STD_REC.STR_VALUE002 := TBL_REC(INDX).A2;
        STD_REC.STR_VALUE003 := TBL_REC(INDX).A3;
        STD_REC.STR_VALUE004 := TBL_REC(INDX).A4;
        STD_REC.STR_VALUE005 := TBL_REC(INDX).A5;
        STD_REC.STR_VALUE006 := TBL_REC(INDX).A6;
        STD_REC.STR_VALUE007 := TBL_REC(INDX).A7;
        STD_REC.STR_VALUE008 := TBL_REC(INDX).A8;
        STD_REC.STR_VALUE009 := TBL_REC(INDX).A9;
        STD_REC.STR_VALUE010 := TBL_REC(INDX).A10;
        STD_REC.STR_VALUE011 := TBL_REC(INDX).A11;
        STD_REC.STR_VALUE012 := TBL_REC(INDX).A12;

        
        /*レコード代入*/
        PIPE ROW ( STD_REC );
    END LOOP;

    /*終了*/
    RETURN;
END FUNC_STD_VALUE30_TABLE;

できたところで動かしてみる。

SELECT * FROM TABLE(FUNC_STD_VALUE30_TABLE())

出力結果