Oracle PL/SQL Forall

PL/SQL引擎会执行过程化语句，但它把SQL语句发送给SQL引擎处理，然后SQL引擎把处理的结果返回给PL/SQL引擎。

PL/SQL和SQL引擎间的频繁切换会大大降低效率。典型的情况就是在一个循环中反复执行SQL语句。

例如，下面的DELETE语句就会在FOR循环中被多次发送到SQL引擎中去：

Declare
  Type numlist Is Varray(20) Of Number;
  depts numlist := numlist(10,30,70);
--department numbers
Begin
  For i In depts.First .. depts.Last Loop
   Delete From emp Where deptno = depts(i);
  End Loop;
End;

这种情况下，如果SQL语句影响了四行或更多行时，使用批量绑定就会显著地提高性能。

用SQL语句中为PL/SQL变量赋值称为绑定，PL/SQL绑定操作可以分为三种：

1.   内绑定(in-bind)：用INSERT或UPDATE语句将PL/SQL变量或主变量保存到数据库。 

2.   外绑定(out-bind)：通过INSERT、UPDATE或DELETE语句的RETURNING子句的返回值为PL/SQL变量或主变量赋值。 

3.   定义(define)：使用SELECT或FETCH语句为PL/SQL变量或主变量赋值。 

DML语句可以一次性传递集合中所有的元素，这个过程就是批量绑定。如果集合有20个元素，批量绑定的一次操作就相当于行20次SELECT、INSERT、UPDATE或DELETE语句。

这项技术是靠减少PL/SQL和SQL引擎间的切换次数来提高性能的。

要对INSERT、UPDATE和DELETE语句使用批量绑定，就要用PL/SQL的FORALL语句。

如果要在SELECT语句中使用批量绑定，我们就要在SELECT语句后面加上一个BULK COLLECT子句来代替INTO子句。

例一：对DELETE语句应用批量绑定 

下面的DELETE语句只往SQL引擎中发送一次，即使是执行了三次DELETE操作：

Declare
 Type numlist Is Varray(20) Of Number;
  depts numlist := numlist(10,30,70);
--department numbers
Begin
 Forall i In depts.First .. depts.Last
   Delete From emp Where deptno = depts(i);
End;

这种情况下，如果SQL语句影响了四行或更多行时，使用批量绑定就会显著地提高性能。

1、批量绑定如何提高性能

用SQL语句中为PL/SQL变量赋值称为绑定，PL/SQL绑定操作可以分为三种：

1.   内绑定(in-bind)：用INSERT或UPDATE语句将PL/SQL变量或主变量保存到数据库。 

2.   外绑定(out-bind)：通过INSERT、UPDATE或DELETE语句的RETURNING子句的返回值为PL/SQL变量或主变量赋值。 

3.   定义(define)：使用SELECT或FETCH语句为PL/SQL变量或主变量赋值。 

DML语句可以一次性传递集合中所有的元素，这个过程就是批量绑定。如果集合有20个元素，批量绑定的一次操作就相当于执行20次SELECT、INSERT、UPDATE或DELETE语句。这项技术是靠减少PL/SQL和SQL引擎间的切换次数来提高性能的。要对INSERT、UPDATE和DELETE语句使用批量绑定，就要用PL/SQL的FORALL语句。

如果要在SELECT语句中使用批量绑定，我们就要在SELECT语句后面加上一个BULK COLLECT子句来代替INTO子句。

·        例一：对DELETE语句应用批量绑定 

下面的DELETE语句只往SQL引擎中发送一次，即使是执行了三次DELETE操作：

Declare
  Type numlist Is Varray(20) Of Number;

  depts numlist := numlist(10, 30, 70); -- department numbers
Begin
  Forall i In depts.First .. depts.Last
    Delete From emp Where deptno = depts(i);
End;

·        例二：对INSERT语句应用批量绑定 

下例中，我们把5000个零件编号和名称放到索引表中。所有的表元素都向数据库插入两次：第一次使用FOR循环，然后使用FORALL语句。实际上，FORALL版本的代码执行速度要比FOR语句版本的快得多。

Set serveroutput On;
Create table parts(pnum Number(4), pname Char(15));

Declare
  Type numtab Is Table Of Number(4) Index By Binary_Integer;
  Type nametab Is Table Of Char(15) Index By Binary_Integer;
  pnums  numtab;
  pnames nametab;
  t1     Number;
  t2     Number;
  t3     Number;

Begin
  For j In 1 .. 5000 Loop
    -- load index-by tables
    pnums(j) := j;
    pnames(j) := 'Part No. ' || to_char(j);
  End Loop;
  t1 := dbms_utility.get_time;
  For i In 1 .. 5000 Loop
    -- use FOR loop
    Insert Into parts Values (pnums(i), pnames(i));
  End Loop;
  t2 := dbms_utility.get_time;
  Forall i In 1 .. 5000 -- use FORALL statement
    Insert Into parts Values (pnums(i), pnames(i));
  t3 := dbms_utility.get_time;
  dbms_output.put_line('Execution Time (secs)');
  dbms_output.put_line('---------------------');
  dbms_output.put_line('FOR loop: ' || to_char(t2 - t1));
  dbms_output.put_line('FORALL: ' || to_char(t3 - t2));
End;