oracle绑定变量的定义和使用方法总结

作者: Arvin Chen 分类: oracle 来源: Break易站(www.breakyizhan.com)

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

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

1. sqlplus中使用variable来定义

SQL> select * from t where id=1;

ID NAME
---------- --------------------------------
1 test

SQL> select * from t where id=2;

ID NAME
---------- --------------------------------
2 test2

SQL> variable i number;
SQL> exec :i :=1;

PL/SQL procedure successfully completed.

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

ID NAME
---------- --------------------------------
1 test

SQL> exec :i :=2;

PL/SQL procedure successfully completed.

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

ID NAME
---------- --------------------------------
2 test2
SQL> 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
1

select * from t where id=1
1

select * from t where id=:i --可以看到这条sql被调用了两次,这两次的使用就包括了一次soft parse

2. sqlplus中通过define定义字符常量

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

SQL> define a=1
SQL> define
DEFINE _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 Production
With 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=&a
new 1: select * from t where id=1

ID NAME
---------- --------------------------------
1 test

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

SQL> select * from t where id=&b;
Enter value for b: 2
old 1: select * from t where id=&b
new 1: select * from t where id=2

ID NAME
---------- --------------------------------
2 test2

SQL> select * from t where id=&;
Enter value for b: 2
old 1: select * from t where id=&b
new 1: select * from t where id=2

ID NAME
---------- --------------------------------
2 test2

SQL> select * from t where id=&&b;
Enter value for b: 2
old 1: select * from t where id=&&b
new 1: select * from t where id=2

ID NAME
---------- --------------------------------
2 test2

SQL> select * from t where id=&&b;
old 1: select * from t where id=&&b
new 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=&c
new 1: select * from t where name=test
select * from t where name=test
*
ERROR at line 1:
ORA-00904: "TEST": invalid identifier

SQL> 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.815
CLOSE #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'
declare
begin
for i in 1 .. 100 loop
insert into tt values(i,'test');
end loop;
commit;
end;
END OF STMT
PARSE #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 a30
SQL> /

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.421
CLOSE #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 STMT
PARSE #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 STMT
PARSE #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 procedure
proc_test(p_id => :p_id,
p_name => :p_name);
end;

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

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

[oracle@centos6 scripts]$ cat sql_parse1.sql
declare
cursor test_cur is select id,name from tt;
begin
for i in test_cur loop
execute 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.sql

PL/SQL procedure successfully completed.

SQL> set linesize 200
SQL> col hash_value format 9999999999
SQL> col sql_id format 99
SQL> col child_latch format 99
SQL> col version_count format 99
SQL> col sql_text format a40
SQL> col parse_calls format 999
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
----------- ------------- ----------- ------------- -------------------------------------------------- -----------
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.sql
declare
cursor test_cur is select id,name from tt;
begin
for i in test_cur loop
execute 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.sql

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

原文链接:https://blog.csdn.net/gumengkai/article/details/53130449

  •   本文标题:oracle绑定变量的定义和使用方法总结 - Break易站
    转载请保留页面地址:https://www.breakyizhan.com/oracle/15689.html
      微信返利机器人
      免费:淘宝,京东,拼多多优惠券
      腾讯,爱奇艺,优酷的VIP视频免费解析,免费看
      即刻扫描二维码,添加微信机器人!

    发表笔记

    电子邮件地址不会被公开。 必填项已用*标注