操作環(huán)境
Server Oracle 11g
Client Oracle 10g
vs_string 輸入?yún)?shù)格式如'info1,info22,info333,info4444',以","做間隔符,每變量長度不確定
vn_num vs_string 中合并拼接的變量數(shù)量
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 函數(shù)只能對查詢的結果集或明確的參數(shù)集合進行多行查詢，對于一個參數(shù)無論其表現(xiàn)形式只能進行一個參數(shù)的判斷查詢
如
EMPLOYEE_T 表中存在如下數(shù)據(jù)
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'" 只能做為一個入?yún)⒍皇莾蓚€入?yún)?參數(shù)值為
'001,002' ,故查不到合適的記錄
在存儲過程中對于以上的入?yún)⒖梢圆扇煞N辦法進行
1、通過拼接動態(tài)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、將拼接串轉(zhuǎn)為結果集輸出至IN函數(shù)中,使用如下腳本
vs_string 輸入?yún)?shù)格式如'info1,info22,info333,info4444',以","做間隔符,每變量長度不確定
vn_num vs_string 中合并拼接的變量數(shù)量
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)
);