在ADF中调用带数组的存储过程

背景：

        有A、B、C三个表，分别对应的是AVO、BVO、CVO三个可更新的view object。

        AVO与BVO是主从关系，BVO与CVO是主从关系，CVO与AVO通过headId进行关联，CVO与BVO通过lineId关联

        三者关系是AVO<-BVO<-CVO

 

需求：在对AVO做审核操作时，须将从表BVO对应的数据插入到CVO所在的表中，并修改AVO的状态。

 

需求分析：在审核操作时，我们仅能看到AVO对应的实例，如果采用常规做法：用java进行二级循环，即循环AVO，取出对应的BVO，然后循环BVO并且进行CVO的Row的创建。很显然的，程序性能会比较差。

              根据需求，我们并不需要马上就能看到CVO的数据插入情况，因此，我们可以分成2步完成这个功能。1）循环AVO，修改状态，同时获得headId并将其存放到数组 2）通过ADF调用存储过程，传数组，由存储过程完成C表的插入工作。

 

功能实现：

   1 ）创建数据库脚本

         创建类型：CREATE OR REPLACE TYPE NUM_ARRAY IS TABLE OF NUMBER

         创建函数：

         FUNCTION insertBToC(p_array IN NUM_ARRAY, p_user_id IN NUMBER)
  RETURN VARCHAR2 AS
  v_result VARCHAR2(300) := 'SUCCESS';
BEGIN
  FOR idx IN 1 .. p_array.COUNT LOOP
    INSERT INTO C
      (payoffId, headId, lineId, itemamount)
      SELECT C_s.NEXTVAL, b.headId, b.lineId, b.itemamount
        FROM B b
       WHERE b.headId = p_array(idx);
  END LOOP;
  RETURN v_result;
END;

 

2）代码调用过程

         public String testFunction() {
        String stmt = "xxx_pkg.insertBToC(?,?)";
        ArrayList list = new ArrayList();//the arraylist should comes from A,but in this section,we used literal instead
        list.add(123);
        list.add(234);
        String s = insertBToCRow(Types.VARCHAR, stmt, list, 413);
        this.getDBTransaction().commit();
        return s;
    }

    protected String insertBToCRow(int sqlReturnType, String stmt,
                                   ArrayList list, Object userId) {
        CallableStatement st = null;
        try {
            // 1. Create a JDBC CallabledStatement
            st =
 getDBTransaction().createCallableStatement("begin ? := " + stmt + "; end;",
                                            0);
            // 2. Register the first bind variable for the return value
            st.registerOutParameter(1, sqlReturnType);
            //make sure the descriptor name is the type name which  you created in the DB
            ARRAY arrList =
                new ARRAY(oracle.sql.ArrayDescriptor.createDescriptor("NUM_ARRAY",
                                                                      st.getConnection()),
                          st.getConnection(), list.toArray());
            //set the array
            st.setArray(2, arrList);
            st.setObject(3, userId);
            // 5. Set the value of user-supplied bind vars in the stmt
            st.executeUpdate();
            return (String)st.getObject(1);
        } catch (SQLException e) {
            throw new JboException(e);
        } finally {
            if (st != null) {
                try {
                    // 7. Close the statement
                    st.close();
                } catch (SQLException e) {
                    System.out.println("SQLException:" + e);
                }
            }
        }
    }

     
 

说明：网络上ADF调用数组的例子挺少的，开始在oracle.sql.ArrayDescriptor.createDescriptor这里，  老报错，说无法解析类型NUM_ARRAY，后来通过查阅资料，发现是类型定义的地方有问题，在java里只支持SQL table Type，而类型NUM_ARRAY一开始是定义在package里的，也就是plsql type，所以才会报错。

可参考资料：http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:712625135727