oracle綁定變數使用方法總結__oracle

在Oracle中，對於一個提交的sql語句，存在兩種可選的解析過程，硬解析和軟解析。

一個硬解析需要經解析，制定執行路徑，最佳化訪問計劃等步驟。硬解析不僅僅會耗費大量的cpu，更重要的是會佔據重要的閂(latch)資源。唯一使得oracle能夠重複利用執行計畫的方法就是採用綁定變數。綁定變數的實質就是使用變數來代替sql語句中的常量。綁定變數能夠使得每次提交的sql語句都完全一樣。

1. sqlplus中使用variable來定義

SQL> select * from t where id=1;        ID NAME---------- --------------------------------         1 testSQL> select * from t where id=2;        ID NAME---------- --------------------------------         2 test2SQL> variable i number;SQL> exec :i :=1;PL/SQL procedure successfully completed.SQL> select * from t where id=:i;        ID NAME---------- --------------------------------         1 testSQL> exec :i :=2;PL/SQL procedure successfully completed.SQL> select * from t where id=:i;        ID NAME---------- --------------------------------         2 test2SQL> select sql_text,parse_calls from v$sql where sql_text like 'select * from t where id=%';SQL_TEXT--------------------------------------------------------------------------------PARSE_CALLS-----------select * from t where id=2          1select * from t where id=1          1select * from t where id=:i  --可以看到這條sql被調用了兩次，這兩次的使用就包括了一次soft parse

2.  (誤區)sqlplus中通過define定義的並不是變數，而只是字元常量，define定義之後，再通過&或&&引用的時候就不需要再輸入了，oracle在執行的時候回自動用定義的值進行替換，僅此而已，並不是綁定變數。

SQL> define a=1SQL> defineDEFINE _DATE           = "30-OCT-16" (CHAR)DEFINE _CONNECT_IDENTIFIER = "ORCL" (CHAR)DEFINE _USER           = "SYS" (CHAR)DEFINE _PRIVILEGE      = "AS SYSDBA" (CHAR)DEFINE _SQLPLUS_RELEASE = "1102000400" (CHAR)DEFINE _EDITOR         = "ed" (CHAR)DEFINE _O_VERSION      = "Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR)DEFINE _O_RELEASE      = "1102000400" (CHAR)DEFINE A               = "1" (CHAR)SQL> select * from t where id=&a;old   1: select * from t where id=&anew   1: select * from t where id=1        ID NAME---------- --------------------------------         1 test

&&和&一樣的功能，不過&&替代過一次之後就不需要再輸入了，可以多次替代。

SQL> select * from t where id=&b;Enter value for b: 2old   1: select * from t where id=&bnew   1: select * from t where id=2        ID NAME---------- --------------------------------         2 test2SQL> select * from t where id=&b; Enter value for b: 2old   1: select * from t where id=&bnew   1: select * from t where id=2        ID NAME---------- --------------------------------         2 test2SQL> select * from t where id=&&b;Enter value for b: 2old   1: select * from t where id=&&bnew   1: select * from t where id=2        ID NAME---------- --------------------------------         2 test2SQL> select * from t where id=&&b;old   1: select * from t where id=&&bnew   1: select * from t where id=2        ID NAME---------- --------------------------------         2 test2

另外，如果define定義的是字元類型，在引用時需要加上單引號

SQL> select * from t where name=&c;old   1: select * from t where name=&cnew   1: select * from t where name=testselect * from t where name=test                           *ERROR at line 1:ORA-00904: "TEST": invalid identifierSQL> select * from t where name='&c';old   1: select * from t where name='&c'new   1: select * from t where name='test'        ID NAME---------- --------------------------------         1 test

可以看到，在執行sql的時候oracle自動進行了替換

SQL> select sql_text from v$sql where sql_text like 'select * from t where name=%';SQL_TEXT--------------------------------------------------------------------------------select * from t where name='test'

3. oracle在解析sql時會把plsql中定義的變數轉為綁定變數

SQL> create table tt(id int,name varchar2(10));Table created.SQL> alter session set sql_trace=true;Session altered.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 procedure successfully completed.SQL> alter session set sql_trace=false;Session altered.

trace檔案內容：

*** 2016-10-30 12:11:22.815CLOSE #140170997623912:c=0,e=6,dep=0,type=0,tim=1477800682815427=====================PARSING IN CURSOR #140170997623912 len=92 dep=0 uid=0 oct=47 lid=0 tim=1477800682817922 hv=218581220 ad='8c89d9b0' sqlid='6pdgqjs6hfk74'declarebeginfor i in 1 .. 100 loopinsert into tt values(i,'test');end loop;commit;end;END OF STMTPARSE #140170997623912:c=1999,e=2431,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1477800682817921=====================PARSING IN CURSOR #140170996439488 len=34 dep=1 uid=0 oct=2 lid=0 tim=1477800682818383 hv=1299226876 ad='86bc23a8' sqlid='9j06ydd6r187w'INSERT INTO TT VALUES(:B1 ,'test')END OF STMT

從硬解析的增長也可以看出：

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%';SQL> col name format a30SQL> /       SID STATISTIC#      VALUE NAME---------- ---------- ---------- ------------------------------        29        264          0 ADG parselock X get attempts        29        265          0 ADG parselock X get successes        29        622          4 parse time cpu        29        623          8 parse time elapsed        29        624        238 parse count (total)        29        625        155 parse count (hard)        29        626          0 parse count (failures)        29        627          0 parse count (describe)8 rows selected.

執行前的硬解析數為155

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 procedure successfully completed.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---------- ---------- ---------- ------------------------------        29        264          0 ADG parselock X get attempts        29        265          0 ADG parselock X get successes        29        622          4 parse time cpu        29        623          8 parse time elapsed        29        624        242 parse count (total)        29        625        157 parse count (hard)        29        626          0 parse count (failures)        29        627          0 parse count (describe)8 rows selected.

執行後的為157，只增長了兩個，如果不是使用了綁定變數，硬解析數絕對不止兩個 4. 預存程序中的參數會自動轉化為綁定變數

SQL> 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;  7  /Procedure created.SQL> alter session set sql_trace=true;Session altered.SQL> exec proc_test(200,'test');PL/SQL procedure successfully completed.SQL> alter session set sql_trace=false;Session altered.

trace檔案內容：

*** 2016-10-31 04:11:23.421CLOSE #140585231805712:c=0,e=6,dep=0,type=0,tim=1477858283421964=====================PARSING IN CURSOR #140585231805712 len=35 dep=0 uid=0 oct=47 lid=0 tim=1477858283423073 hv=526484776 ad='86b57878' sqlid='asc6yd8gq3198'BEGIN proc_test(200,'test'); END;END OF STMTPARSE #140585231805712:c=999,e=1047,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1477858283423072=====================PARSING IN CURSOR #140585233135112 len=32 dep=1 uid=0 oct=2 lid=0 tim=1477858283423304 hv=1422618771 ad='8697d9b8' sqlid='1yqc845acqw4m'INSERT INTO TT VALUES(:B2 ,:B1 )END OF STMTPARSE #140585233135112:c=0,e=100,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=0,tim=1477858283423304

其實從預存程序的調用過程也可以看出是使用了綁定變數

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

5. 動態sql中使用綁定變數

a. 直接使用遊標中的值拼接

[oracle@centos6 scripts]$ cat sql_parse1.sqldeclarecursor test_cur is select id,name from tt;beginfor i in test_cur loopexecute immediate 'insert into tt values('||i.id||','||chr(39)||i.name||chr(39)||')';end loop;commit;end;

這樣直接使用遊標中的值拼接是屬於非綁定變數，為硬解析

SQL> alter system flush shared_pool;System altered.SQL> @sql_parse1.sqlPL/SQL procedure successfully completed.SQL> set linesize 200SQL> col hash_value format 9999999999SQL> col sql_id format 99SQL> col child_latch format 99SQL> col version_count format 99SQL> col sql_text format a40SQL> col parse_calls format 999SQL> select hash_value,sql_id,child_latch,version_count,sql_text,parse_calls from v$sqlarea where sql_text like 'insert into tt%'; HASH_VALUE SQL_ID        CHILD_LATCH VERSION_COUNT          SQL_TEXT                                           PARSE_CALLS----------- ------------- ----------- ------------- -------------------------------------------------- ----------- 3161064081 196c4s2y6n0nj           0             1 insert into tt values(45,'test')                             1 3718124844 6hfpagbftw59c           0             1 insert into tt values(70,'test')                             1 4046592725 c5txty7sm46qp           0             1 insert into tt values(28,'test')                             1  961289967 4n0n3dnwns7rg           0             1 insert into tt values(30,'test')                             1 2124685404 g70mmhxza882w           0             1 insert into tt values(26,'test')                             1  608576974 3nm07v4k4c9ff           0             1 insert into tt values(31,'test')                             1 3770952793 2xcry8ghc8b2t           0             1 insert into tt values(1,'test')                              1

b. 綁定變數寫法

[oracle@centos6 scripts]$ cat sql_parse2.sqldeclarecursor test_cur is select id,name from tt;beginfor i in test_cur loopexecute immediate 'insert into tt values(:a,:b)' using i.id,i.name;end loop;commit;end;/SQL> alter system flush shared_pool;System altered.SQL> @sql_parse2.sqlPL/SQL procedure successfully completed.SQL> select hash_value,sql_id,child_latch,version_count,sql_text,parse_calls from v$sqlarea where sql_text like 'insert into tt%'; HASH_VALUE SQL_ID        CHILD_LATCH VERSION_COUNT SQL_TEXT                                           PARSE_CALLS----------- ------------- ----------- ------------- -------------------------------------------------- ----------- 2034333845 gbkazctwn2y4p           0             1 insert into tt values(:a,:b)                                 1