oracle绑定变量的使用

Oracle里的绑定变量

使用绑定变量，是可以重用解析树和执行计划基础条件.

绑定变量的语法：

--sql
var x number;
exec :x := 7369;
select ename from emp where empno= :x;
--plsql
declare
  vc_name varchar2(10);
begin
  execute immediate 'select ename from emp where empno= :1' into vc_name using 7788;
  dbms_output.put_line(vc_name);
end;
/
--execute immediate [带绑定变量的sql] using [对应绑定变量的具体输入值]

插入语句的使用

declare
   vc_sql_1 varchar2(4000);
   vc_sql_2 varchar2(4000);
   n_temp_1 number;
   n_temp_2 number;
begin
  vc_sql_1 := 'insert into sap(num_1,num_2) values(:1,:2)';
  execute immediate vc_sql_1 using 7370,7788;
  n_temp_1 := sql%rowcount;
  vc_sql_2 := 'insert into sap(num_1,num_2) values(:1,:1)';
  execute immediate vc_sql_2 using 7371,7799;
  n_temp_2 := sql%rowcount;
  dbms_output.put_line(to_char(n_temp_1+n_temp_2));
  commit;
end；

--using 根据位置传入相关变量参数值

--动态sql可以使用绑定变量,returning 可以和带绑定变量的目标sql连用，目的把受该sql影响的行记录的对应字段值给取出来. 
--eg
declare
    vc_column varchar2(10);
    vc_sql varchar2(4000);
    n_temp number;
    vc_name varchar2(10);
begin
   vc_column := 'empno';
   vc_sql :='delete from emp where ' || vc_column || ' = :1 returning ename into :2';
   execute immediate vc_sql using 7369 returning into vc_name;
   dbms_output.put_line(vc_ename);
   commit;
end;

 

PL/SQL中批量绑定的典型用法

主要优势，一次处理一批数据。

可以见到地将PL/SQL引擎看做专门用来处理PL/SQL代码块中除了sql之外的所有部分(eg:变量、复制、循环等)子系统，SQL引擎用来处理sql语句的子系统。 这里的PL/SQL引擎和SQL引起上下文切换就是指他们之间的交互。

--减少交互，提高性能
fetch cursorname bulk collect into [自定义的属组] <limit CN_BATCH_SIZE>


--eg  "forall" 表示一次执行一批sql
declare
    cur_emp sys_refcursor;
    vc_sql  varchar2(4000);
    type namelist is table of varchar2(10);
    enames namelist;
    CN_BATCH_SIZE constant pls_integer :=1000;
begin
   vc_sql := 'select ename from emp where empno > :1';
   open cur_emp for vc_sql using 7900;
   loop
     fetch cur_emp bulk collect into enames limit CN_BATCH_SIZE;
     for i in 1..enames.count loop
      dbms_output.put_line(enames(i));
     end loop;
    exit when enames.count < CN_BATCH_SIZE;
    end loop;
close cur_emp;
end;

Oracle里的应用类型

应用类型一 硬解析

• 没有使用绑定变量，系统硬解析的比率非常高
• 性能和可扩展性是最差的，可用于olap类型系统
• 将CURSOR_SHARING设置为force，可以有效降低硬解析，提高系统的性能和可扩展性

应用类型二 软解析

每次都会经历 Open Parse,Bind,Execute,Fetch Close

参数 SESSION_CACHED_CURSORS 修改为较大值，能进一步提升性能。如果为0，无法通过已缓存的Session Cursor中的父游标来建立目标sql的Session Cursor和父游标之间的联系。

• 有效降低了硬解析
• 执行时，其对应的Session Cursor都需要经历Open Parse,Bind,Execute,Fetch Close
• 由于软解析不断的open，close，以及对库缓存相关Latch的争用(Oracle11g之前的版本)，会影响性能
• SESSION_CACHED_CURSORS 修改为较大值，能进一步提升性能
• 纯粹的软解析很少见，SESSION_CACHED_CURSORS默认非0

应用类型三 软软解析

当Session Cursor对应的sql解析和执行的次数超过3次，Oracle就不会对上述sql执行Close操作，而是标记为Soft Closed.

• open和Close只需经历一次，其他都需要经历，如Parse,Bind,Execute,Fetch

应用类型四 一次解析，多次执行

该类型通过一些手段：PL/SQL代码的循环内部执行目标SQL，使得每一条SQL语句所对应的Session Cursor会反复经历Execute和Fetch，其他只用经历一次。

 

详细的可以查看：http://blog.itpub.net/29487349/viewspace-2786117/