jdbc从存储过程返回单个对象或PL/SQL表

返回单个PL/SQL表，元素为标量类型
创建类型，包头，包体
create or replace type tab_array is table of varchar2(38);
/

create or replace package addnum
is
  procedure abc(e_name in tab_array,t_name out tab_array);
end;
/

create or replace package body addnum is
  procedure abc
  (e_name in tab_array,t_name out tab_array)
  is
  begin
      for i in 1..e_name.count loop
          insert into t(name) values(e_name(i));
      end loop;    
     
      select name bulk collect into t_name from t;
  end;
end;
/


JDBC操作
package com.lovo;


import java.sql.Connection;
import java.sql.DriverManager;


import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.Datum;

public class JDBCMain {
    public static void main(String[] args) throws Exception {
        new oracle.jdbc.driver.OracleDriver();
       
        Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.99:1521:HXM", "me", "123");
        String[] strs = new String[]{"abc","cba"};
        OracleCallableStatement  call = (OracleCallableStatement) conn.prepareCall("{call addnum.abc(?,?)}");
        ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor("TAB_ARRAY", conn); /*TAB_ARRAY是类型名，必须大写*/
        call.setArray(1, new ARRAY(descriptor,conn,strs));
        call.registerOutParameter(2, OracleTypes.ARRAY,"TAB_ARRAY");
        call.execute();        
        ARRAY arr = call.getARRAY(2);
        Datum[] dat = arr.getOracleArray();
        for(int i=0;i<dat.length;i++){
            System.out.println(dat[i]);
        }
       
        conn.close();
    }
}


返回单个对象
    建立对象类型
        create or replace type t_obj as object
        (id int,
         name varchar2(20)
        );
       
    建立对象表
        create table t of t_obj;
       
        insert into t values(t_obj(1,'abc1'));
        insert into t values(t_obj(2,'abc2'));
        insert into t values(t_obj(3,'abc3'));
        insert into t values(t_obj(4,'abc4'));
        insert into t values(t_obj(5,'abc5'));
       
    建立包头和包体
    create or replace package addnum
    is
      procedure abc(e_name in int,t_per out t_obj);
    end;
    /
   
    create or replace package body addnum is
      procedure abc
      (e_name in int,t_per out t_obj)
      is
      begin
           select value(p) into t_per from t p where p.id=e_name;
      end;
    end;
    /

jdbc操作
    TypeDescriptor td = TypeDescriptor.getTypeDescriptor("T_OBJ", (OracleConnection) conn);
        OracleCallableStatement call = (OracleCallableStatement) conn.prepareCall("{call addnum.abc(?,?)}");
        call.setInt(1, 3);
        call.registerOutParameter(2, OracleTypes.STRUCT, "T_OBJ");
        call.execute();
       
        STRUCT str = call.getSTRUCT(2);
       
        Object[] objs = str.getAttributes();
        for(int i=0;i<objs.length;i++){
            System.out.println(str.getDescriptor().getMetaData().getColumnName(i+1) + "," + objs[i]);
        }
       
返回1个表，元素是对象
    建立对象类型，表类型，包头，包体
create or replace type t_obj as object
(id int,
 name varchar2(20)
);
/
create or replace type t_table is table of t_obj;
/

create or replace package addnum
is
  procedure abc(e_name in int,t_per out t_table);
end;
/

create or replace package body addnum is
  procedure abc
  (e_name in int,t_per out t_table)
  is
  begin
       select value(p) bulk collect into t_per from t p;
  end;
end;
/
jdbc操作
        TypeDescriptor td = TypeDescriptor.getTypeDescriptor("T_TABLE", (OracleConnection) conn);
        OracleCallableStatement call = (OracleCallableStatement) conn.prepareCall("{call addnum.abc(?,?)}");
        call.setInt(1, 1);
        call.registerOutParameter(2, OracleTypes.ARRAY,"T_TABLE");
        call.execute();
       
        ARRAY arr = call.getARRAY(2);
        Datum[] dat = arr.getOracleArray();
       
        for(int i=0;i<dat.length;i++){
            STRUCT struct = (STRUCT) dat[i];
           
            Object[] objs = struct.getAttributes();
            System.out.println("id:" + objs[0] + ",name:" + objs[1]);
        }

 

在oracle 10g上测试通过

评论

2 楼 ITvision 2013-09-27  

楼主万岁，万谢

1 楼 xnxfire 2010-03-16  

哇，写得太精辟了，这个问题我研究了好久，没想到在这里找到答案了！