Chinaunix首页 | 论坛 | 博客
  • 博客访问: 662217
  • 博文数量: 98
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 3009
  • 用 户 组: 普通用户
  • 注册时间: 2011-03-30 13:00
个人简介

About me:optimistic,passionate and harmonious. Focus on oracle programming,peformance tuning,db design, j2ee,Linux/AIX,web2.0 tech,etc

文章分类

全部博文(98)

文章存档

2020年(63)

2014年(3)

2013年(32)

分类: Oracle

2020-06-08 08:37:21

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


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

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


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

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


  
为了避免复杂性,测试的大部分例子只根据where in list计算COUNT,这里测试的都是基于IN子查询的,当然也可以使用JOINEXISTS等实现,对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会拼凑很多常量,而且数目不定,会导致无法使用绑定变量而影响效率(可能你认为可以使用动态SQLUSING,这是不行的,因为你不知道要绑定多少变量),而且IN中列表数目限制最大是1000。所以,针对这种方法,在实际开发中不推荐使用。

阅读(4175) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~