ORACLE批量处理数据之COLLECT BULK INTO 和FORALL的用法

1.PL/SQL块的执行过程

当ORALCE运行一块代码时,PL/SQL引擎将执行过程化的代码,SQL引擎而将执行SQL语句,因此执行过程中PL/SQL引擎和SQL引擎会不断切换和交互,称为上下文交换(context switch)。

2.BULK COLLECT和FORALL特点

  • BULK COLLECT INTO 可以将多个行引入一个或多个集合中,提供对数据的高速检索。
  • FORALL 可以将多个DML批量发送给SQL引擎来执行,可大大改进INSERT、UPDATE和DELETE操作的性能。

Oracle数据库使用这些语句大大减少了PL/SQL与SQL语句执行引擎的环境切换次数,从而使其性能有了显著提高。

3. 批量游标的使用

当需要批量处理大数据量,又要防止出现大事务时。比如需要分批次的更新数据,可使用BUIK COLLECT INTO 和FORALL来提高效率。
如下代码示例:

declare 
       --定义游标
       cursor c_job
       is
       select empno,ename,job,sal
       from emp
       where job='MANAGER';
       --定义游标变量,
       --v_row c_job%rowtype; 不能再用这种方式定义
       type c_job_type is table of c_job%rowtype;
       v_row c_job_type;
begin
       open c_job;
         loop
           --限制一次提取的数据量(1000)到v_row
           fetch c_job bulk collect into v_row limit 1000;
            --exit when c_job%notfound; 不能用这种方式定义
            exit when v_row.count=0;
            forall i in v_row.first..v_row.last
                update emp_bak a
                  set a.empno = v_row(i).empno,
                      a.ename = v_row(i).ename,
                      a.job = v_row(i).job,
                      a.sal = v_row(i).sal
                where a.job='MANAGER';
            commit;
         end loop;
       --关闭游标
      close c_job;
end;
/

注意:

  • FORALL语句的执行体必须是一个单独的DML语句,比如INSERT,UPDATE或DELETE
  • FORALL语句不要显式定义index_row,它被PL/SQL引擎隐式定义为PLS_INTEGER类型
  • BULK COLLECT的批量特性,可以使用LIMIT子句来限制一次提取的数据量。
上一篇:Java8 stream


下一篇:Stream(Java 8)