Oracle 绑定变量(bind variable)

转载： http://www.ecdoer.com/post/oracle-dynamic-sql.html

【动态SQL定义】

动态SQL是指在PL/SQL块中，可以根据不同参数拼接不同的SQL字符串，即执行前不能确定该SQL是什么（如表名、字段名或条件值未知）。

【动态SQL与静态SQL区别】

1）静态SQL是确定的，在执行前已经完成编译（随PL/SQL块一起完成了编译），执行时数据库直接执行编译好的SQL；而动态SQL是不确定的，是在程序运行时才编译并执行（不随PL/SQL块编译时编译）。

2）静态SQL一次编译，多次调用，使用相同的执行计划。动态SQL每次运行均要先对其编译，即多次调用则需要多次编译。

3）静态SQL使用相同的执行计划，对于确定的任务而言，静态SQL更具有高效性，但缺乏灵活性；动态SQL使用了不同的执行计划，效率不如静态SQL，但能够解决复杂的问题。

4）动态SQL容易产生SQL注入，为数据库安全带来隐患。

【动态SQL用途】

1）处理PL/SQL块中不能处理的DDL或DCL语句；

2）处理PL/SQL块中带参数具有不确定性的SQL（如表名、字段名或条件值作为变量）；

3）PL/SQL中静态SQL出现性能瓶颈；

【动态SQL语法】

execute immediate 'sql';

--区别于静态SQL变量赋值的INTO位置，select col into var from table;

execute immediate 'sql_select' into var_1, var_2;

execute immediate 'sql' using bind_var_1,bind_var_2;

execute immediate 'sql_select' into var_1, var_2 using bind_var_1,bind_var_2;

-- returning子句在insert之后返回新加的值，update之后返回修改后的值，delete返回删除前的值

execute immediate 'sql_dml' returning into var_1;

--批量动态SQL，即在动态SQL中使用BULK子句，或使用游标变量时在fetch中使用BULK，或在FORALL语句中使用BULK子句来实现

execute immediate 'sql' bulk collect into var_array;

--动态REF游标，不同于静态游标声明方式

open cursor_name for ‘sql’ using bind_var_1;

【动态SQL举例】

/*动态SQL执行DDL语句（不能跟using）*/
declare
  sql_statement varchar2(100);
  table_name    varchar2(20);
begin
  table_name := 'emp';
  sql_statement := 'truncate table ' || table_name;
  execute immediate sql_statement;
end;

–注：变量不能放在引号内，否则会解析成文本而不是变量，但若在DML语句的绑定变量，则需要在引号内，拼SQL时一定要注意关键字后的空格，如truncate table之后是有一个空格的

/*动态SELECT语句（不能跟returning）*/
/*SELECT中表名是变量*/
declare
  sql_stat varchar2(100);
  v_tab    varchar2(20);
begin
  v_tab := 'emp';
  sql_stat := 'select sal from '|| v_tab || ' where empno = 1';
  dbms_output.put_line(sql_stat);
  execute immediate sql_stat;
end;
--注：注意from后面和where前面是有空格的
/*SELECT INTO变量赋值*/
declare
  sql_stat varchar2(100);
  v_sal    number(6, 2);
begin
  sql_stat := 'select sal from emp where empno = :1';
  execute immediate sql_stat
    into v_sal
    using 1;
  dbms_output.put_line(v_sal);
end;

–注：INTO的位置，不像静态SQL一样在字段名后，而是在execute immediate时才加，且关键字在using之前；另外，注意区分有returning子句的，returning子句在SQL文本和execute immediate子句中均有出现，但此时INTO的位置在using之后。

/*动态SQL执行DML语句（含returning子句）*/
declare
  v_sal    number(6, 2);
  sql_stat varchar2(100);
  v_eno    number(2);
begin
  v_eno    := 1;
  sql_stat := 'update emp set sal = sal * (1 + :percent / 100) where empno = :2 returning sal into :3';
  execute immediate sql_stat
    using &1, v_eno
    returning into v_sal;
  commit;
  dbms_output.put_line('new salary: ' || v_sal);
end;

–注：动态SQL内的结尾不加分号“;”，但PL/SQL的语句结尾加“;”，且冒号后的占位符是字母或数字完全没有影响，using的变量值可以是绑定变量、或变量或常量均可。如：占位符:percent对应绑定变量&1，虽然名字完全不同，但也不影响，另外，占位符:2对应了变量v_eno，也是完全不同。即，按顺序占位，与占位符名无关。按顺序绑定变量，与绑定变量名无关。给占位符或绑定变量命名只是为了程序的可读性。

declare
  type cur_totalvaue_type is ref cursor;
  cur_totalvaue     cur_totalvaue_type;
  sql_stat          VARCHAR2(100);
  record_totalvalue t_threeyear_hour%rowtype;
begin
  sql_stat := 'select * from t_threeyear_hour t where t.time = :1';
  open cur_totalvaue for sql_stat
    using to_date('20130209', 'yyyymmdd');
  loop
    fetch cur_totalvaue
      into record_totalvalue;
    exit when cur_totalvaue%NOTFOUND;
    dbms_output.put_line(record_totalvalue.time || ' is ' || record_totalvalue.totalvalue);
  end loop;
  close cur_totalvaue;
end;
/*批量动态SQL（BULK COLLECT INTO ）*/
declare
  type ename_table_type is table of emp.ename%type index by binary_integer;
  type sal_table_type is table of emp.sal%type index by binary_integer;
  ename_table ename_table_type;
  sal_table   sal_table_type;
  sql_stat    varchar2(120);
  v_percent   number := &percent;
  v_dno       number := &dno;
begin
  sql_stat := 'update emp set sal = sal * (1 + :percent / 100)' ||
              ' where deptno = :dno' ||
              ' returning ename, sal into :name, :salary';
  execute immediate sql_stat
    using v_percent, v_dno
    returning bulk collect
    into ename_table, sal_table;
  for i in 1 .. ename_table.count loop
    dbms_output.put_line('employee ' || ename_table(i) || ' salary is: ' || sal_table(i));
  end loop;
end;
/*动态游标+BULK*/
declare
  type cur_emp_type is ref cursor;
  cur_emp cur_emp_type;
  type ename_table_type is table of emp.ename%type index by binary_integer;
  ename_table ename_table_type;
  sql_stat    varchar2(120);
begin
  sql_stat := 'select ename from emp where deptno = :dno';
  open cur_emp for sql_stat
    using &dno;
  fetch cur_emp bulk collect
    into ename_table;
  for i in 1 .. ename_table.count loop
    dbms_output.put_line('employee name is ' || ename_table(i));
  end loop;
  close cur_emp;
end;
/*FORALL+BULK（仅支持DML）*/
declare
  type ename_table_type is table of tb2.ename%type;
  type sal_table_type is table of tb2.sal%type;
  ename_table ename_table_type;
  sal_table   sal_table_type;
  sql_stat    varchar2(100);
begin
  ename_table := ename_table_type('blake', 'ford', 'miller'); --为复合类型赋值
  sql_stat    := 'update tb2 set sal = sal * 1.1 where ename = :1 returning sal into :2';
  forall i in 1 .. ename_table.count
  execute immediate sql_stat using ename_table(i)
  returning bulk collect into sal_table;
  for j in 1 .. ename_table.count loop
    dbms_output.put_line('the new salary is ' || sal_table(j) || ' for ' || ename_table(j));
  end loop;
end;

【绑定变量】

Oracle会自动把循环中带参数SQL语句转换为采用绑定变量方式执行，以减少硬解析和latch竞争。所以，很多时候我们并不需要专门去注意在PL/SQL块中使用绑定变量，Oracle会自动帮我们完成这个动作。至于SQLPLUS中使用绑定变量，其实我们日常也较少使用SQLPLUSE，故也无需专门注意该问题。

-- sqlplus中使用绑定变量

variable x number(4);

exec :x := 1;

select * from emp t where empno = :x;

–注：必须先使用关键字variable声明变量，再使用exec给绑定变量赋值（变量前带冒号），最后使用绑定变量。

绑定变量效率高的原因

Oracle中，对于一个提交的sql语句，存在两种可选的解析过程，一种叫做硬解析，一种叫做软解析。当一个sql语句提交后，oracle会首先检查一下共享缓冲池（shared pool）里有没有与之完全相同的语句，如果有的话只须执行软分析即可，否则就得进行硬分析。

硬解析需要经解析，制定执行路径、优化访问计划等许多的步骤。硬解释不仅仅耗费大量的cpu，更重要的是会占据重要闩（latch）资源，严重的影响系统规模扩大（即限制了系统的并发行），而且引起的问题不能通过增加内存条和cpu的数量来解决。之所以这样是因为闩是为了顺序访问以及修改一些内存区域而设置的，这些内存区域是不能被同时修改。

若Oracle在shared pool中查找相同SQL语句的过程中，SQL语句使用了绑定变量（bind variable），那么就是比较SQL语句的静态部分，前面我们已经知道，静态部分是有限的，很容易就能够缓存在内存里，从而找到相同的SQL语句的概率很高。如果没有使用绑定变量，则就是比较SQL语句的静态部分和动态部分，而动态部分的变化是无限的，因此这样的SQL语句很难被缓存在shared pool里。毕竟内存是有限的，不可能把所有的动态部分都缓存在sharedpool里，即便能够缓存，管理这样一个无限大的shared pool也是不可能完成的任务。不使用绑定变量导致的直接结果就是，找到相同的SQL语句的概率很低，导致必须完整的解析SQL语句，也就导致消耗更多的资源。

绑定变量的使用环境

由于在OLTP中，SQL语句大多是比较简单或者操作的结果集都很小。当一个表上创建了索引，那么这种极小结果集的操作使用索引最合适，并且几乎所有的SQL的执行计划的索引都会被选择，因为这种情况下，索引可能只需要扫描几个数据块就可以定位到数据，而全表扫描将会相当耗资源。因此，这种情况下，即使每个用户的谓词条件不一样，执行计划也是一样的，就是都用索引来访问数据，基本不会出现全表扫描的情况。在这种执行计划几乎唯一的情况下，使用绑定变量来代替谓词常量是合适的。

注意，在OLTP中使用绑定变量一定要注意，变量的类型要与表字段类型一样，否则若造成了隐式转换，索引将失效而采用全表扫描，此时会造成系统性能的极大下降。

在OLAP系统中，SQL的操作就复杂很多，OLAP数据库上大多数时候运行的一些报表SQL，这些SQL经常会用到聚合查询（如：groupby），而且结果集也是非常庞大，在这种情况下，索引并不是必然的选择，甚至有时候全表扫描的性能会更优于索引，即使相同的SQL，如果谓词不同，执行计划都可能不同。让Oracle对每条SQL做硬分析，确切的知道谓词条件的值，这对执行计划的选择至关重要，这样做的原因是为了得到一个最优的执行计划。在OLAP系统中，系统的资源基本上是用于做大的SQL查询，和查询比起来SQL解析消耗的资源显得微不足道，SQL硬分析的代价是可以忽略的。因此让Oracle确切地知道谓词的数值至关重要，它直接决定了SQL执行计划的选择，所以在OLAP系统完全没有必要绑定变量，那样很可能带来负面影响，比如导致SQL选择错误的执行，这个代价有时是灾难性的。

【文章参考】

http://blog.csdn.net/leshami/article/details/6118010

http://czmmiao.iteye.com/blog/1489625