绑定变量的分析理解

 绑定变量
在查询中，绑定变量是一个占位符。例如，为了检索员工123的记录，可以查询：
select * from scott.emp where empno = 123;
另外，也可以查询：
select * from scott.emp where empno = :empno;
在典型系统中，查询员工123一次，可能再也不会查询。以后，将查询员工456，然后查询员工789等等。
如果在查询中使用文字（常量），那每次每个查询都是一个新查询，即在数据库中以前没有过的查询。每次查询必需经过分析、限定（名称解析）、
安全检查、优化等等，简单的说，执行的每条语句在每次执行时都将必需经过编译。
第二个查询使用了绑定变量:empno,它的值在查询执行时提供。查询经过一次编译后，查询方案存储在共享池（库高速缓存）中，可以用来检索和重用。
在性能的差异是巨大的。

---
如果使用绑定变量，提交引用相同对象的完全相同的查询的人将使用共享池中的编译方案。只需编译子例程一次，就可以重复使用。
不仅使用较少的资源，而且可以减少锁存时间，降低锁存频率。这会他提高性能，并大大地提高可伸缩性。

--测试(根据机器不同而不同哦，本测试同一机器)
--使用了绑定变量的
declare
type rc is ref cursor;
l_rc rc;
l_dummy all_objects.object_name%type;
l_start number default sys.dbms_utility.get_time;
begin
for i in 1..1000 loop
open l_rc for
'select a.object_name from all_objects a where a.object_id = :X' using i;
fetch l_rc into l_dummy;
close l_rc;
end loop;
dbms_output.put_line(round((sys.dbms_utility.get_time - l_start)/100,2)||'秒');
end;

-------------------------------
--结果输出为0.91秒
-------------------------------

--没有绑定
declare
type rc is ref cursor;
l_rc rc;
l_dummy all_objects.object_name%type;
l_start number default sys.dbms_utility.get_time;
begin
for i in 1..1000 loop
open l_rc for
'select a.object_name from all_objects a where a.object_id = ' ||i;
fetch l_rc into l_dummy;
close l_rc;
end loop;
dbms_output.put_line(round((sys.dbms_utility.get_time - l_start)/100,2)||'秒');
end;

-------------------------------
--结果输出为22.42秒
-------------------------------

 

 

 

 

 
查询通常只是因为改变where子句中的内容而产生不同的结果。为了在这种情况下避免硬解析，需要使用绑定变量(bind variable)。它是用户放入查询中的占位符，它会告诉Oracle"我会随后为这个变量提供一个值，现在需要生成一个方案，但我实际执行语句的时候，我会为您提供应该使用的实际值"。
select * from emp where ename='KING'; //不使用绑定变量
select * from emp where ename=:bv //使用绑定变量

一般在 procedure or function 中使用，可以优化共享池的使用。

 

例子：

SQL> set serveroutput on

SQL> drop table hxz_12 ;

表已丢弃。

SQL> create table hxz_12(c number);

表已创建。

SQL> --未绑定变量

SQL> declare

  2    l_start number default dbms_utility.get_time;

  3    l_tabname varchar2(10) default 'hxz_12';

  4  begin

  5    for x in 1 .. 10000 loop

  6      Execute immediate 'insert into '||l_tabname||'(c) values (' || x || ')';

  7    end loop;

  8    dbms_output.put_line(round((dbms_utility.get_time - l_start) / 100, 2) ||

  9                         ' seconds...');

 10    commit;

 11  end;

 12  /

2.89 seconds...

PL/SQL 过程已成功完成。

 

SQL> declare

  2    l_start number default dbms_utility.get_time;

  3    l_tabname varchar2(10) default 'hxz_12';

  4  begin

  5    for x in 1 .. 10000 loop

  6      Execute immediate 'insert into '||l_tabname||'(c) values (:v)'

  7        using x;

  8    end loop;

  9    dbms_output.put_line(round((dbms_utility.get_time - l_start) / 100, 2) ||

 10                         ' seconds...');

 11    commit;

 12  end;

 13  /

.74 seconds...

PL/SQL 过程已成功完成。

SQL>

可见进行了变量绑定执行起来效果比没有进行变量绑定的要好很多。