欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

程序员文章站 2022-07-14 21:21:36
...
数据库定义
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的不分大小写,这里可能就会郁闷下.
这里是必须使用大写的,记下来提醒下!