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 }
执行结果:
总结:
上面的小程序只是为了快速入门Java调用存储过程,那么在什么情况下使用Java调用存储过程比较合适呢?
工具类:
1 import java.sql.Connection; 2 import java.sql.DriverManager; 3 import java.sql.ResultSet; 4 import java.sql.SQLException; 5 import java.sql.Statement; 6 7 public class JDBCUtils { 8 private static String url="jdbc:oracle:thin:@//localhost:1521/myoracle"; 9 private static String user="scott"; 10 private static String password="tiger"; 11 12 private JDBCUtils(){ 13 14 } 15 16 static{ 17 try { 18 Class.forName("oracle.jdbc.driver.OracleDriver"); 19 } catch (ClassNotFoundException e) { 20 throw new ExceptionInInitializerError(e); 21 } 22 } 23 24 public static Connection getConnection() throws SQLException{ 25 return DriverManager.getConnection(url, user, password); 26 } 27 28 public static void free(ResultSet rs,Statement st,Connection conn){ 29 try{ 30 if(rs!=null) 31 rs.close(); 32 }catch(SQLException e){ 33 e.printStackTrace(); 34 }finally{ 35 try{ 36 if(st!=null) 37 st.close(); 38 }catch(SQLException e){ 39 e.printStackTrace(); 40 }finally{ 41 if(conn!=null) 42 try { 43 conn.close(); 44 } catch (SQLException e) { 45 e.printStackTrace(); 46 } 47 } 48 } 49 } 50 }