java设置游标输出参数_Java 接收Oracle函数游标返回结果集

-- 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 threadLocal=new ThreadLocal(){

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;

*/

}

}