oracle绑定变量（bindvariable）

oracle绑定变量（bindvariable）
oracle 绑定变量(bind variable)
oracle 中，对于⼀个提交的sql语句,存在两种可选的解析过程, ⼀种叫做硬解析,⼀种叫做软解析.
⼀个硬解析需要经解析,制定执⾏路径,优化访问计划等许多的步骤.硬解释不仅仅耗费⼤量
的cpu，更重要的是会占据重要的们闩（latch）资源，严重的影响系统的规模的扩⼤（即限制了系统的并发⾏），⽽且引起的问题不能通过增加内存条和cpu的数量来解决。

之所以这样是因为门闩是为了顺序访问以及修改⼀些内存区域⽽设置的，这些内存区域是不能被同时修改。

当⼀个sql语句提交后，oracle会⾸先检查⼀下共享缓冲池（shared pool）⾥有没有与之完全相同的语句，如果有的话只须执⾏软分析即可，否则就得进⾏硬分析。

⽽唯⼀使得oracle 能够重复利⽤执⾏计划的⽅法就是采⽤绑定变量。

绑定变量的实质就是⽤于替代sql语句中的常量的替代变量。

绑定变量能够使得每次提交的sql语句都完全⼀样。

普通sql语句：
SELECT fname, lname, pcode FROM cust WHERE id = 674;
SELECT fname, lname, pcode FROM cust WHERE id = 234;
SELECT fname, lname, pcode FROM cust WHERE id = 332;
含绑定变量的sql 语句：
SELECT fname, lname, pcode FROM cust WHERE id = :cust_no;
Sql*plus 中使⽤绑定变量：
sql> variable x number;
sql> exec :x := 123;
sql> SELECT fname, lname, pcode FROM cust WHERE id =:x;
pl/sql
pl/sql很多时候都会⾃动绑定变量⽽⽆需编程⼈员操⼼，即很多你写得sql语句都会⾃动利⽤绑定变量，如下例所⽰：
create or replace procedure dsal(p_empno in number)
as
begin
update emp
set sal=sal*2
where empno = p_empno;
commit;
end;
/
也许此时你会想要利⽤绑定变量来替代p_empno,但是这是完全没有必要的，因为在pl/sql 中，引⽤变量即是引⽤绑定变量。

但是在pl/sql中动态sql并不是这样。

在vb，java以及其他应⽤程序中都得显式地利⽤绑定变量。

对于绑定变量的⽀持不仅仅限于oracle,其他RDBMS向SQLＳＥＲＶＥＲ也⽀持这⼀特性。

但是并不是任何情况下都需要使⽤绑定变量，下⾯是两种例外情况：
1．对于隔相当⼀段时间才执⾏⼀次的ｓｑｌ语句，这是利⽤绑定变量的好处会被不能有效
利⽤优化器⽽抵消
2．数据仓库的情况下。

ORACLE 绑定变量⽤法总结
在oracle 中，对于⼀个提交的sql语句,存在两种可选的解析过程, ⼀种叫做硬解析,⼀种叫做软解析.
⼀个硬解析需要经解析,制定执⾏路径,优化访问计划等许多的步骤.硬解释不仅仅耗费⼤量
的cpu，更重要的是会占据重要的们闩（latch）资源，严重的影响系统的规模的扩⼤（即限制了系统的并发⾏），⽽且引起的问题不能通过增加内存条和cpu的数量来解决。

之所以这样是因为门闩是为了顺序访问以及修改⼀些内存区域⽽设置的，这些内存区域是不能被同时修改。

当⼀个sql语句提交后，oracle会⾸先检查⼀下共享缓冲池（shared pool）⾥有没有与之完全相同的语句，如果有的话只须执⾏软分析即可，否则就得进⾏硬分析。

⽽唯⼀使得oracle 能够重复利⽤执⾏计划的⽅法就是采⽤绑定变量。

绑定变量的实质就是⽤于替代sql语句中的常量的替代变量。

绑定变量能够使得每次提交的sql语句都完全⼀样。

1.
sqlplus中如何使⽤绑定变量，可以通过variable来定义
代码：
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>
NOTE：鉴于本⼈⽤的是customers的表，所以变成了这样：
2.
前两天看到有⼈在pub上问在sqlplus中通过define和variable定义的变量的区别。

其实define定义的我理解不是变量⽽是字符常量，通过define定义之后，在通过&或者&&引⽤的时候不需要输
⼊了，仅此⽽已。

oracle在执⾏的时候⾃动⽤值进⾏了替换；⽽variable定义的是绑定变量,上⾯已经提到。

代码：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 NOTE：显⽰所有的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'; NOTE：将a的define值赋给b
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
=%'; NOTE：查看sql语句记录
SQL_TEXT
--------------------------------------------------------------------------------
select * from tt where name=1
select * from tt where name='a'
SQL>
3.
oracle在解析sql时会把plsql中定义的变量转为为绑定变量
代码：
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
=====================
NOTE：就看到这⾥。

/wh62592855/article/details/4778343
再次为了以防万⼀：
D:oracle-lproduct10.2.0adminorcludump
trace⽂件在这⾥。