ORACLE 綁定變量用法總結

之前對ORACLE中的變量一直沒個太清楚的認識，比如說使用：、&、&&、DEIFINE、VARIABLE……等等。今天正好閑下來，上網搜了搜相關的文章，彙總了一下，貼在這裡，友善學習。

==================================================================================

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

一個硬解析需要經解析,制定執行路徑,優化通路計劃等許多的步驟.硬解釋不僅僅耗費大量的cpu，更重要的是會占據重要的們闩（latch）資源，嚴重的影響系統的規模的擴大（即限制了系統的并發行），而且引起的問題不能通過增加記憶體條和cpu的數量來解決。之是以這樣是因為門闩是為了順序通路以及修改一些記憶體區域而設定的，這些記憶體區域是不能被同時修改。當一個sql語句送出後，oracle會首先檢查一下共享緩沖池（shared pool）裡有沒有與之完全相同的語句，如果有的話隻須執行軟分析即可，否則就得進行硬分析。

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

sqlplus中如何使用綁定變量，可以通過variable來定義

[c-sharp] view plaincopy

SQL> select * from tt where id=1;

ID NAME

---------- ----------------------------------------

1 test

SQL> select * from tt where id=2;

2 test

SQL> variable i number;

SQL> exec :i :=1;

PL/SQL 過程已成功完成。

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

SQL> exec :i :=2;

SQL> print i;

----------

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

前兩天看到有人在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

連接配接到:

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;

---------- ----------

1 a

2 a

3 "abc"

SQL> define a

SP2-0135: 符号 a 未定義

SQL> define a=1

With the Partitioning, OLAP, Data Mining and Real Application Testing options" (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

SQL> select * from tt where id=&&a;

原值 1: select * from tt where id=&&a

SQL> define b='a';

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'

SQL> select * from tt where name='&b';

原值 1: select * from tt where name='&b'

--執行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'

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 /

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')

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> 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)

5 and b.name like '%parse%'

6 /

159 330 345 parse count (total)

159 331 167 parse count (hard)

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

過程中的參數會自動轉化為綁定變量

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> exec proc_test(200,'test');

--trace file：

alter session set sql_trace=true

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;

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 )

PARSE #6:c=0,e=246,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=7403001286

另外也可以直覺的觀察：

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

在sqlplus裡執行過程不能觀察出來

下面在plsql developer執行一次過程之後再來看執行的情況

begin -- Call the procedure proc_test(p_id =>:p_id, p_name =>:p_name); end;

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

看出來：

-- Call the procedure

proc_test(p_id => :p_id,

p_name => :p_name);

在輸入參數清單框上面的執行文法就是這樣的。

在動态sql中使用綁定變量，動态sql中使用綁定變量非常明顯也容易了解，下面給出2個簡單的例子

SQL> set serveroutput on

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;

3 v_id tt.id%type;

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' ;

SQL> select * from tt where id=1000;

ID NAME

1000 test

=============================下面加上一些其他變量的使用方法=========================

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行。

1 select xh 學号,xm 姓名,avg(cj) as 平均成績

2* from system.xs_xkb group by xh,xm

學号姓名平均成績

------ -------- ----------

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;

輸入 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='離散數學'

原值 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 變量名清除

1 select xs.xh,&name,kcm,&&column

5* order by &column

原值 1: select xs.xh,&name,kcm,&&column

原值 5: order by &column

已選擇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

eg004

ACCEPT variable[datatype[NUMBER|CHAR|DATE]][FORMAT format][PROMPT text][HIDE]

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

原值 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

061204 計算機基礎 91

061110 計算機基礎 95

已選擇13行。

ORACLE 綁定變量用法總結

繼續閱讀

Python中變量作用域

MicroPython-GPS開發闆控制5110顯示溫度濕度亮度

從零開始前端學習[48]:js中的變量和this的初步認識

c#中combox綁定datatable資料源

PHP如何判斷變量為空

Go語言入門（二）Go語言中的變量、常量、資料類型、流程控制以及函數

批處理(bat)腳本語言---SET指令和變量使用

JAVA基礎05——變量，常量以及作用域JAVA基礎05——變量，常量以及作用域

四、Python學習（一）可愛的變量

使用ReactiveCocoa初探MVVM

scala 中的變量一、變量

PHP中變量的概念及命名規則變量

c++指針（二）——定義變量時的記憶體配置設定

golang~變量、資料類型概覽變量資料類型

PHP中變量類型之特殊類型：資源類型、空類型

018-變量，值和指針