where in list问题

where in list问题
1.使用正则
var v_emp varchar2(200);
exec :v_emp := 'CLARK,MILLER,KING';
select * from emp
where ename in 
(
  SELECT regexp_substr(:v_emps, '[^,]+', 1, LEVEL) AS ename
    FROM dual
  CONNECT BY LEVEL <= (length(translate(:v_emps, ',' || :v_emps, ',')) + 1)
);
2.使用context
DBMS_SESSION.SET_CONTEXT (
   namespace VARCHAR2,
   attribute VARCHAR2,
   value     VARCHAR2,
   username  VARCHAR2,
   client_id VARCHAR2 );
--这个上下文的名字是inlist_ctx，需要由过程set_inlist_ctx_prc创建
create or replace context inlist_ctx using set_inlist_ctx_prc;
create or replace procedure set_inlist_ctx_prc(p_val in varchar2)
  /**
  ||程序说明：
  ||上下文inlist_ctx属性名为str
  ||p_val为属性对应的值
  **/
as
begin
  dbms_session.set_context('inlist_ctx','str',p_val);
end;
/
创建动态视图，让sys_context动态给视图传参数
create or replace view v_inlist
  as
  select substr(inlist,
    instr(inlist,',',1,level)+1,
    instr(inlist,',',1,level+1) - instr(inlist,',',1,level)-1) as value_str
  from (select ','||sys_context('inlist_ctx','str')||',' as inlist from dual)
  connect by level<=length(sys_context('inlist_ctx','str')) - length(replace(sys_context('inlist_ctx','str'),','))+1;
测试
exec set_inlist_ctx_prc('ab,bc,cd');
select value_str from v_inlist;
VALUE_STR
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ab
bc
cd
修改上下文的属性值，则试图也改变
exec set_inlist_ctx_prc('papapa,hahah,keke');
select value_str from v_inlist;
VALUE_STR
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
papapa
hahah
keke
通过测试发现，动态试图正常工作，而且因为保存在context内的树形是在session范围内的，具有很好地并发性。
exec set_inlist_ctx_prc;
select * from emp
where ename in (
  select value_str from v_inlist);
写到过程里就是先接收传入的参数，然后执行set_inlist_ctx_prc，最后执行查询
3.使用集合构造伪表
一个函数用来解析字符串并返回一个集合，然后用table()函数
 
注意不能是index by表，必须是schema级别的类型，数组有容量也不常使用

create or replace type varchar2_tt as table of varchar2(1000);
  /
create or replace function f_str2list(in_str in varchar2,
                           in_delimiter in varchar2 default ',')
  return varchar2_tt
/**
||程序说明：将按指定分隔符分隔的字符串转为嵌套表类型变量返回
||输入变量：
||        in_str 字符串,如'a,b,c'
||        in_delimiter 分隔符,默认是逗号
||输出变量：
||        varchar2_tt类型,嵌套表
**/
  as
      v_str     varchar2(32767) default in_str||in_delimiter;
      v_result  varchar2_tt := varchar2_tt();
      i number;
begin
  loop
      exit when v_str is null;
      i := instr(v_str,in_delimiter);
      v_result.extend;
      v_result(v_result.count) := trim(substr(v_str,1,i-1));
      v_str := substr(v_str,i+1);
  end loop;
  return v_result;
end;
/
var str varchar2(100);
exec :str := 'XY,YZ';
select count(*) from emp where ename in (
  select column_value from table(cast(f_str2list(:str) as varchar2_tt))
  );
一般来说in列表返回行数都比较少，一般来说应该走NL
要注意的是,用table()函数CBO会认为rows为8168(其实这个默认的table函数基数是8168依赖于块大小,我这里是8K的块)
Execution Plan
----------------------------------------------------------
Plan hash value: 2659767359
--------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |            |     1 |     8 |    33   (4)| 00:00:01 |
|   1 |  SORT AGGREGATE                     |            |     1 |     8 |            |          |
|*  2 |   HASH JOIN SEMI                    |            |     1 |     8 |    33   (4)| 00:00:01 |
|   3 |    TABLE ACCESS FULL                | EMP        |    14 |    84 |     3   (0)| 00:00:01 |
|   4 |    COLLECTION ITERATOR PICKLER FETCH| F_STR2LIST |  8168 | 16336 |    29   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ENAME"=VALUE(KOKBF$))
通过hint固定执行计划
select count(*) from emp where ename in (
  select /*+ cardinality(tab,5) */column_value from table(cast(f_str2list(:str) as varchar2_tt) a)
  );

多行in list

WITH TEMP AS
(
  SELECT 1 INT_ID,'ni' NAME,'12,13,14,a,b,~' REGION_ID FROM DUAL
  UNION
  SELECT 2 INT_ID,'wo' NAME,'13,14,F' REGION_ID FROM DUAL
  UNION
  SELECT 3 INT_ID,'ta' NAME,'11' REGION_ID FROM DUAL
)
SELECT T1.INT_ID,T1.NAME,REGEXP_SUBSTR(T1.REGION_ID,'[0-9a-zA-Z~]+',1,T2.LEV) REGION_ID FROM
(SELECT TEMP.*,LENGTH(REGION_ID)-LENGTH(REPLACE(REGION_ID,',',''))+1 RN FROM TEMP) T1,
(SELECT LEVEL LEV FROM DUAL 
CONNECT BY LEVEL <= (SELECT SUM(LENGTH(REGION_ID)-LENGTH(REPLACE(REGION_ID,',',''))+1) RN FROM TEMP)) T2
WHERE T2.LEV <= T1.RN ORDER BY INT_ID,LEV
select department_id,regexp_substr(first_name,'[^,]+',1,t2.lev) first_name from 
(select x.*,LENGTH(first_name)-LENGTH(REPLACE(first_name,',',''))+1 RN FROM x) t1,
(select level lev from dual connect by level <=(SELECT SUM(LENGTH(first_name)-LENGTH(REPLACE(first_name,',',''))+1) RN FROM x)) T2
where t2.lev <= t1.rn