oracle 绑定变量(bind variable)

1、oracle 绑定变量(bind variable)oracle 中，对于一个提交的sql语句,存在两种可选的解析过程, 一种叫做硬解析,一种叫做软解析.一个硬解析需要经解析,制定执行路径,优化访问计划等许多的步骤.硬解释不仅仅耗费大量的cpu，更重要的是会占据重要的们闩（latch）资源，严重的影响系统的规模的扩大（即限制了系统的并发行）， 而且引起的问题不能通过增加内存条和cpu的数量来解决。之所以这样是因为门闩是为了顺序访问以及修改一些内存区域而设置的，这些内存区域是不能被同时修改。当一个sql语句提交后，oracle会首先检查一下 共享缓冲池（shared pool

2、）里有没有与之完全相同的语句，如果有的话只须执行软分析即可，否则就得进行硬分析。而唯一使得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

3、语句：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/sqlpl/sql很多时候都会自动绑定变量而无需编程人员操心，即很多你写得sql语句都会自动利用绑定变量，如下例所示：create or replace procedure dsal(p_empno in number)asb

4、eginupdate empset sal=sal*2where empno = p_empno;commit;end;/也许此时你会想要利用绑定变量来替代p_empno,但是这是完全没有必要的，因为在pl/sql中，引用变量即是引用绑定变量。但是在pl/sql中动态sql并不是这样。在vb，java以及其他应用程序中都得显式地利用绑定变量。对于绑定变量的支持不仅仅限于oracle,其他RDBMS向SQL也支持这一特性。但是并不是任何情况下都需要使用绑定变量，下面是两种例外情况：1对于隔相当一段时间才执行一次的语句，这是利用绑定变量的好处会被不能有效利用优化器而抵消2数据仓库的情况下。ORAC

5、LE 绑定变量用法总结在oracle 中，对于一个提交的sql语句,存在两种可选的解析过程, 一种叫做硬解析,一种叫做软解析.一个硬解析需要经解析,制定执行路径,优化访问计划等许多的步骤.硬解释不仅仅耗费大量的cpu，更重要的是会占据重要的们闩（latch）资源，严重的影响系统的规模的扩大（即限制了系统的并发行），而且引起的问题不能通过增加内存条和cpu的数量来解决。之所以这样是因为门闩是为了顺序访问以及修改一些内存区域而设置的，这些内存区域是不能被同时修改。当一个sql语句提交后，oracle会首先检查一下共享缓冲池（shared pool）里有没有与之完全相同的语句，如果有的话只

6、须执行软分析即可，否则就得进行硬分析。 而唯一使得oracle 能够重复利用执行计划的方法就是采用绑定变量。绑定变量的实质就是用于替代sql语句中的常量的替代变量。绑定变量能够使得每次提交的sql语句都完全一样。1.sqlplus中如何使用绑定变量，可以通过variable来定义代码：SQL> select * from tt where id=1;ID NAME- -1 testSQL> select * from tt where id=2;ID NAME- -2 testSQL> variable i number;SQL> exec :i :=1;PL

7、/SQL 过程已成功完成。SQL> select *from tt where id=:i;ID NAME- -1 testSQL> exec :i :=2;PL/SQL 过程已成功完成。SQL> select *from tt where id=:i;ID NAME- -2 testSQL> print i;I-2SQL> select sql_text,parse_calls from v$sql where sql_text like 'select * from tt where id=%'SQL_TEXT PARSE_CALLS- -se

8、lect * from tt where id=2 1select * from tt where id=1 1select * from tt where id=:i 2SQL> NOTE：鉴于本人用的是customers的表，所以变成了这样： 2.前两天看到有人在pub上问在sqlplus中通过define和variable定义的变量的区别。其实define定义的我理解不是变量而是字符常量，通过define定义之后，在通过&或者&&引用的时候不需要输入了，仅此而已。oracle在执行的时候自动用值进行了替换；而variable定义的是绑定变

9、量,上面已经提到。代码： C:>sqlplus xys/managerSQL*Plus: Release 11.1.0.6.0 - Production on 星期二 4月 1 14:03:00 2008Copyright (c) 1982, 2007, Oracle. All rights reserved.连接到:Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application

10、Testing optionsSQL> defineDEFINE _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)DE

11、FINE _O_VERSION = "Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR)DEFINE _O_RELEASE = "1101000600" (CHAR)SQL> select *from tt;ID NAME- -1 a2 a3 "abc"SQL> de

12、fine aSP2-0135: 符号 a 未定义SQL> define a=1SQL> define  NOTE：显示所有的defineDEFINE _DATE = "01-4月 -08" (CHAR)DEFINE _CONNECT_IDENTIFIER = "db11" (CHAR)DEFINE _USER = "XYS" (CHAR)DEFINE _PRIVILEGE = "" (CHAR)DEFINE _SQLPLUS_RELEASE = "1101000600&qu

13、ot; (CHAR)DEFINE _EDITOR = "Notepad" (CHAR)DEFINE _O_VERSION = "Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR)DEFINE _O_RELEASE = "1101000600" (CHAR)DEFINE A = &

14、quot;1" (CHAR)-通过上面显示define定义的应该是字符（串）常量。SQL> select * from tt where id=&a;原值 1: select * from tt where id=&a新值 1: select * from tt where id=1ID NAME- -1 aSQL> select * from tt where id=&&a;原值 1: select * from tt where id=&&a新值 1: select * from tt where id=1ID NAME

15、- -1 aSQL> define b='a' NOTE：将a的define值赋给bSQL> defineDEFINE _DATE = "01-4月 -08" (CHAR)DEFINE _CONNECT_IDENTIFIER = "db11" (CHAR)DEFINE _USER = "XYS" (CHAR)DEFINE _PRIVILEGE = "" (CHAR)DEFINE _SQLPLUS_RELEASE = "1101000600" (CHAR)DE

16、FINE _EDITOR = "Notepad" (CHAR)DEFINE _O_VERSION = "Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR)DEFINE _O_RELEASE = "1101000600" (CHAR)DEFINE A = "1"

17、 (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=aselect * from tt where name=a*第 1 行出现错误:ORA-00904: "A": 标识符无效SQL> select

18、* from tt where name='&&b'原值 1: select * from tt where name='&&b'新值 1: select * from tt where name='a'ID NAME- -1 a2 aSQL> select * from tt where name='&b'原值 1: select * from tt where name='&b'新值 1: select * from tt where name='a

19、'ID NAME- -1 a2 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=1select * 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> declare2 begin3 for i in 1.100 loop4 insert into tt values(i,'test');5 end loop;6 commit;7