********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的存储过程和存储函数