java 存储返回 数组_java调用oralc存储过程返回数组

CREATE TYPE t_arr AS OBJECT(

id NUMBER ,

name varchar2(20)

);

CREATE type t_arr_re as table of t_arr;

create or replace procedure test_array(v_cfjg out t_arr_re) is

begin

DECLARE

i number;

v_res_fun pub_res_function%rowTYPE;

-- D_nr dic_cfjg%rowTYPE;

cursor c_fun is

SELECT * FROM pub_res_function f;

BEGIN

i := 0;

v_cfjg := t_arr_re(); --Êý×é³õʼ»¯

open c_fun;

LOOP

fetch c_fun

into v_res_fun;

EXIT WHEN c_fun%NOTFOUND or i > 10;

i := i + 1;

v_cfjg.EXTEND;

-- DBMS_OUTPUT.PUT_LINE(TO_CHAR(D_cfjg.dm));

v_cfjg(v_cfjg.count) := t_arr(v_res_fun.func_id, v_res_fun.func_name);

DBMS_OUTPUT.PUT(to_char(v_res_fun.func_name));

dbms_output.new_line();

END LOOP;

end;

EXCEPTION

WHEN TOO_MANY_ROWS THEN

DBMS_OUTPUT.PUT_LINE('TOO_MANY_ROWS');

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(sqlerrm);

end test_array;

java的调用

public void callPrTest() {

Connection conn = null;

ResultSet rs = null;

CallableStatement stmt = null;

try {

conn = this.getConnection();

stmt = null;

String procName = new StringBuffer().append(

"{ call test_array(?) } ").toString();

stmt = conn.prepareCall(procName);

// stmt.setObject(1, array, oracle.jdbc.OracleTypes.ARRAY);

stmt.registerOutParameter(1, Types.ARRAY,"T_ARR_RE");

stmt.execute();

ARRAY arr = (ARRAY) stmt.getArray(1);

rs = arr.getResultSet();

while (rs.next()) {

STRUCT struct = (STRUCT) rs.getObject(2);

Object[] obs = struct.getAttributes();

for (int i = 0; i < obs.length; i++) {

System.out.println(obs[0] + " " + obs[0].getClass());

}

System.out.println("-------------------------");

}

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

} finally {

clear(rs, null, conn);

}

}

注意T_ARR_RE,习惯oralce的不分大小写,这里可能就会郁闷下.

这里是必须使用大写的,记下来提醒下!

2

顶

0

踩

分享到：

2009-02-23 17:38

浏览 2408

分类:数据库

评论

1 楼

mixo44co2

2009-10-29

LZ是中兴通讯的吧

我运行

ARRAY arr = (ARRAY) stmt.getArray(1);

获取的arr为空，不知道问题在哪？