ORACLE 繫結變數用法總結
之前對ORACLE中的變數一直沒個太清楚的認識，比如說使用：、&、&&、DEIFINE、VARIABLE……等等。今天正好閒下來，上網搜了搜相關的文章，彙總了一下，貼在這裡，方便學習。
==================================================================================
在oracle 中，對於一個提交的sql語句,存在兩種可選的解析過程, 一種叫做硬解析,一種叫做軟解析.
一個硬解析需要經解析,制定執行路徑,最佳化訪問計劃等許多的步驟.硬解釋不僅僅耗費大量的cpu，更重要的是會佔據重要的們閂（latch）資源，嚴重的影響系統的規模的擴大（即限制了系統的併發行），而且引起的問題不能透過增加記憶體條和cpu的數量來解決。之所以這樣是因為門閂是為了順序訪問以及修改一些記憶體區域而設定的，這些記憶體區域是不能被同時修改。當一個sql語句提交後，oracle會首先檢查一下共享緩衝池（shared pool）裡有沒有與之完全相同的語句，如果有的話只須執行軟分析即可，否則就得進行硬分析。
而唯一使得oracle 能夠重複利用執行計劃的方法就是採用繫結變數。繫結變數的實質就是用於替代sql語句中的常量的替代變數。繫結變數能夠使得每次提交的sql語句都完全一樣。
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>
=============================下面加上一些其他變數的使用方法=========================
eg001（&替換變數）
- SQL> select xh,xm from system.xs where zym='&zym';
- 輸入 zym 的值: 計算機
- 原值 1: select xh,xm from system.xs where zym='&zym'
- 新值 1: select xh,xm from system.xs where zym='計算機'
- XH XM
- ------ --------
- 061101 王林
- 061102 程明
- 061103 王燕
- 061104 韋嚴平
- 061106 李方方
- 061107 李明
- 061108 林一帆
- 061109 張強民
- 061110 張蔚
- 061111 趙琳
- 061113 嚴紅
- 已選擇11行。
- SQL> edit
- 已寫入 file afiedt.buf
- 1 select xh 學號,xm 姓名,avg(cj) as 平均成績
- 2* from system.xs_xkb group by xh,xm
- SQL> /
- 學號 姓名 平均成績
- ------ -------- ----------
- 061103 王燕 71
- 061210 李紅慶 76
- 061110 張蔚 91.3333333
- 061220 吳薇華 82
- 061104 韋嚴平 79.6666667
- 061101 王林 78
- 061204 馬林林 91
- 061106 李方方 72
- 061218 孫研 70
- 061102 程明 78
- 061241 羅林琳 90
- 學號 姓名 平均成績
- ------ -------- ----------
- 061111 趙琳 80.5
- 061109 張強民 76.5
- 061216 孫祥欣 81
- 061221 劉燕敏 79
- 已選擇15行。
- SQL> select * from system.xs_xkb where cj>=&cj; /*替換變數可以使用WHERE子句；ORDER BY子句；列表示式；表名；整個SELECT語句*/
- 輸入 cj 的值: 90
- 原值 1: select * from system.xs_xkb where cj>=&cj
- 新值 1: select * from system.xs_xkb where cj>=90
- SQL> select xs.xh,&name,kcm,&column
- 2 from system.xs,&kc,system.xs_kc
- 3 where xs.xh=xs_kc.xh and &condition
- 4 and kcm=&kcm
- 5 order by & column;
- 輸入 name 的值: xm
- 輸入 column 的值: cj
- 原值 1: select xs.xh,&name,kcm,&column
- 新值 1: select xs.xh,xm,kcm,cj
- 輸入 kc 的值: system.kc
- 原值 2: from system.xs,&kc,system.xs_kc
- 新值 2: from system.xs,system.kc,system.xs_kc
- 輸入 condition 的值: kc.kch=xs_kc.kch
- 原值 3: where xs.xh=xs_kc.xh and &condition
- 新值 3: where xs.xh=xs_kc.xh and kc.kch=xs_kc.kch
- 輸入 kcm 的值: '離散數學'
- 原值 4: and kcm=&kcm
- 新值 4: and kcm='離散數學'
- 輸入 column 的值: cj
- 原值 5: order by & column
- 新值 5: order by cj
- XH XM KCM CJ
- ------ -------- ---------------- ----------
- 061104 韋嚴平 離散數學 65
- 061109 張強民 離散數學 70
- 061101 王林 離散數學 76
- 061102 程明 離散數學 78
- 061106 李方方 離散數學 80
- 061103 王燕 離散數學 81
- 061110 張蔚 離散數學 89
eg002（&&替換變數）
- --&&替換變數系統一直用同一個值處理，清除用undefine 變數名清除
- SQL> edit
- 已寫入 file afiedt.buf
- 1 select xs.xh,&name,kcm,&&column /*清除替換變數（undefine column）*/
- 2 from system.xs,&kc,system.xs_kc
- 3 where xs.xh=xs_kc.xh and &condition
- 4 and kcm=&kcm
- 5* order by &column
- SQL> /
- 輸入 name 的值: xm
- 輸入 column 的值: cj
- 原值 1: select xs.xh,&name,kcm,&&column
- 新值 1: select xs.xh,xm,kcm,cj
- 輸入 kc 的值: system.kc
- 原值 2: from system.xs,&kc,system.xs_kc
- 新值 2: from system.xs,system.kc,system.xs_kc
- 輸入 condition 的值: kc.kch=xs_kc.kch
- 原值 3: where xs.xh=xs_kc.xh and &condition
- 新值 3: where xs.xh=xs_kc.xh and kc.kch=xs_kc.kch
- 輸入 kcm 的值: '離散數學'
- 原值 4: and kcm=&kcm
- 新值 4: and kcm='離散數學'
- 原值 5: order by &column /*使用&&替換變數的好處,相同變數只輸第一次就OK*/
- 新值 5: order by cj
- XH XM KCM CJ
- ------ -------- ---------------- ----------
- 061104 韋嚴平 離散數學 65
- 061109 張強民 離散數學 70
- 061101 王林 離散數學 76
- 061102 程明 離散數學 78
- 061106 李方方 離散數學 80
- 061103 王燕 離散數學 81
- 061110 張蔚 離散數學 89
- 已選擇7行。
eg003
DEFINE[variable[=value]]
UNDEFINE清除定義的變數
- SQL> define specialty=通訊工程
- SQL> define specialty
- DEFINE SPECIALTY = "通訊工程" (CHAR)
- SQL> select xh,xm,xb,cssj,zxf from system.xs
- 2 where zym='&specialty';
- XH XM XB CSSJ ZXF
- ------ -------- -- -------------- ----------
- 061202 王林 男 29-10月-85 40
- 061210 李紅慶 女 01-5月 -85 44
- 061201 王敏 男 10-6月 -84 42
- 061203 王玉民 男 26-3月 -86 42
- 061204 馬林林 女 10-2月 -84 42
- 061206 李計 女 20-9月 -85 42
- 061216 孫祥欣 女 09-3月 -84 42
- 061218 孫研 男 09-10月-86 42
- 061220 吳薇華 女 18-3月 -86 42
- 061221 劉燕敏 女 12-11月-85 42
- 061241 羅林琳 女 30-1月 -86 50
- 已選擇11行。
eg004
ACCEPT variable[datatype[NUMBER|CHAR|DATE]][FORMAT format][PROMPT text][HIDE]/*variable：指定接收值的變數。該名稱的變數不存在，那麼SQL重建該變數；datatype:變數資料型別，預設為CHAR*/
- SQL> accept num prompt'請輸入課程號:'
- 請輸入課程號:101
- SQL> set verify on
- SQL>
- 1 select xh,kcm,cj from system.xs_kc,system.kc
- 2 where xs_kc.kch=kc.kch and kc.kch='&num'
- 3* order by cj
- SQL> /
- 原值 2: where xs_kc.kch=kc.kch and kc.kch='&num'
- 新值 2: where xs_kc.kch=kc.kch and kc.kch='101'
- XH KCM CJ
- ------ ---------------- ----------
- 061103 計算機基礎 62
- 061106 計算機基礎 65
- 061218 計算機基礎 70
- 061210 計算機基礎 76
- 061221 計算機基礎 79
- 061101 計算機基礎 80
- 061216 計算機基礎 81
- 061220 計算機基礎 82
- 061241 計算機基礎 90
- 061104 計算機基礎 90
- 061111 計算機基礎 91
- XH KCM CJ
- ------ ---------------- ----------
- 061204 計算機基礎 91
- 061110 計算機基礎 95
- 已選擇13行。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29913993/viewspace-1330235/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【ORACLE】Oracle繫結變數知識梳理Oracle變數
- 在繫結變數下使用outline變數
- MySQL高階特性——繫結變數MySql變數
- [20180930]in list與繫結變數.txt變數
- [20180912]PLSLQ與繫結變數.txt變數
- Vue select 繫結動態變數Vue變數
- Oracle臨時表的用法總結FLOracle
- 總結Sass 變數變數
- Oracle中如何查詢未使用繫結變數的SQL語句？Oracle變數SQL
- Oracle資料傾斜導致的問題-無繫結變數Oracle變數
- Oracle資料傾斜導致的問題-有繫結變數Oracle變數
- [20180930]in list與繫結變數個數.txt變數
- [20210120]in list與繫結變數個數.txt變數
- 繫結變數窺視測試案例變數
- oracle 11g 新特性之動態繫結變數窺視(一)Oracle變數
- oracle 11g 新特性之動態繫結變數窺視(二)Oracle變數
- 【CURSOR】Oracle繫結變數、執行計劃對遊標的影響Oracle變數
- Promise用法總結Promise
- layui用法總結UI
- axios用法總結iOS
- less用法總結
- pandas用法總結
- Const 用法總結
- [20200326]繫結變數抓取與NULL值.txt變數Null
- 如何用FGA得到繫結變數的值變數
- 如何在對in操作使用變數繫結(轉)變數
- [20220414]toad與繫結變數peek.txt變數
- [20231210]執行計劃與繫結變數.txt變數
- [20190506]檢視巢狀與繫結變數.txt巢狀變數
- STL:vector用法總結
- STL:list用法總結
- STL:set用法總結
- STL:map用法總結
- xPath 用法總結整理
- Quartz：基本用法總結quartz
- jq命令用法總結
- OpenMP 環境變數使用總結變數
- Oracle面對“資料傾斜列使用繫結變數”場景的解決方案Oracle變數
- Oracle特性總結Oracle