【Oracle】DBMS APIでCSVを出力してみる

DBMS APIを使って、CSVの出力するようなファンクションを作ってみた。

SQL文を引数にして、テキストが吐き出せるので
何かしら便利に使えそうだとは思っている。

とりあえず、適当な型を用意しよう。

CREATE OR REPLACE TYPE STD_VALUE4 AS OBJECT (
    VALUE01   VARCHAR2(4000),
    VALUE02   VARCHAR2(4000),
    VALUE03   VARCHAR2(4000),
    VALUE04   VARCHAR2(4000)    
);

/

CREATE OR REPLACE TYPE STD_VALUE4_TABLE IS  TABLE OF STD_VALUE4;

まずはプロシージャーを作る。
カーソルをIN OUTで定義するのがミソのようだ。

CREATE OR REPLACE  PROCEDURE SP_TEXT_OUT (

    /*カーソル*/
    REF_CUR IN OUT SYS_REFCURSOR,

    /*区切る文字　デフォルトは無し*/
    DELIM_STR   VARCHAR DEFAULT '',

    /*ヘッダーの有無 0:つけない（デフォルト) 1:つける*/
    HEAD_FLG    NUMBER DEFAULT 0,

    STD_ARRY    OUT STD_VALUE4_TABLE,
    OUT_STS     OUT VARCHAR2,
    OUT_MSG     OUT VARCHAR2
) IS
    /*レコード型*/
    REC_VALUE   STD_VALUE4;

    /*定義表*/
    DESC_TBL    DBMS_SQL.DESC_TAB;

    /*カーソルのカラム数*/
    COL_COUNT   NUMBER(3);

    /*カーソルID*/
    CUR_NUM     NUMBER(38);

    /*一時的に格納する文字列*/
    TEMP_STR    VARCHAR(255);
    TEMP_NUM    NUMBER(38,10);
    TEMP_DATE   DATE;

    /*レコード型に入れる文字列*/
    T_STR       VARCHAR(4000);
    
    /*行位置　ヘッダーは0になる*/
    TEXT_CNT NUMBER(38) := 0;
BEGIN




    /*初期化*/
    OUT_STS     := 'OK';
    OUT_MSG     := '';
    T_STR       := '';
    TEMP_STR    := '';
    REC_VALUE   := STD_VALUE4(' ',' ',' ',' ');
    STD_ARRY    := STD_VALUE4_TABLE ();

    CUR_NUM     := DBMS_SQL.TO_CURSOR_NUMBER(REF_CUR);

    /*カーソルで取得した時の定義情報を取得する*/
    DBMS_SQL.DESCRIBE_COLUMNS(CUR_NUM,COL_COUNT,DESC_TBL);

    /*定義情報を展開する*/
    FOR I IN 1..COL_COUNT LOOP

           /*VARCHAR2*/
        IF
            ( DESC_TBL(I).COL_TYPE = 1 )
        THEN
            DBMS_SQL.DEFINE_COLUMN(CUR_NUM,I,TEMP_STR,4000);

            /*NUMBER*/
        ELSIF ( DESC_TBL(I).COL_TYPE = 2 ) THEN
            DBMS_SQL.DEFINE_COLUMN(CUR_NUM,I,TEMP_NUM);

            /*DATE*/
        ELSIF ( DESC_TBL(I).COL_TYPE = 12 ) THEN
            DBMS_SQL.DEFINE_COLUMN(CUR_NUM,I,TEMP_DATE);

            /*その他*/
        ELSE
            DBMS_SQL.DEFINE_COLUMN(CUR_NUM,I,TEMP_STR,4000);
        END IF;


        /*ヘッダー情報を無関係に取得しておく*/
        TEMP_STR   := DESC_TBL(I).COL_NAME;

        /*NULLだったら、項目は連結させない。*/
        IF
            TRIM(TEMP_STR) IS NOT NULL
        THEN
            T_STR   := T_STR || TEMP_STR;
        END IF;

        /*最後の要素以外はデルミタ文字をつける*/
        IF
            I < COL_COUNT
        THEN
            /* NULLであればつけない*/
            IF
                DELIM_STR IS NOT NULL
            THEN
                T_STR   := T_STR || DELIM_STR;
            END IF;
        END IF;
    END LOOP;

    /*1の場合、要素を格納する*/
    IF
        HEAD_FLG = 1
    THEN
    /*要素追加*/
        REC_VALUE.VALUE01              := TO_CHAR(TEXT_CNT);
        REC_VALUE.VALUE02              := T_STR;
        REC_VALUE.VALUE03              := LENGTH(T_STR);
        STD_ARRY.EXTEND;
        STD_ARRY(STD_ARRY.COUNT)   := REC_VALUE;
    END IF;

/*フェッチでループ*/
    WHILE DBMS_SQL.FETCH_ROWS(CUR_NUM) > 0 LOOP
 
    /* 再初期化 */
        T_STR                      := '';
        TEMP_STR                   := '';
        TEMP_NUM                   := 0;
        TEMP_DATE                  := NULL;
        REC_VALUE                  := STD_VALUE4(' ',' ',' ',' ');
        TEXT_CNT                   := TEXT_CNT + 1;
        FOR I IN 1..COL_COUNT LOOP

        /*定義情報を利用してカーソル内のフィールドの値を取得する*/
        /*VARCHAR2*/
            IF
                ( DESC_TBL(I).COL_TYPE = 1 )
            THEN
                DBMS_SQL.COLUMN_VALUE(CUR_NUM,I,TEMP_STR);

            /*NUMBER 文字列に変換する*/
            ELSIF ( DESC_TBL(I).COL_TYPE = 2 ) THEN
                DBMS_SQL.COLUMN_VALUE(CUR_NUM,I,TEMP_NUM);
                TEMP_STR   := TO_CHAR(TEMP_NUM);

            /*DATE  YYYY/MM/DDの形式にする*/
            ELSIF ( DESC_TBL(I).COL_TYPE = 12 ) THEN
                DBMS_SQL.COLUMN_VALUE(CUR_NUM,I,TEMP_DATE);
                TEMP_STR   := TO_CHAR(TEMP_DATE,'YYYY/MM/DD');

            /*その他*/
            ELSE
                DBMS_SQL.COLUMN_VALUE(CUR_NUM,I,TEMP_STR);
            END IF;

        /*TRIMかけてNULLだった場合、項目は連結させない。*/
            IF
                TRIM(TEMP_STR) IS NOT NULL
            THEN
                T_STR   := T_STR || TEMP_STR;
            END IF;

        /*最後の要素以外は区切り文字を付加する*/
            IF
                I < COL_COUNT
            THEN

            /* 区切り文字はNULLだったら付けない。*/
                IF
                    DELIM_STR IS NOT NULL
                THEN
                    T_STR   := T_STR || DELIM_STR;
                END IF;
            END IF;
        END LOOP;

        /*要素追加*/
        REC_VALUE.VALUE01              := TEXT_CNT;
        REC_VALUE.VALUE02              := T_STR;
        REC_VALUE.VALUE03              := LENGTH(T_STR);
        STD_ARRY.EXTEND;
        STD_ARRY(STD_ARRY.COUNT)   := REC_VALUE;
    END LOOP;

/*カーソルクローズ*/
    DBMS_SQL.CLOSE_CURSOR(CUR_NUM);
    OUT_STS     := 'OK';
    OUT_MSG     := ''; 
/*終了*/
    RETURN;

/*エラー*/
EXCEPTION
    WHEN OTHERS THEN

    /*念のためカーソルをオープンしてた場合は閉じる*/
        IF
            REF_CUR%ISOPEN
        THEN
            DBMS_SQL.CLOSE_CURSOR(CUR_NUM);
        END IF;

    /*エラー内容追加*/
        OUT_STS   := 'NG';
        OUT_MSG   := SQLERRM;
        RETURN;
END;

上記のプロシージャーを呼び出してパイプラインのファンクションにする。

CREATE OR REPLACE  FUNCTION FUNC_TEXT_OUT (

    /*SQL文*/
    STRSQL      VARCHAR2,


    /*区切る文字　デフォルトは無し*/
    DELIM_STR   VARCHAR2 DEFAULT '',


    /*ヘッダーの有無 0:つけない（デフォルト) 1:つける*/
    HEAD_FLG    NUMBER DEFAULT 0

) RETURN STD_VALUE4_TABLE
    PIPELINED
IS
    /*レコード型*/
    REC_VALUE   STD_VALUE4;

    /*戻り値のコレクション*/
    STD_ARRY    STD_VALUE4_TABLE;

    /*カーソル*/
    REF_CUR     SYS_REFCURSOR;

    /*レコード型に入れる文字列*/
    W_STR       VARCHAR2(4000);


    /*プロシージャー結果*/
    OUT_STS     VARCHAR2(4000);
    OUT_MSG     VARCHAR2(4000);
BEGIN

    /*初期化*/
    REC_VALUE   := STD_VALUE4(' ',' ',' ',' ');


    /*カーソルオープン*/
    OPEN REF_CUR FOR STRSQL;
    SP_TEXT_OUT(REF_CUR,DELIM_STR,HEAD_FLG,STD_ARRY,OUT_STS,OUT_MSG);
    IF
        OUT_STS = 'OK'
    THEN
        FOR I IN 1..STD_ARRY.COUNT LOOP
            PIPE ROW ( STD_ARRY(I) );
        END LOOP;
    ELSE

        /*エラー内容追加*/
        REC_VALUE.VALUE04   := OUT_MSG;
        PIPE ROW ( REC_VALUE );
        RETURN;
    END IF;



/*終了*/
    RETURN;

/*エラー*/
EXCEPTION
    WHEN OTHERS THEN

    /*念のためカーソルをオープンしてた場合は閉じる*/
        IF
            REF_CUR%ISOPEN
        THEN
            CLOSE REF_CUR;
        END IF;

    /*エラー内容追加*/
        REC_VALUE.VALUE04   := SQLERRM;
        PIPE ROW ( REC_VALUE );
        RETURN;
END;

CSV出力よりも、もっと他の何かに役に立ちそうな気がするな。