OracleDB の Function を使って複数行の SELECT 結果を取得する

概要

Function の戻り値に SELECT文での抽出結果を設定したときのメモ

環境

• Oracle Database 12c

詳細

まずは、抽出結果の型を定義します。

-- 抽出したいデータの項目をTYPEとして定義する
CREATE OR REPLACE TYPE TYPE_TEST AS OBJECT (
  ID    NUMBER(8,0)
  ,NAME VARCHAR2(256)
);
/

-- 定義したデータ項目のTYPEのTABLEを定義する
CREATE OR REPLACE TYPE TYPE_TEST_TABLE AS TABLE OF TYPE_TEST;
/

上記で定義した型を戻り値として、Function を作成していきます。
実際のクエリは以下のものになります。

CREATE OR REPLACE FUNCTION FETCH_TEST(
  vNAME IN VARCHAR2
) RETURN TYPE_TEST_TABLE PIPELINED
IS
  cursor cTest is 
    SELECT
      ROWNUM ID
      ,vNAME || ROWNUM NAME
    FROM dual 
    CONNECT BY ROWNUM <= 3
;
BEGIN
  for row in cTest loop
    PIPE ROW(
      TYPE_TEST(
        row.ID
        ,row.NAME
      ) 
    );
  end loop;
  return;
END;
/

呼び出すときは、以下のような感じで呼び出せます。

SELECT * FROM table(FETCH_TEST('Q'));

ポイントとしては、

• 戻り値の型を [戻り値にしたいTABLE型] PIPELINED で宣言する
• cursor の変数を宣言し、SELECT結果を格納する
• PIPE ROW ～の部分でSELECT結果を定義した型で詰め替える

といったところだと思います。

まとめ

集計データを作成する VIEW を定義しようと思うけど、集計期間は変数で指定できるようにしたい
と思ったときに使った内容でした。
（FUNCTION だとパラメータを渡せるため、条件を変えられるので）