使用繫結變數的一點總結！
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 => :p_id, p_name => :
p_name); end;
SQL>
很顯然oracle在執行過程時把引數轉化為繫結變數了，其實從plsql developer中執行過程時的語法就能看出來：
begin
-- Call the procedure
proc_test(p_id => :p_id,
p_name => :p_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等開發語言中如何使用繫結變數有熟悉的弟兄可以補充！
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/19602/viewspace-1001883/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- ORACLE 繫結變數用法總結Oracle變數
- PLSQL使用繫結變數SQL變數
- 【優化】使用繫結變數 OR 不使用繫結變數，這不是問題！優化變數
- 【sql調優】使用繫結變數（一）SQL變數
- 繫結變數優缺點、使用、繫結變數窺探、 Oracle自適應共享遊標變數Oracle
- 繫結變數的使用範圍變數
- 關於繫結變數的使用變數
- 【最佳化】使用繫結變數 OR 不使用繫結變數，這不是問題！變數
- 繫結變數及其優缺點變數
- 繫結變數的一個例子變數
- 繫結變數變數
- 繫結變數之繫結變數窺探(Bind Peeking)變數
- SQL Developer中使用繫結變數SQLDeveloper變數
- Oracle 繫結變數Oracle變數
- PLSQL中使用繫結變數的語法SQL變數
- 繫結變數的測試變數
- 【sql調優】使用繫結變數（二）SQL變數
- 關於繫結變數的SQL繫結什麼值變數SQL
- 繫結變數窺測的演變變數
- 檢視繫結變數變數
- 繫結變數窺測變數
- Oracle之繫結變數Oracle變數
- 關於繫結變數變數
- 繫結變數就一定好麼變數
- oracle 查詢未使用繫結變數的sqlOracle變數SQL
- SpringMVC中的引數繫結總結SpringMVC
- oracle繫結變數的測試Oracle變數
- 查詢繫結變數的值變數
- oracle中使用繫結變數的好處的例子Oracle變數
- 如何在對in操作使用變數繫結(轉)變數
- 在繫結變數下使用outline變數
- java程式裡怎麼使用繫結變數Java變數
- SQL使用繫結變數，測試例項。SQL變數
- clickhouse使用的一點總結
- SQL*Plus中使用DATE型別的繫結變數SQL型別變數
- 在oracle的plsql中為cursor使用繫結變數OracleSQL變數
- 查詢沒有使用繫結變數的sql zt變數SQL
- Oracle 繫結變數窺探Oracle變數