静态SQL和动态SQL之where in list问题（读书笔记之一）

来源：互联网发布：饿了么数据分析编辑：程序博客网时间：2024/06/05 15:19

--建两个表xy和yz作为测试用，所以表很简单，不需要数据

DROP TABLE xy;CREATE TABLE xy(ID NUMBER);DROP TABLE yz;CREATE TABLE yz(ID NUMBER);

--将all_objects copy到表t中，以便测试

DROP TABLE t1;CREATE TABLE t1 AS SELECT * FROM all_objects;CREATE INDEX idx_t1 ON t1(object_name);

--分析表和索引

BEGIN  dbms_stats.gather_table_stats(ownname => USER,tabname=>'t1');  dbms_stats.gather_index_stats(ownname => USER,indname=>'idx_t1');END;/

--运行完上述语句后查看表t1的行数

SELECT COUNT(*) FROM t1;

---56298行
--下面是简单的拼凑条件，使用的是静态SQL

SET serveroutput ON SIZE 1000DECLAREv_condition VARCHAR2(100);v_sql       VARCHAR2(1000);v_count     NUMBER(10);BEGIN  v_condition:='''XY'''||','||'''YZ''';----这里时拼凑'XY'，'YX'，有很多人会写成'XY,YZ'  SELECT COUNT(*) INTO v_count FROM t1  WHERE object_name IN(v_condition);  dbms_output.put_line(v_count);  dbms_output.put_line(v_condition);  v_sql:='SELECT COUNT(*) FROM t1 WHERE object_name IN('||v_condition||')';  dbms_output.put_line(v_sql);END;/

----可以看到，打印出来的v_sql看似正确，但是为什么执行结果却是0呢，下面进行分析

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

---2
/*的确有结果，为什么pl/sql执行拼凑的静态SQL没有结果呢，原因是在pl/sql中打印出的sql不是真正执行的sql，打印的是动态拼凑的sql，
而真正执行的是静态SQL
注意：SELECT COUNT(*) INTO v_count FROM t1 WHERE object_name IN(v_condition);中的v_condition是一个varchar2类型，
在静态sql中拼凑的条件相当于一个整体，'XY','YX'是一个字符串，在sql中相当于'''XY'',''YZ''',因此实际执行的sql是：*/
SELECT COUNT(*) FROM t1 WHERE object_name IN('''XY'',''YZ''');---0
---使用动态SQL

DECLAREv_condition VARCHAR2(100);v_sql       VARCHAR2(1000);v_count     NUMBER(10);BEGIN  v_condition:='''XY'''||','||'''YZ''';  --放入动态SQL中  v_sql:='SELECT COUNT(*) FROM t1 WHERE object_name IN('||v_condition||')';  EXECUTE IMMEDIATE v_sql INTO v_count;  dbms_output.put_line(v_count);  dbms_output.put_line(v_sql);END;/

/*执行结果是正确的，但是动态SQL会拼凑很多常量，而且数目不对，会导致无法使用绑定变量而影响效率，
可能有人认为可以使用动态SQL的using，这是不行的，因为根本不知道要绑定多少变量，而且In的列表数目最大限制是1000，
所以针对这种方法，在实际开发中不推荐使用*/

0 0