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

Java(CallableStatement)调用Oracle存储过程返回结果集(ResultSet) 一：无返回值的存储过程调用存储过程：CREATE OR REPLACE PROCEDURE PRO_1(PARA1 IN VARCHAR2,PARA2 IN VARCHAR2) ASBEGININSERT 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:@127.0.0.1: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) ASBEGINSELECT 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:@127.0.0.1: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 ASTYPE MY_CURSOR IS REF CURSOR;end MYPACKAGE;2. 建立存储过程，如下：CREATE OR REPLACE PROCEDURE PRO_3(p_CURSOR out MYPACKAGE.MY_CURSOR) ISBEGINOPEN 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:@127.0.0.1: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路径里，否则会报错。

另外的例子：// CallableStatement 1 - 调用一个含有out参数的procedureCallableStatement cs=conn.prepareCall("{call mytestprc10(?,?,?)}");cs.setString(1,"10");cs.setString(2,"20");cs.registerOutParameter(3,Types.INTEGER);//注意此处对返回参数的设置方法cs.executeUpdate();int t=cs.getInt(3);System.out.println("CallableStatement 1 :"+t);cs.close();//CallableStatement 2 - 调用一个返回数值型参数的函数CallableStatement cs2=conn.prepareCall("{?=call mytestpkg1.myf_mult(?,?)}");cs2.registerOutParameter(1,Types.INTEGER);//注意此处对返回参数的设置方法cs2.setInt(2,2);cs2.setInt(3,3);cs2.executeUpdate();int t2=cs2.getInt(1);System.out.println("CallableStatement 2 : " + t2);cs2.close();//CallableStatement 3 - 调用一个返回Cursor类型参数的函数CallableStatement cs3=conn.prepareCall("{?=call mytestpkg1.myf_rtnrcd(?)}");cs3.registerOutParameter(1, OracleTypes.CURSOR);//注意此处对返回参数的设置方法，和上面的有所不同cs3.setInt(2,20);cs3.executeUpdate();ResultSet rs3=(ResultSet)cs3.getObject(1);rs3.next();System.out.println("CallableStatement 3 : " + rs3.getString(2));rs3.close();cs3.close();。