oracle绑定变量使用方法总结

骑猪看日落 2022-05-29 06:22 481阅读 0赞

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

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

### 1. sqlplus中使用variable来定义 ###

**\[sql\]** [view plain][] [copy][view plain]

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][view plain]

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][view plain]

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][view plain]

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][view plain]

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][view plain]

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][view plain]

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][view plain]

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][view plain]

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][view plain]

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][view plain]

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][view plain]

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][view plain]

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][view plain]

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][view plain]

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

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

DML使用绑定变量  
\--1.INSERT插入语句  
PROCEDURE INSERT\_TEST\_BDING(I\_PARA1             IN    NUMBER,        
                          I\_PARA2             IN    VARCHAR2,      
                          I\_PARA3             IN    VARCHAR2,      
                          I\_PARA4             IN    VARCHAR2,      
                          R\_CODE              OUT   VARCHAR2,      --返回结果代码  
                          R\_MSG               OUT   VARCHAR2       --返回结果信息      
                          ) AS  
    V\_PARA1           NUMBER;  
    V\_PARA2           DATE;  
    V\_PARA3        TEST.PARA3%TYPE;  
    V\_PARA4           TEST.PARA4%TYPE;;  
    V\_ID NUMBER;            
    V\_SQL                 VARCHAR2(32767);          --存放SQL字符串        
  /\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
  \*\[ 功能描述 \]  INSERT操作多个入参绑定变量语法使用  
  \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*/    
 BEGIN  
    SAVEPOINT INSERT\_TEST\_BDING;  
    BEGIN  
          V\_PARA1    := I\_PARA1;  
          V\_PARA2    := TO\_DATE(I\_PARA2,'YYYY-MM-DD HH24:MI:SS');  
          V\_PARA3   := I\_PARA3;  
          V\_PARA4    := I\_PARA4;  
   EXCEPTION WHEN OTHERS THEN  
          R\_CODE := 'ERR1';  
          R\_MSG  := '参数转换错误'||SQLERRM;     
    END;    
    BEGIN  
SELECT NVL(MAX(ID),0)+1 INTO V\_ID FROM TEST;  
        --插入数据  
        V\_SQL  := 'INSERT INTO TEST(ID,CREATETIME,PARA1,PARA2,PARA3,PARA4)  
        VALUES (:1, :1, :1, :1, :1, :1)';  
          
        EXECUTE IMMEDIATE V\_SQL USING V\_ID,SYSDATE,V\_PARA1,V\_PARA2,V\_PARA3,V\_PARA4;  
             
           R\_CODE   := 'S00';  
           R\_MSG    := '跟进内容添加成功';  
    COMMIT;  
    EXCEPTION WHEN OTHERS THEN  
        ROLLBACK TO INSERT\_TEST\_BDING;  
        R\_CODE := 'ERR3';  
        R\_MSG  := '订单催收跟进详情，跟进内容与计划内容添加失败'||SQLERRM;  
    END;  
END INSERT\_TEST\_BDING;  
  
\--2.UPDATE 更新语句  
         V\_SQL := 'UPDATE TEST  
              SET PARA2 = :1,  
                  PARA3 = :2  
            WHERE PARA1 = V\_PARA1';  
           
         EXECUTE IMMEDIATE V\_SQL USING SYSDATE,V\_PARA3;  
\--3.DELETE 删除语句  
          V\_SQL := 'DELETE FROM TEST  
            WHERE PARA1 = :1  
              AND PARA3 = :2';  
            
          EXECUTE IMMEDIATE V\_SQL USING V\_PARA1,V\_PARA3;

[view plain]: http://blog.csdn.net/gumengkai/article/details/53130449#

发表评论取消回复

表情：

评论列表（有 0 条评论，481人围观）

还没有评论，来说两句吧...

相关阅读

相关 oracle绑定变量 java_Oracle 11g“绑定变量不存在”

我在以下代码中得到“第15行中不存在”ORA01006绑定变量“错误： DECLARE v\_search\_string varchar2(4000) := 'OK';

本是古典何须时尚/ 2022年11月05日 15:47/ 0 赞/ 185 阅读

相关 bind peeking--绑定变量窥视

绑定变量窥视，就是指oracle在第一次解析SQL语句的时候（也就是说该SQL第一次传入shared pool），会将你输入的绑定变量的值带入SQL语句里，从而参考你的字面值来

ゝ一世哀愁。/ 2022年08月11日 03:27/ 0 赞/ 97 阅读

相关 Oracle 利用FORCE_MATCHING_SIGNATURE捕获非绑定变量SQL

做为一个DBA，你大概习惯了定期要抓取数据库中的非绑定变量SQL，这些SQL经常扮演着一箱苹果中蛀虫的角色。看到下列SQL你必定觉得眼熟: SELECT sub

阳光穿透心脏的1/2处/ 2022年06月07日 03:58/ 0 赞/ 124 阅读

相关 oracle绑定变量使用方法总结

在Oracle中，对于一个提交的sql语句，存在两种可选的解析过程，硬解析和软解析。一个硬解析需要经解析，制定执行路径，优化访问计划等步骤。硬解析不仅仅会耗费大量的cpu，

骑猪看日落/ 2022年05月29日 06:22/ 0 赞/ 482 阅读

相关在java中使用oracle绑定变量

为什么要使用绑定变量：在JAVA中的SQL 语句的编写方面，没有使用ORACLE 绑定变量，很大程度上降低了数据库的性能，表现在两个方面： 1、SQL语句

我不是女神ヾ/ 2022年05月28日 08:46/ 0 赞/ 237 阅读

相关 Oracle 绑定变量窥探

Bind Peeking是Oracle 9i中引入的新特性，一直持续到Oracle 10g R2。它的作用就是在SQL语句硬分析的时候，查看一下当前SQL谓词的值，以

电玩女神/ 2022年05月15日 13:09/ 0 赞/ 155 阅读

相关绑定变量及其优缺点

绑定变量是Oracle解决硬解析的首要利器，能解决OLTP系统中library cache的过度耗用以提高性能。然刀子磨的太快，使起来锋利，却容易折断。凡事皆有利弊二性

曾经终败给现在/ 2022年05月15日 11:27/ 0 赞/ 197 阅读

相关 Android DataBinding 双向数据绑定、事件绑定、使用类方法

Android DataBinding 双向数据绑定、事件绑定、使用类方法 -------------------- [一、Android DataBinding 基础

矫情吗；*/ 2022年01月14日 23:49/ 0 赞/ 1108 阅读

相关绑定方法与非绑定方法

一,复习 1. xml模块解析xml格式的文档可扩展的标记语言 <tag name="123" >text</ta

雨点打透心脏的1/2处/ 2022年01月08日 01:55/ 0 赞/ 305 阅读

相关 Oracle SQL调优系列之绑定变量用法简介

最近在看《基于Oracle的SQL优化一书》，并做了笔记，作者的个人博客：[http://www.dbsnake.net/][http_www.dbsnake.net] 文

迈不过友情╰/ 2021年08月19日 19:35/ 0 赞/ 350 阅读