Java调用Oracle存储过程返回结果集

这里使用包的方式来实现存储过程，首先声明包头，再实现包体，如下：

声明包头：

create or replace package arvin_test_pack as
       --声明游标类型
       type arvin_test_cursor is ref cursor;
       --声明存储过程
       procedure arvin_test_1(vendor_ids in integer,result out arvin_test_cursor);
end arvin_test_pack;

实现包体：

create or replace package body arvin_test_pack as
       --实现包头中的存储过程
       procedure arvin_test_1(vendor_ids in integer,result out arvin_test_cursor) is
         begin
             if vendor_ids<>0 then
               open result for select * from arvin_test;
             else
               open result for select * from arvin_test where vendor_id=vendor_ids;
             end if;
         end arvin_test_1;
end arvin_test_pack;

在Java中调用：

package com.tenghu.iterator.test;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import oracle.jdbc.OracleTypes;
public class CallableTest {
 public static void main(String[] args) {
  try {
   //获取Oracle驱动器
   Class.forName("oracle.jdbc.driver.OracleDriver");
   //通过DriverManager获取连接对象
   Connection conn=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ERPDEV", "gmdoms", "omsdev");
   //调用存储过程
   CallableStatement cs=conn.prepareCall("{call arvin_test_pack.arvin_test_1(?,?)}");
   //CallableStatement cs=conn.prepareCall("begin arvin_test_pack.arvin_test_1(?,?); end;");
   //设置参数
   cs.setInt(1, 11029);
   //注册输出参数类型
   cs.registerOutParameter(2, OracleTypes.CURSOR);
   //执行
   cs.execute();
   //获取结果集
   ResultSet rs=(ResultSet) cs.getObject(2);
   //遍历结果集
   while(rs.next()){
    System.out.println(rs.getInt(1));
   }
  } catch (ClassNotFoundException e) {
   e.printStackTrace();
  } catch (SQLException e) {
   System.out.println(e.getErrorCode()+"\t\t"+e.getLocalizedMessage());
  }
 }
}