ORACLE将分隔数据转换为多值IN列表

来源：互联网发布：惠普打印机驱动for mac 编辑：程序博客网时间：2024/04/20 13:37

看看下面的语句

select ename,sal,deptno

from emp

where empno in('7654,7698,7782,7788')

这个语句是错误的，因为DEPTNO是一个数值列，而此IN列表是一个字符串值，所以此SQL语句会失败。现在要将此字符串转换为用逗号分解的数值列表。使用如下语句可以达到目的。

select ename,sal,deptno
from emp
where empno in (
select
to_number(
rtrim(
substr(csv.emps,instr(csv.emps,',',1,pos)+1,instr(csv.emps,',',1,pos+1)-instr(csv.emps,',',1,pos))
,',')
)
from
(select ','||'7654,7698,7782,7788'||',' emps from t1) csv,
(select rownum pos from emp) iter
where iter.pos<=(length(csv.emps)-length(replace(csv.emps,',')))/length(',')-1
)

下面贴出来一些部分过程的结果，便于理解。

SQL> select emps,pos
2 from
3 (select ','||'7654,7698,7782,7788'||',' emps from t1) csv,
4 (select rownum pos from emp) iter
5 where iter.pos<=(length(csv.emps)-length(replace(csv.emps,',')))/length(','
)-1;

EMPS                         POS
--------------------- ----------
,7654,7698,7782,7788,          1
,7654,7698,7782,7788,          2
,7654,7698,7782,7788,          3
,7654,7698,7782,7788,          4

SQL> select ename,sal,deptno
2 from emp
3 where empno in (
4 select
5 to_number(
6 rtrim(
7 substr(csv.emps,instr(csv.emps,',',1,pos)+1,instr(csv.emps,',',1,pos+1)-instr(csv.emps,',',1,pos))
8 ,',')
9 )
10 from
11 (select ','||'7654,7698,7782,7788'||',' emps from t1) csv,
12 (select rownum pos from emp) iter
13 where iter.pos<=(length(csv.emps)-length(replace(csv.emps,',')))/length(',')-1
14 );

ENAME             SAL     DEPTNO
---------- ---------- ----------
CLARK            2450         10
BLAKE            2850         30
MARTIN           1250         30
SCOTT            3000         20