oracle中procedure是不能有返回值的,要想返回值,就得有输出参数,同样要想返回记录集,可以把游标类型作为输出参数。
下面是详细情况说明:
一:无返回值的存储过程调用
存储过程:
create or replace Procedure PRO_1(PARA1in VARCHAR2,PARA2 in VARCHAR2)as
begin
insert into EMP(ID,NAME)values (PARA1, PARA2);
end PRO_1;
java程序调用:(和下面类同)
二:有返回值的存储过程调用(非结果集)
存储过程:
create or replace Procedure PRO_2(PARA1in VARCHAR2,PARA2 OUT VARCHAR2)as
bigin
select NAME into PARA2from EMPwhere ID= PARA1;
endPRO_2;
java程序调用:
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
Connection conn = DriverManager.getConnection(
"jdbc:oracle:thin:@202.96.28.250:1521:he", "username", "password");
CallableStatement st = conn.prepareCall("{call 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:he", "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) {
}