操作環境
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)
);