工作中遇到了个小问题,有这样的SQL SELECT * FROM STUDENS WHERE ID IN (:1,:2,:3,.....:1000); 这里面的参数个数是动态变化的, 由用户界面上不同的选择后的数据进行批量处理. 比如说选择英语低于60分的去进行校外劳动.
学校的学生最大数量为1000. 同时ORACLE IN 也最大1000个值. 那么这样就会产生1000个SQL语句. 所以想把1000个参数 变成1个参数,然后值间采用逗号分割.
这样 一来 ORACLE 这端要进行逗号拆分, JAVA这端 要进行逗号合并.
ORACLE 后端的代码 增加函数和表数组类型
create or replace type TYPE_SPLIT_TABLE as table of VARCHAR2(32676);
/
/* 创建 split 函数 */
CREATE OR REPLACE FUNCTION F_ZFK_SPLIT (p_list CLOB, p_sep VARCHAR2 := ',')
RETURN tabletype
PIPELINED
/**************************************
* Name: f_zfk_split
* Author: FanKun.Zheng
# BLOG:http://blog.csdn.net/zengmuansha.
* Date: 2015-09-03.
* Function: 返回字符串被指定字符分割后的表类型。
* Parameters: p_list: 待分割的字符串。
p_sep: 分隔符,默认逗号,也可以指定字符或字符串。
* Example: SELECT * FROM users WHERE u_id IN (SELECT COLUMN_VALUE FROM table (split ('1,2')))
返回u_id为1和2的两行数据。
**************************************/
IS
l_idx PLS_INTEGER;
v_list VARCHAR2 (32676) := p_list;
BEGIN
LOOP
l_idx := INSTR (v_list, p_sep);
IF l_idx > 0
THEN
PIPE ROW (SUBSTR (v_list, 1, l_idx - 1));
v_list := SUBSTR (v_list, l_idx + LENGTH (p_sep));
ELSE
PIPE ROW (v_list);
EXIT;
END IF;
END LOOP;
END;
/
SELECT * FROM STUDENS WHERE ID IN (SELECT * FROM TABLE(F_ZFK_SPLIT(?)));
那么JAVA 该如何传CLOB 进去了? 可百度所有的都是讲CLOB字段的读取和写入. 都没讲CLOB做为参数传递!!!
Clob clob_par = conn.createClob();
clob_par.setString(1, Parameters);
PreparedStatement pstmt = conn.prepareStatement(SQLs);
pstmt.setString(1,"2015-07-13 16:49:53");
pstmt.setString(2,"2015-08-31 00:59:15");
pstmt.setClob(3,clob_par);
pstmt.setInt(4,1);
rs = pstmt.executeQuery();
这里的CONN.CREATECLOB() 必须是用ORACLE OJDBC驱动包, 其他JNDI和THIN没有测试.
PARAMETERS 是STRING类型 里面组装好了1千个值并逗号分割.
clob_par.setString(1, Parameters);
把PARAMETERS 赋值给CLOB对象.
PreparedStatement pstmt = conn.prepareStatement(SQLs);
预处理语句 SQLS 就是存放我们的查询语句
pstmt.setClob(3,clob_par);
把CLOB对象 传给第3个参数.