java操作oracle的存储过程 或函数的数据主要有以下几种:无返回参数,返回单个参数,返回结果集;前两种容易使用,下面主要介绍第三种返回结果集;下面给出两种方式,分别是使用存储过程和函数的形式;
存储过程,首先为了方便建立一个自己的包,如下:
CREATE OR REPLACE PACKAGE pkg_test AS
TYPE OEPSMWS_CURSOR IS REF CURSOR;
PROCEDURE get_data(p_id NUMBER,p_rc OUT OEPSMWS_CURSOR);
function funcGetdatas(p_id number) return OEPSMWS_CURSOR;
END pkg_test;
分别定义了返回的游标,和过程及函数的定义;
CREATE OR REPLACE PACKAGE BODY pkg_test AS
procedure get_datas(p_id number,o_cursor out OEPSMWS_CURSOR )
is
result_sql varchar2(200);
begin
if p_id is null or p_id=0 then
open o_cursor for select * from area;
else result_sql:='select * from area where id =:v_id';
open o_cursor for result_sql using p_id;
end if;
end get_datas;
-----
function funcGetdatas(p_id number) return o_cursor is rc OEPSMWS_CURSOR ; --定义ref cursor变量
sqlstr2 varchar2(500);
begin
if p_id =0 then
--静态测试,直接用select语句直接返回结果
open o_cursor for SELECT ID,name,code FROM area;
else
--动态sql赋值,用:w_id来申明该变量从外部获得
sqlstr2 := 'select id,name,code from area where id =:w_id';
--动态测试,用sqlstr字符串返回结果,用using关键词传递参数
open o_cursor for sqlstr2 using p_id;
end if;
return o_cursor ;
end funcGetdatas;
end pkg_test;
这样就通过存储过程或函数分别获取返回结果集的游标了;下面是java调用方式:
public static void testOrclcursor(){
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@127.0.0.1:1521:XE ";
String user = "";
String pwd = "";
Connection conn = null;
CallableStatement cs = null;
ResultSet rs = null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(url, user, pwd);
cs = conn.prepareCall("{ call pkg_test.funcGetdatas(?,?) }");
cs.setInt(1, 290);
cs.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);
cs.execute();
rs = ((OracleCallableStatement) cs).getCursor(2);
// 或者下一种方式
// rs=(ResultSet) cs.getObject(2);
if (rs != null) {
while (rs.next()) {
System.out.println(rs.getString("code"));
}
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}