Oracle-FORALL与BULK COLLECT语句

sqlplus 在运行plsql过程化的sql语句时（程序块），使用plsql引擎执行过程化的程序，而将sql语句发送给sql引擎来执行。sql语句执行完后将结果集再发送给plsql引擎。这种plsql和sql引擎之间的交互，称上下文交换。每增加一次引擎之间的交互，会增加额外的开销。执行效率就会降低。

BULK COLLECT：用来增强sql引擎到plsql引擎之间的交互。

FORALL:用来增强plsql引擎到sql引擎的交互。

一、BUIL COLLECT

BUIL COLLECT 子句会批量检索查询结果，即一次性将结果集绑定到一个集合变量中，从sql引擎发送到plsql引擎。

通常在 select..into..、fetch..into.. 、returning..into..子句中使用bulk collect。

①在select into子句中使用

declare
    type emp_type is record (
       empno emp.empno@type,
       ename emp.ename%type,
       hiredate emp.hiredate%type);
    
    type nested_emp_type is table of emp_type;
    emp_tab nested_emp_type;
begin
    select * bulk collect into emp_tab from emp;
    for i in emp_tab.first..emp_tab.last 
        loop
            dbms_output.put_line(emp_tab(i).empno);
            dbms_output.put_line(emp_tab(i).empno);
            dbms_output.put_line(emp_tab(i).empno);
        end loop;
 end;
/

说明：使用bulk collect 一次即可提取所有行并绑定到记录变量------->批量绑定。

②在fetch into 中使用

在游标中可以使用bulk collect 一次取出一个数据集合，比用游标单条取数据效率高，尤其在网络不好的情况下。

语法：
fetch.... bulk collect into ......[limit row_number]；

在使用bulk collect子句时，对于集合类型会自动对其进行初始化以及扩展，因此如果使用bulk collect 子句操作集合，则无需对集合进行初始化以及扩展，由于bulk collect批量特性，如果数据量较大，而集合在此时又自动扩展，为避免过大的数据集造成性能下降，因此可使用limit子句限制一次提取的数据量。limit子句只允许在fetch操作语句中。

declare
 cursor emp_cur is select empno,ename,hiredate from emp;

 type emp_type is record (
  empno emp.empno%type,
  ename emp.ename%type,
  hiredate emp.ename%type);

 type nested_emp_type is table of emp_type;

 emp_tab nested_emp_type;

 v_limit pls_integer:=5;
 v_counter pls_integer:=0;

begin
 open emp_cur;
 loop
     fetch emp_cur bulk collect into emp_tab limit v_limit;
     exit when emp_tab.count=0;-----此时游标退出使用 ，不是emp_cur%notfound
     v_counter:=v_counter+1;-----记录fetch的次数
 
     for i in emp_tab.first..emp_tab.last 
     loop
        dbms_output.put_line(emp_tab(i).empno);
        dbms_output.put_line(emp_tab(i).ename);
        dbms_output.put_line(emp_tab(i).hiredate);
      end loop;
 end loop;
 close emp_cur;
 dbms_output.put_line('总共获取次数：'||v_counter);
end;

③在returning into

bulk collect除了与select into，fetch进行批量绑定之外，还可以与insert、update、delete语句结合使用，当与这几个dml语句结合使用时，需要returning into 子句实现批量绑定。

declare
 type emp_type is record(
 empno emp.empno%type,
 ename emp.ename%type,
 hiredate emp.hiredate%type);
 
type nested_emp_type is table of emp_type;
emp_tab nested_emp_type;
begin
    delete from emp where deptno=20 returning empno,ename,hiredate bulk collect into emp_tab;
    dbms_output.put_line('删除'||sql%rowcount||'行记录'):
    commit;

    if emp_tab.count>0 then ----当集合变量不为空，输出
     for i in emp_tab.first..emp_tab.last loop
        dbms_output.put_line('删除记        
        录：'||emp_tab(i).empno||chr(32)||emp_tab(i).ename||chr(32)||emp_tab(i).hiredate);
      end loop;
end if;
end;

BUIL COLLECT 注意事项：

1、目标对象必须是集合类型。

2、只能在服务端的程序中使用bulk collect 。在客户端不支持。

3、不能对使用字符串类型作键的关联数组使用BULK COLLECT子句。

4、复合目标(如对象类型)不能在RETURNING INTO子句中使用。

5、如果有多个隐式的数据类型转换的情况存在，多重复合目标就不能在BULK COLLECT INTO子句中使用。

6、如果有一个隐式的数据类型转换，复合目标的集合(如对象类型集合)就不能用于BULK COLLECTINTO子句中。

 

二\、FORALL

语法：
forall index_name in {

lower_bound..upper_bound
| indices of collection_name[between lower_bound and upper_bound]
| values of index_collection
}
[save exceptions] dml_statement;

说明：
index_name:一个无需声明的标识符，作为集合的下标使用。
lower_bound..upper_bound:数字表达式，来指定一组连续有效的索引数字下限和上限。该表达式只需解析一次。
INDICES OF collection_name：用于指向稀疏数组的实际下标。跳过没有赋值的元素，例如被 DELETE 的元素，NULL 也算值。

VALUES OF index_collection_name：把该集合中的值当作下标，且该集合值的类型只能是 PLS_INTEGER/BINARY_INTEGER。

SAVE EXCEPTIONS：可选关键字，表示即使一些DML语句失败，直到FORALL LOOP执行完毕才抛出异常。可以使用SQL%BULK_EXCEPTIONS 查看异常信息。

dml_statement：静态语句，例如：UPDATE或者DELETE；或者动态（EXECUTE IMMEDIATE）DML语句

①FORALL批量插入、修改、删除数据

create table tmp_tab(
 id number(5),
 name varchar2(50));
----批量插入
declare
    type  tb_table_type is table of tmp_tab%rowtype index by binary_integer;
    tb_table tb_table_type;
    
begin
     for i in 1..100 loop
        tb_table(i).id=i;
        tb_table(i).name='name'||i;
      end loop;
     
      forall i in 1..tb_table.count
      insert into tmp_tab values tb_table(i);
end;
--------批量修改
declare  
    type  tb_table_type is table of tmp_tab%rowtype index by binary_integer;
    tb_table tb_table_type;
    
begin
    for i in 1..100 loop
        tb_table(i).id=i;
        tb_table(i).name:='my_name'||i;
    end loop;
    forall i in tb_table.count update tmp_tab set row=tb_table(i) where t.id=tb_table(i).id;
end;
----------批量删除
declare  
    type  tb_table_type is table of tmp_tab%rowtype index by binary_integer;
    tb_table tb_table_type;
    
begin
    for i in 1..100 loop
        tb_table(i).id=i;
        tb_table(i).name:='my_name'||i;
    end loop;
    forall i in 1..tb_table.count delete from tmp_tab where id =tb_table（i).id;
end;