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: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) {
}
posted on 2012-07-04 17:26 Panda_Luffy 阅读(1291) 评论(0) 编辑 收藏 举报