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的不分大小写,这里可能就会郁闷下.
这里是必须使用大写的,记下来提醒下!

评论

1 楼 mixo44co2 2009-10-29  

LZ是中兴通讯的吧
我运行
ARRAY arr = (ARRAY) stmt.getArray(1); 
获取的arr为空，不知道问题在哪？