Oracle存储过程:运用游标返回结果集的示例2

游标返回结果集的示例2"/>

Oracle存储过程:运用游标返回结果集的示例2

最近调查了关于在Java中调用Oracle的存储过程，取得返回纪录集的情况，调查了半天作了如下一个例子，仅供今后或别人参考：

1）创建Function

create or replace package arsweb as
type refcursor is ref cursor;
function getresultset(key in varchar2) return refcursor;
end;
/
create or replace package body arsweb is
function getresultset (key in varchar2) return refcursor
is
v_temp refcursor;
begin
open v_temp for
select tname, tabtype, clusterid from tab where tname like '%'|| key;
return v_temp;
end getresultset;
end;
/

2）在Java中调用Oralce的存储过程：

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

import oracle.jdbc.OracleTypes;
import oracle.jdbc.driver.OracleCallableStatement;

public class PLSQLFuncationCall {

public static void main(String[] args) throws Exception {
PLSQLFuncationCall.sendEarlyDeaths();
}
static void sendEarlyDeaths() throws Exception
{
Connection con = null;
CallableStatement toesUp = null;
try
{
Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();

con = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:yochi", "system", "system");

con.setAutoCommit(false);

// Setup the call.
toesUp = con.prepareCall("{ ? = call ARSWEB.GetResultSet(?) }");
toesUp.setString(2, "T");
toesUp.registerOutParameter(1,OracleTypes.CURSOR);
toesUp.execute();
// Execute the Call

ResultSet rs = ((OracleCallableStatement)toesUp).getCursor(1);
while (rs.next())
{
String name = rs.getString(1);
String type = rs.getString(2);
System.out.println("Name="+name + "/tType=" + type);
}
rs.close();
}
catch (SQLException e)
{
// We should protect these calls.
e.printStackTrace();
toesUp.close();
con.close();
}
}
}