JAVA调用ORACLE存储过程返回集合

比如传一个学生的集合至ORACLE存储过程

1、首先定义STUDENT TYPE

 

CREATE OR REPLACE TYPE STU AS OBJECT
(
  id number,
  name varchar2(20),
  code varchar2(20)
)

 2、定义集合类型 STULIST TYPE

CREATE OR REPLACE TYPE STULIST AS TABLE OF stu

 3、建立对应的表

drop table student
create table student(
id number primary key not null,
name varchar2(20),
code varchar2(20)
)

 4、建立存储过程（测试）

create or replace procedure getStu(uList in STULIST,ret_cursor out sys_refcursor) as
begin 
  dbms_output.put_line(uList.Count);
  open ret_cursor for select * from student;
end;

 5、java代码调用（这里用的HIBERNATE,以及tomcat连接池（存储过程名称以及TYPE名称都需要大写）

public void test(){
	    getSession().doWork(new Work() {
            @Override
            public void execute(Connection conn)
                throws SQLException {
                CallableStatement call = conn.prepareCall("{Call getStu(?,?)}");
                PoolableConnection poolConnection = (PoolableConnection)conn.getMetaData().getConnection();
                Connection oracleConn = poolConnection.getDelegate();//这里获取的才是native连接
                StructDescriptor recDesc = StructDescriptor.createDescriptor("STU", oracleConn); 
                ArrayList<STRUCT> pstruct = new ArrayList<STRUCT>();  
                List<Student> stuList = Student.createList();
                for(int i=0;i<stuList.size();i++){
                    Object[] record = new Object[3];  
                    record[0] = stuList.get(i).getId();
                    record[1] = stuList.get(i).getName();
                    record[2] =  stuList.get(i).getCode();
                    STRUCT item = new STRUCT(recDesc, oracleConn, record);                  
                    pstruct.add(item);
                }
                
                
                oracle.sql.ArrayDescriptor uListDesc =
                    oracle.sql.ArrayDescriptor.createDescriptor("STULIST", oracleConn);
                oracle.sql.ARRAY uListArray = new oracle.sql.ARRAY(uListDesc,  oracleConn, pstruct.toArray());
                call.setArray(1, uListArray);
                call.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);
                call.execute();
                ResultSet rs = (ResultSet)call.getObject(2);
                while(rs.next()){
                    System.out.println(rs.getString("name"));
                }
                
            }
        });
	}

 注意:如果用的是C3P0,并且使用了SPRING可以用以下方法获取本地连接

Connection conn = setter.getPreparedStatement().getConnection();
        C3P0NativeJdbcExtractor cp30NativeJdbcExtractor = new C3P0NativeJdbcExtractor();
        conn = (OracleConnection) cp30NativeJdbcExtractor
                .getNativeConnection(conn);