使用常规字符串函数及动态视图解决where in list问题（读书笔记之三）

----使用常规字符串函数及动态视图
/*如果数据库版本较低，无法使用正则表达式，则使用instr\SUBSTR等函数处理指定分隔符的字符串，将字符串按分隔符转为行，这样就可以像上面那样处理
首先要解决的问题就是，如何使用instr,SUBSTR等函数将字符串按分隔符转为多行记录，比如'ab,bc,cd'这个字符串来说，要转为3行记录分别为
'ab'、'bc'、'cd'，如何转换呢？一般要用到connect BY的，试想如果将字符串转为',ab,bc,cd,',那么就很好转换了，找第一个值'ab'就是从
第一个逗号后面的位置开始，然后截取的长度就是第2个逗号位置减去第一个逗号位置再减去1，其他值类似，有了这个分析，就能很好的实现这个需求了：*/
/*instr函数：返回要截取的字符串在源字符串中的位置，语法如下：
instr( string1, string2 [, start_position [, nth_appearance ] ] )
参数分析：
　　string1
　　源字符串，要在此字符串中查找。
　　string2
　　要在string1中查找的字符串.
　　start_position
　　代表string1 的哪个位置开始查找。此参数可选，如果省略默认为1. 字符串索引从1开始。如果此参数为正，从左到右开始检索，如果此参数为负，从右到左检索，返回要查找的字符串在源字符串中的开始索引。
　　nth_appearance
　　代表要查找第几次出现的string2. 此参数可选，如果省略，默认为 1.如果为负数系统会报错。
　　注意：
　　如果String2在String1中没有找到，instr函数返回0.*/

VAR str VARCHAR2(100);EXEC :str :='ab,bc,cd';SELECT SUBSTR(inlist,INSTR(inlist,',',1,LEVEL)+1,INSTR(inlist,',',1,LEVEL+1)-INSTR(inlist,',',1,LEVEL)-1) AS value_strFROM (SELECT ','||:str||',' AS inlist FROM dual)CONNECT BY LEVEL <= LENGTH(:str)-LENGTH(REPLACE(:str,',',''))+1;

/*有了上面的结果作为子查询就和正则表达式一样可以解决where IN LIST的问题，由于这样的子查询可能会很复杂，为了隐藏子查询的复杂性，
可以降子查询封装为一个动态视图
所谓动态视图就是传入不同的字符串，视图的结果是不同的
在pl/sql中有内置包dbms_session，这个包的方法set_context可以创建绑定名字的上下文，并且具有属性名和属性值，通过sys_context函数就可以
获取指定上下文的属性值，这样只要视图中的字符串值是通过sys_context获取的就可以了，每次调用存储过程重置context。
注意创建context必须在一个命名过程或包过程中调用dbms_session.set_context，而不能在匿名过程中直接使用dbms_session.set_context*/
首先以DBA身份赋权限：grant create any context to scott;
--创建上下文，这个上下文的名字是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)ASBEGIN  dbms_session.set_context('inlist_ctx','str',p_val);END;/

--建立视图，创建动态视图，让sys_context动态给视图传常参数，只需要将前面语句中的绑定变量:str改为sys_context('inlist_ctx','str')就可以

CREATE OR REPLACE VIEW v_inlistASSELECT SUBSTR(inlist,INSTR(inlist,',',1,LEVEL)+1,INSTR(inlist,',',1,LEVEL+1)-INSTR(inlist,',',1,LEVEL)-1) AS value_strFROM (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;

--创建上下文，并给予属性str初始值为'ab,bc,cd'

EXEC set_inlist_ctx_prc('ab,bc,cd');SELECT value_str FROM v_inlist;

--修改上下文属性值，则视图也改变

EXEC set_inlist_ctx_prc('x,y,z');SELECT value_str FROM v_inlist;

/*动态视图正常工作，而且因为保存在context内的属性是session范围内的，具有很好的并发性
接下来，用这个动态视图解决where IN LIST的问题就简单多了，只要将视图放在子查询中即可*/

EXEC set_inlist('XY,YZ');SELECT COUNT(*) FROM T1 WHERE OBJECT_NAME IN (SELECT value_str FROM v_inlist);

使用动态视图可以隐藏查询的复杂性，只要在每次查询前调用存储过程重置context即可，这是可以在低版本数据库中适用的方法