有表test,以下是测试数据:
select * from test;
id info
----------------------
1 测试数据1
2 测试数据2
3 测试数据3
4 测试数据4
5 测试数据5
6 测试数据6
7 测试数据7
8 测试数据8
9 测试数据9
10 测试数据10
11 测试数据11
12 测试数据12
13 测试数据13
编写存储过程如下:
create or replace procedure test1(
n IN number, --输入参数,主键id
info1 OUT varchar2 --输出参数,此存储过程只能返回单个值
)
as
begin
select info into info1 from test where id=n;
end;
/
ORACLE的调用方式为:
declare
accp varchar2(20);
begin
test1(10,accp);
DBMS_OUTPUT.PUT_LINE(accp);
end;
/
JAVA程序的调用方式为:
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn=DriverManager.getConnection("jdbc:oracle:thin:@192.168.10.77:1521:ora10","scott","tiger");
System.out.println(conn);
CallableStatement stm=conn.prepareCall("{call test1(?,?)}");
stm.setInt(1, 10);
stm.registerOutParameter(2, Types.VARCHAR);
stm.execute();
String info=stm.getString(2);//根据索引2得到输出参数的值
System.out.println(info);
conn.close();
如果是以游标作为参数,就是能返回多条记录,则存储过程如下:
IS
begin
open p_CURSOR for select * from test;
end test2;
/
Connection conn=DriverManager.getConnection("jdbc:oracle:thin:@192.168.10.77:1521:ora10","scott","tiger");
System.out.println(conn);
CallableStatement stm=conn.prepareCall("{call test2(?)}");
stm.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);
stm.execute(); --不能使用executeQuery()
ResultSet rs=(ResultSet)stm.getObject(1);
while(rs.next()){
System.out.println(rs.getInt(1)+" "+rs.getString(2));
}
conn.close();