ORACLE绑定变量的使用 [转载]

在ORACLE中，使用绑定变量，可以降低硬解析，通常可以提高系统的性能（注意，是通常，不是任何情况下）。

       以表tabletest为例，我们来看看如何使用绑定变量，tabletest的表结构为

       field1 number(10)

       field2 number(10)

       field3 number(10)

       field4 number(10)

       field5 number(10)

        绑定变量可以理解为一个占位符 ,例如：

declare

    i number;

    j number;

    sqlstr varchar2(200);

begin

    i:=1;

    j:=2; 

    sqlstr:='insert into 测试表 (field1,field2,field3,field4,field5)    values(:x,:x,:y,:x,:x)';
      execute immediate sqlstr using i,i,j,i,i;

end;

这样的一段代码中，使用i,i,j,i,i来对应:x,:x,:y,:x,:x。这段代码是正确的，但如果以为sqlstr中只有:x,:y这两个绑定变量，而把语句execute immediate sqlstr using i,i,j,i,i;改为execute immediate sqlstr using i,j;就会在运行中出现绑定变量数量不够的错误。

上面的正确代码执行完后，插入的记录从field1到field5的数据应该是1,1,2,1,1。

如果我们把execute immediate sqlstr using i,i,j,i,i;改成execute immediate sqlstr using i,j,j,i,i;执行之后，察看插入的记录，就会发现插入的记录是1,2,2,1,1

从上面可以看出，绑定变量只是起到占位的作用，同名的绑定变量并不意味着在它们是同样的，在传递时要考虑的是传递的值与绑定变量出现顺序的对位，而不是绑定变量的名称。

ORACLE系统本身是能够对变量做绑定的，例如下面的代码：

declare

    i number；

begin

  for i in 1..1000 loop

      insert into 测试表 (i,i+1,i*1,i*2,i-1)

  end loop;

end;

这段代码是不需要使用绑定变量的方法来提高效率的，ORACLE会自动将其中的变量绑定。

我们可以这样理解：这段代码执行了1000次的 insert into 测试表 (i,i+1,i*1,i*2,i-1) 语句，每次发出去的语句都是一样的。

如果把这段代码改成如下：

declare

    i number；

    sqlstr varchar2(200);

begin

  for i in 1..1000 loop

      sqlstr:='insert into 测试表 ('||to_char(i)||','||to_char(i)||'+1,'||to_char(i)||'*1,'||to_char(i)||'*2,'||to_char(i)||'-1) ';

      execute immediate sqlstr;

  end loop;

end;

这段代码同样是执行了1000条insert语句，但是每一条语句都是不同的，因此ORACLE会把每条语句硬解析一次，其效率就比前面那段就低得多了。如果要提高效率，不妨使用绑定变量将循环中的语句改为

      sqlstr:='insert into 测试表 (:i,:i+1,:i*1,:i*2,:i-1) ';

      execute immediate sqlstr using i,i,i,i,i;

这样执行的效率就高得多了。

我曾试着使用绑定变量来代替表名、过程名、字段名等，结果是语句错误，结论就是绑定变量不能当作嵌入的字符串来使用，只能当作语句中的变量来用。

从效率来看，由于oracle10G放弃了RBO，全面引入CBO，因此，在10G中使用绑定变量效率的提升比9i中更为明显。

最后，前面说到绑定变量是在通常情况下能提升效率，那哪些是不通常的情况呢？

答案是：在字段(包括字段集)建有索引，且字段(集)的集的势非常大（也就是有个值在字段中出现的比例特别的大）的情况下，使用绑定变量可能会导致查询计划错误，因而会使查询效率非常低。这种情况最好不要使用绑定变量。

【转载地址】http://blog.csdn.net/allanmorgan/article/details/2772090

绑定变量

在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;



用Oracle绑定变量替代sql语句里常量
Oracle绑定变量的实质其实就是用来替代sql语句里常量的替代变量。绑定变量能使每次提交的sql语句都完全一样，以下是相关内容的介绍。

在实际相关应用操作过程中的具体操作中唯一能使得Oracle 能重复的利用执行计划的相关方法就是用绑定变量的方法。其实Oracle绑定变量的实质就是用于替代sql语句中的常量的替代变量。

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

一个硬解析需要经解析,制定执行路径,优化访问计划等许多的步骤.硬解释不仅仅耗费大量的cpu，更重要的是会占据重要的们闩（latch）资源，严重的影响系统的规模的扩大（即限制了系统的并发行）， 而且引起的问题不能通过增加内存条和cpu的数量来解决。

之所以这样是因为门闩是为了顺序访问以及修改一些内存区域而设置的，这些内存区域是不能被同时修改。当一个sql语句提交后，Oracle 会首先检查一下共享缓冲池（shared pool）里有没有与之完全相同的语句，如果有的话只须执行软分析即可，否则就得进行硬分析。

而唯一使得Oracle 能够重复利用执行计划的方法就是采用绑定变量。绑定变量的实质就是用于替代sql语句中的常量的替代变量。绑定变量能够使得每次提交的sql语句都完全一样。

绑定变量只是起到占位的作用，同名的绑定变量并不意味着在它们是同样的，在传递时要考虑的是传递的值与绑定变量出现顺序的对位，而不是绑定变量的名称。


create table t (a varchar2(20),b varchar2(20)); 如何使用Oracle绑定变量


declare  v_sql varchar2(50);  begin  for i in 1..100000 loop  v_sql := 'insert into t values (:1,:2)';  execute immediate v_sql using 'a'||i,'b'||i;  end loop;  commit;  end;  / 
清表


truncate table t; 不使用绑定变量


declare  begin  for i in 1..100000 loop  insert into t values ('a'||i,'b'||i);  end loop;  commit;  end;  / 
以上的相关内容就是对Oracle绑定变量的相关内容介绍，望你能有所收获。