Oracle綁定變數

 

ORACLE綁定變數的使用

 

在ORACLE中，使用綁定變數，可以降低硬解析，通常可以提高系統的效能（注意，是通常，不是任何情況下）。

       以表tabletest為例，我們來看看如何使用綁定變數，tabletest的表結構為

       field1 number(10)

       field2 number(10)

       field3 number(10)

       field4 number(10)

       field5 number(10)

        綁定變數可以理解為一個預留位置 ,例如：

declare

    i number;

    j number;

    sqlstr varchar2(200);

begin

    i:=1;

    j:=2; 

    sqlstr:='insert into 測試表 (field1,field2,field3,field4,field5)    values (:x,:x,:y,:x,:x)';
    execute immediate sqlstr using i,i,j,i,i;

end;

這樣的一段代碼中，使用i,i,j,i,i來對應:x,:x,:y,:x,:x。這段代碼是正確的，但如果以為sqlstr中只有:x,:y這兩個綁定變數， 而把語句execute immediate sqlstr using i,i,j,i,i;改為execute immediate sqlstr using i,j;就會在運行中出現綁定變數數量不夠的錯誤。

上面的正確代碼執行完後，插入的記錄從field1到field5的資料應該是1,1,2,1,1。

如果我們把execute immediate sqlstr using i,i,j,i,i;改成execute immediate sqlstr using i,j,j,i,i;執行之後，察看插入的記錄，就會發現插入的記錄是1,2,2,1,1

從上面可以看出， 綁定變數只是起到佔位的作用，同名的綁定變數並不意味著在它們是同樣的 ，在傳遞時要考慮的是傳遞的值與綁定變數出現順序的對位，而不是綁定變數的名稱。

ORACLE系統本身是能夠對變數做綁定的，例如下面的代碼：

declare

    i number；

begin

  for i in 1..1000 loop

      insert into 測試表 ( i,i+1,i*1,i*2,i-1)

  end loop;

end;

這段代碼是不需要使用綁定變數的方法來提高效率的，ORACLE會自動將其中的變數綁定。

我們可以這樣理解： 這段代碼執行了1000次的 insert into 測試表 (i,i+1,i*1,i*2,i-1) 語句，每次發出去的語句都是一樣的。

如果把這段代碼改成如下：

declare

    i number；

    sqlstr varchar2(200);

begin

  for i in 1..1000 loop

      sqlstr:='insert into 測試表 ('||to_char(i)||','||to_char(i)||'+1,'||to_char(i)||'*1,'||to_char(i)||'*2,'||to_char(i)||'-1) ';

      execute immediate sqlstr;

  end loop;

end;

這段代碼同樣是執行了1000條insert語句， 但是每一條語句都是不同的，因此ORACLE會把每條語句硬解析一次，其效率就比前面那段就低得多了。如果要提高效率，不妨使用綁定變數將迴圈中的語句改為

      sqlstr:='insert into 測試表 (:i,:i+1,:i*1,:i*2,:i-1) ';

      execute immediate sqlstr using i,i,i,i,i;

這樣執行的效率就高得多了。

我曾試著使用綁定變數來代替表名、過程名、欄位名等，結果是語句錯誤， 結論就是綁定變數不能當作嵌入的字串來使用，只能當作語句中的變數來用。

從效率來看，由於oracle10G放棄了RBO，全面引入CBO，因此，在10G中使用綁定變數效率的提升比9i中更為明顯。

最後，前面說到綁定變數是在通常情況下能提升效率， 那哪些是不通常的情況呢？

答案是： 在欄位(包括欄位集)建有索引，且欄位(集)的集的勢非常大（也就是有個值在欄位中出現的比例特別的大）的情況下， 使用綁定變數可能會導致查詢計劃錯誤，因而會使查詢效率非常低。這種情況最好不要使用綁定變數。