oracle.notes: FORALL

Краткие итоги

Входной массив

Может быть и PL/SQL массивом и Nested Table.
Три метода перебора элементов
1.Указание интервала индексов


forall i in l_col.first .. l_col.last
forall i in 1 .. 10
-- В этом случае индексы округлятся
forall i in 1.3 .. 2.4

В этом случае нельзя работать с коллекциями с дырками (удаленными элементами)

2. INDICES OF


FORALL i IN INDICES OF l_t
FORALL i IN INDICES OF l_t BETWEEN 1 AND 1

Позволяет работать с коллекцияями с дырками. Можно ограничить интервал
3. VALUES OF


 FORALL i IN VALUES OF l_tsubscripts

В одной коллекции храним индексы (PLS_INTEGER или BINARY_INTEGER) от рабочей коллекции. Если указанного элемента в рабочей коллекции нет -- будет ошибка. Индексы могут храниться как в PL/SQL так и в Nested массиве.

Обработка ошибок

Если на одном из элементов коллекции проиисходит иисключение, то транзакцияя сохранит все предыдущие успешные операции.

Можно использовать конструкцию SAVE EXCEPTION. В этом случае операция дойдет до конца и, если при выполнении встретились ошибки, будет сгенерировано исключение ORA-24381: error(s) in array DML. Для обработки ошибок можно использовать массив SQL%BULK_EXCEPTIONS

SQL%BULK_EXCEPTIONS

1. Нумерация всегда с 1. Чаще всего для обработки будет использоваться
FOR err IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP 2. SQL%BULK_EXCEPTIONS(err).ERROR_INDEX - номер итерации. Если массив неразряженный и начинается с 1 -- то получить входные данные, вызвавшие ошибку очень просто. В остальных случаях придется яотсчитывать в цикле

3. SQL%BULK_EXCEPTIONS(err).ERROR_CODE -- код ошибки

4. SQLERRM(-SQL%BULK_EXCEPTIONS(err).ERROR_CODE) -- сообщение об ошибке

SQL%BULK_ROWCOUNT

Массив, который хранит в себе сколько строк было обработано на каждой итерации. Массив индексируется индексами от рабочего массива, т.е. если в рабочем массиве элементы со 2-го по 5-й, то и в SQL%BULK_ROWCOUNT будут 2, 3, 4, 5

Пример кода


DROP TABLE a;
CREATE TABLE a (a NUMBER CHECK (a > 0));

DECLARE
 l_cnt NUMBER;  

  TYPE t IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  l_t t;
  
  TYPE t_nest IS TABLE OF NUMBER;
  l_tn t_nest := t_nest();

  -- suscript -- только PLS_INTEGER and BINARY_INTEGER
  TYPE t_subscripts IS TABLE OF PLS_INTEGER;
  l_tsubscripts t_subscripts := t_subscripts();

  TYPE t_subscripts2 IS TABLE OF PLS_INTEGER index by pls_integer;
  l_tsubscripts2 t_subscripts2;
  
BEGIN
  l_t(1) := 1;
  l_t(3) := 3;
  -- ora-22160 -- коллекция разряженная
  BEGIN
    FORALL i IN l_t.first .. l_t.last
      INSERT INTO a VALUES (l_t(i));
    dbms_output.put_line('Should be 0');      
  EXCEPTION WHEN OTHERS THEN
    dbms_output.put_line('Error: ' || SQLCODE || ' - ' || SQLERRM);
  END;
  
  -- можно использовать indices of
  FORALL i IN INDICES OF l_t
    INSERT INTO a VALUES (l_t(i));
  dbms_output.put_line('Row count: ' || SQL%ROWCOUNT);

  -- можно использовать indices of
  FORALL i IN INDICES OF l_t BETWEEN 1 AND 1
    INSERT INTO a VALUES (l_t(i));
  dbms_output.put_line('Indices of with lower and upper bound Row count: ' || SQL%ROWCOUNT);
  
  -- Аналогично работает с nested tables
  l_tn.EXTEND(3);
  l_tn(1) := 1;
  l_tn(2) := 2;  
  l_tn(3) := 3;
  l_tn.delete(2);
  BEGIN
    FORALL i IN l_tn.first .. l_tn.last
      INSERT INTO a VALUES (l_tn(i));
      dbms_output.put_line('Should be 0');          
  EXCEPTION WHEN OTHERS THEN
    dbms_output.put_line('Nested tables Error: ' || SQLCODE || ' - ' || SQLERRM);
  END;

  FORALL i IN INDICES OF l_tn
    INSERT INTO a VALUES (l_tn(i));
  dbms_output.put_line('Nested Row count: ' || SQL%ROWCOUNT);

  -- values of
  l_tsubscripts.EXTEND(1);
  l_tsubscripts(1) := 3;

  -- При использованиии values of + nested table коллекция должна начинаться с 1
  BEGIN
    FORALL i IN VALUES OF l_tsubscripts
      INSERT INTO a VALUES (l_tn(i));
    dbms_output.put_line('values of + nested table Row count: ' || SQL%ROWCOUNT);
  EXCEPTION WHEN OTHERS THEN
    dbms_output.put_line('values of + nested table Error: ' || SQLCODE || ' - ' || SQLERRM);
  END;

  -- При использованиии values of + pl/sql массивы коллекция может быть какая угодно
  l_tsubscripts2(1000) := 3;
  BEGIN
    FORALL i IN VALUES OF l_tsubscripts2
      INSERT INTO a VALUES (l_tn(i));
    dbms_output.put_line('Values of Row count: ' || SQL%ROWCOUNT);
  EXCEPTION WHEN OTHERS THEN
    dbms_output.put_line('Error: ' || SQLCODE || ' - ' || SQLERRM);
  END;

  -- Нет элемента -- это ошибка
  l_tsubscripts2(1000) := 3;
  l_tsubscripts2(1001) := 10;
  BEGIN
    FORALL i IN VALUES OF l_tsubscripts2
      INSERT INTO a VALUES (l_tn(i));
    dbms_output.put_line('Values of Row count: ' || SQL%ROWCOUNT);
  EXCEPTION WHEN OTHERS THEN
    dbms_output.put_line('No element Error: ' || SQLCODE || ' - ' || SQLERRM);
  END;
  l_t.delete;  
  l_t(1) := 1;
  l_t(2) := 2;
  l_t(3) := 3;
  -- Границы округляются до ближайшего целого
  FORALL i IN 1.3 .. 2.4
    INSERT INTO a VALUES (l_t(i));
    dbms_output.put_line('bounds rounded Row count: ' || SQL%ROWCOUNT);  


  -- SAVE EXCEPTION
  -- Если не все гладко, то рейсит ошибку ORA-24381
  -- Далее разбираемся с SQL%BULK_EXCEPTIONS
  l_t.delete;
  l_t(1) := 1;
  l_t(2) := 2;
  l_t(3) := 3;
  l_t(4) := -4;
  l_t.delete(2);
  BEGIN
    FORALL i IN 1 .. 4 SAVE EXCEPTIONS
      INSERT INTO a VALUES (l_t(i));
      dbms_output.put_line('With save exception Row count: ' || SQL%ROWCOUNT);  
  EXCEPTION WHEN OTHERS THEN
    dbms_output.put_line('Error: ' || SQLCODE || ' - ' || SQLERRM);
    FOR err IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
      dbms_output.put_line('Row ' || err || ' iteration ' || SQL%BULK_EXCEPTIONS(err).ERROR_INDEX 
          || ' code ' || SQL%BULK_EXCEPTIONS(err).ERROR_CODE 
          || ' message: ' || SQLERRM(-SQL%BULK_EXCEPTIONS(err).ERROR_CODE));
    END LOOP;
  END;

  -- SAVE EXCEPTION для разряженных коллекций с INDICES OF
  l_t.delete;
  l_t(10) := 1;
  l_t(20) := 2;
  l_t(30) := -3;
  BEGIN
    FORALL i IN INDICES OF l_t SAVE EXCEPTIONS
      INSERT INTO a VALUES (l_t(i));
      dbms_output.put_line('With save exception indices of Row count: ' || SQL%ROWCOUNT);  
  EXCEPTION WHEN OTHERS THEN
    dbms_output.put_line('indices of Error: ' || SQLCODE || ' - ' || SQLERRM);
    FOR err IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
      dbms_output.put_line('indices of Row ' || err || ' iteration ' || SQL%BULK_EXCEPTIONS(err).ERROR_INDEX 
          || ' code ' || SQL%BULK_EXCEPTIONS(err).ERROR_CODE 
          || ' message: ' || SQLERRM(-SQL%BULK_EXCEPTIONS(err).ERROR_CODE));
    END LOOP;
  END;
 
  ROLLBACK;
 
  -- при ошибке все предыдущие операции остаются
  l_t.delete;  
  l_t(1) := 1;
  l_t(2) := -1;
  BEGIN
    FORALL i IN 1 .. 2
      INSERT INTO a VALUES (l_t(i));
  EXCEPTION WHEN OTHERS THEN
    dbms_output.put_line('insert with error Row count: ' || SQL%ROWCOUNT);
    SELECT COUNT(*) INTO l_cnt FROM a;
    dbms_output.put_line('insert with error rows in table ' || l_cnt);
  END;

  -- использование SQL%BULK_ROWCOUNT
  ROLLBACK;
  l_t.delete;
  l_t(1) := 1;
  l_t(2) := 1;
  FORALL i IN 1 .. 2
    INSERT INTO a VALUES (l_t(i));

  -- Индекс в BULK_ROWCOUNT совпадает с тем, что мы обрабатывали
  FORALL i IN 2 .. 2
    UPDATE a SET a = l_t(i) WHERE a = l_t(i);
  dbms_output.put_line('Updated SQL%BULK_ROWCOUNT=' || SQL%BULK_ROWCOUNT(2));
  
   
END;
/

oracle.notes

2013-11-22

FORALL