oracle 执行管道函数,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 数组