Oracle扩展PL/SQL简介(六)

8.    bulk collect /forall
使用bulk collect可以成块地读取数据，它可使SQL引擎在返回输出结果给PL/SQL引擎之前大批绑定输出集合。这样可以一次性地把数据动态地装载到集合中，但bulk collect需要大量内存。bulk collect可用于select into、fetch into和returning into语句中。

●    select into中使用bulk collect
declare
  type t_email is table of employees.email%type;
  v_email_list t_email;
begin
  select email bulk collect
    into v_email_list
    from employees
   where department_id = 50;
  dbms_output.put_line('获取email地址数：' || v_email_list.count);
end;
/
获取email地址数：45

●    fetch into 中使用bulk collect
declare
  type t_emp is table of employees%rowtype;
  v_emp_list t_emp;
  cursor c_emp is
    select * from employees where department_id = 50;
begin
  open c_emp;
  --在fetch into中使用bulk collect
  fetch c_emp bulk collect
    into v_emp_list;
  dbms_output.put_line('获取雇员总数：' || v_emp_list.count);
end;
/
获取雇员总数：45

●    在returning into中使用bulk collect
通过使用returning子句bulk collect可返回值给调用过程，不需要额外获取fetch语句。
--创建测试表
create table emptemp as select * from employees where department_id=50;
--执行过程
declare
  type t_id_list is table of number;
  type t_name_list is table of varchar2(32);
  ids   t_id_list;
  names t_name_list;
begin
  delete from emptemp
   where commission_pct is null
  returning employee_id, first_name bulk collect into ids, names;
  dbms_output.put_line('Deleted ' || sql%rowcount || ' rows:');
  for i in ids.first .. ids.last loop
    dbms_output.put_line('Employees #' || ids(i) || ': ' || names(i));
  end loop;
  commit;
exception
  when others then
    rollback;
end;
/
Deleted 45 rows:
Employees #198: Donald
Employees #199: Douglas
Employees #120: Matthew
…

●    forall绑定
使用forall绑定可以使PL/SQL引擎和SQL引擎之间的转换最小化，提高DML性能。使用forall实现一次性批量执行大量数据操作，只切换一次上下文，而for将与循环次数一样多的上下文间切换。
在forall中，使用save exceptions来保存forall语句在forall执行期间产生的异常。在遇到错误时，就保存错误继续处理下一条数据。

--创建测试表
create table emppart as select * from employees where department_id=50 and rownum<=3;
--增加主键约束
alter table emppart add constraint pk_emp_id primary key  (employee_id);

select employee_id,department_id from emppart;

EMPLOYEE_ID DEPARTMENT_ID
----------- -------------
        198            50
        199            50
        120            50
--执行过程
declare
  type t_emp is table of employees%rowtype;
  v_emp_list t_emp;
  cursor c_emp is
    select * from employees where department_id = 50;
begin
  open c_emp;
  loop
    fetch c_emp bulk collect
      into v_emp_list limit 10;
    exit when c_emp%notfound;
  end loop;
  forall i in 1 .. v_emp_list.count save exceptions
    insert into emppart values v_emp_list (i);
exception
  when others then
    for idx in 1 .. sql%bulk_exceptions.count loop
      dbms_output.put_line('Error ' || idx || ' :' ||sql%bulk_exceptions(idx).error_index);
      dbms_output.put_line(sqlerrm(-1 * sql%bulk_exceptions(idx).error_code));
    end loop;
  commit;
end;
/
Error 1 :1
ORA-00001: unique constraint (.) violated
Error 2 :2
ORA-00001: unique constraint (.) violated
Error 3 :3
ORA-00001: unique constraint (.) violated

SQL > select count(*) from emppart;

  COUNT(*)
----------
        45
有3条违反约束的数据未进入表并触发异常。
forall除了用于批量插入之外，也可用于批量更新和删除。

提示：在下列情况应使用bulk collect/forall：
a.如果INSERT，UPDATE或DELETE语句执行包含循环和参考集合元素，建议用FORALL语句。
b如果在SELECT INTO, FETCH INTO或RETURNING INTO子句引用集合，把语句合并到BULK COLLECT子句中。
c如果可能，使用应用程序数组和PL/SQL集合在程序和数据库服务器来回传递数据。
d.如果一个DML操作在一个特定行的失败不是一个严重的问题，那么使用包含关键字SAVE EXCEPTIONS的FORALL语句，使用%BULK_EXCEPTIONS属性来报告或清理随后的循环中的错误。