[ Oracle PL/SQL ] SELECTのクエリ結果をリターン値で返す関数

Tweet

Pocket

select ～ into 文によって取得した値をリターン値とすることはよくありますが、リターン値は1レコード分なので、複数行を返すことはできません。ここでは、リターン値の結果に表形式の複数レコードを返すサンプルを掲載します。

タイプ作成(create type)

前準備として、結果として返す型を定義する必要があります。TYPE_MACHINE オブジェクトがクラスで、TYPE_MACHINE_TABLE オブジェクトテーブルが TYPE_MACHINE オブジェクトのリストのようなイメージとなっています。

/*
 * 結果として返すオブジェクトを作成する
 */
CREATE OR REPLACE TYPE TYPE_MACHINE AS OBJECT (
  MACHINE_CD     VARCHAR2(256),
  MACHINE_NAME   VARCHAR2(256)
)

/*
 * 上記で作成したオブジェクトのテーブルを作成する
 */
CREATE OR REPLACE TYPE TYPE_MACHINE_TABLE AS TABLE OF TYPE_MACHINE 

前準備はこれで終了です。

ファンクションの作成

上記で定義した TYPE_MACHINE_TABLE をリターン値とた関数を作成します。詳細はサンプル内のコメントを参照ください。

CREATE OR REPLACE FUNCTION 
  FNC_GET_MACHINE_BY_COLOR(pColor IN VARCHAR2)
                           RETURN TYPE_MACHINE_TABLE PIPELINED
  /*
   * リターンの型の後方に PIPELINED を記述する
   */
IS
  -- パラメータで指定されたカラーのマシンを取得するカーソル
  cursor cMachines is select MACHINE_CD MACHINE_NAME from M_MACHINE where COLOR=pColor;

BEGIN

  -- カーソルで取得したレコード分リターン値に設定する
  for row in cMachines loop

    /*
     * 関数のリターン値に行を追加
     * PIPE ROW で追加するのは定義したオブジェクトとする(リターン値の型ではない)。
     */
    PIPE ROW(
         TYPE_MACHINE(row.MACHINE_CD, row.MACHINE_NAME) 
    );

  end loop;

  /*
   * リターンするのみで、変数やカーソルをリターンするわけではない
   */
  return;
END;
/

ファンクションの呼び出し方

作成した関数を呼び出す例になります。

select * from table(FNC_GET_MACHINE_BY_COLOR('black'))

Tweet

Pocket