Oracle 绑定变量 示例

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

Oracle绑定变量详解

http://blog.youkuaiyun.com/xujinyang/article/details/6832627

Event 10053执行计划绑定变量Bind peeking

http://blog.youkuaiyun.com/xujinyang/article/details/6882133

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

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

Oracle索引的维护

http://blog.youkuaiyun.com/xujinyang/article/details/6829355

示例

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

Table created.

SQL> declare

2i number;

3j number;

4str varchar2(200);

5begin

6i :=1;

7j :=2;

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

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

10end;

11/

PL/SQL procedure successfully completed.

SQL> select * from t1;

C1C2C3C4

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

1212

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

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

SQL>set timing on

SQL> declare

2i number;

3begin

4for i in 1..1000 loop

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

6end loop;

7end;

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

2i number;

3str varchar2(200);

4begin

5for i in 1..1000 loop

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

7execute immediate str;

8end loop;

9end;

10/

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.88

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

SQL> declare

2i number;

3str varchar2(200);

4begin

5for i in 1..1000 loop

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

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

8end loop;

9end;

10/

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.19

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

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