PLSQL - BULK COLLECT在数据批量处理中的使用

与循环遍历不同，使用BULK COLLECT子句可以批量检索结果，即一次性将结果集绑定到一个表变量中，并从SQL引擎发送到PLSQL引擎，大大减少循环处理的成本。需要注意的是，BULK COLLECT必须将结果集绑定到表类型的变量中。通常可以在SELECT INTO、FETCH INTO以及RETURNING INTO子句中使用BULK COLLECT。

一、SELECT BULK COLLECT INTO

DECLARE
  TYPE emp_rcd_type IS RECORD(
     empno emp.empno%TYPE
    ,ename emp.ename%TYPE);
  TYPE emp_tbl_type IS TABLE OF emp_rcd_type;

  t_emp emp_tbl_type := emp_tbl_type();
BEGIN
  SELECT e.empno, e.ename BULK COLLECT INTO t_emp FROM emp e;
END;

在SELECT BULK COLLECT INTO中，不会报NO_DATA_FOUND异常，更不会报TOO_MANY_ROWS异常。

二、FETCH BULK COLLECT INTO

DECLARE
  TYPE emp_rcd_type IS RECORD(
     empno emp.empno%TYPE
    ,ename emp.ename%TYPE);
  TYPE emp_tbl_type IS TABLE OF emp_rcd_type;

  t_emp emp_tbl_type := emp_tbl_type();

  CURSOR cur_emp IS
    SELECT e.empno, e.ename FROM emp e;
BEGIN
  OPEN cur_emp;
  FETCH cur_emp BULK COLLECT
    INTO t_emp;
  CLOSE cur_emp;
END;

过大的数据量一次性批量FETCH存在性能下降的风险，此时一般采用LIMIT子句来限制每次绑定的数据行数。

DECLARE
  TYPE number_tbl_type IS TABLE OF NUMBER;

  t_trx number_tbl_type := number_tbl_type();

  CURSOR cur_trx IS
    SELECT mmt.transaction_id
      FROM mtl_material_transactions mmt
     WHERE mmt.organization_id = 100
       AND mmt.transaction_type_id = 33
       AND mmt.invoiced_flag = 'N';
BEGIN
  OPEN cur_trx;
  LOOP
    FETCH cur_trx BULK COLLECT
      INTO t_trx LIMIT 500; --fetch 500 records each time
    EXIT WHEN t_trx.count = 0;
  END LOOP;
  CLOSE cur_trx;
END;

三、RETURNING BULK COLLECT INTO

DECLARE
  TYPE number_tbl_type IS TABLE OF NUMBER;

  t_empno number_tbl_type := number_tbl_type();
BEGIN
  UPDATE emp e
     SET e.comm = 500
   WHERE e.sal < 1500
  RETURNING e.empno BULK COLLECT INTO t_empno;
END;