java 调用oracle 管道表函数

         
      需求：用户导入100万白名单，如果该导入的白名单存在于数据库中，就不执行白名单导入功能。所以要对该导入白名单与数据库进行比对，将不再白名单中的数据传入。

   单次10000个白名单批量插入前，将其过滤，单次操作session 开启时间不会太长，数据压力不大，session系统数量可以保证。
   
     该过滤功能逻辑很简单，下面贴出管道表函数在java中的灵活运用
1）构建一个rowtype 这个row type 与java中传入的数组中单条数据结构保持一致
create or replace type MdnUser_row_type as object
(
mdn VARCHAR(15),
mdnOwner VARCHAR(20),
whiteid VARCHAR(20),
batchid VARCHAR(20),
status VARCHAR(20),
addTIME VARCHAR(20)
)
2）定义自定义数组table 类型
CREATE OR REPLACE TYPE MDNUSER_TABLE_TYPE AS TABLE OF MdnUser_row_type

3）构建管道表函数
create or replace function fn_to_table(MdnUser_TABLE IN MdnUser_TABLE_TYPE )
return MdnUser_TABLE_TYPE PIPELINED
as
MdnUser_row MdnUser_row_type ;
BEGIN
for i in 1..MdnUser_TABLE.count LOOP
MdnUser_row := MdnUser_TABLE(i);
PIPE ROW (MdnUser_row);
END LOOP ;

RETURN ;
end;

4）组装sql
  String tblName = "NM_NET_USER_" +spid;
  StringBuffer sb = new StringBuffer("");
  sb.append("SELECT TEMPTBL.MDN ,TEMPTBL.MDNOWNER ,TEMPTBL.WHITEID, ");
  sb.append("TEMPTBL.BATCHID,TEMPTBL.STATUS,TEMPTBL.ADDTIME ");
  sb.append("FROM TABLE(FN_TO_TABLE(?)) TEMPTBL WHERE NOT EXISTS ");
  sb.append("(SELECT U.MDN FROM "+tblName+" U WHERE U.MDN = TEMPTBL.MDN)") ;
  return sb.toString();


5）数据访问部分代码
   conn = conn.getMetaData().getConnection();
   ArrayDescriptor arrayDesc = ArrayDescriptor.createDescriptor("MDNUSER_TABLE_TYPE", conn);
   oracle.sql.ARRAY array = new oracle.sql.ARRAY(arrayDesc, conn,userArray);
   pstmt = conn.prepareStatement(sqlQuery);
   pstmt.setArray(1, array);
   rs = pstmt.executeQuery();

6) 说明

  TABLE(FN_TO_TABLE(?))   管道表函数可以在sql中直接调用
  conn = conn.getMetaData().getConnection();
  hibernate session 获得的connection 无法直接向  oracle.jdbc.OracleConnection转型 ，要特出处理
  MDNUSER_TABLE_TYPE
  一定要大写 保持与数据库中type一致
  oracle.sql.ARRAY array = new oracle.sql.ARRAY(arrayDesc, conn,userArray);
  将java 数组转换为oracle 数组