ストアドプロシージャで複数ResultSetを取得

S2JDBCで地味に便利なのがストアド周りです。

たとえば、Oracleで3つのカーソルを返すストアドプロシージャを用意します。

create or replace PROCEDURE PROC 
( cur1 OUT SYS_REFCURSOR, 
  cur2 OUT SYS_REFCURSOR,
  cur3 OUT SYS_REFCURSOR
) AS
BEGIN
  OPEN cur1 FOR SELECT * FROM EMPLOYEE WHERE EMPLOYEE_ID < 10; 
  OPEN cur2 FOR SELECT * FROM DEPARTMENT WHERE DEPARTMENT_ID < 10; 
  OPEN cur3 FOR SELECT * FROM ADDRESS WHERE ADDRESS_ID < 10; 
END PROC;

これはこんな感じで呼び出せます。

public void testProcedure() throws Exception {
    Param param = new Param();
    jdbcManager.call("PROC", param).execute();

    assertNotNull(param.employees);
    assertTrue(param.employees.size() > 0);
    assertNotNull(param.departments);
    assertTrue(param.departments.size() > 01);
    assertNotNull(param.addresses);
    assertTrue(param.addresses.size() > 0);
}

public static class Param {

    @ResultSet
    public List<Employee> employees;

    @ResultSet
    public List<Department> departments;

    @ResultSet
    public List<Address> addresses;
}

ポイントはパラメータ用のクラスに結果セットに対応するListを用意して@ResultSetをつけておくこと。一度に複数の結果セットを取得できちゃうのが便利。ストアド好きの人は結構うれしんじゃないでしょうか。

ちなみに、RDBMSごとに結果セットを返すストアドの書き方はぜんぜん違うのです（結果セットを返さなくてもちがうけど）。S2JDBCのテストをするときに、Oracle、SQL Server、DB2、MySQL、PostgreSQLと5つのDB用のストアドプロシージャを書いたのですが、はっきり言ってどれも覚えてない。。。。SVNにコードがあるから見ればわかりますけど。

次のストアドプロシージャのコードをぱっと見てどのRDBMSのものかわかったらすごいです。

CREATE OR REPLACE PROCEDURE PROC_RESULTSETS
( empCur OUT SYS_REFCURSOR, 
  deptCur OUT SYS_REFCURSOR, 
  employeeId IN NUMERIC, 
  departmentId IN NUMERIC
) AS
BEGIN
  OPEN empCur FOR SELECT * FROM EMPLOYEE WHERE employee_id > employeeId ORDER BY employee_id;  
  OPEN deptCur FOR SELECT * FROM DEPARTMENT WHERE department_id > departmentId ORDER BY department_id;
END PROC_RESULTSETS;
/

CREATE PROCEDURE dbo.PROC_RESULTSETS
    @employeeId int,
    @departmentId int
AS
BEGIN
    SELECT * FROM EMPLOYEE WHERE employee_id > @employeeId ORDER BY employee_id;  
    SELECT * FROM DEPARTMENT WHERE department_id > @departmentId ORDER BY department_id;
END
GO

CREATE PROCEDURE PROC_RESULTSETS(
  IN employeeId INTEGER,
  IN departmentId INTEGER)
DYNAMIC RESULT SETS 2
BEGIN
  DECLARE c_emp CURSOR WITH RETURN FOR
    SELECT * FROM EMPLOYEE WHERE employee_id > employeeId ORDER BY employee_id;
  DECLARE c_dept CURSOR WITH RETURN FOR
    SELECT * FROM DEPARTMENT WHERE department_id > departmentId ORDER BY department_id;
  OPEN c_emp;
  OPEN c_dept;
END@

CREATE PROCEDURE PROC_RESULTSETS(
  IN employeeId INTEGER,
  IN departmentId INTEGER)
BEGIN
    SELECT * FROM EMPLOYEE WHERE employee_id > employeeId ORDER BY employee_id;  
    SELECT * FROM DEPARTMENT WHERE department_id > departmentId ORDER BY department_id;
END
/

CREATE OR REPLACE FUNCTION PROC_RESULTSETS(
  empCur OUT refcursor,
  deptCur OUT refcursor,
  employeeId IN INTEGER,
  departmentId IN INTEGER)
AS $$
BEGIN
  OPEN empCur FOR SELECT * FROM EMPLOYEE WHERE employee_id > employeeId ORDER BY employee_id;
  OPEN deptCur FOR SELECT * FROM DEPARTMENT WHERE department_id > departmentId ORDER BY department_id;
  RETURN;
END;
$$ language plpgsql;

答えは、上から順にOracle、SQL Server、DB2、MySQL、PostgreSQLとなります。カーソルの定義の仕方、パラメータのタイプ（INとかOUT）を書く位置、区切り文字などもそれぞれ異なっていてとても覚えられないですよねー。