Oracle 绑定变量示例

Oracle 绑定变量示例

绑定变量对Oracle 的性能来说是非常重要的，之前有整理一些理论知识：

       Oracle 绑定变量详解

      

 

       在这里演示一下绑定变量的例子, 注意，绑定变量对索引选择性不好的列不适用，可能会导致错误的执行计划，从而影响SQL 效率。

 

有关索引的选择性，参考：

       Oracle 索引的维护

      

 

 

示例

 

SQL> create table t1(c1 number,c2 number,c3 number,c4 number);

Table created.

 

SQL> declare

  2  i number;

  3  j number;

  4  str varchar2(200);

  5  begin

  6  i :=1;

  7  j :=2;

  8  str :='insert into t1 values(:x,:y,:x,:y)';

  9  execute immediate str using i,j,i,j;

 10  end;

 11  /

 

PL/SQL procedure successfully completed.

 

SQL> select * from t1;

 

C1         C2         C3         C4

---------- ---------- ---------- ----------

1          2          1          2

 

这里是绑定变量的一个简单应用, 绑定变量起到占位的作用.

 

进一步的测试：ORACLE系统本身是能够对变量做绑定的。

 

SQL> set timing on

SQL> declare       

  2  i number;

  3  begin

  4  for i in 1..1000 loop

  5     insert into t1 values(i,i+1,i+2,i+3);

  6  end loop;

  7  end;

  8  /

 

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.28

 

SQL> select count(*) from t1;

  COUNT(*)

----------

      1001

 

Elapsed: 00:00:00.01

 

       在这段代码块的执行过程中，ORACLE会自动将其中的变量进行绑定。 也就是说：这段代码执行了1000次 insert 语句，但是每次发出去的语句都是一样的,这1000次SQL的hash 值是一样的，他们的执行计划也是一样的。

 

 

对上述代码进行修改：

 

SQL> declare

  2     i number;

  3     str varchar2(200);

  4  begin

  5     for i in 1..1000 loop

  6     str:='insert into t1 values ('||to_char(i)||','||to_char(i)||'+1,'||to_char(i)||'+2,'||to_char(i)||'+3)';

  7     execute immediate str;

  8  end loop;

  9  end;

 10  /

 

PL/SQL procedure successfully completed.

 

Elapsed: 00:00:02.88

 

这段代码没有自动使用绑定变量，所以时间上明显长。 我们使用绑定变量看看。

 

SQL> declare

  2     i number;

  3     str varchar2(200);

  4  begin

  5     for i in 1..1000 loop

  6     str:='insert into t1 values (:i,:i+1,:i+2,:i+3)';

  7     execute immediate str using i,i,i,i;

  8  end loop;

  9  end;

 10  /

 

PL/SQL procedure successfully completed.

 

Elapsed: 00:00:00.19

 

使用绑定变量后，时间明显要少很多。