Oracle 綁定變量

來源：http://blog.csdn.net/tianlesoftware/article/details/4678335

oracle 中，對于一個送出的sql語句,存在兩種可選的解析過程, 一種叫做硬解析,一種叫做軟解析.

一個硬解析需要經解析,制定執行路徑,優化通路計劃等許多的步驟.硬解釋不僅僅耗費大量的cpu，更重要的是會占據重要的們闩（latch）資源，嚴重的影響系統的規模的擴大（即限制了系統的并發行）， 而且引起的問題不能通過增加記憶體條和cpu的數量來解決。

之是以這樣是因為門闩是為了順序通路以及修改一些記憶體區域而設定的，這些記憶體區域是不能被同時修改。當一個sql語句送出後，oracle會首先檢查一下共享緩沖池（shared pool）裡有沒有與之完全相同的語句，如果有的話隻須執行軟分析即可，否則就得進行硬分析。

而唯一使得oracle 能夠重複利用執行計劃的方法就是采用綁定變量。綁定變量的實質就是用于替代sql語句中的常量的替代變量。綁定變量能夠使得每次送出的sql語句都完全一樣。

綁定變量隻是起到占位的作用，同名的綁定變量并不意味着在它們是同樣的，在傳遞時要考慮的是傳遞的值與綁定變量出現順序的對位，而不是綁定變量的名稱。

綁定變量是在通常情況下能提升效率，非正常的情況如下：

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

但是并不是任何情況下都需要使用綁定變量，下面是兩種例外情況：

1．對于隔相當一段時間才執行一次的SQL語句，這是利用綁定變量的好處會被不能有效利用優化器而抵消

2．資料倉庫的情況下。

綁定變量不能當作嵌入的字元串來使用，隻能當作語句中的變量來用。不能用綁定變量來代替表名、過程名、字段名等.

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

舉例：

普通sql語句：

SELECT fname, lname, pcode FROM cust WHERE id = 674;

SELECT fname, lname, pcode FROM cust WHERE id = 234;

SELECT fname, lname, pcode FROM cust WHERE id = 332;

含綁定變量的sql 語句：

SELECT fname, lname, pcode FROM cust WHERE id = :cust_no;

Sql*plus 中使用綁定變量：

SQL> set timing on

SQL> variable x number;

SQL> exec :x :=8

PL/SQL 過程已成功完成。

已用時間:  00: 00: 00.03

SQL> select * from A;

        ID

----------

         3

         5

已用時間:  00: 00: 00.06

SQL> insert into A values(:x);

已建立 1 行。

已用時間:  00: 00: 00.01

SQL> select * from A;

        ID

----------

         3

         8

         5

已用時間:  00: 00: 00.01

PL/SQL很多時候都會自動綁定變量而無需程式設計人員操心，即很多你寫得sql語句都會自動利用綁定變量，如下例所示： 

SQL> Set timing on

SQL> declare

  2  I NUMBER;

  3  BEGIN

  4  FOR I IN 1..1000 LOOP

  5  INSERT INTO A VALUES(I);

  6  end loop;

  7  end;

  8  /

PL/SQL 過程已成功完成。

已用時間:  00: 00: 00.12

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

SQL> create table D ( id varchar(10));

表已建立。

已用時間:  00: 00: 00.50

SQL> declare

  2  i number;

  3  sqlstr varchar(2000);

  4  begin

  5  for i in 1..1000 loop

  6  sqlstr :=' insert into d values('||to_char(i)||')';

  7  execute immediate sqlstr;

  8  end loop;

  9  end;

 10  /

PL/SQL 過程已成功完成。

已用時間:  00: 00: 00.68

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

SQL> declare

  2  i number;

  3  sqlstr varchar(2000);

  4  begin

  5  for i in 1..1000 loop

  6  sqlstr :=' insert into d values(:i)';

  7  execute immediate sqlstr using i;

  8  end loop;

  9  end;

 10  /

PL/SQL 過程已成功完成。

已用時間:  00: 00: 00.18

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

在PL/SQL中，引用變量即是引用綁定變量。但是在pl/sql中動态sql并不是這樣。