java 调用存储过程 传入数组

创建一个表
create table test(
    id number(10)
    );

 

创建一个与test的id同类型的不定长数组的定义

create or replace type T_NUMBER as table of number(10);

 

 

创建一个存储过程

create or replace procedure transferData(autoTypeIds t_number) is

        
         for i in 1..autoTypeIds.COUNT loop

        insert into test values(autoTypeIds(i) );
       
    end loop;

 
     
end transferData;

 

判断数据为空

if autoTypeIds .COUNT = 0 then

 

或者

if autoTypeIds .First is null then

 

 

部分Java代码

 private void dealARRAY(int autoTypeIds) {

 final String T_NUMBER = "T_NUMBER";

  Connection conn = getConnection();

  CallableStatement cstmt = null;

  Session session = getSession();

  Transaction tx = null;

  boolean finished = false;

  String procedure = "{call transferData(?) }";

 

  ArrayDescriptor varchar2Desc = null;

  try {

   tx = session.beginTransaction();

   cstmt = conn.prepareCall(procedure);

   varchar2Desc = ArrayDescriptor.createDescriptor(T_NUMBER, conn);

   // 将字符串数组转换为oralce能识别的数组

   ARRAY vArray = new ARRAY(varchar2Desc, conn, ids);

   cstmt.setArray(1, vArray);

   cstmt.executeUpdate();

 

   session.flush();

   tx.commit();

   conn.commit();

   finished = true;

  } catch (SQLException e) {

   // TODO Auto-generated catch block
   e.printStackTrace();
  }

  finally {

   if (!finished) {
    if (tx != null) {
     tx.rollback();
    }

    if (conn != null) {
     try {
      conn.rollback();
     } catch (SQLException e1) {
     }
    }

   }
   try {
    if (cstmt != null)
     cstmt.close();
   } catch (SQLException x) {
   }

   closeConnection(conn);

  }