1、只有输入参数而没有返回结果的存储过程。
sql:
1 create or replace procedure prc_1(deptno in number,dname in varchar2,loc in varchar2)
2 is
3 begin
4 insert into dept values(deptno,dname,loc)
5 end prc_1
java:
1 static void test1(){
2 Connection conn=null
3 CallableStatement csmt=null
4 try {
5 conn=JDBCUtils.getConnection()
6 conn.setAutoCommit(false)
7 csmt=conn.prepareCall("call prc_1(?,?,?)")
8 csmt.setInt(1,80)
9 csmt.setString(2,"ioc")
10 csmt.setString(3,"fhp")
11 csmt.execute()
12 conn.commit()
13 System.out.println("success insert data")
14 } catch (SQLException e) {
15 e.printStackTrace()
16 }
17 }
2、有输入参数且有一个返回值的存储过程。
sql:
1 create or replace procedure prc_2(p_deptno in number,p_loc out varchar2) is
2 begin
3 select loc into p_loc from dept where deptno=p_deptno
4 end prc_2
java:
1 static void test2(){
2 Connection conn=null
3 CallableStatement csmt=null
4 try {
5 conn=JDBCUtils.getConnection()
6 conn.setAutoCommit(false)
7 csmt=conn.prepareCall("call prc_2(?,?)")
8 csmt.setInt(1,70)
9 csmt.registerOutParameter(2,Types.VARCHAR)
10 csmt.execute()
11 conn.commit()
12 System.out.println("MIS位置:"+csmt.getString(2))
13 } catch (SQLException e) {
14 e.printStackTrace()
15 }
16 }
3、返回多行记录(游标)的存储过程。
sql:
首先要建立一个返回游标,以便接收返回结果。
1 create or replace package testpackage is
2 type test_cursor is ref cursor
3 end testpackage
4
5 create or replace procedure prc_3(p_cursor out testpackage.test_cursor)is
6 begin
7 open p_cursor for
8 select * from dept order by deptno
9 end prc_3
java:
1 static void test3(){
2 Connection conn=null
3 CallableStatement csmt=null
4 ResultSet rs=null
5 try {
6 conn=JDBCUtils.getConnection()
7 conn.setAutoCommit(false)
8 csmt=conn.prepareCall("call prc_3(?)")
9 csmt.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR)
10 csmt.execute()
11 rs=(ResultSet) csmt.getObject(1)
12 while(rs.next()){
13 System.out.println(rs.getString("deptno")+'\t'+rs.getString("dname")+'\t'+rs.getString("loc"))
14 }
15 } catch (SQLException e) {
16 // TODO Auto-generated catch block
17 e.printStackTrace()
18 }finally{
19 JDBCUtils.free(rs, csmt, conn)
20 }
21 }