スケーラブルデータベースサーバ HiRDB Version 8 UAP開発ガイド
Javaストアドプロシジャ定義時に,CREATE PROCEDUREのDYNAMIC RESULT SETS句に1以上の値を指定した場合,結果集合返却機能を使用できます。なお,Javaストアドファンクションについては,結果集合返却機能は使用できません。
Javaストアドプロシジャ内での,SELECT文の実行によって得られるカーソルを,Javaストアドプロシジャの呼び出し元で参照する機能を,結果集合返却機能といいます。
結果集合返却機能の概要を次の図に示します。
図9-11 結果集合返却機能の概要(Javaストアドプロシジャの場合)
結果集合返却機能を使用できる呼び出し元の言語を次に示します。
Javaストアドプロシジャ内で,表emps_1及び表emps_2に対して,rank<10の条件を満たす列rank,name,及びageを取得します。呼び出し元で2個の結果集合を受け取り,これらを操作します。
CREATE PROCEDURE proc2(IN param1 INTEGER) .........................1 DYNAMIC RESULT SETS 2 ...........................................2 LANGUAGE JAVA ...................................................3 EXTERNAL NAME ...................................................4 'mypack.jar:JStrSmp1.getEmp2(int, ResultSet[], ResultSet[])' ..4 PARAMETER STYLE JAVA; ...........................................5 |
import java.sql.*; ................................................1 public class JStrSmp1{ ............................................2 public static void getEmp2 ......................................3 (int jparam1, ResultSet[] rs1_out, ResultSet[] rs2_out) .......4 throws SQLException { ...................................4 Connection con = DriverManager.getConnection ( ................5 "jdbc:hitachi:hirdb"); .................5 con.setAutoCommit(false); ....................................6 PreparedStatement pstmt1 = con.prepareStatement ...............7 ("select rank,name,age from emps_1 where rank < ? .........7 order by rank"); .........................................7 pstmt1.setInt(1, jparam1); ....................................7 ResultSet rs1 = pstmt1.executeQuery(); ........................8 rs1_out[0] = rs1; .............................................9 PreparedStatement pstmt2 = con.prepareStatement ...............10 ("select rank,name,age from emps_2 where rank < ? .........10 order by rank"); .........................................10 pstmt2.setInt(1, jparam1); ....................................10 ResultSet rs2 = pstmt2.executeQuery(); ........................11 rs2_out[0] = rs2; .............................................12 return; .......................................................13 } } |
import java.sql.*; ....................................................1 public class Caller{ ..................................................2 public static void main(String[] args) throws SQLException { ........3 Connection con = DriverManager.getConnection( .....................4 "jdbc:hitachi:hirdb","USER1","PASS1"); ...........4 CallableStatement cstmt = con.prepareCall("{call proc2(?)}"); .....5 cstmt.setInt(1, 10); ..............................................5 ResultSet rs; .....................................................6 int emp_rank; .....................................................6 String emp_name; ..................................................6 int emp_age; ......................................................6 if(cstmt.execute()){ ..............................................7 rs = cstmt.getResultSet(); .....................................8 System.out.println("*** emps_1 ***"); ..........................9 while(rs.next()){ ..............................................9 emp_rank = rs.getInt(1); .....................................9 emp_name = rs.getString(2); ..................................9 emp_age = rs.getInt(3); ......................................9 System.out.println("RANK =" + emp_rank + .....................9 " NAME = " + emp_name + " AGE = " + emp_age); ............9 } } if(cstmt.getMoreResults()){ .......................................10 rs= cstmt.getResultSet(); ......................................11 System.out.println("*** emps_2 ***"); ..........................12 while(rs.next()){ ..............................................12 emp_rank = rs.getInt(1); .....................................12 emp_name = rs.getString(2); ..................................12 emp_age = rs.getInt(3); ......................................12 System.out.println("RANK =" + emp_rank + .....................12 " NAME = " + emp_name + " AGE = " + emp_age); ............12 } rs.close(); ....................................................13 } } } |
検索結果(ResultSet)は,クローズしないでResultSet[]型のOUTパラメタに設定してください。
All Rights Reserved. Copyright (C) 2006, 2016, Hitachi, Ltd.