CallableStatement调用Oracle存储过程返回结果集(ResultSet).doc

Java(CallableStatement)调用Oracle存储过程返回结果集(ResultSet)一：无返回值的存储过程调用存储过程：CREATE OR REPLACE PROCEDURE PRO_1(PARA1 IN VARCHAR2,PARA2 IN VARCHAR2) AS BEGIN INSERT INTO DBO.EMP (ID,NAME) VALUES (PARA1, PARA2);END PRO_1;Java代码：package com.icesoft.service;import java.sql.*;import java.sql.ResultSet;public class CallProcedureTest1 public CallProcedureTest1() super();public static void main(String args) String driver = oracle.jdbc.driver.OracleDriver; String url = jdbc:oracle:thin::1521:orcl ; String user = admin; String pwd = password; Connection conn = null; CallableStatement cs = null; ResultSet rs = null; try Class.forName(driver); conn = DriverManager.getConnection(url, user, pwd); cs = conn.prepareCall( call DBO.PRO_1(?,?) ); cs.setString(1, 10); cs.setString(2, Peter); cs.execute(); catch (SQLException e) e.printStackTrace(); catch (Exception e) e.printStackTrace(); finally try if (rs != null) rs.close(); if (cs != null) cs.close(); if (conn != null) conn.close(); catch (SQLException e) 备注，存储过程PRO_1中用到了表EMP(ID, NAME)，需事先建好二：有返回值的存储过程（非结果集）存储过程：CREATE OR REPLACE PROCEDURE PRO_2(PARA1 IN VARCHAR2,PARA2 OUT VARCHAR2) ASBEGIN SELECT INTO PARA2 FROM EMP WHERE ID= PARA1;END PRO_2;Java代码： package com.icesoft.service;import java.sql.*;public class CallProcedureTest2 public CallProcedureTest2() super();public static void main(String args) String driver = oracle.jdbc.driver.OracleDriver; String url = jdbc:oracle:thin::1521:orcl; String user = admin; String pwd = password; Connection conn = null; CallableStatement cs = null; ResultSet rs = null; try Class.forName(driver); conn = DriverManager.getConnection(url, user, pwd); cs = conn.prepareCall( call DBO.PRO_2(?,?) ); cs.setString(1, 10); cs.registerOutParameter(2, Types.VARCHAR); cs.execute(); String name = cs.getString(2); System.out.println(name: + name); catch (SQLException e) e.printStackTrace(); catch (Exception e) e.printStackTrace(); finally try if (rs != null) rs.close(); if (cs != null) cs.close(); if (conn != null) conn.close(); catch (SQLException e) 注意：cs.getString(2)中的数值2并非任意的，而是和存储过程中的out列对应的，如果out是在第一个位置，那就是proc.getString(1)，如果是第三个位置，就是proc.getString(3)，当然也可以同时有多个返回值，那就是再多加几个out参数了。 三：返回列表由于oracle存储过程没有返回值，它的所有返回值都是通过out参数来替代的，列表同样也不例外，但由于是集合，所以不能用一般的参数，必须要用pagkage了.所以要分两部分， 1. 建一个程序包。如下： CREATE OR REPLACE PACKAGE MYPACKAGE AS TYPE MY_CURSOR IS REF CURSOR; end MYPACKAGE; 2. 建立存储过程，如下： CREATE OR REPLACE PROCEDURE PRO_3(p_CURSOR out MYPACKAGE.MY_CURSOR) IS BEGIN OPEN p_CURSOR FOR SELECT * FROM DBO.EMP; END PRO_3; 可以看到，它是把游标（可以理解为一个指针），作为一个out 参数来返回值的。 Java代码： package com.icesoft.service;import java.sql.*;import java.sql.ResultSet;public class CallProcedureTest2 public CallProcedureTest2() super();public static void main(String args) String driver = oracle.jdbc.driver.OracleDriver; String url = jdbc:oracle:thin::1521:orcl; String user = admin; String pwd = password; Connection conn = null; CallableStatement cs = null; ResultSet rs = null; try Class.forName(driver); conn = DriverManager.getConnection(url, user, pwd); cs = conn.prepareCall( call DBO.PRO_3(?) ); cs.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR); cs.execute(); rs = (ResultSet) cs.getObject(1); while (rs.next() System.out.println(t + rs.getString(1) + t + rs.getString(2) + t); catch (SQLException e) e.printStackTrace(); catch (Exception e) e.printStackTrace(); finally try if (rs != null) rs.close(); if (cs != null) cs.close(); if (conn != null) conn.close(); catch (SQLException e) 注意：在执行前一定要先把oracle的驱动包放到class路径里，否则会报错。 另外的例子：/CallableStatement1-调用一个含有out参数的procedureCallableStatementcs=conn.prepareCall(callmytestprc10(?,?,?);cs.setString(1,10);cs.setString(2,20);cs.registerOutParameter(3,Types.INTEGER);/注意此处对返回参数的设置方法cs.executeUpdate();intt=cs.getInt(3);System.out.println(CallableStatement1:+t);cs.close();/CallableStatement2-调用一个返回数值型参数的函数CallableStatementcs2=conn.prepareCall(?=callmytestpkg1.myf_mult(?,?);cs2.registerOutParameter(1,Types.INTEGER);/注意此处对返回参数的设置方法cs2.setInt(2,2);cs2.setInt(3,3);cs2.executeUpdate();intt2=cs2.getInt(1);System.out.println(CallableStatement2:+t2);cs2.close();/CallableStatement3-调用一个返回Cursor类型参数的函数CallableStatementcs3=conn.prepareCall(?=callmytestpkg1.myf_rtnrcd(?);cs3.registerOutPar