csdn格式有问题,字被吃了,可以看下面的链接
http://fromwiz.com/share/s/2OSLun3V44CI2LVCy70ROCI31nJ7Hb3RrAVK21KHDM2HfHt6
为什么in值列表不能拼'CLARK','KING','MILLER','SCOTT'
因为in('MILLER','CLARK','KING')相当于
in(select ename from emp where deptno=10)
select ename from emp where deptno=10;
ENAME
----------
CLARK
KING
MILLER
而select ename from emp where deptno=10是返回的三行,也就是说in()相当于in N行,每一个''是一行
显然select ename from emp where deptno=10的每一行是不带引号的
我这种拼接相当于找ename= 'MILLER','CLARK','KING' 把他看作一个整体,因为他是一行
SQL> insert into emp_row values(1,q'[CLARK','KING','MILLER','SCOTT]');
1 row created.
SQL> select * from emp_row;
2
SQL>
SQL> select * from emp_row;
ID NAME
---------- ----------------------------------------
1 CLARK','KING','MILLER','SCOTT
SQL> select * from emp_row where name in (select replace(v_in.emps, ',', q'[',']') from v_in);
ID NAME
---------- ----------------------------------------
1 CLARK','KING','MILLER','SCOTT
SQL> select replace(v_in.emps, ',', q'[',']') from v_in;
REPLACE(V_IN.EMPS,',',Q'[',']
-----------------------------
CLARK','KING','MILLER','SCOTT
SQL> select * from emp_row;
ID NAME
---------- ----------------------------------------
1 CLARK','KING','MILLER','SCOTT
如上所示证明了我的观点
而PLSQL中动态SQL实际就是拼出字符串,再把这个字符串当做SQL执行,所以是可以的
declare
v_ename varchar(32);
begin
select rpad(lpad(replace(v_in.emps, ',', q'[',']'),length(replace(v_in.emps, ',', q'[',']')) + 1,''''),
length(replace(v_in.emps, ',', q'[',']')) + 2,
'''') into v_ename
from v_in;
execute immediate 'select max(ename) from emp where ename in ('||v_ename||')' into v_ename;
dbms_output.put_line(v_ename);
end;
OUTPUT结果是 SCOTT