操作环境
Server Oracle 11g
Client Oracle 10g
vs_string 输入参数格式如'info1,info22,info333,info4444',以","做间隔符,每变量长度不确定
vn_num vs_string 中合并拼接的变量数量
SELECT SUBSTR(vs_string,
DECODE(LEVEL,1,0,(INSTR(vs_string,',',1,LEVEL-1)))+1,
DECODE(LEVEL,vn_num,LENGTH(vs_string),INSTR(vs_string,',',1,DECODE(LEVEL,1,1,LEVEL))-1)-
(DECODE(LEVEL,1,0,(INSTR(vs_string,',',1,LEVEL-1)))+1)+1)
FROM DUAL
CONNECT BY LEVEL <= (SELECT REGEXP_COUNT(vs_string,',',1,'i')+1 FROM DUAL);
在oracle 中 IN 函数只能对查询的结果集或明确的参数集合进行多行查询，对于一个参数无论其表现形式只能进行一个参数的判断查询
如
EMPLOYEE_T 表中存在如下数据
EMPLOYEE_ID EMPLOYEE_NAME
--------------------------------
001 znp
002 zsj
003 zhsj
SELECT EMPLOYEE_ID,EMPLOYEE_NAME FROM EMPLOYEE_T WHERE EMPLOYEE_ID IN ('001','002')的查询结果如下
EMPLOYEE_ID EMPLOYEE_NAME
--------------------------------
001 znp
002 zsj
SELECT EMPLOYEE_ID,EMPLOYEE_NAME FROM EMPLOYEE_T WHERE EMPLOYEE_ID IN ('001,002')的查询结果如下
EMPLOYEE_ID EMPLOYEE_NAME
--------------------------------
在 SELECT * FROM EMPLOYEE_T WHERE EMPLOYEE_ID IN ('001,002') 中 "'001,002'" 只能做为一个入参而不是两个入参,参数值为
'001,002' ,故查不到合适的记录
在存储过程中对于以上的入参可以采取两种办法进行
1、通过拼接动态SQL进行查询
vs_sql := 'SELECT EMPLOYEE_ID,EMPLOYEE_NAME FROM EMPLOYEE_T WHERE EMPLOYEE_ID IN ('||CHR(39)||'001'||CHR(39)||','||CHR(39)||'002'||CHR(39)||');
OPEN _cur FOR vs_sql;
2、将拼接串转为结果集输出至IN函数中,使用如下脚本
vs_string 输入参数格式如'info1,info22,info333,info4444',以","做间隔符,每变量长度不确定
vn_num vs_string 中合并拼接的变量数量
SELECT SUBSTR(vs_string,
DECODE(LEVEL,1,0,(INSTR(vs_string,',',1,LEVEL-1)))+1,
DECODE(LEVEL,vn_num,LENGTH(vs_string),INSTR(vs_string,',',1,DECODE(LEVEL,1,1,LEVEL))-1)-
(DECODE(LEVEL,1,0,(INSTR(vs_string,',',1,LEVEL-1)))+1)+1)
FROM DUAL
CONNECT BY LEVEL <= (SELECT REGEXP_COUNT(vs_string,',',1,'i')+1 FROM DUAL);
照办上例,即为
SELECT EMPLOYEE_ID,EMPLOYEE_NAME FROM EMPLOYEE_T
WHERE EMPLOYEE_ID IN
(
SELECT SUBSTR(vs_string,
DECODE(LEVEL,1,0,(INSTR(vs_string,',',1,LEVEL-1)))+1,
DECODE(LEVEL,vn_num,LENGTH(vs_string),INSTR(vs_string,',',1,DECODE(LEVEL,1,1,LEVEL))-1)-
(DECODE(LEVEL,1,0,(INSTR(vs_string,',',1,LEVEL-1)))+1)+1)
FROM DUAL
CONNECT BY LEVEL <= (SELECT REGEXP_COUNT(vs_string,',',1,'i')+1 FROM DUAL)
);