南京Oracle培训 Oracle技术之如何绑定变量

　　绑定变量是指在sql语句的条件中使用变量而不是常量。比如shared pool里有两条sql语句，南京OCP培训

　　select * from tab1 where col1=1;

　　select * from tab1 where col1=2;

　　对Oracle数据库来说，这是两条完全不同的SQL，对这两条语句都需要进行hard parse。因为Oracle会根据sql语句的文本去计算每个字符在内存里的hash值，因此虽然上述两条SQL只有一个字符不一样，Oracle根据hash算法在内存中得到的hash地址就不一样，所以Oracle就会认为这是两条完全不同的语句。而如果将上述SQL改写成select * from tab1 where col1=:var1;,然后通过对变量var1的赋值去查询，那么Oracle对这条语句第一次会进行hard parse，以后就只进行soft parse。假设某条语句被重复执行了几十万次，那么使用bind var带来的好处是巨大的。

南京Oracle认证培训

　　下面给大家列举一个在存储过程中使用绑定变量的例子

　　SQL> declare

　　2      v_sql_text varchar2(1000);

　　3      empno_no varchar2(10);

　　4      dept_no varchar2(10);

　　5      begin

　　6         empno_no:=7788;

　　7         dept_no:=30;

　　v_sql_text:='update emp set comm=10000 where empno=:1 and deptno=:2';

　　execute immediate v_sql_text using empno_no,dept_no;

　　10      end;

　　11  /

　　PL/SQL procedure successfully completed.

　　执行完毕之后，查看v$sql这个视图，查询执行情况

　　SQL> select SQL_TEXT,SQL_ID,PARSE_CALLS,EXECUTIONS from v$sql where sql_text like 'update emp set comm=10000 where empno=%';

　　SQL_TEXT                                                     SQL_ID               PARSE_CALLS EXECUTIONS

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

　　update emp set comm=10000 where empno=:1 and deptno=:2       1zrg4pnp8td77         1          1

　　然后更改查询谓语，将empno=7788,deptno=30改为empno=7369,deptno=20，然后执行上面的存储过程 南京Oracle培训

　　SQL> declare

　　2      v_sql_text varchar2(1000);

　　3      empno_no varchar2(10);

　　4      dept_no varchar2(10);

　　5      begin

　　6         empno_no:=7369;

　　7         dept_no:=20;

　　v_sql_text:='update emp set comm=10000 where empno=:1 and deptno=:2';

　　execute immediate v_sql_text using empno_no,dept_no;

　　10      end;

　　11  /

　　PL/SQL procedure successfully completed.

　　执行完毕之后，查看v$sql这个视图，查询执行情况

　　SQL> select SQL_TEXT,SQL_ID,PARSE_CALLS,EXECUTIONS from v$sql where sql_text like 'update emp set comm=10000 where empno=%';

　　SQL_TEXT                                                     SQL_ID               PARSE_CALLS EXECUTIONS

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

　　update emp set comm=10000 where empno=:1 and deptno=:2       1zrg4pnp8td77         2          2

　　从上面可以看出第二次执行存储过程时候，Oracle没有再进行硬解析，而是调用sql_id为“1zrg4pnp8td77”生成的执行计划。南京Oracle认证培训