Oracleでパイプライン表関数を利用して表データを返却する

Oracleで、プロシージャを使ってデータを返す事が可能。

SQLが複雑になりすぎた時や、そもそもSQLで組めないときなど
適切な単位でプロシージャ分割して組むことにより、処理性能、
可読性、保守性の向上に役立ちます。

※詳しくは調べていませんが、Oracle9iで搭載された機能のようです。

概略

返却する表を定義
プロシージャの戻り値に、定義した表を指定
プロシージャ内で表のデータを生成して返却

使うキーワード

RETURN 表名 PIPELINED
PIPE ROW(レコード定義)

サンプル

※年月の開始と終了を指定すると、年月のレコードを返す

CREATE OR REPLACE PACKAGE PKG_DATE_TABLE_UTIL
IS
  -- 返却するテーブルの型を定義する
  TYPE T_TABLE_YM_ROW IS RECORD(
    YEARMONTH      NUMBER(6)
  );
  -- テーブルを定義する
  TYPE T_REC_YM_ROW  IS TABLE OF T_TABLE_YM_ROW;
  -- 関数を定義する。返却値はTABLE定義名 PIPELINED
  FUNCTION GET_YM_TABLE(IN_START_YM NUMBER , IN_END_YM  NUMBER)
  RETURN T_REC_YM_ROW PIPELINED;
END;
 /

CREATE OR REPLACE PACKAGE BODY PKG_DATE_TABLE_UTIL
IS
  FUNCTION GET_YM_TABLE(IN_START_YM NUMBER , IN_END_YM  NUMBER)
  RETURN T_REC_YM_ROW PIPELINED
  IS
    ERROR_01   EXCEPTION;
    /* 変数定義 */
    L_WORK_DATE   DATE;
    L_END_DATE    DATE;

    /* レコード格納定義 */
    W_RET_REC T_TABLE_YM_ROW;
  BEGIN
    /* パラメータチェック  */
    IF IN_START_YM IS NULL THEN
      RAISE ERROR_01;
    END IF;
    IF IN_END_YM IS NULL THEN
      L_END_DATE := TO_DATE(IN_START_YM || '01' );
    ELSE
      L_END_DATE := TO_DATE(IN_END_YM || '01');
    END IF;
    L_WORK_DATE :=TO_DATE(IN_START_YM || '01' );
    -- 初めの1月目を出力
    W_RET_REC.YEARMONTH := TO_CHAR(L_WORK_DATE,'YYYYMM');
    PIPE ROW(W_RET_REC);
    L_WORK_DATE :=  ADD_MONTHS(L_WORK_DATE,1);
    -- 以降は指定された分だけ、繰り返し
    WHILE ( L_WORK_DATE <= L_END_DATE ) LOOP
      -- 定義に対してデータを設定
      W_RET_REC.YEARMONTH := TO_CHAR(L_WORK_DATE,'YYYYMM');
      -- 1行を返却する
      PIPE ROW(W_RET_REC);
      L_WORK_DATE :=  ADD_MONTHS(L_WORK_DATE,1);
    END LOOP;

    RETURN;
  END;
END;

/