引言

Oracle 使用两种引擎来处理 PL/SQL 代码。所有存储过程的代码由 PL/SQL 引擎处理，所有的 SQL 由 SQL 语句执行器/SQL 引擎处理。

在两个引擎直间的上下文切换会带来开销。如果 PL/SQL 代码在一个集合上循环，为集合里的每个元素执行同样的 DML 操作，那么可以通过一次 bulk 绑定整个集合到 DML 语句上以减少上下文切换。

BULK COLLECT

Bulk 绑定可以在从查询里加载数据集时提升性能。BULK COLLECT INTO 把查询的数据结构化绑定到集合上。

CREATE TABLE bulk_collect_test AS
SELECT owner,
       object_name,
       object_id
FROM   all_objects;


--  使用
DECLARE
  TYPE t_bulk_collect_test_tab IS TABLE OF bulk_collect_test%ROWTYPE;

  l_tab    t_bulk_collect_test_tab := t_bulk_collect_test_tab();
  l_start  NUMBER;
BEGIN

  SELECT *
  BULK COLLECT INTO l_tab
  FROM   bulk_collect_test;

END;

集合是维护在内存里的，因此从一个大查询里做 bulk collect 会对性能有显著的影响。事实上，你不应该以这样的方式直接使用 bulk collect 。应当使用 LIMIT 子句限制返回的行数，这让你得到 bulk 方式的好处，又不会占用大量的服务器内存。

SET SERVEROUTPUT ON
DECLARE
  TYPE t_bulk_collect_test_tab IS TABLE OF bulk_collect_test%ROWTYPE;

  l_tab t_bulk_collect_test_tab;

  CURSOR c_data IS
    SELECT *
    FROM bulk_collect_test;
BEGIN
  OPEN c_data;
  LOOP
    FETCH c_data
    BULK COLLECT INTO l_tab LIMIT 10000;
    EXIT WHEN l_tab.count = 0;

    -- Process contents of collection here.
    DBMS_OUTPUT.put_line(l_tab.count || ' rows');
  END LOOP;
  CLOSE c_data;
END;

从 Oracle 10g 及后续，PL/SQL 优化器把游标 for loop 转换为 bulk collect，用一个大小为 100 的数组。这并不意味着可以忘记 bulk collect，它的意义在于可以控制数组的大小。如果数据行很小，可以增大数组的大小，如果行很宽，可以用小的数组。

FORALL

FORALL 语法允许我们绑定一个集合的内容到单个 DML 语句，允许 DML 对每一行执行而不需要上下文切换。

Oracle 9i R2 允许 update 语句使用 ROW 关键字来定义记录。

FORALL i IN l_tab.first .. l_tab.last
    INSERT INTO forall_test VALUES l_tab(i);

FORALL i IN l_tab.first .. l_tab.last
    UPDATE forall_test
    SET    ROW = l_tab(i)
    WHERE  id  = l_id_tab(i);

SQL%BULK_ROWCOUNT

游标的 SQL%BULK_ROWCOUNT 属性给出了 FORALL 语句一次迭代里每条语句影响的行数。（也是个数组，对应 bulk 集合里的每一行，类似于 JDBC executeBatch 返回的数组）

CREATE TABLE bulk_rowcount_test AS
SELECT *
FROM   all_users;

SET SERVEROUTPUT ON
DECLARE
  TYPE t_array_tab IS TABLE OF VARCHAR2(30);
  l_array t_array_tab := t_array_tab('SCOTT', 'SYS',
                                     'SYSTEM', 'DBSNMP', 'BANANA'); 
BEGIN
  -- Perform bulk delete operation.
  FORALL i IN l_array.first .. l_array.last 
    DELETE FROM bulk_rowcount_test
    WHERE username = l_array(i);

  -- Report affected rows.
  FOR i IN l_array.first .. l_array.last LOOP
    DBMS_OUTPUT.put_line('Element: ' || RPAD(l_array(i), 15, ' ') ||
      ' Rows affected: ' || SQL%BULK_ROWCOUNT(i));
  END LOOP;
END;

SAVE EXCEPTIONS and SQL%BULK_EXCEPTION

对于 BULK 集合的操作，如果没有异常处理器，当前的 bulk 已完成的操作将回滚。如果有异常处理器，在异常之前已完成的工作将保留，但之后的不会继续处理。SAVE EXCEPTIONS 子句可以捕获异常，允许继续跳过他们（异常）。后面我们可以检测异常，通过游标的 SQL%BULK_EXCEPTION 的属性。

  -- Perform a bulk operation.
  BEGIN
    FORALL i IN l_tab.first .. l_tab.last SAVE EXCEPTIONS
      INSERT INTO exception_test
      VALUES l_tab(i);
  EXCEPTION
    WHEN ex_dml_errors THEN
      l_error_count := SQL%BULK_EXCEPTIONS.count;
      DBMS_OUTPUT.put_line('Number of failures: ' || l_error_count);
      FOR i IN 1 .. l_error_count LOOP
        DBMS_OUTPUT.put_line('Error: ' || i || 
          ' Array Index: ' || SQL%BULK_EXCEPTIONS(i).error_index ||
          ' Message: ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
      END LOOP;
  END;

Bulk Binds and Triggers

对于 bulk update 和 delete ， timing points 保持不变。集合里的每一行触发一个 before statement, before row, after row 和 after statement timing point 。

对于 bulk insert，语句级的触发器只在整个 bulk 操作的开始和结束时触发，而不是集合里的每一行。

欢迎关注我的微信公众号: coderbee笔记，可以更及时回复你的讨论。