ORACLE绑定变量的使用

原创 2007年10月09日 13:36:00

        在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中更为明显。

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

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

ORACLE绑定变量的使用

转自:http://www.cnblogs.com/HondaHsu/archive/2008/12/17/1356444.html 在ORACLE中,使用绑定变量,可以降低硬解析,通常可以...
  • it_taojingzhan
  • it_taojingzhan
  • 2015年11月24日 15:34
  • 1906

oracle绑定变量使用方法总结

在Oracle中,对于一个提交的sql语句,存在两种可选的解析过程,硬解析和软解析。 一个硬解析需要经解析,制定执行路径,优化访问计划等步骤。硬解析不仅仅会耗费大量的cpu,更重要的是会占据重要的闩(...
  • gumengkai
  • gumengkai
  • 2016年11月11日 17:36
  • 9937

Oracle_绑定变量窥探测试

绑定变量窥探受隐含参数:_optim_peek_user_binds的限制,自9i之后改参数默认为True,为自动开启状态: 查询sql SQL> col name for a50 SQL> col ...
  • xuzhenxiang
  • xuzhenxiang
  • 2015年08月14日 00:35
  • 867

在java中使用oracle绑定变量

为什么要使用绑定变量: 在JAVA中的SQL 语句的编写方面,没有使用ORACLE 绑定变量,很大程度上降低了数据库的性能,表现在两个方面: 1、SQL语句硬分析(Hard Parse)太...
  • z69183787
  • z69183787
  • 2014年04月15日 12:43
  • 657

[ORACLE]从两个简单的存储过程看绑定变量的重要

[ORACLE]从两个简单的存储过程看绑定变量的重要
  • KISS52254
  • KISS52254
  • 2015年12月04日 11:24
  • 896

Oracle数据库SQL语句绑定变量一----性能问题

SQL语句编写时,一般都是这样的: select * from emp where empno=7369; 或者select * from emp where empno=7499; 这两条SQL语句...
  • chuan_day
  • chuan_day
  • 2017年02月09日 09:38
  • 450

Oracle 绑定变量

oracle 中,对于一个提交的sql语句,存在两种可选的解析过程, 一种叫做硬解析,一种叫做软解析. 一个硬解析需要经解析,制定执行路径,优化访问计划等许多的步骤.硬解释不仅仅耗费大量的c...
  • u011246046
  • u011246046
  • 2015年01月07日 09:15
  • 792

关于绑定变量、关于占位符

在PLSQL中使用EXECUTE IMMEDIATE语句处理动态SQL语句。 语法如下: EXECUTE IMMEDIATE dynamic_string [INTO {define_var...
  • rockpk008
  • rockpk008
  • 2014年05月05日 01:42
  • 3683

oracle cursor和绑定变量

1、SHARED CURSOR (位于SGA)      PARENT CURSOR 只对应SQL文本(V$SQLAREA 不同SCHEMA下相同的SQL ,PARENT CURSOR也相同)    ...
  • web_gus
  • web_gus
  • 2016年07月19日 16:28
  • 217

查看Oracle数据库中未绑定变量的SQL

1.CURSOR_SHARING参数 该参数转换SQL语句中的字面值到绑定变量。转换值提高了游标共享,且可能会影响SQL语句的执行计划。优化器是基于绑定变量的存在生成执行计划,而不是实际字面量值。 C...
  • chiclewu
  • chiclewu
  • 2013年12月12日 20:03
  • 1927
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:ORACLE绑定变量的使用
举报原因:
原因补充:

(最多只允许输入30个字)