Oracle 繫結變數

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並不是這樣。

在vb，java以及其他應用程式中都得顯式地利用繫結變數。
對於繫結變數的支援不僅僅限於oracle,其他RDBMS向SQL SERVER也支援這一特性。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28673746/viewspace-757326/，如需轉載，請註明出處，否則將追究法律責任。