Java call SP with return result

 

********Preparation**********

create table tab_test(
trxn_id varchar2(4),
a varchar2(4),
b varchar2(4)
)

SQL> select * from tab_test;

TRXN A    B
---- ---- ----
11   aaaa 111
11   bbbb 111
11   cccc 111
22   dddd 222

case1: procedure return vaule to java class

**********create procedure: sp_total_trxn***********
create or replace procedure sp_total_trxn( i in varchar2,t out number) --return refcursor
as
begin
SELECT count(1) into t FROM tab_test where trxn_id=i;
end sp_total_trxn;
/

---------execute procedure-------------------
SQL> var rset number;
SQL>  exec SP_TOTAL_TRXN('11',:rset);

PL/SQL 过程已成功完成。

SQL> print rset;

      RSET
----------
         3

*******create java class to call above procedure*************
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Types;

public class CallSPTest {
 
 public static void main(String[] args) {  
  Connection conn=null;
  CallableStatement st = null;

  try {    
   try{  
         Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();               
   }catch(ClassNotFoundException   e){  
         System.out.println(e.toString());  
      }  

   String url = "jdbc:oracle:thin:@192.168.84.12:1521:ora10g";
   String user="system";
   String pwd="tiger";
   conn = DriverManager.getConnection(url, user, pwd);

   st = conn.prepareCall("{ call sp_total_trxn(?,?) }");    
   st.setString(1, "11");
   st.registerOutParameter(2, Types.INTEGER);
   st.execute();
   
   int record = st.getInt(2);
   System.out.println("return result after call SP:"+record);   

  } catch (Exception e) {
   e.printStackTrace();
  }finally{
   try {
    if(null!=st){
     st.close();
    }
    if(null!=conn){
     conn.close();
    }
   } catch (SQLException e1) {
    e1.printStackTrace();
   }    
  }
 }
}

case2: procedure return result list to java class

**********create procedure: sp_list_trxn***********
create or replace procedure sp_list_trxn( i in varchar2,cur out SYS_REFCURSOR)
as
begin
open cur
for SELECT trxn_id, a,b FROM tab_test where trxn_id=i;
end sp_list_trxn;
/

----execute procudure using sqlplus--------
SQL> var rset refcursor;
SQL> exec sp_list_trxn('11',:rset);

PL/SQL 过程已成功完成。

SQL> print rset;

TRXN A    B
---- ---- ----
11   aaaa 111
11   bbbb 111
11   cccc 111

*******create java class to call above procedure*************
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

public class CallSPTest2 {
 
 public static void main(String[] args) {  
  Connection conn=null;
  CallableStatement st = null;
  ResultSet rs = null;
  try {    
   try{  
          Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();               
   }catch(ClassNotFoundException   e){  
          System.out.println(e.toString());  
      }  

   String url = "jdbc:oracle:thin:@192.168.84.12:1521:ora10g";
   String user="system";
   String pwd="tiger";
   conn = DriverManager.getConnection(url, user, pwd);

   st = conn.prepareCall("{ call sp_list_trxn(?,?) }");    
   st.setString(1, "11");
   st.registerOutParameter(2, oracle.jdbc.driver.OracleTypes.CURSOR);
   st.execute();
   System.out.println("Return result..");
   rs = (ResultSet) st.getObject(2);
   
   while (rs.next()) {
    String trxn_id = rs.getString(1);
    String reta = rs.getString(2);
    String retb = rs.getString(3);
    System.out.println("trxn id:"+trxn_id + " a:" + reta + " b:" + retb);
    
   }     

  } catch (Exception e) {
   e.printStackTrace();
  }finally{
   try {
    if(null!=rs){
     rs.close();
    }
    if(null!=st){
     st.close();
    }
    if(null!=conn){
     conn.close();
    }
   } catch (SQLException e1) {
    e1.printStackTrace();
   }    
  }
 }
}
-------run java class----------
Return result..
trxn id:11 a:aaaa b:111
trxn id:11 a:bbbb b:111
trxn id:11 a:cccc b:111

 

Ref Cursor就是我们定义在服务器端的结果集的reference。 当我们打开一个Ref Cursor的时候，没有任何的数据返回到客户端，
相反，数据在服务器上的地址将会被返回到客户端。这样用户就可以自己决定什么时间和以那种方式通过Ref Cursor去取数据。
使用Oracle Database 10g Release2的新功能，我们能够很简单的把Ref Cursor作为Input参数传递给PL/SQL的存储过程和存储函数