关于Java调用Oracle存储过程时,传入动态参数作为in语句条件的问题
需求:需要将多个参数作为一个参数传给存储过程执行,如:参数'1000','2000','3000','4000'等多个参数 作为一个字符串参数传递给存储过程PROC_RPT110(?)。
存储过程将这个参数作为查询语句 in('1000','2000','3000','4000')的条件查询多个值。
遇到的问题:由于当我们把这些参数拼接成一个字符串传给存储过程时,ORACLE会把这些字符串当作一个整体,
并不能像Java一样区分它们,因此会查询不到想要的结果,以下我总结了三种方法可以解决上述问题。
1、最简单的方法---用oracle正则表达式
java调用存储过程前先将上述参数拼接成'1000,2000,3000,4000'作为一个字符串传给存储过程。
存储过程再利用正则表达式将这个字符分解查询出来就可以。
如:select * from tb_table where rowm in(
select regexp_substr('1000,2000,3000,4000','[^,]+',1,rownum)net_code from dual
connect by rownum <= (length('1000,2000,3000,4000') - length(REGEXP_REPLACE('1000,2000,3000,4000', ',','')) + 1))
2、函数法,原理与上面类似,这里先创建个函数,作为分割字符串的方法。
先定义函数的返回类型为table类型
create or replace type tabletype as table of VARCHAR2(1000);
定义函数splitStr
create or replace function splitStr(var_str in string, var_split In String)
return tabletype
PIPELINED as
var_temp varchar2(4000);
var_element varchar2(4000);
v_len Number := length(var_split);
begin
var_temp := var_str;
while instr(var_temp, var_split) > 0 loop
var_element := substr(var_temp, 1, instr(var_temp, var_split) - 1);
var_temp := substr(var_temp,instr(var_temp, var_split) + v_len,length(var_temp));
pipe row(var_element);
end loop;
pipe row(var_temp);
return;
end splitStr;
使用方法:
select * from tb_table where rowm in(select * from table (splitStr('1000,2000,3000,4000',',')))
3、通过创建临时表,调用前,把那些参数存入临时表里,存储过程再通过查询临时表取值
-- 创建临时表 tb_temp_nets
DECLARE
VC_STR VARCHAR2(5000);
VN_COUNT NUMBER;
BEGIN
SELECT COUNT(*) INTO VN_COUNT FROM USER_TABLES
WHERE TABLE_NAME = upper('tb_temp_nets');
IF VN_COUNT < 1 THEN
VC_STR := 'create global temporary table tb_temp_nets (
net_code varchar2(200)
) on commit delete rows';
EXECUTE IMMEDIATE VC_STR;
END IF;
END;
/
-- ON COMMIT DELETE ROWS :说明临时表是事务指定,每次提交后ORACLE将截断表(删除全部行)
调用存储过程时先把参数批量插入这个临时表,此时存储过程不需要传递参数,存储过程再通过如下语句查询
select * from tb_table where rowm in(select * from tb_temp_nets)
需求:需要将多个参数作为一个参数传给存储过程执行,如:参数'1000','2000','3000','4000'等多个参数 作为一个字符串参数传递给存储过程PROC_RPT110(?)。
存储过程将这个参数作为查询语句 in('1000','2000','3000','4000')的条件查询多个值。
遇到的问题:由于当我们把这些参数拼接成一个字符串传给存储过程时,ORACLE会把这些字符串当作一个整体,
并不能像Java一样区分它们,因此会查询不到想要的结果,以下我总结了三种方法可以解决上述问题。
1、最简单的方法---用oracle正则表达式
java调用存储过程前先将上述参数拼接成'1000,2000,3000,4000'作为一个字符串传给存储过程。
存储过程再利用正则表达式将这个字符分解查询出来就可以。
如:select * from tb_table where rowm in(
select regexp_substr('1000,2000,3000,4000','[^,]+',1,rownum)net_code from dual
connect by rownum <= (length('1000,2000,3000,4000') - length(REGEXP_REPLACE('1000,2000,3000,4000', ',','')) + 1))
2、函数法,原理与上面类似,这里先创建个函数,作为分割字符串的方法。
先定义函数的返回类型为table类型
create or replace type tabletype as table of VARCHAR2(1000);
定义函数splitStr
create or replace function splitStr(var_str in string, var_split In String)
return tabletype
PIPELINED as
var_temp varchar2(4000);
var_element varchar2(4000);
v_len Number := length(var_split);
begin
var_temp := var_str;
while instr(var_temp, var_split) > 0 loop
var_element := substr(var_temp, 1, instr(var_temp, var_split) - 1);
var_temp := substr(var_temp,instr(var_temp, var_split) + v_len,length(var_temp));
pipe row(var_element);
end loop;
pipe row(var_temp);
return;
end splitStr;
使用方法:
select * from tb_table where rowm in(select * from table (splitStr('1000,2000,3000,4000',',')))
3、通过创建临时表,调用前,把那些参数存入临时表里,存储过程再通过查询临时表取值
-- 创建临时表 tb_temp_nets
DECLARE
VC_STR VARCHAR2(5000);
VN_COUNT NUMBER;
BEGIN
SELECT COUNT(*) INTO VN_COUNT FROM USER_TABLES
WHERE TABLE_NAME = upper('tb_temp_nets');
IF VN_COUNT < 1 THEN
VC_STR := 'create global temporary table tb_temp_nets (
net_code varchar2(200)
) on commit delete rows';
EXECUTE IMMEDIATE VC_STR;
END IF;
END;
/
-- ON COMMIT DELETE ROWS :说明临时表是事务指定,每次提交后ORACLE将截断表(删除全部行)
调用存储过程时先把参数批量插入这个临时表,此时存储过程不需要传递参数,存储过程再通过如下语句查询
select * from tb_table where rowm in(select * from tb_temp_nets)