绑定变量及其优缺点

               

    绑定变量是Oracle解决硬解析的首要利器，能解决OLTP系统中library cache的过度耗用以提高性能。然刀子磨的太快，使起来锋利，却容
易折断。凡事皆有利弊二性，因地制宜，因时制宜，全在如何权衡而已。本文讲述了绑定变量的使用方法，以及绑定变量的优缺点、使用场合。

 

一、绑定变量

    提到绑定变量，就不得不了解硬解析与软解析。硬解析简言之即一条SQL语句没有被运行过，处于首次运行，则需要对其进行语法分析，语
义识别，跟据统计信息生成最佳的执行计划，然后对其执行。而软解析呢，则是由于在library cache已经存在与该SQL语句一致的SQL语句文本
、运行环境，即有相同的父游标与子游标，采用拿来主义，直接执行即可。软解析同样经历语法分析，语义识别，且生成hash value ，接下来
在library cache搜索相同的hash value ，如存在在实施软解析。有关更多的硬解析与软解析以及父游标，子游标请作如下参考：
   
    有关硬解析与软解析，请参考：Oracle 硬解析与软解析
    有关父游标、子游标，请参考：父游标、子游标与共享游标
   
    绑定变量
      首先其实质是变量，有些类似于我们经常使用的替代变量，替代变量使用&占位符，只不过绑定变量使用:
      替代变量使用时为 &variable_para，相应的绑定变量则为 :bind_variable_para
      通常一个SQL语句包含动态部分和静态部分，占位符实质是SQL语句中容易发生变化的部分，通常为其条件或取值范围。动态部分在一般情
      况下(数据倾斜除外)，对执行计划的生成的影响是微乎其微的。故同一SQL语句不同的动态部分产生的执行计划都是相同的。
       
二、绑定变量的使用
    1、在SQLPlus中使用绑定变量

SQL> variable eno number;                           -->使用variable定义变量                                            SQL> exec :eno:=7788;                                                                                                  SQL> select ename,job,sal from emp where empno=:eno;                                                                                                                                                                                          ENAME      JOB              SAL                                                                                        ---------- --------- ----------                                                                                        SCOTT      ANALYST         3000                                                                                                                                                                                                               SQL> col sql_text format a55                                                                                           SQL> select sql_id,sql_text,executions from v$sqlarea   -->首次查询后在v$sqlarea保存父游标且执行次数EXECUTIONS为1        2  where sql_text like '%select ename,job,sal%' and sql_text not like '%from v$sql%';                                                                                                                                                       SQL_ID        SQL_TEXT                                                EXECUTIONS                                       ------------- ------------------------------------------------------- ----------                                       dbc6vx6z6n1zv select ename,job,sal from emp where empno=:eno                   1                                                                                                                                                              SQL> select sql_id,hash_value,child_number,sql_text from v$sql -->查询视图v$sql查看该SQL对应的子游标，且CHILD_NUMBER为0  2  where sql_text like '%select ename,job,sal%' and sql_text not like '%from v$sql%';                                                                                                                                                       SQL_ID        HASH_VALUE CHILD_NUMBER SQL_TEXT                                                                         ------------- ---------- ------------ -------------------------------------------------------                          dbc6vx6z6n1zv 3194619899            0 select ename,job,sal from emp where empno=:eno                                                                                                                                                          SQL> exec :eno:=7369;                                                                                                  SQL> select ename,job,sal from emp where empno=:eno;  -->再次对变量赋值并查询                                                                                                                                                                 ENAME      JOB              SAL                                                                                        ---------- --------- ----------                                                                                        SMITH      CLERK            800                                                                                                                                                                                                               SQL> exec :eno:=7521                                                                                                   SQL> select ename,job,sal from emp where empno=:eno;                                                                                                                                                                                          ENAME      JOB              SAL                                                                                        ---------- --------- ----------                                                                                        WARD       SALESMAN        1250                                                                                                                                                                                                               SQL> select sql_id,sql_text,executions from v$sqlarea -->视图v$sqlarea中EXECUTIONS值为3，对应的SQL被执行了3次            2  where sql_text like '%select ename,job,sal%' and sql_text