java程序调用Oracle 存储过程 获取返回值(无返回,非结
果集,结果集)
oracle中procedure是不能有返回值的,要想返回值,就得有
输出参数,同样要想返回记录集,可以把游标类型作为输出参
数。
下面是详细情况说明:
一:无返回值的存储过程调用
存储过程:
create or replace Procedure PRO_1(PARA1 in
VARCHAR2,PARA2 in VARCHAR2) as
begin
insert into EMP(ID,NAME) values (PARA1,
PARA2);
end PRO_1;
java程序调用:(和下面类同)
二:有返回值的存储过程调用(非结果集)
存储过程:
create or replace Procedure PRO_2(PARA1 in
VARCHAR2,PARA2 OUT VARCHAR2) as
bigin
select NAME into PARA2 from EMP where ID=
PARA1;
end PRO_2;
java程序调用:
DriverManager.registerDriver(new
oracle.jdbc.driver.OracleDriver());
Connection conn =
DriverManager.getConnection(
"jdbc:oracle:thin:@202.96.28.250:1521:
"username", "password");
CallableStatement st = conn.prepareCall("{
PRO_2(?,?)}");
st.setString(1,"1");
st.registerOutParameter(2,OracleTypes.VARCHAR
st.execute();
String name = st.getString(2);//2与上面存储
的out参数位置对应,也可以同时有多个返回值,那就是
再多加几个out参数了
System.out.println(r.getString("title"));
三:有返回值的存储过程调用(返回结果集)
存储过程:
create or replace procedure Test(p_cur in out
sys_refcursor)
as
begin
open p_cur for select * from BOOK;
end;
java程序调用:
try {
DriverManager.registerDriver(new
oracle.jdbc.driver.OracleDriver());
Connection conn =
DriverManager.getConnection(
"jdbc:oracle:thin:@202.96.28.250:1521:
"username", "password");
CallableStatement st = conn.prepareCall(
"{call Test(?)}");
st.registerOutParameter(1,OracleTypes.CURSOR)
st.execute();
ResultSet r = (ResultSet) st.getObject(1);
while (r.next()) {
System.out.println(r.getString("title"));
}
}
catch(Exception e) {
}