-- Create table
create table ABIN1
(
ID1 NUMBER,
NAME1 NVARCHAR2(100),
CREATETIME1 DATE default sysdate
)
create or replace function getFunction(myname varchar2)
return sys_refcursor
as
v_sql varchar2(4000);
mylist sys_refcursor;
begin
v_sql:='select * from abin1 where name1=:1';
open myList for v_sql using myname;
return(myList);
end;
package com.abin.lee.db.oracle;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class OracleConnection {
private static final String USER="abin";
private static final String PWD="abin";
private static final String URL="jdbc:oracle:thin:@localhost:1521:XE";
private static final String DRIVER="oracle.jdbc.driver.OracleDriver";
private static ThreadLocal<Connection> threadLocal=new ThreadLocal<Connection>(){
protected Connection initialValue(){
Connection conn=null;
try {
Class.forName(DRIVER);
if(null==conn||conn.isClosed()){
conn=DriverManager.getConnection(URL, USER, PWD);
}
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
};
public static Connection getConnection() throws ClassNotFoundException, SQLException{
Connection conn=null;
if(threadLocal.get()==null){
Class.forName(DRIVER);
conn=DriverManager.getConnection(URL, USER, PWD);
threadLocal.set(conn);
}else {
return threadLocal.get();
}
return conn;
}
}
package com.abin.lee.db.oracle;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import junit.framework.TestCase;
import oracle.jdbc.OracleTypes;
public class GetFunctionResultSet extends TestCase{
public void test(){
Connection conn=null;
CallableStatement cs=null;
ResultSet rs=null;
try {
conn=OracleConnection.getConnection();
/*此處是關鍵，不要寫錯了。第一個問號代表返回值，getFunction（）括號里面是function的輸入參數。*/
cs=conn.prepareCall("{?=call getFunction(?)}");
cs.registerOutParameter(1, OracleTypes.CURSOR);
cs.setString(2, "a1");
cs.execute();
rs=(ResultSet)cs.getObject(1);
while(rs.next()&&rs!=null){
System.out.println("ID="+rs.getInt("id1"));
System.out.println("NAME="+rs.getString("name1"));
System.out.println("NAME="+rs.getString("createtime1"));
}
} catch (Exception e) {
e.printStackTrace();
}
/**
* create or replace function getFunction(myname varchar2)
return sys_refcursor
as
v_sql varchar2(4000);
mylist sys_refcursor;
begin
v_sql:='select * from abin1 where name1=:1';
open myList for v_sql using myname;
return(myList);
end;
*/
}
}
參考資料：
http://lanselixiangxc.bokee.com/267623.html