如何綁定變數使用

在oltp系統中提倡使用綁定變數，使用綁定變數可以減少hard parse，避免因解析sql而過渡消耗cpu時間以及引起latch爭用等一系列問題。那麼到底如何使用綁定變數？可能是困擾很多人的一個問題，下面列舉了一些使用綁定變數的例子
1.sqlplus中如何使用綁定變數，可以通過variable來定義
SQL> select * from tt where id=1;

ID NAME
---------- ----------------------------------------
1 test

SQL> select * from tt where id=2;

ID NAME
---------- ----------------------------------------
2 test

SQL> variable i number;
SQL> exec :i :=1;

PL/SQL 過程已成功完成。

SQL> select *from tt where id=:i;

ID NAME
---------- ----------------------------------------
1 test

SQL> exec :i :=2;

PL/SQL 過程已成功完成。

SQL> select *from tt where id=:i;

ID NAME
---------- ----------------------------------------
2 test

SQL> print i;

I
----------
2

SQL> select sql_text,parse_calls from v$sql where sql_text like 'select * from t
t where id=%';

SQL_TEXT PARSE_CALLS
------------------------------------------------------------ -----------
select * from tt where id=2 1
select * from tt where id=1 1
select * from tt where id=:i 2
SQL>
從上面實驗發現綁定變數i的使用使查詢id=1和id=2的sqlselect *from tt where id=:i得以重複
使用，從而避免了hard parse，這裡的PARSE_CALLS＝2包括了一次soft parse

2.前兩天看到有人在pub上問在sqlplus中通過define和variable定義的變數的區別。其實define定義的我

理解不是變數而是字元常量，通過define定義之後，在通過&或者&&引用的時候不需要輸入了，僅此而已。

oracle在執行的時候自動用值進行了替換；而variable定義的是綁定變數,上面已經提到。
C:>sqlplus xys/manager
SQL*Plus: Release 11.1.0.6.0 - Production on 星期二 4月 1 14:03:00 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.

串連到:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> define
DEFINE _DATE = "01-4月 -08" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "db11" (CHAR)
DEFINE _USER = "XYS" (CHAR)
DEFINE _PRIVILEGE = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1101000600" (CHAR)
DEFINE _EDITOR = "Notepad" (CHAR)
DEFINE _O_VERSION = "Oracle Database 11g Enterprise Edition Release 11.1.0.
6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options" (
CHAR)
DEFINE _O_RELEASE = "1101000600" (CHAR)
SQL> select *from tt;
ID NAME
---------- ----------
1 a
2 a
3 "abc"
SQL> define a
SP2-0135: 符號 a 未定義
SQL> define a=1
SQL> define
DEFINE _DATE = "01-4月 -08" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "db11" (CHAR)
DEFINE _USER = "XYS" (CHAR)
DEFINE _PRIVILEGE = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1101000600" (CHAR)
DEFINE _EDITOR = "Notepad" (CHAR)
DEFINE _O_VERSION = "Oracle Database 11g Enterprise Edition Release 11.1.0.
6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options" (
CHAR)
DEFINE _O_RELEASE = "1101000600" (CHAR)
DEFINE A = "1" (CHAR)
--通過上面顯示define定義的應該是字元（串）常量。
SQL> select * from tt where id=&a;
原值 1: select * from tt where id=&a
新值 1: select * from tt where id=1
ID NAME
---------- ----------
1 a
SQL> select * from tt where id=&&a;
原值 1: select * from tt where id=&&a
新值 1: select * from tt where id=1
ID NAME
---------- ----------
1 a
SQL> define b='a';
SQL> define
DEFINE _DATE = "01-4月 -08" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "db11" (CHAR)
DEFINE _USER = "XYS" (CHAR)
DEFINE _PRIVILEGE = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1101000600" (CHAR)
DEFINE _EDITOR = "Notepad" (CHAR)
DEFINE _O_VERSION = "Oracle Database 11g Enterprise Edition Release 11.1.0.
6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options" (
CHAR)
DEFINE _O_RELEASE = "1101000600" (CHAR)
DEFINE A = "1" (CHAR)
DEFINE B = "a" (CHAR)

--如果是字元類型那麼在引用時別忘了加上單引號，另外通過define定義之後在引用時不需要輸入了。
SQL> select * from tt where name=&&b;
原值 1: select * from tt where name=&&b
新值 1: select * from tt where name=a
select * from tt where name=a
*
第 1 行出現錯誤:
ORA-00904: "A": 標識符無效

SQL> select * from tt where name='&&b';
原值 1: select * from tt where name='&&b'
新值 1: select * from tt where name='a'
ID NAME
---------- ----------
1 a
2 a
SQL> select * from tt where name='&b';
原值 1: select * from tt where name='&b'
新值 1: select * from tt where name='a'
ID NAME
---------- ----------
1 a
2 a
--執行sql時進行了替換
SQL> select sql_text from v$sql where sql_text like 'select * from tt where name
=%';
SQL_TEXT
--------------------------------------------------------------------------------
select * from tt where name=1
select * from tt where name='a'
SQL>

3.oracle在解析sql時會把plsql中定義的變數轉為為綁定變數
SQL> create table tt(id int , name varchar2(10));

表已建立。

SQL> alter session set sql_trace=true;

會話已更改。

SQL> declare
2 begin
3 for i in 1..100 loop
4 insert into tt values(i,'test');
5 end loop;
6 commit;
7 end;
8 /

PL/SQL 過程已成功完成。

SQL> alter session set sql_trace=false;
--trace file:
=====================
PARSING IN CURSOR #3 len=90 dep=0 uid=31 oct=47 lid=31 tim=7109565004 hv=962259239

ad='668ec528'
declare
begin
for i in 1..100 loop
insert into tt values(i,'test');
end loop;
commit;
end;
END OF STMT
PARSE #3:c=15625,e=5678,p=0,cr=3,cu=0,mis=1,r=0,dep=0,og=1,tim=7109564996
=====================
PARSING IN CURSOR #5 len=34 dep=1 uid=31 oct=2 lid=31 tim=7109565520 hv=1299226876

ad='66869934'
INSERT INTO TT VALUES(:B1 ,'test')
END OF STMT
PARSE #5:c=0,e=226,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=7109565513
=====================
另外從hard parse的資料量上其實也可以大致猜測oracle會把plsql中定義的變數轉換為綁定變數處理
SQL> connect /as sysdba
已串連。
SQL> shutdown immediate
資料庫已經關閉。
已經卸載資料庫。
ORACLE 常式已經關閉。
SQL> startup
ORACLE 常式已經啟動。

Total System Global Area 167772160 bytes
Fixed Size 1247900 bytes
Variable Size 67110244 bytes
Database Buffers 96468992 bytes
Redo Buffers 2945024 bytes
資料庫裝載完畢。
資料庫已經開啟。
SQL> connect xys/manager
已串連。
SQL> drop table tt;

表已刪除。

SQL> create table tt(id int , name varchar2(10));

表已建立。
SQL> col name format a30
SQL> select a.*,b.name
2 from v$sesstat a , v$statname b
3 where a.statistic#=b.statistic#
4 and a.sid=(select distinct sid from v$mystat)
5 and b.name like '%parse%';

SID STATISTIC# VALUE NAME
---------- ---------- ---------- ------------------------------
159 328 39 parse time cpu
159 329 74 parse time elapsed
159 330 339 parse count (total)
159 331 165 parse count (hard)
159 332 0 parse count (failures)

SQL> declare
2 begin
3 for i in 1..100 loop
4 insert into tt values(i,'test');
5 end loop;
6 commit;
7 end;
8 /

PL/SQL 過程已成功完成。

SQL> select a.*,b.name
2 from v$sesstat a , v$statname b
3 where a.statistic#=b.statistic#
4 and a.sid=(select distinct sid from v$mystat)
5 and b.name like '%parse%'
6 /

SID STATISTIC# VALUE NAME
---------- ---------- ---------- ------------------------------
159 328 39 parse time cpu
159 329 74 parse time elapsed
159 330 345 parse count (total)
159 331 167 parse count (hard)
159 332 0 parse count (failures)

SQL>
這裡發現hard parse只增加了2，如果沒有使用綁定變數的話，相信hard parse會更多

4.過程中的參數會自動轉化為綁定變數
SQL> edit
已寫入 file afiedt.buf

1 create or replace procedure proc_test(p_id int, p_name varchar2)
2 is
3 begin
4 insert into tt values(p_id , p_name);
5 commit;
6* end;
SQL> /

過程已建立。

SQL> alter session set sql_trace=true;

會話已更改。

SQL> exec proc_test(200,'test');

PL/SQL 過程已成功完成。

SQL> alter session set sql_trace=false;

會話已更改。
--trace file：
alter session set sql_trace=true
END OF STMT
EXEC #3:c=0,e=749,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=7393908487
=====================
PARSING IN CURSOR #1 len=35 dep=0 uid=31 oct=47 lid=31 tim=7403000735 hv=526484776

ad='6687b0b8'
BEGIN proc_test(200,'test'); END;
END OF STMT
PARSE #1:c=0,e=2584,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=7403000727
=====================
PARSING IN CURSOR #6 len=33 dep=1 uid=31 oct=2 lid=31 tim=7403001293 hv=2874748229

ad='668e9cd8'
INSERT INTO TT VALUES(:B2 , :B1 )
END OF STMT
PARSE #6:c=0,e=246,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=7403001286
=====================
另外也可以直觀的觀察：
SQL> exec proc_test(200,'test');

PL/SQL 過程已成功完成。

SQL> select sql_text from v$sql where sql_text like '%proc_test%';

SQL_TEXT
--------------------------------------------------------------------------------
BEGIN proc_test(200,'test'); END;

SQL>
在sqlplus裡執行過程不能觀察出來
下面在plsql developer執行一次過程之後再來看執行的情況
SQL> select sql_text from v$sql where sql_text like '%proc_test%';

SQL_TEXT
--------------------------------------------------------------------------------
begin -- Call the procedure proc_test(p_id => _id, p_name => :

p_name); end;

SQL>
很顯然oracle在執行過程時把參數轉化為綁定變數了，其實從plsql developer中執行過程時的文法就能

看出來：
begin
-- Call the procedure
proc_test(p_id => _id,
p_name => _name);
end;
在輸入參數列表框上面的執行文法就是這樣的。

5.在動態sql中使用綁定變數，動態sql中使用綁定變數非常明顯也容易理解，下面給出2個簡單的例子
SQL> set serveroutput on
SQL> declare
2 v_string varchar2(100);
3 v_id tt.id%type ;
4 v_name tt.name%type ;
5 begin
6 v_string:='select * from tt where id=:v_id';
7 execute immediate v_string into v_id , v_name using &a;
8 dbms_output.put_line(v_id||' '||v_name) ;
9 end;
10 /
輸入 a 的值: 1
原值 7: execute immediate v_string into v_id , v_name using &a;
新值 7: execute immediate v_string into v_id , v_name using 1;
1 test

PL/SQL 過程已成功完成。

SQL> declare
2 v_string varchar2(100);
3 v_id tt.id%type;
4 v_name tt.name%type ;
5 begin
6 v_string:='insert into tt values(:id,:name)';
7 execute immediate v_string using &id,&name ;
8 end;
9 /
輸入 id 的值: 1000
輸入 name 的值: 'test'
原值 7: execute immediate v_string using &id,&name ;
新值 7: execute immediate v_string using 1000,'test' ;

PL/SQL 過程已成功完成。

SQL> select * from tt where id=1000;

ID NAME
---------- ----------
1000 test

SQL>

6.java,.net等開發語言中如何使用綁定變數有熟悉的弟兄可以補充