java存储过程返回游标_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();

分享到：

2009-06-09 18:29

浏览 3715

评论