[20180930]in list與繫結變數.txt

[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最佳化》提到,這個我給測試看看,另外
--//寫一篇文章.