日期:2014-05-17  浏览次数:21367 次

如何实现参数类型是List的存储过程
java 的List<Object> 用在批量添加
网上的代码

SQL code

  --定义一个与list中各元素的数据类型相同的数据库对象  
 CREATE TYPE AOBJECT AS OBJECT(    
      aaaa          NUMBER(8),    
      bbbb         NUMBER(8)    
    )    
 --定义一个list数据库对象    
   CREATE TYPE ALIST AS VARRAY(100) OF AOBJECT    
     



Java code

   public static long addRecords(ArrayList list) {    
   Connection con = null;    
   CallableStatement stmt = null;    
   int backVal = 0;    
   try {    
    con = pool.getConnection();    
    if (con != null) {    
     ARRAY aArray = getArray(con, "AOBJECT","ALIST", list);//该函数调用的第二三个参数必须大写    
     stmt = con.prepareCall("{call produce1(?,?)}"); //调用存储过程    
     ((OracleCallableStatement) stmt).setARRAY(1, aArray);    
     stmt.registerOutParameter(2, java.sql.Types.INTEGER);    
     stmt.execute();    
     backVal = stmt.getInt(2);    
    }     
   } catch (Exception e) {    
    ....    
   } finally {    
    ....//释放数据库连接    
   }    
   return backVal;    
  }    
   
  private static ARRAY getArray(Connection con, String OracleObj,    
   String Oraclelist, ArrayList objlist) throws Exception {    
   ARRAY list = null;    
   if (objlist != null && objlist.size() > 0) {    
    StructDescriptor structdesc = new StructDescriptor(OracleObj, con);    
    STRUCT[] structs = new STRUCT[objlist.size()];    
    Object[] result = new Object[0];    
    for (int i = 0; i < objlist.size(); i++) {    
     result = new Object[2];//数组大小应和你定义的数据库对象(AOBJECT)的属性的个数    
     result[0] = new Integer(..);  //将list中元素的数据传入result数组    
     result[1] = new Integer(..);  //    
         
     structs[i] = new STRUCT(structdesc, con, result);    
    }    
    ArrayDescriptor desc = ArrayDescriptor.createDescriptor(Oraclelist,    
      con);    
    list = new ARRAY(desc, con, structs);    
   } // if    
   return list;    
  } 



我想知道的是存储过程produce1里面如何写?


------解决方案--------------------
将你的list用sql的游标存储。。。通过循环游标来取出对应的记录即可。。。
------解决方案--------------------
EATE TYPE ALIST AS VARRAY(100) OF AOBJECT

create or replace procedure procedure1(p1 in ALIST ,p2 out number) as
 
begin
for i in 1..p1.count loop
insert into 表 values(p1(i).aaaa,p1(i).bbbb....);
p2 := i ;
end loop;
end