oracle绑定变量使用方法总结

转自：https://blog.csdn.net/gumengkai/article/details/53130449

在Oracle中，对于一个提交的sql语句，存在两种可选的解析过程，硬解析和软解析。

一个硬解析需要经解析，制定执行路径，优化访问计划等步骤。硬解析不仅仅会耗费大量的cpu，更重要的是会占据重要的闩(latch)资源。唯一使得oracle能够重复利用执行计划的方法就是采用绑定变量。绑定变量的实质就是使用变量来代替sql语句中的常量。绑定变量能够使得每次提交的sql语句都完全一样。

1. sqlplus中使用variable来定义

[sql]  view plain  copy

1. SQL> select * from t where id=1;  
2.   
3.         ID NAME  
4. ---------- --------------------------------  
5.          1 test  
6.   
7. SQL> select * from t where id=2;  
8.   
9.         ID NAME  
10. ---------- --------------------------------  
11.          2 test2  
12.   
13. SQL> variable i number;  
14. SQL> exec :i :=1;  
15.   
16. PL/SQL procedure successfully completed.  
17.   
18. SQL> select * from t where id=:i;  
19.   
20.         ID NAME  
21. ---------- --------------------------------  
22.          1 test  
23.   
24. SQL> exec :i :=2;  
25.   
26. PL/SQL procedure successfully completed.  
27.   
28. SQL> select * from t where id=:i;  
29.   
30.         ID NAME  
31. ---------- --------------------------------  
32.          2 test2  
33. SQL> select sql_text,parse_calls from v$sql where sql_text like 'select * from t where id=%';  
34.   
35. SQL_TEXT  
36. --------------------------------------------------------------------------------  
37. PARSE_CALLS  
38. -----------  
39. select * from t where id=2  
40.           1  
41.   
42. select * from t where id=1  
43.           1  
44.   
45. select * from t where id=:i  --可以看到这条sql被调用了两次，这两次的使用就包括了一次soft parse  

2.  (误区)sqlplus中通过define定义的并不是变量，而只是字符常量，define定义之后，再通过&或&&引用的时候就不需要再输入了，oracle在执行的时候回自动用定义的值进行替换，仅此而已，并不是绑定变量。

[sql]  view plain  copy

1. SQL> define a=1  
2. SQL> define  
3. DEFINE _DATE           = "30-OCT-16" (CHAR)  
4. DEFINE _CONNECT_IDENTIFIER = "ORCL" (CHAR)  
5. DEFINE _USER           = "SYS" (CHAR)  
6. DEFINE _PRIVILEGE      = "AS SYSDBA" (CHAR)  
7. DEFINE _SQLPLUS_RELEASE = "1102000400" (CHAR)  
8. DEFINE _EDITOR         = "ed" (CHAR)  
9. DEFINE _O_VERSION      = "Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production  
10. With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR)  
11. DEFINE _O_RELEASE      = "1102000400" (CHAR)  
12. DEFINE A               = "1" (CHAR)  
13. SQL> select * from t where id=&a;  
14. old   1: select * from t where id=&a  
15. new   1: select * from t where id=1  
16.   
17.         ID NAME  
18. ---------- --------------------------------  
19.          1 test  

&&和&一样的功能，不过&&替代过一次之后就不需要再输入了，可以多次替代。

[sql]  view plain  copy

1. SQL> select * from t where id=&b;  
2. Enter value for b: 2  
3. old   1: select * from t where id=&b  
4. new   1: select * from t where id=2  
5.   
6.         ID NAME  
7. ---------- --------------------------------  
8.          2 test2  
9.   
10. SQL> select * from t where id=&b;   
11. Enter value for b: 2  
12. old   1: select * from t where id=&b  
13. new   1: select * from t where id=2  
14.   
15.         ID NAME  
16. ---------- --------------------------------  
17.          2 test2  
18.   
19. SQL> select * from t where id=&&b;  
20. Enter value for b: 2  
21. old   1: select * from t where id=&&b  
22. new   1: select * from t where id=2  
23.   
24.         ID NAME  
25. ---------- --------------------------------  
26.          2 test2  
27.   
28. SQL> select * from t where id=&&b;  
29. old   1: select * from t where id=&&b  
30. new   1: select * from t where id=2  
31.   
32.         ID NAME  
33. ---------- --------------------------------  
34.          2 test2  

另外，如果define定义的是字符类型，在引用时需要加上单引号

[sql]  view plain  copy

1. SQL> select * from t where name=&c;  
2. old   1: select * from t where name=&c  
3. new   1: select * from t where name=test  
4. select * from t where name=test  
5.                            *  
6. ERROR at line 1:  
7. ORA-00904: "TEST": invalid identifier  
8.   
9.   
10. SQL> select * from t where name='&c';  
11. old   1: select * from t where name='&c'  
12. new   1: select * from t where name='test'  
13.   
14.         ID NAME  
15. ---------- --------------------------------  
16.          1 test  

可以看到，在执行sql的时候oracle自动进行了替换

[sql]  view plain  copy

1. SQL> select sql_text from v$sql where sql_text like 'select * from t where name=%';  
2.   
3. SQL_TEXT  
4. --------------------------------------------------------------------------------  
5. select * from t where name='test'  

3. oracle在解析sql时会把plsql中定义的变量转为绑定变量

[sql]  view plain  copy

1. SQL> create table tt(id int,name varchar2(10));  
2.   
3. Table created.  
4.   
5. SQL> alter session set sql_trace=true;  
6.   
7. Session altered.  
8.   
9. SQL> declare  
10.   2  begin  
11.   3  for i in 1 .. 100 loop  
12.   4  insert into tt values(i,'test');  
13.   5  end loop;  
14.   6  commit;  
15.   7  end;  
16.   8  /  
17.   
18. PL/SQL procedure successfully completed.  
19.   
20. SQL> alter session set sql_trace=false;  
21.   
22. Session altered.  

trace文件内容：

[sql]  view plain  copy

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

从硬解析的增长也可以看出：

[sql]  view plain  copy

1. SQL> select a.*,b.name   
2.   
3.   2   from v$sesstat a , v$statname b  
4.   3   where a.statistic#=b.statistic#  
5.   4   and a.sid=(select distinct sid from v$mystat)  
6.   5   and b.name like '%parse%';  
7.   
8. SQL> col name format a30  
9. SQL> /  
10.   
11.        SID STATISTIC#      VALUE NAME  
12. ---------- ---------- ---------- ------------------------------  
13.         29        264          0 ADG parselock X get attempts  
14.         29        265          0 ADG parselock X get successes  
15.         29        622          4 parse time cpu  
16.         29        623          8 parse time elapsed  
17.         29        624        238 parse count (total)  
18.         29        625        155 parse count (hard)  
19.         29        626          0 parse count (failures)  
20.         29        627          0 parse count (describe)  
21.   
22. 8 rows selected.  

执行前的硬解析数为155

[sql]  view plain  copy

1. SQL> declare  
2.   2  begin  
3.   3  for i in 1 .. 100 loop  
4.   4  insert into tt values(i,'test');  
5.   5  end loop;  
6.   6  commit;  
7.   7  end;  
8.   8  /  
9.   
10. PL/SQL procedure successfully completed.  
11.   
12. SQL> select a.*,b.name  
13.   2   from v$sesstat a , v$statname b  
14.   3   where a.statistic#=b.statistic#  
15.   4   and a.sid=(select distinct sid from v$mystat)  
16.   5   and b.name like '%parse%';  
17.   
18.        SID STATISTIC#      VALUE NAME  
19. ---------- ---------- ---------- ------------------------------  
20.         29        264          0 ADG parselock X get attempts  
21.         29        265          0 ADG parselock X get successes  
22.         29        622          4 parse time cpu  
23.         29        623          8 parse time elapsed  
24.         29        624        242 parse count (total)  
25.         29        625        157 parse count (hard)  
26.         29        626          0 parse count (failures)  
27.         29        627          0 parse count (describe)  
28.   
29. 8 rows selected.  

执行后的为157，只增长了两个，如果不是使用了绑定变量，硬解析数绝对不止两个

4. 存储过程中的参数会自动转化为绑定变量

[sql]  view plain  copy

1. SQL> create or replace procedure proc_test(p_id int,p_name varchar2)  
2.   2  is  
3.   3  begin  
4.   4  insert into tt values(p_id,p_name);  
5.   5  commit;  
6.   6  end;  
7.   7  /  
8.   
9. Procedure created.  
10.   
11. SQL> alter session set sql_trace=true;  
12.   
13. Session altered.  
14.   
15. SQL> exec proc_test(200,'test');  
16.   
17. PL/SQL procedure successfully completed.  
18.   
19. SQL> alter session set sql_trace=false;  
20.   
21. Session altered.  

trace文件内容：

[sql]  view plain  copy

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

其实从存储过程的调用过程也可以看出是使用了绑定变量

[sql]  view plain  copy

1. begin  
2. -- Call the procedure  
3. proc_test(p_id => :p_id,  
4. p_name => :p_name);  
5. end;  

5. 动态sql中使用绑定变量

a. 直接使用游标中的值拼接

[sql]  view plain  copy

1. [oracle@centos6 scripts]$ cat sql_parse1.sql  
2. declare  
3. cursor test_cur is select id,name from tt;  
4. begin  
5. for i in test_cur loop  
6. execute immediate 'insert into tt values('||i.id||','||chr(39)||i.name||chr(39)||')';  
7. end loop;  
8. commit;  
9. end;  

这样直接使用游标中的值拼接是属于非绑定变量，为硬解析

[sql]  view plain  copy

1. SQL> alter system flush shared_pool;  
2.   
3. System altered.  
4.   
5. SQL> @sql_parse1.sql  
6.   
7. PL/SQL procedure successfully completed.  
8.   
9. SQL> set linesize 200  
10. SQL> col hash_value format 9999999999  
11. SQL> col sql_id format 99  
12. SQL> col child_latch format 99  
13. SQL> col version_count format 99  
14. SQL> col sql_text format a40  
15. SQL> col parse_calls format 999  
16. SQL> select hash_value,sql_id,child_latch,version_count,sql_text,parse_calls from v$sqlarea where sql_text like 'insert into tt%';  
17.  HASH_VALUE SQL_ID        CHILD_LATCH VERSION_COUNT          SQL_TEXT                                           PARSE_CALLS  
18. ----------- ------------- ----------- ------------- -------------------------------------------------- -----------  
19.  3161064081 196c4s2y6n0nj           0             1 insert into tt values(45,'test')                             1  
20.  3718124844 6hfpagbftw59c           0             1 insert into tt values(70,'test')                             1  
21.  4046592725 c5txty7sm46qp           0             1 insert into tt values(28,'test')                             1  
22.   961289967 4n0n3dnwns7rg           0             1 insert into tt values(30,'test')                             1  
23.  2124685404 g70mmhxza882w           0             1 insert into tt values(26,'test')                             1  
24.   608576974 3nm07v4k4c9ff           0             1 insert into tt values(31,'test')                             1  
25.  3770952793 2xcry8ghc8b2t           0             1 insert into tt values(1,'test')                              1  

b. 绑定变量写法

[sql]  view plain  copy

1. [oracle@centos6 scripts]$ cat sql_parse2.sql  
2. declare  
3. cursor test_cur is select id,name from tt;  
4. begin  
5. for i in test_cur loop  
6. execute immediate 'insert into tt values(:a,:b)' using i.id,i.name;  
7. end loop;  
8. commit;  
9. end;  
10. /  
11.   
12. SQL> alter system flush shared_pool;  
13.   
14. System altered.  
15.   
16. SQL> @sql_parse2.sql  
17.   
18. PL/SQL procedure successfully completed.  
19.   
20. SQL> select hash_value,sql_id,child_latch,version_count,sql_text,parse_calls from v$sqlarea where sql_text like 'insert into tt%';  
21.   
22.  HASH_VALUE SQL_ID        CHILD_LATCH VERSION_COUNT SQL_TEXT                                           PARSE_CALLS  
23. ----------- ------------- ----------- ------------- -------------------------------------------------- -----------  
24.  2034333845 gbkazctwn2y4p           0             1 insert into tt values(:a,:b)                                 1