java调用oralc存储过程返回数组
程序员文章站
2022-07-14 21:21:36
...
数据库定义
java的调用
注意T_ARR_RE,习惯oralce的不分大小写,这里可能就会郁闷下.
这里是必须使用大写的,记下来提醒下!
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的不分大小写,这里可能就会郁闷下.
这里是必须使用大写的,记下来提醒下!
上一篇: 让canvas 支持鼠标事件
下一篇: 解决org.json中的日期问题