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

最新推荐文章于 2022-02-25 20:00:37 发布

alongytl

最新推荐文章于 2022-02-25 20:00:37 发布

阅读量282

点赞数

CC 4.0 BY-SA版权

分类专栏： database 文章标签： Java 中兴 Oracle C C++

本文链接：https://blog.youkuaiyun.com/alongytl/article/details/83356710

database 专栏收录该内容

5 篇文章

订阅专栏

本文介绍了一个Oracle存储过程的创建与使用示例，该过程通过游标遍历数据库表，并将数据填充到对象数组中。此外，还提供了一个Java示例来展示如何调用这个存储过程并处理返回的对象数组。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

数据库定义


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);
		}
	}