[Oracle12C]with句でストアドプロシージャの定義

Oracle12Cでは、with句内でストアドプロシージャが定義できるようになりました。

使い方は、with句内にストアドプロシージャを定義して、それをクエリで使います。
この例では、パラメータの値を2倍するファンクションを定義して、それをselect句で使用しています。

WITH
FUNCTION func(x IN PLS_INTEGER) RETURN PLS_INTEGER
IS
BEGIN
    RETURN X * 2;
END;
SELECT func(100)
FROM DUAL;

実行結果です。ファンクションに渡した値が2され取得されているのがわかります。

SQL> r
  1  WITH
  2  FUNCTION func(x IN PLS_INTEGER) RETURN PLS_INTEGER
  3  IS
  4  BEGIN
  5  RETURN X * 2;
  6  END;
  7  SELECT func(100)
  8  FROM DUAL
  9
 10*

 FUNC(100)
----------
       200

JPA2.1でストアドプロシージャ実行

JPA2.1のストアドプロシージャの実行機能の使い方サンプル。

実行対象のプロシージャ

INパラメータを2つ受け取って、その値の合計値をアウトパラメータで返却する単純なプロシージャです。

CREATE OR REPLACE FUNCTION PUBLIC.test_function(X INT, Y INT, RET OUT INT)
AS
$BODY$
BEGIN
  RET = Y + X;
END;
$BODY$
LANGUAGE plpgsql

実行例

EntityManagerのcreateStoredProcedureQueryにストアドプロシージャ名を指定してStoredProcedureQueryを生成します。
StoredProcedureQueryのregisterStoredProcedureParameterを使用して、パラメータ情報を設定します。（パラメータの位置、イン or アウトパラメータや型情報を設定します。）
入力パラメータの場合には、setParameterを呼び出してストアドプロシージャに引き渡す値を設定します。

実行準備が整ったら、executeでストアドプロシージャを実行します。アウトパラメータはgetOutputParameterValueを使用して取得します。

CallableStatementと使い方は近いので特に迷うこともなさそうな気がします。

final StoredProcedureQuery storedProcedureQuery = em.createStoredProcedureQuery("test_function")
        .registerStoredProcedureParameter(1, Integer.class, ParameterMode.IN)
        .setParameter(1, 10)
        .registerStoredProcedureParameter(2, Integer.class, ParameterMode.IN)
        .setParameter(2, 20)
        .registerStoredProcedureParameter(3, Integer.class, ParameterMode.OUT);
storedProcedureQuery.execute();
System.out.println("result = " + storedProcedureQuery.getOutputParameterValue(3));

NamedStoredProcedureQueryを使った場合の実行例

Entity側の準備

Entityには以下のように名前付きでストアドプロシージャの情報を定義します。
上の例で示したregisterStoredProcedureParameterの呼び出し部が、NamedStoredProcedureQueryのparameters属性に設定されているイメージになります。

@NamedStoredProcedureQuery(
        name = "calc",
        procedureName = "test_function",
        parameters = {
                @StoredProcedureParameter(name = "x", type = Integer.class, mode = ParameterMode.IN),
                @StoredProcedureParameter(name = "y", type = Integer.class, mode = ParameterMode.IN),
                @StoredProcedureParameter(name = "result", type = Integer.class, mode = ParameterMode.OUT)
        })

実行分

createNamedStoredProcedureQueryにEntityで定義したストアドプロシージャクエリの名前を指定して、Queryを生成します。
生成した、Queryに対してパラメータ情報を設定して、executeを呼び出しアウトパラメータがある場合には結果を取得します。
上の例と比べると、パラメータの情報の設定がEntity側で定義されているのでなくなっています。

final StoredProcedureQuery func = em.createNamedStoredProcedureQuery("calc")
        .setParameter("x",1)
        .setParameter("y",2);
func.execute();
System.out.println("func.getOutputParameterValue(\"result\") = "+func.getOutputParameterValue(3));