Oracle 绑定变量

1、定义一个绑定变量

VAR[IABLE] [variable [NUMBER|CHAR|CHAR (n)|NCHAR|NCHAR (n) |VARCHAR2 (n)|NVARCHAR2 (n)|CLOB|NCLOB|REFCURSOR]]
定义一个绑定变量，该变量可以在pl/sql中引用。
可以用print命令显示该绑定变量的信息。

 

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

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

之所以这样是因为门闩是为了顺序访问以及修改一些内存区域而设置的，这些内存区域是不能被同时修改。当一个sql语句提交后，oracle会首先检查一下共享缓冲池（shared pool）里有没有与之完全相同的语句，如果有的话只须执行软分析即可，否则就得进行硬分析。
而唯一使得oracle 能够重复利用执行计划的方法就是采用绑定变量。绑定变量的实质就是用于替代sql语句中的常量的替代变量。绑定变量能够使得每次提交的sql语句都完全一样。

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

绑定变量是在通常情况下能提升效率，非正常的情况如下：

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

但是并不是任何情况下都需要使用绑定变量，下面是两种例外情况：
1．对于隔相当一段时间才执行一次的SQL语句，这是利用绑定变量的好处会被不能有效利用优化器而抵消
2．数据仓库的情况下。

 

绑定变量不能当作嵌入的字符串来使用，只能当作语句中的变量来用。不能用绑定变量来代替表名、过程名、字段名等.

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

举例：

普通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;

Sql*plus 中使用绑定变量：

SQL> set timing on

SQL> variable x number;

SQL> exec :x :=8

PL/SQL 过程已成功完成。

已用时间:  00: 00: 00.03

SQL> select * from A;

        ID

----------

         3

         5

已用时间:  00: 00: 00.06

SQL> insert into A values(:x);

已创建 1 行。

已用时间:  00: 00: 00.01

SQL> select * from A;

        ID

----------

         3

         8

         5

已用时间:  00: 00: 00.01

PL/SQL很多时候都会自动绑定变量而无需编程人员操心，即很多你写得sql语句都会自动利用绑定变量，如下例所示：
SQL> Set timing on

SQL> declare

  2  I NUMBER;

  3  BEGIN

  4  FOR I IN 1..1000 LOOP

  5  INSERT INTO A VALUES(I);

  6  end loop;

  7  end;

  8  /

PL/SQL 过程已成功完成。

已用时间:  00: 00: 00.12

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

SQL> create table D ( id varchar(10));

表已创建。

已用时间:  00: 00: 00.50

SQL> declare

  2  i number;

  3  sqlstr varchar(2000);

  4  begin

  5  for i in 1..1000 loop

  6  sqlstr :=' insert into d values('||to_char(i)||')';

  7  execute immediate sqlstr;

  8  end loop;

  9  end;

 10  /

PL/SQL 过程已成功完成。

已用时间:  00: 00: 00.68

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

SQL> declare

  2  i number;

  3  sqlstr varchar(2000);

  4  begin

  5  for i in 1..1000 loop

  6  sqlstr :=' insert into d values(:i)';

  7  execute immediate sqlstr using i;

  8  end loop;

  9  end;

 10  /

PL/SQL 过程已成功完成。

已用时间:  00: 00: 00.18

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

 

在PL/SQL中，引用变量即是引用绑定变量。但是在pl/sql中动态sql并不是这样。

在vb，java以及其他应用程序中都得显式地利用绑定变量。
对于绑定变量的支持不仅仅限于oracle,其他RDBMS向SQL SERVER也支持这一特性。