where in list问题解析_PART1

  在系统开发中，经常有这样的需求：前台传入一个字符串，而且此字符串具有指定分隔符，并且长度不定，那么如何根据传入的这个字符串查询对应的结果呢?考虑这样的需求，你肯定已经想到，采用构造SQL语句来解决，的确，你的想法没错，最简单的也就是构造SQL：

--将字符串转换为convert_value_list,convert_value_list类似于值的列表，比如将'a,b,c'转为'a','b','c'

SELECT ….. FROM ….. WHERE column in (convert_value_list);

  的确可以通过构造SQL来解决这样的问题(比如在JAVA中可以将传入的字符串通过String的split方法处理，然后将结果拼凑到SQL中)，但是另一方面，这样的写法有一定的限制：Oracle WHERE条件中IN列表数目不能超过1000个，另外列表数目不定会导致无法使用绑定变量而影响效率。那么怎样才能使列表长度不定而又能使用绑定变量呢？解决方法有很多种，下面逐一分析，从而使你能够根据实际情况来选择何种方法(动态SQL构造也会作为例子进行反面探讨，这个例子在PL/SQL中实现，当然在JAVA等语言中实现方式也类似)。

  解决where in list问题，首要考虑的两个问题就是解决列表长度问题和效率问题，效率问题首要考虑绑定变量问题，另外还要考虑比如cardinality(基数)对执行计划的影响等。

  为了避免复杂性，测试的大部分例子只根据where in list计算COUNT，这里测试的都是基于IN子查询的，当然也可以使用JOIN或EXISTS等实现，对10g来说效率基本没有什么差别(读者可以自己测试下其它方法)。
建立测试表：

--建两个表xy和yz作为测试用，所以这两个表很简单，不需要数据
DROP TABLE xy;
CREATE TABLE xy(id NUMBER);
DROP TABLE yz;
CREATE TABLE yz(id NUMBER);

--将all_objects copy到表t中，以便测试
DROP TABLE t;
CREATE TABLE t AS SELECT * FROM all_objects;
CREATE INDEX idx_t ON t(object_name);

--分析表和索引
BEGIN
 DBMS_STATS.GATHER_TABLE_STATS(ownname => USER,tabname => 't');
 DBMS_STATS.GATHER_INDEX_STATS(ownname => USER,indname => 'idx_t');
END;
/

--运行完上述语句后查看表t行数
DINGJUN123>SELECT COUNT(*) FROM t;
  COUNT(*)
----------
     14006
已选择 1 行。

7.1问题引入:动态SQL构造

  本节主要研究动态SQL解决where in list问题以及相关分析。

  下面使用一个简单的拼凑条件进行初步试验，这里我使用的SQL是静态SQL，看看会发生什么情况?

DINGJUN123>SET SERVEROUTPUT ON SIZE 10000

DINGJUN123>DECLARE

  2    v_condition VARCHAR2(100);

  3    v_sql       VARCHAR2(1000);

  4    v_count     NUMBER(10);

  5  BEGIN

  6    v_condition := '''XY''' || ',' || '''YZ'''; --本意是拼凑'XY','YZ'，有很多人会写成'XY,YZ'

  7    SELECT COUNT(*)

  8      INTO v_count

  9      FROM t

 10     WHERE object_name IN (v_condition);

 11    --打印结果

 12    DBMS_OUTPUT.PUT_LINE(v_count);

 13    --打印SQL

 14    v_sql := 'SELECT COUNT(*) FROM t WHERE object_name IN (' ||

 15             v_condition || ')';

 16    DBMS_OUTPUT.PUT_LINE(v_sql);

 17  END;

 18  /

0

SELECT COUNT(*) FROM t WHERE object_name IN ('XY','YZ')

  从上面的结果看到，通过拼凑的SQL，打印出来的是SELECT COUNT(*) FROM t WHERE object_name IN ('XY','YZ')，看似正确的，但是为什么执行结果是0呢?下面分析一下，执行此SQL：

DINGJUN123>SELECT COUNT(*)

  2  FROM t

  3  WHERE object_name IN ('XY','YZ');

  COUNT(*)

----------

         2

已选择 1 行。

  的确是有结果的，但是为什么在PL/SQL中执行拼凑的静态SQL没有结果呢?原因在于在PL/SQL中打印出的SQL不是真正执行的SQL，打印的是动态拼凑的SQL，而真正执行的是静态SQL，注意：

  SELECT COUNT(*) INTO  v_count FROM t WHERE object_name IN  (v_condition);

中的v_condition是一个varchar2类型，在静态SQL中拼凑的条件相当于一个整体，'XY','YZ'是一个字符串，在SQL中相当于'''XY'',''YZ'''，因此实际执行的SQL是：

DINGJUN123>SELECT COUNT(*)

  2  FROM t

  3  WHERE object_name IN ('''XY'',''YZ''');

  COUNT(*)

----------

         0

  没有找到数据，而不是SELECT COUNT(*) FROM t WHERE object_name IN ('XY','YZ')则返回2

  这个错误很多人初次解决类似问题会碰到，而且可能百思不得其解，通过上面的分析，你可能已经发现静态SQL与动态SQL有很多不同的地方值得注意。

  使用动态SQL，就可以正确查询结果了，如下：

DINGJUN123> DECLARE

  2     v_condition VARCHAR2(100);

  3     v_sql VARCHAR2(1000);

  4     v_count NUMBER(10);

  5   BEGIN

  6     v_condition:='''XY'''||','||'''YZ''';

  7     --放入动态SQL中，结果正确

  8     v_sql:='SELECT COUNT(*)  FROM t WHERE object_name IN ('||v_condition||')';

  9     EXECUTE IMMEDIATE v_sql INTO v_count;

 10     DBMS_OUTPUT.PUT_LINE(v_count);

 11     DBMS_OUTPUT.PUT_LINE(v_sql);

 12   END;

 13   /

2

SELECT COUNT(*)  FROM t WHERE object_name IN ('XY','YZ')

PL/SQL 过程已成功完成。

  现在的结果和预期结果一致，查询返回2。动态SQL的确可以解决这个问题，但是动态SQL会拼凑很多常量，而且数目不定，会导致无法使用绑定变量而影响效率(可能你认为可以使用动态SQL的USING,这是不行的，因为你不知道要绑定多少变量)，而且IN中列表数目限制最大是1000。所以，针对这种方法，在实际开发中不推荐使用。