http://blog.csdn.net/wh62592855/article/details/4778343
之前對ORACLE中的變量一直沒個太清楚的認識，比如說使用：、&、&&、DEIFINE、VARIABLE……等等。今天正好閑下來，上網搜了搜相關的文章，匯總了一下，貼在這里，方便學習。
?
==================================================================================
?
?在oracle 中，對于一個提交的sql語句,存在兩種可選的解析過程, 一種叫做硬解析,一種叫做軟解析.
一個硬解析需要經解析,制定執行路徑,優化訪問計劃等許多的步驟.硬解釋不僅僅耗費大量的cpu，更重要的是會占據重要的們閂（latch）資源，嚴重的影響系統的規模的擴大（即限制了系統的并發行），而且引起的問題不能通過增加內存條和cpu的數量來解決。之所以這樣是因為門閂是為了順序訪問以及修改一些內存區域而設置的，這些內存區域是不能被同時修改。當一個sql語句提交后，oracle會首先檢查一下共享緩沖池（shared pool）里有沒有與之完全相同的語句，如果有的話只須執行軟分析即可，否則就得進行硬分析。
?而唯一使得oracle 能夠重復利用執行計劃的方法就是采用綁定變量。綁定變量的實質就是用于替代sql語句中的常量的替代變量。綁定變量能夠使得每次提交的sql語句都完全一樣。
?
1.
sqlplus 中如何使用綁定變量，可以通過variable來定義
[c-sharp] view plaincopyprint?
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>??
SQL> select * from tt where id=1;ID NAME---------- ----------------------------------------1 testSQL> select * from tt where id=2;ID NAME---------- ----------------------------------------2 testSQL> variable i number;SQL> exec :i :=1;PL/SQL 過程已成功完成。SQL> select *from tt where id=:i;ID NAME---------- ----------------------------------------1 testSQL> exec :i :=2;PL/SQL 過程已成功完成。SQL> select *from tt where id=:i;ID NAME---------- ----------------------------------------2 testSQL> print i;I----------2SQL> select sql_text,parse_calls from v$sql where sql_text like 'select * from tt where id=%';SQL_TEXT PARSE_CALLS------------------------------------------------------------ -----------select * from tt where id=2 1select * from tt where id=1 1select * from tt where id=:i 2SQL>
2.
前兩天看到有人在pub上問在 sqlplus 定義的變量的區別 理解不是變量而是字符常量，通過define定義之后，在通過&或者&&引用的時候不需要輸入了，僅此而已。 oracle 在執行的時候自動用值進行了替換；而variable定義的是綁定變量,上面已經提到。
[c-sharp] view plaincopyprint?
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>??
C:>sqlplus xys/managerSQL*Plus: Release 11.1.0.6.0 - Production on 星期二 4月 1 14:03:00 2008Copyright (c) 1982, 2007, Oracle. All rights reserved.連接到:Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> defineDEFINE _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 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR)DEFINE _O_RELEASE = "1101000600" (CHAR)SQL> select *from tt;ID NAME---------- ----------1 a2 a3 "abc"SQL> define aSP2-0135: 符號 a 未定義SQL> define a=1SQL> defineDEFINE _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 - ProductionWith 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=1ID NAME---------- ----------1 aSQL> select * from tt where id=&&a;原值 1: select * from tt where id=&&a新值 1: select * from tt where id=1ID NAME---------- ----------1 aSQL> define b='a';SQL> defineDEFINE _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 - ProductionWith 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=aselect * 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 a2 aSQL> select * from tt where name='&b';原值 1: select * from tt where name='&b'新值 1: select * from tt where name='a'ID NAME---------- ----------1 a2 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=1select * from tt where name='a'SQL>
3.
oracle
[c-sharp] view plaincopyprint?
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?? =====================??
SQL> create table tt(id int , name varchar2(10));表已創建。SQL> alter session set sql_trace=true;會話已更改。SQL> declare2 begin3 for i in 1..100 loop4 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'declarebeginfor i in 1..100 loopinsert into tt values(i,'test');end loop;commit;end;END OF STMTPARSE #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 STMTPARSE #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中定義的變量轉換為綁定變量處理
[c-sharp] view plaincopyprint?
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>??
SQL> connect /as sysdba已連接。SQL> shutdown immediate數據庫已經關閉。已經卸載數據庫。ORACLE 例程已經關閉。SQL> startupORACLE 例程已經啟動。Total System Global Area 167772160 bytesFixed Size 1247900 bytesVariable Size 67110244 bytesDatabase Buffers 96468992 bytesRedo Buffers 2945024 bytes數據庫裝載完畢。數據庫已經打開。SQL> connect xys/manager已連接。SQL> drop table tt;表已刪除。SQL> create table tt(id int , name varchar2(10));表已創建。SQL> col name format a30SQL> select a.*,b.name2 from v$sesstat a , v$statname b3 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 cpu159 329 74 parse time elapsed159 330 339 parse count (total)159 331 165 parse count (hard)159 332 0 parse count (failures)SQL> declare2 begin3 for i in 1..100 loop4 insert into tt values(i,'test');5 end loop;6 commit;7 end;8 /PL/SQL 過程已成功完成。SQL> select a.*,b.name2 from v$sesstat a , v$statname b3 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 cpu159 329 74 parse time elapsed159 330 345 parse count (total)159 331 167 parse count (hard)159 332 0 parse count (failures)SQL>
這里發現hard parse只增加了2，如果沒有使用綁定變量的話，相信hard parse會更多
4.
過程中的參數會自動轉化為綁定變量
[c-sharp] view plaincopyprint?
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> edit已寫入 file afiedt.buf1 create or replace procedure proc_test(p_id int, p_name varchar2)2 is3 begin4 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=trueEND OF STMTEXEC #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 STMTPARSE #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 STMTPARSE #6:c=0,e=246,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=7403001286=====================
另外也可以直觀的觀察：
[c-sharp] view plaincopyprint?
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>??
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執行一次過程之后再來看執行的情況
[c-sharp] view plaincopyprint?
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>??
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中執行過程時的語法就能 看出來：
[c-sharp] view plaincopyprint?
begin?? --?Call?the?procedure?? proc_test(p_id?=>?:p_id,?? p_name?=>?:p_name);?? end;??
begin-- Call the procedureproc_test(p_id => :p_id,p_name => :p_name);end;
5.
在動態 中使用綁定變量
[c-sharp] view plaincopyprint?
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>??
SQL> set serveroutput onSQL> 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 testPL/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 testSQL>?
=============================下面加上一些其他變量的使用方法========================= ?
eg001
[c-sharp] view plaincopyprint?
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??
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,xmSQL> /學號 姓名 平均成績------ -------- ----------061103 王燕 71061210 李紅慶 76061110 張蔚 91.3333333061220 吳薇華 82061104 韋嚴平 79.6666667061101 王林 78061204 馬林林 91061106 李方方 72061218 孫研 70061102 程明 78061241 羅林琳 90學號 姓名 平均成績------ -------- ----------061111 趙琳 80.5061109 張強民 76.5061216 孫祥欣 81061221 劉燕敏 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>=90SQL> 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 cjXH XM KCM CJ------ -------- ---------------- ----------061104 韋嚴平 離散數學 65061109 張強民 離散數學 70061101 王林 離散數學 76061102 程明 離散數學 78061106 李方方 離散數學 80061103 王燕 離散數學 81061110 張蔚 離散數學 89??
eg002
[c-sharp] view plaincopyprint?
--&&替換變量系統一直用同一個值處理，清除用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行。??
--&&替換變量系統一直用同一個值處理，清除用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 &columnSQL> /輸入 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 cjXH XM KCM CJ------ -------- ---------------- ----------061104 韋嚴平 離散數學 65061109 張強民 離散數學 70061101 王林 離散數學 76061102 程明 離散數學 78061106 李方方 離散數學 80061103 王燕 離散數學 81061110 張蔚 離散數學 89已選擇7行。 eg003
DEFINE[variable[=value]]
[c-sharp] view plaincopyprint?
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行。??
SQL> define specialty=通信工程SQL> define specialtyDEFINE 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 40061210 李紅慶 女 01-5月 -85 44061201 王敏 男 10-6月 -84 42061203 王玉民 男 26-3月 -86 42061204 馬林林 女 10-2月 -84 42061206 李計 女 20-9月 -85 42061216 孫祥欣 女 09-3月 -84 42061218 孫研 男 09-10月-86 42061220 吳薇華 女 18-3月 -86 42061221 劉燕敏 女 12-11月-85 42061241 羅林琳 女 30-1月 -86 50已選擇11行。
eg004
[c-sharp] view plaincopyprint?
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行。?? ?? SQL>??
?
總結
以上是生活随笔 為你收集整理的ORACLE 绑定变量用法总结 的全部內容，希望文章能夠幫你解決所遇到的問題。
如果覺得生活随笔 網站內容還不錯，歡迎將生活随笔 推薦給好友。
