[20180930]in list与绑定变量.txt

--//前几天写的链接 http://blog.itpub.net/267265/viewspace-2214966/=>[20180926]神奇的规避ORA-01795方法.txt.
--//跟别人交流,我发现居然许多开发认为in list无法实现绑定变量.

--//我在链接 http://blog.itpub.net/267265/viewspace-2214727/=>[20180920]等待事件SQLNet more data from client 3.txt 已经提到几种方法.

1.使用str2numlist,str2varlist函数,源代码在网上很容易找到.
2.使用xmltable,可能仅仅适合11g:
SQL> var a varchar2(60);
SQL> exec :a := '10,20';
PL/SQL procedure successfully completed.

SQL> select * from dept where deptno in  (select (column_value).getnumberval() from xmltable(:a));
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS

--//这种方式如果变量是字符串拚接有点麻烦.要使用引号,建议使用双引号.例子:
SQL> exec :a := '"SALES","RESEARCH"';
PL/SQL procedure successfully completed.

SQL> select * from dept where dname in  (select (column_value).getstringval() from xmltable(:a));
    DEPTNO DNAME          LOC
---------- -------------- -------------
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO

3.正则表达式例子:

SELECT *
  FROM dept
 WHERE deptno IN
 ( SELECT TO_NUMBER (REGEXP_SUBSTR ( '10,20' ,'[^,]+' ,1 ,LEVEL)) FROM DUAL CONNECT BY REGEXP_SUBSTR ( '10,20' ,'[^,]+' ,1 ,LEVEL) IS NOT NULL);

--//如果in 的变量是字符串,去掉TO_NUMBER就ok了.
--//实际上这些方法在网上很容易找到,不知道开发有这样的疑问.
--//以上方法最大的缺点受字符串大小限制(4000),我不知道开发工具有否有这个限制,12c是否可以增加32K等等.

4.临时表:
--//就是把要查询的数据先插入临时表,缺点要注意选择临时表的属性,建议选择ON COMMIT DELETE ROWS;
--//ON COMMIT DELETE ROWS 说明临时表是事务指定，每次提交后ORACLE将截断表(删除全部行)
--//这样提交时数据消失.
--//另外注意就是查询完后最好加一个提交,清除临时表数据.避免下次查询临时表里面还有数据.

5.使用集合:
--//这种方法可以突破in list 里面 1000个值的限制,具体看链接:http://blog.itpub.net/267265/viewspace-2214966/

6.实际上开发为什么说in list无法实现绑定变量.
--//主要问题在于每次带入的变量个数不一致,实际上这些可以写死了.比如定义一个数组变量大小1000.
--//数组赋值后剩下的可以使用-1或者0代替(只要表没有对应信息就可以了).这样个数确定,写出的sql就很简单,缺点就是语句写的有点长罢了.

--//不过别人讲如果使用绑定变量参数太多,会影响替换的效率,在dbsnake 《基于Oracle的SQL优化》提到,这个我给测试看看,另外
--//写一篇文章.