Два (или более) DML внутри одного цикла операции массового сбора

Является ли что-то "хорошим подходом" очень субъективно - это зависит от того, с чем вы пытаетесь сравнить.

Если мы предположим, что ваш запрос к 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, где вызывающий не будет знать, что произошла ошибка, где стек ошибок потерян и где нет гарантии, что вызывающее приложение даже выделило буфер для данных, которые будут написано в является ошибкой, ожидающей своего появления. Если в вашей системе есть код такого типа, вы неизбежно в конечном итоге столкнетесь с трудностями при воспроизведении ошибок, потому что какой-то фрагмент кода где-то встретил и проглотил исключение, вызывающее неожиданный сбой последующих фрагментов кода.

Что-то не так в вашей исходной процедуре, касающейся управления ошибками, и это затрудняет преобразование логики в массовую обработку.

По сути, логика вашей первой процедуры такова: запустите два оператора в цикле, успешно завершите работу в первый раз, когда вы столкнетесь с ошибкой или в конце курсора, в зависимости от того, что произойдет раньше.

Это неправильная логика транзакции. Если два ваших оператора работают в тандеме, а второй терпит неудачу, первый не откатывается!

Что вы, вероятно, захотите сделать, так это: запустить два оператора в цикле; если вы столкнулись с ошибкой, зарегистрируйте информацию и отмените изменения, если не удалось завершить работу. Отменить изменения очень легко в PL/SQL, вам нужно только позволить ошибке распространяться:

PROCEDURE FOO(IN_FOO IN VARCHAR2) IS
BEGIN
  FOR CUR IN (SELECT COL1,COL2,COL3 FROM SOME_TABLE) LOOP
    BEGIN
       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;
    EXCEPTION
       WHEN OTHERS THEN
          dbms_output.put_line(cur.col1/*...*/); -- log **useful** debug info
          RAISE;-- very important for transactional logic
    END;
  END LOOP;
END;

Кстати, DBMS_OUTPUT не лучший инструмент ведения журнала, вы можете создать таблицу ведения журнала и автономную процедуру транзакции для вставки соответствующего сообщения об ошибке и идентификаторов.

Если вы хотите преобразовать приведенную выше процедуру с помощью массовой логики, лучше всего использовать метод, описанный Justin Cave. (отдельные операторы DML). При использовании объемных массивов необходимо использовать SAVE EXCEPTIONS , если вы хотите регистрировать отдельные исключения. Не забудьте повторно вызвать ошибку. Это должно работать:

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 SAVE EXCEPTIONS -- important clause
            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 SAVE EXCEPTIONS -- 
            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
            FOR i IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
               dbms_output.put_line('error '||i||':'||
                      SQL%BULK_EXCEPTIONS(i).error_code);
               dbms_output.put_line('col1='|| 
                      lt_cur(SQL%BULK_EXCEPTIONS(i).error_index).col1);-- 11g+
            END LOOP;
         raise_application_error(-20001, 'error in bulk processing');
      END;
   END LOOP;
END foo_fast;

Я нашел решение, используя такой поток:

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 SAVE EXCEPTIONS
        INSERT INTO OTHER_TABLE (C1,C2,C3) VALUES (LT_CUR(I).COL1,LT_CUR(I).COL2,LT_CUR(I).COL3);
    EXCEPTION
      WHEN DML_EXCEPTION THEN
        FOR I IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
          DBMS_OUTPUT.PUT_LINE(SQLERRM(-SQL%BULK_EXCEPTIONS(1).ERROR_CODE));
          LT_CUR.DELETE(SQL%BULK_EXCEPTIONS(1).ERROR_INDEX);
    END;
    FORALL I IN INDICES OF LT_CUR 
        UPDATE THIRD_TABLE T SET T.C_SUM = LT_CUR(I).COL2 + LT_CUR(I).COL3 WHERE T.C_ID = LT_CUR(I).COL1);
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERROR || ': ' || SQLERRM);
END FOO_FAST;

В этом потоке:

1. Все исключения, возникшие в INSERT, будут храниться в коллекции SQL%BULK_EXCEPTIONS.
2. Каждое исключение будет регистрироваться DBMS_OUTPUT.PUT_LINE (в реальной жизни в таблице журнала AUTONOMOUS TRANSACTION процедурой)
3. Каждый индекс ошибки LT_CUT будет удален методом DELETE при сборе.
4. В UPDATE будут использоваться только «хорошие» строки, потому что предложение INDICES OF позволяет выполнять массовую операцию над разреженной коллекцией путем удаления ссылки на определенные элементы.