java调用存储过程（返回游标变量，利用索引表，临时表）

/*创建临时表及存储过程*/

Create Global Temporary Table test1
(name varchar2(20),age int)
On Commit Delete Rows;

create or replace package addnum
is
  type emp_table_type is table of varchar2(200)
  index by binary_integer;
  type emp_cur is ref cursor;
end;
/

create or replace procedure test2
(abc in out addnum.emp_table_type)
is
begin
   abc(0) := 'aaaaa';
   abc(1) := 20;
   abc(3) := 'bbbbb';
   abc(4) := 30;
end;
/
create or replace procedure test3
(ccc in out addnum.emp_cur)
is
   cba addnum.emp_table_type;
begin
   test2(cba);
   insert into test1 values(cba(0),cba(1));
   insert into test1 values(cba(3),cba(4));
end;
/

create or replace procedure test4
(e_cur in out addnum.emp_cur)
is
begin
   test3(e_cur);
   open e_cur for select * from test1;
end;
/

 

 

 

 

 

/*java调用*/

  Connection conn = DaoUtil.getConnection();
  conn.setAutoCommit(false);
  
  CallableStatement call = conn.prepareCall("call test4(?)");
  call.registerOutParameter(1, OracleTypes.CURSOR);
  call.execute();
  ResultSet rs = (ResultSet) call.getObject(1); 
  while(rs.next()){
   System.out.println(rs.getString(1) + "," + rs.getInt(2));
  }
  conn.commit();
  rs.close();
  call.close();
  conn.close();