plsql - 一个批量收集操作循环中的两个(或更多)DML

Question

我对 Oracle 11g 上的 BULK COLLECT 逻辑有疑问。

存储过程中的原始逻辑是:

PROCEDURE FOO(IN_FOO IN VARCHAR2) IS
BEGIN
  FOR CUR IN (SELECT COL1,COL2,COL3 FROM SOME_TABLE) LOOP
    INSERT INTO OTHER_TABLE (C1,C2,C3) VALUES (CUR.COL1,CUR.COL2,CUR.COL3);
    UPDATE THIRD_TABLE T SET T.C_SUM = CUR.COL2 + CUR.COL3 WHERE T.C_ID = CUR.COL1);
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERROR || ': ' || SQLERRM);
END FOO;

但我想使用BULK COLLECT 功能。

我写了类似的东西:

PROCEDURE FOO_FAST(IN_FOO IN VARCHAR2) IS
  CURSOR CUR IS SELECT COL1,COL2,COL3 FROM SOME_TABLE;
  TYPE RT_CUR IS TABLE OF CUR%ROWTYPE;
  LT_CUR RT_CUR;
  DML_EXCEPTION EXCEPTION;
  PRAGMA EXCEPTION_INIT(DML_EXCEPTION, -24381);
BEGIN
  OPEN CUR;
  LOOP
    FETCH CUR BULK COLLECT INTO LT_CUR LIMIT 1000;
    EXIT WHEN LT_CUR.COUNT = 0;
    BEGIN
      FORALL I IN 1 .. LT_CUR.COUNT 
        INSERT INTO OTHER_TABLE (C1,C2,C3) VALUES (LT_CUR(I).COL1,LT_CUR(I).COL2,LT_CUR(I).COL3);
      FORALL I IN 1 .. LT_CUR.COUNT 
        UPDATE THIRD_TABLE T SET T.C_SUM = LT_CUR(I).COL2 + LT_CUR(I).COL3 WHERE T.C_ID = LT_CUR(I).COL1);
    EXCEPTION
      WHEN DML_EXCEPTION THEN
        FORALL I IN 1 .. SQL%BULK_EXCEPTIONS(1).ERROR_INDEX-1
          UPDATE THIRD_TABLE T SET T.C_SUM = LT_CUR(I).COL2 + LT_CUR(I).COL3 WHERE T.C_ID = LT_CUR(I).COL1);
        DBMS_OUTPUT.PUT_LINE(SQLERRM(-SQL%BULK_EXCEPTIONS(1).ERROR_CODE));
        RETURN;
    END;
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERROR || ': ' || SQLERRM);
END FOO_FAST;

这是解决这个问题的好方法吗？

如果我有更多 DML 要执行怎么办？

---

好的。我的问题更复杂，但我想简化它并用漂亮的示例代码丰富它。错误 OTHERS 处理不是此问题的一部分。也许这会更清楚:

这是怎么回事:

  FOR CUR IN (SELECT COL1,COL2,COL3 FROM SOME_TABLE) LOOP
    INSERT INTO OTHER_TABLE (C1,C2,C3) VALUES (CUR.COL1,CUR.COL2,CUR.COL3);
    UPDATE THIRD_TABLE T SET T.C_SUM = CUR.COL2 + CUR.COL3 WHERE T.C_ID = CUR.COL1);
  END LOOP;

更改为 BULK COLLECT 和 FORALL 语句？

Answer 1

某事是否是“好方法”是非常主观的——这取决于您要与之比较的对象。

如果我们假设您对 some_table 的查询没有谓词，那么几乎可以肯定的是，在集合中工作比进行任何类型的循环更有效(除了更少的代码之外)

PROCEDURE FOO(IN_FOO IN VARCHAR2) IS
BEGIN
  INSERT INTO other_table( c1, c2, c3 )
    SELECT col1, col2, col3
      FROM some_table;

  UPDATE third_table tt
     SET tt.c_sum = (SELECT st.col2 + st.col3
                       FROM some_table st
                      WHERE tt.c_id = st.col1)
   WHERE EXISTS( SELECT 1
                   FROM some_table st
                  WHERE tt.c_id = st.col1);
END;

通常，WHEN OTHERS 异常处理程序不是一个好主意。捕获异常只是为了尝试将其写入 DBMS_OUTPUT ，调用者不知道发生了错误，错误堆栈丢失，并且无法保证调用应用程序甚至分配要写入的数据的缓冲区是一个等待发生的错误。如果您的系统中有此类代码，您将不可避免地最终会努力追寻重现错误，因为某处的某些代码遇到并吞下了异常，导致后面的代码以意想不到的方式失败。