where in list问题解析_PART2

接PART1：

7.2 使用正则表达式解决

  Oracle10G支持正则表达式的确给程序开发带来了很大方便，正则表达式是字符串处理的利器，Perl、JAVA、JAVASCRIPT等主流开发语言都支持正则表达式，Oracle也意识到正则表达式的重要性，所以在10G中也引入了对正则表达式的支持。在本节中将使用正则表达式REGEXP_SUBSTR，将按指定分隔符组成的字符串转为中间查询结果集，然后使用子查询(IN、EXISTS)或JOIN解决where in list问题。对于正则表达式，如需详细了解，请参考官方文档，使用正则表达式解决where in list问题的方法如下：

DINGJUN123>VAR str VARCHAR2(100);

DINGJUN123>EXEC :str := 'XY,YZ';

PL/SQL 过程已成功完成。

  2   FROM t

  3   WHERE object_name IN

  4   (

  5     SELECT REGEXP_SUBSTR(:str, '[^,]+', 1, LEVEL) AS value_str

  6     FROM DUAL

  7     CONNECT BY LEVEL <=

  8     LENGTH(TRIM(TRANSLATE(:str,TRANSLATE(:str, ',', ' '), ' '))) + 1 

  9   );

  COUNT(*)

----------

         2

已选择 1 行。

  上面的SQL使用REGEXP_SUBSTR将逗号分隔的字符串转为行结果集，其中LENGTH(TRIM(TRANSLATE(:str,TRANSLATE(:str, ',', ' '), ' '))) + 1就是查询出有多少个值列表，注意' '是1个空格，当然也可以使用LENGTH(:str) -LENGTH(REPLACE(:str,

',',''))+1实现(这里的''是空字符串，相当于NULL)，这种方法在10G环境中的大部分情况下是可以使用的，好处是可以用到绑定变量，而且列表数可以超过1000个。这里的正则表达式的子查询还是有点复杂的，下一节会讲解如何将子查询封装为一个动态视图，从而屏蔽子查询的复杂性。

7.2  使用常规字符串函数以及动态视图

  上一节使用正则表达式解决where in list问题，但是如果你的Oracle版本较低(10G之前)，无法使用正则表达式怎么办?那么就可以用本节的知识来解决了，使用INSTR、SUBSTR等函数处理指定分隔符的字符串，将字符串按分隔符转为行，这样就可以像上一节那样处理了。

  首先要解决的问题就是如何使用INSTR、SUBSTR等函数将字符串按分隔符转为多行记录，比如对于'ab,bc,cd'这个字符串来说，要转为3行记录分别为'ab'、'bc'、'cd'，如何转换呢?一般要用到CONNECT BY的，试想如果将字符串转为',ab,bc,cd,'，那么就很好转换了，找第1个值'ab'就是从第1个逗号后面的位置开始，然后截取的长度就是第2个逗号位置-第1个逗号位置-1，其他值类似，有了这个分析，就能很好实现这个需求了：

DINGJUN123>VAR str VARCHAR2(100);

DINGJUN123>EXEC :str := 'ab,bc,cd';

PL/SQL 过程已成功完成。

-- LENGTH(:str)-LENGTH(REPLACE(:str,',',''))+1是计算有多少个值，和前一节的TRANSLATE一样

DINGJUN123>SELECT

  2   SUBSTR (inlist,

  3         INSTR (inlist, ',', 1, LEVEL  ) + 1,

  4         INSTR (inlist, ',', 1, LEVEL+1)

  5         - INSTR (inlist, ',', 1, LEVEL) -1 )

  6    AS value_str

  7    FROM (SELECT ','||:str||',' AS inlist

  8            FROM DUAL)

  9   CONNECT BY LEVEL <=

 10   LENGTH(:str)-LENGTH(REPLACE(:str,',','')) + 1;

VALUE_STR

------------------------------------------------------------

ab

bc

cd

已选择3行。

  有了上面的结果作为子查询就和正则表达式一样可以解决where in list问题，在上一节我说过，这样的子查询可能会很复杂，为了隐藏子查询的复杂性，可以将子查询封装为一个动态视图，所谓动态视图就是传入不同的字符串，视图的结果是不同的，那么如何实现动态视图功能呢?

  在PL/SQL中有内置包DBMS_SESSION，这个包的方法SET_CONTEXT可以创建绑定名字的上下文，并且具有属性名和属性值，通过SYS_CONTEXT函数就可以获取指定上下文的属性值。这样只要视图中的字符串值是通过SYS_CONTEXT获取的就可以了，每次调用存储过程重置CONTEXT。注意创建CONTEXT必须在一个命名过程或包过程中调用DBMS_SESSION.SET_CONTEXT，而不能在匿名过程中直接使用DBMS_SESSION.SET_CONTEXT，对于DBMS_SESSION包的详细使用请参考相关文档。详细如下：

1)  创建上下文

--这个上下文的名字是INLIST_CTX，需要由过程SET_INLIST_CTX_PRC创建

DINGJUN123>CREATE OR REPLACE CONTEXT INLIST_CTX USING set_inlist_ctx_prc;

上下文已创建。

2)  建立与上下文创建相关的过程

DINGJUN123>CREATE OR REPLACE PROCEDURE set_inlist_ctx_prc(p_val  IN VARCHAR2)

  2  /**

  3  ||程序说明:

  4  ||上下文INLIST_CTX属性名为STR

  5  ||p_val为属性对应的值

  6  **/

  7  AS

  8  BEGIN

  9    DBMS_SESSION.set_context('INLIST_CTX', 'STR', p_val);

 10  END;

 11  /

过程已创建。

3)  建立视图

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

DINGJUN123>CREATE OR REPLACE VIEW v_inlist

  2  AS

  3   SELECT

  4   SUBSTR (inlist,

  5         INSTR (inlist, ',', 1, LEVEL  ) + 1,

  6         INSTR (inlist, ',', 1, LEVEL+1)

  7         - INSTR (inlist, ',', 1, LEVEL) -1 )

  8    AS value_str

  9    FROM (SELECT ','||SYS_CONTEXT('INLIST_CTX', 'STR')||','

 10             AS inlist

 11            FROM DUAL)

 12   CONNECT BY LEVEL <=

 13   LENGTH(SYS_CONTEXT('INLIST_CTX', 'STR'))

 14   -LENGTH(REPLACE(SYS_CONTEXT('INLIST_CTX', 'STR'),',',''))+1;

视图已创建。

4)  测试
下面测试此动态视图，看是否满足要求：

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

DINGJUN123>EXEC set_inlist_ctx_prc('ab,bc,cd');

PL/SQL 过程已成功完成。

--视图成功输出3行记录

DINGJUN123>SELECT value_str

  2  FROM v_inlist;

VALUE_STR

--------------

ab

bc

cd

已选择3行。

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

DINGJUN123>EXEC set_inlist_ctx_prc('x,y,z');

PL/SQL 过程已成功完成。

DINGJUN123>SELECT value_str

  2  FROM v_inlist;

VALUE_STR

---------------

x

y

z

已选择3行。

  通过测试发现，动态视图正常工作，而且因为保存在CONTEXT内的属性是在SESSION范围内的，具有很好的并发性。

  下面就用这个动态视图实现本章讨论的where in list问题，其实很简单，只要将视图放入到子查询中即可，如下：

--先重置CONTEXT

DINGJUN123>EXEC set_inlist_ctx_prc('XY,YZ');

PL/SQL 过程已成功完成。

DINGJUN123>SELECT COUNT(*)

  2  FROM t

  3  WHERE object_name IN

  4  (

  5   SELECT value_str

  6   FROM v_inlist

  7  );

  COUNT(*)

----------

         2

已选择 1 行。

  这个查询是符合要求的，使用动态视图，可以隐藏查询的复杂性，只需要每次查询前调用存储过程重置CONTEXT即可，而且和正则表达式一样，列表数目可以不定，也使用到了绑定变量。

  本节主要讨论使用INSTR+SUBSTR代替正则表达式在低版本Oracle中的使用，并且介绍了使用DBMS_SESSION包创建CONTEXT和建立动态视图放入子查询中隐藏查询复杂性的方法。