FORALL и BULK COLLECT - ускоряем запросы в PL/SQL

Ускорение запросов в PL/SQL через FORALL и BULK COLLECTВ Oracle были введены новые средства, значительно повышающие производительность запросов в PL/SQL, — команда FORALL и секция BULK COLLECT. Они объединяются общим термином конструкций массовой обработки (bulk processing). Для чего нужны эти конструкции, спросите вы? Как известно, язык PL/SQL тесно интегрирован с SQL-ядром базы данных Oracle. Он является основным языком программирования для Oracle, несмотря на то что теперь в базе данных также может использоваться язык Java. Однако эта интеграция не означает, что выполнение кода SQL из программы PL/SQL не сопряжено с затратами.



В ходе обработки блока программного кода ядро PL/SQL выполняет процедурные команды самостоятельно, а команды SQL передает ядру SQL. Уровень SQL выполняет команды и при необходимости возвращает результаты ядру PL/SQL. Передача управления между ядрами PL/SQL и SQL (рис. 1) называется переключением контекста. Каждое переключение контекста приводит к дополнительным затратам ресурсов. Необходимость в переключении контекста, приводящим к снижению производительности, встречается во многих ситуациях. PL/SQL и SQL тесно интегрированы на синтаксическом уровне, но во внутренней реализации они связаны отнюдь не так тесно, как кажется на первый взгляд.

 

Переключения контекста между PL/SQL и SQL

Рис. 1. Переключения контекста между PL/SQL и SQL

 

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

Рассмотрим команду FORALL, изображенную на рис. 2. Вместо того чтобы перебирать обновляемые записи в курсорном цикле FOR или в цикле со счетчиком, мы используем заголовок FORALL для определения общего количества итераций. На стадии выполнения ядро PL/SQL «расширяет» команду UPDATE в набор команд для выполнения всех итераций, а затем передает их ядру SQL за одно переключение контекста. Иначе говоря, выполняются те же команды SQL, но за одно обращение к ядру SQL.

 

 Одно переключение контекста при использовании FORALL

Рис. 2. Одно переключение контекста при использовании FORALL

 

Сокращение количества переключений контекста приводит к значительному ускорению выполнения в PL/SQL команд SQL, выполняющих выборку нескольких строк данных. Давайте поближе познакомимся с конструкциями BULK COLLECT и FORALL.

 

Ускорение выборки с использованием BULK COLLECT

Синтаксис BULK COLLECT позволяет за одно обращение к базе данных извлечь из явного или неявного курсора несколько строк данных. Выборка данных с помощью запроса с секцией BULK COLLECT сокращает количество переключений контекста между PL/SQL и SQL, благодаря чему работа выполняется быстрее и с меньшими затратами. Рассмотрим следующий фрагмент кода. Нам нужно прочитать сотни строк данных об автомобилях, загрязняющих окружающую среду. Эти данные помещаются в набор коллекций, что значительно упрощает их дальнейший анализ и обработку:

DECLARE
   TYPE names_t IS TABLE OF transportation.name%TYPE;
   TYPE mileage_t IS TABLE OF transportation.mileage %TYPE; 
   names names_t; 
   mileages mileage_t;
   CURSOR major_polluters_cur 
   IS
      SELECT name, mileage FROM transportation 
      WHERE transport_type = 'AUTOMOBILE' AND mileage < 20;
BEGIN
   FOR bad_car IN major_polluters_cur 
   LOOP
      names.EXTEND;
      names (major_polluters_cur %ROWCOUNT) := bad_car.NAME; 
      mileages.EXTEND;
      mileages (major_polluters_cur%ROWCOUNT) := bad_car.mileage;
   END LOOP;
   -- Далее можно работать с данными в коллекциях 
END;

Этот код выполняет поставленную задачу, но далеко не самым эффективным образом. Если, к примеру, таблица transportation содержит 2000 строк, PL/SQL придется выполнить 2000 операций выборки данных из глобальной системной области.

Секция BULK COLLECT способна значительно ускорить выборку. Включив ее в курсор (явный или неявный), вы указываете ядру SQL на необходимость перед возвратом управления PL/SQL связать выходные данные из множества строк с заданными коллекциями. Синтаксис этой секции таков:

... BULK COLLECT INTO имя_коллекции[, имя_коллекции] ...

Здесь имя_коллекции определяет коллекцию, в которую должны быть помещены выходные данные курсора.

Некоторые правила и ограничения, связанные с использованием секции BULK COLLECT:

  •  До выхода Oracle9i секция BULK COLLECT могла использоваться только в статических командах SQL. Последующие версии поддерживают ее применение и в динамическом SQL.
  •  Ключевые слова BULK COLLECT могут использоваться только в секциях SELECT INTO, FETCH INTO и RETURNING INTO.
  •  Ядро SQL автоматически инициализирует и расширяет коллекции, заданные в секции BULK COLLECT. Заполнение начинается с индекса 1, далее элементы вставляются последовательно (без пропусков), с заменой определенных ранее элементов.
  •  Команда select...bulk collect не выдает исключение no_data_found, если при выборке не получено ни одной строки. Наличие данных проверяется по содержимому коллекции.
  •  Если запрос не вернул ни одной строки, метод COUNT коллекции возвращает 0.

Рассмотрим эти правила на примерах. Новая версия предыдущего примера, усовершенствованная с помощью секции BULK COLLECT:

DECLARE
   TYPE names_t IS TABLE OF transportation.name%TYPE;
   TYPE mileage_t IS TABLE OF transportation.mileage %TYPE; 
   names names_t;
   mileages mileage_t;
BEGIN
   SELECT name, mileage BULK COLLECT INTO names, mileages 
      FROM transportation
         WHERE transport_type = 'AUTOMOBILE'
            AND mileage < 20;
   /* Далее можно работать с данными в коллекциях */
END;

Из программы удаляется код инициализации и расширения коллекций.

Для решения задачи не обязательно использовать неявные курсоры — этот пример также можно переписать с использованием явного курсора:

DECLARE
   TYPE names_t IS TABLE OF transportation.name%TYPE;
   TYPE mileage_t IS TABLE OF transportation.mileage %TYPE; 
   names names_t; 
   mileages mileage_t;

   CURSOR major_polluters_cur IS
      SELECT name, mileage FROM transportation 
       WHERE transport_type = 'AUTOMOBILE' AND mileage < 20;
BEGIN
   OPEN major_polluters_cur;
   FETCH major_polluters_cur BULK COLLECT INTO names, mileages;
   CLOSE major_polluters_cur;
   ...
END;

Я также могу упростить свою работу, выполнив выборку в коллекцию записей:

DECLARE
   TYPE transportation_aat IS TABLE OF transportation%ROWTYPE 
      INDEX BY PLS_INTEGER; 
   l_transportation transportation_aat;
BEGIN
   SELECT * BULK COLLECT INTO l_transportation 
      FROM transportation
         WHERE transport_type = 'AUTOMOBILE'
            AND mileage < 20;
   -- Теперь работаем с данными в коллекциях 
END;

В OraclelOg и последующих версиях компилятор PL/SQL автоматически оптимизирует курсорный цикл FOR, чтобы его производительность была сравнима с производительностью BULK COLLECT. Вам не нужно заниматься явным преобразованием этого кода — если только в теле цикла не выполняются (прямо или косвенно) команды DML. База данных не оптимизирует команды DML в FORALL, поэтому вы должны явно преобразовать курсорный цикл FOR для использования BULK COLLECT. После этого коллекции, заполненные с BULK COLLECT, используются для управления командой FORALL.

 

Ограничение на количество возвращаемых строк

Для секций BULK COLLECT Oracle поддерживает условие LIMIT, ограничивающее количество строк, выбираемых из базы данных. Его синтаксис таков:

FETCH курсор BULK COLLECT INTO ... [LIMIT строки];

Здесь строки — это литерал, переменная или выражение, возвращающее значение типа NUMBER (иначе инициируется исключение VALUE_ERROR).

Ограничение очень полезно при использовании BULK COLLECT, потому что оно помогает управлять объемом памяти, используемым программой при обработке данных. Допустим, вам потребовалось запросить и обработать 10 000 строк данных. Вы можете использовать BULK COLLECT для выборки всех записей и заполнения объемистой коллекции, однако такой подход приводит к значительным затратам памяти в PGA. Его выполнение из разных схем Oracle приведет к снижению производительности приложения из-за необходимости выгрузки PGA.

В следующем блоке секция LIMIT используется в команде FETCH, вызываемой в простом цикле:

DECLARE
   CURSOR allrows_cur IS SELECT * FROM employees;
   TYPE employee_aat IS TABLE OF allrows_cur%ROWTYPE 
      INDEX BY BINARY_INTEGER; 
   l_employees employee_aat;
BEGIN
   OPEN allrows_cur;
   LOOP
      FETCH allrows_cur BULK COLLECT INTO l_employees LIMIT 100;

      /* Обработка данных с перебором содержимого коллекции. */
     FOR l_row IN 1 .. l_employees.COUNT 
      LOOP
         upgrade_employee_status (l_employees(l_row).employee_id);
      END LOOP;

      EXIT WHEN allrows_cur%NOTFOUND;
   END LOOP;

   CLOSE allrows_cur;
END;

Обратите внимание: цикл прерывается по проверке значения allrows_cur%NOTFOUND в конце цикла. При выборке данных по одной строке этот код обычно размещается сразу же за командой FETCH. С конструкцией BULK COLLECT так поступать не стоит, потому что при достижении последнего набора строк курсор будет исчерпан (а %NOTFOUND вернет TRUE), но при этом в коллекции останутся элементы, которые необходимо обработать. Проверьте либо атрибут %NOTFOUND в конце цикла, либо содержимое коллекции непосредственно после выборки:

LOOP
   FETCH allrows_cur BULK COLLECT INTO l_employees LIMIT 100;
   EXIT WHEN l_employees.COUNT = 0;

Недостаток второго решения заключается в выполнении дополнительной выборки, не возвращающей строк (по сравнению с проверкой %NOTFOUND в конце тела цикла).

В Oracle Database 12c появилась возможность использования секции FIRST ROWS для ограничения количества строк, возвращаемых выборкой с BULK COLLECT. В следующем программном блоке используется команда FETCH с секцией LIMIT, которая выполняется в простом цикле. Этот код читает только первые 50 строк, определяемых командой SELECT:

DECLARE
   TYPE salaries_t IS TABLE OF employees.salary%TYPE; 
   l_salaries salaries_t;
BEGIN
   SELECT salary BULK COLLECT INTO sals FROM employees 
   FETCH FIRST 50 ROWS ONLY;
END;
/

 

Выборка нескольких столбцов

Как показывают приведенные примеры, секция BULK COLLECT позволяет выбрать из курсора данные нескольких столбцов и поместить их в разные коллекции. Несомненно, было бы элегантнее извлечь набор столбцов в одну коллекцию записей. Такая возможность появилась в Oracle9i Release 2.

Допустим, нам нужно извлечь из таблицы transportation информацию обо всех машинах с расходом топлива менее галлона на 20 миль. Задача решается с минимальным объемом кода:

DECLARE
   -- Объявление типа коллекции
   TYPE VehTab IS TABLE OF transportation%ROWTYPE;

   -- Создание экземпляра коллекции на основе типа 
   gas_guzzlers VehTab;
BEGIN
   SELECT *
      BULK COLLECT INTO gas_guzzlers 
      FROM transportation 
     WHERE mileage < 20;
     ...

До Oracle9i Release 2 при выполнении этого кода было бы выдано исключение (PLS-00597). При извлечении данных из курсора в коллекцию записей также можно использовать секцию LIMIT, ограничивающую количество выбираемых строк.

 

Использование RETURNING с BULK COLLECT

Вы уже знаете, как использовать секцию BULK COLLECT с явными и неявными курсорами. Ее также можно включить в команду FORALL, чтобы воспользоваться секцией RETURNING.

Секция RETURNING позволяет получить информацию из команды DML. Таким образом, благодаря RETURNING вы обходитесь без дополнительных запросов к базе данных для определения результата только что завершенной операции DML.

Допустим, конгресс принял закон, по которому оклад самых высокооплачиваемых сотрудников компании не должен превышать оклад низкооплачиваемых сотрудников более чем в 50 раз. Общее число сотрудников составляет 250 000 человек. Поскольку руководство компании не намерено уменьшать доходы генерального директора, решено повысить оклады сотрудников, зарабатывающих менее 1/50 части его дохода в 145 миллионов долларов, и урезать оклады всего остального высшего руководства — должен же кто-то компенсировать расходы.

Чтобы пересчитать и обновить такое количество данных, придется изрядно потрудиться. Команда FORALL ускорит работу, однако после выполнения всех расчетов и внесения изменений нам нужно будет вывести отчет со старыми и новыми окладами. В этом нам пригодится секция RETURNING.

Начнем с функции, которая возвращает величину оклада генерального директора:


FUNCTION salforexec (title_in IN VARCHAR2) RETURN NUMBER 
IS
   CURSOR ceo_compensation IS
      SELECT salary + bonus + stock_options +
            mercedes_benz_allowance + yacht_allowance 
        FROM compensation 
      WHERE title = title_in; 
   big_bucks NUMBER;
BEGIN
   OPEN ceo_compensation;
   FETCH ceo_compensation INTO big_bucks;
   RETURN big_bucks;
END;

В главном блоке программы обновления мы объявим ряд локальных переменных и запрос для определения низкооплачиваемых и высокооплачиваемых сотрудников, чья зарплата будет обновлена:

DECLARE
   big_bucks NUMBER := salforexec ('CEO'); 
   min_sal NUMBER := big_bucks / 50; 
   names name_varray; 
   old_salaries number_varray; 
   new_salaries number_varray;

   CURSOR affected_employees (ceosal IN NUMBER)
   IS
      SELECT name, salary + bonus old_salary 
        FROM compensation 
      WHERE title != 'CEO'
         AND ((salary + bonus < ceosal / 50)
               OR (salary + bonus > ceosal / 10)) ;

В начале исполняемого раздела определяемые запросом данные помещаются в две коллекции командой FETCH, содержащей секцию BULK COLLECT:

OPEN affected_employees (big_bucks);
FETCH affected_employees
   BULK COLLECT INTO names, old_salaries;

Затем команда FORALL выполняет последовательный перебор элементов коллекции names:

FORALL indx IN names.FIRST .. names.L*
   UPDATE compensation
      SET salary =
        GREATEST(
           DECODE (
              GREATEST (min_sal, salary), 
                 min_sal, min_sal, 
              salary / 5), 
           min_sal )
   WHERE name = names (indx)
   RETURNING salary BULK COLLECT INTO new_salaries;

Команда DECODE либо увеличивает оклад сотрудника на 80%, либо уменьшает его. В конце используется секция RETURNING, с помощью которой мы помещаем новые значения в коллекцию new_salaries.

Вот и все. Благодаря секции RETURNING нам не придется выполнять еще один запрос, выбирающий из базы данных новые оклады сотрудников, и можно сразу приступать к формированию отчета:

FOR indx IN names.FIRST .. names.LAST 
LOOP
   DBMS_OUTPUT.PUT_LINE (
      RPAD (names(indx), 20) ||
      RPAD (' Old: ' || old_salaries(indx), 15) ||
      ' New: ' || new_salaries(indx)
      );
END LOOP;

А вот как выглядит отчет, сгенерированный сценарием onlyfair.sql:

John DayAndNight     Old:   10500    New: 2900000 
Holly Cubicle        Old:   52000    New: 2900000
Sandra Watchthebucks Old:   22000000 New: 4000000

Значения столбцов и выражения, возвращаемые секцией RETURNING команды FORALL, добавляются в коллекции после ранее записанных туда значений. Если же секция RETURNING используется в обычном цикле FOR, предыдущие значения заменяются данными, возвращенными последней командой DML.

 

Быстрое выполнение операций DML и команда FORALL

BULK COLLECT ускоряет выполнение запросов на выборку. FORALL делает то же самое для операций вставки, обновления, удаления и слияния (FORALL с командой MERGE поддерживается только в Oracle11g). FORALL приказывает ядру PL/SQL выполнить массовую привязку всех элементов одной или нескольких коллекций перед отправкой команд ядру SQL.

С учетом основополагающей роли SQL в приложениях на базе Oracle и влияния команд DML на общую производительность команда FORALL, вероятно, является самым важным средством оптимизации в языке PL/SQL.

Итак, если вы еще не используете FORALL, у меня есть для вас и плохие, и хорошие новости. Плохие новости: кодовая база вашего приложения годами не совершенствовалась для использования важнейших нововведений Oracle. Хорошие новости: когда вы начнете использовать FORALL, ваши пользователи будут радоваться очень приятному (и относительно легко достижимому) приросту производительности.

Ниже объясняются основные особенности и нюансы использования FORALL с множеством примеров.

 

Синтаксис оператора FORALL

Хотя команда FORALL выполняет итеративную обработку (то есть перебирает все строки коллекции), она не является циклом FOR, а потому не имеет ни команды LOOP, ни команды END LOOP. Ее синтаксис выглядит так:

FORALL индекс IN
   [ нижняя_граница ... верхняя_граница |
      INDICES OF коллекция |
      VALUES OF коллекция
   ]
   [ SAVE EXCEPTIONS ] 
   команда_sqL;

Здесь индекс — целочисленная переменная, неявно объявляемая Oracle; нижняя граница — начальное значение индекса (строка или элемент коллекции); верхняя граница — конечное значение индекса (строка или элемент коллекции); Kомaнда_sql — команда SQL, выполняемая для каждого элемента коллекции; коллекция — коллекция PL/SQL, используемая для выборки индексов в массиве, упоминаемом в командe sql. Конструкции INDICES OF и VALUES OF поддерживаются начиная с OraclelOg. Необязательная секция SAVE EXCEPTIONS указывает FORALL на необходимость обработки всех строк данных с сохранением всех возникающих исключений.

При использовании FORALL необходимо соблюдать следующие правила:

  •  Тело команды FORALL должно представлять собой одну команду DML — INSERT, UPDATE, DELETE или MERGEOraclellg и выше).
  •  Команда DML должна содержать ссылки на элементы коллекции, индексируемые в команде FORALL. Область видимости переменной индекс ограничивается командой FORALL; ссылаться на нее за пределами цикла нельзя. Помните, что нижняя граница и верхняя граница не обязаны задавать все множество элементов коллекции.
  •  Переменная цикла не должна объявляться явно. Ядро PL/SQL объявляет ее автоматически с типом PLS_INTEGER.
  •  Нижняя граница и верхняя граница должны задавать допустимый диапазон смежных индексов для коллекции, используемой в команде SQL. Для разреженных коллекций выдается сообщение об ошибке (ORA-22160).

Соответствующий пример приведен в файле missing_element.sql. Начиная с версии Oracle Database 10g допускается использование синтаксиса INDICES OF и VALUES OF для работы с разреженными коллекциями (содержащими неопределенные элементы между FIRST и LAST). Эти конструкции рассматриваются позднее в этом моем блоге.

  •  До Oracle Database llg ссылки на поля в коллекциях записей в командах DML были запрещены. Вместо этого можно было ссылаться только на строку коллекции в целом, независимо от того, были ли ее поля коллекциями скалярных значений или коллекциями более сложных объектов. Например, следующий код:
DECLARE
   TYPE employee_aat IS TABLE OF employees%ROWTYPE 
      INDEX BY PLS_INTEGER; 
   l_employees employee_aat;
BEGIN
   FORALL l_index IN l_employees.FIRST .. l_employees.LAST 
      INSERT INTO employee (employee_id, last_name)
         VALUES (l_employees (l_index).employee_id 
               , l_employees (l_index).last_name
         );
END;

в версиях, предшествующих Oracle Database 11g, приводил к выдаче ошибки компилятора (PLS-00436).

Чтобы использовать FORALL в этом случае, приходилось загружать идентификаторы работников и фамилии в две разные коллекции. К счастью, в Oracle Database 11g это ограничение было снято.

  •  Индекс коллекции, используемый в команде DML, не мог быть выражением. Например, в следующем сценарии:
DECLARE
   names name_varray := name_varray ();
BEGIN
   FORALL indx IN names.FIRST .. names.LAST
      DELETE FROM emp WHERE ename = names(indx+10);
END;

компилятор выдавал сообщение об ошибке (PLS-00430).

 

Примеры использования FORALL

Несколько примеров использования FORALL:

  •  Изменение количества страниц для всех книг, коды ISBN которых присутствуют в коллекции isbns_in:
PROCEDURE order_books ( 
   isbns_in IN name_varray, 
   new_counts_in IN number_varray)
IS
BEGIN
   FORALL indx IN isbns_in.FIRST .. isbns_in.LAST 
      UPDATE books
         SET page_count = new_counts_in (indx)
      WHERE isbn = isbns_in (indx);
END;

В этом примере изменения сводятся к замене FOR на FORALL, а также удалению ключевых слов LOOP и END LOOP. В этой команде FORALL ядру SQL передаются все записи, определяемые в двух коллекциях. Изменения в поведении представлены на рис. 2.

 

  •  Следующий пример показывает, что команда DML может содержать ссылки на несколько коллекций. В данном случае используются три коллекции: denial, patient_ name и illnesses. При этом индексируются только первые две коллекции, то есть при вызове INSERT передаются отдельные элементы коллекции. В третий столбец health_coverage для каждой вставляемой записи включается коллекция:
FORALL indx IN denial.FIRST .. denial.LAST 
   INSERT INTO health_coverage
      VALUES (denial(indx), patient_name(indx), illnesses);
  •  Использование секции RETURNING в команде FORALL для получения информации о каждой отдельной операции DELETE. Обратите внимание на необходимость использования BULK COLLECT INTO в секции RETURNING:
FUNCTION remove_emps_by_dept (deptlist IN dlist_t)
   RETURN enolist_t 
IS
   enolist enolist_t;
BEGIN
   FORALL aDept IN deptlist.FIRST..deptlist.LAST
      DELETE FROM employees WHERE department_id IN deptlist(aDept)
         RETURNING employee_id BULK COLLECT INTO enolist;
   RETURN enolist;
END;
  •  Использование индексов, определяемых в одной коллекции, для определения строк данных коллекции, которые должны использоваться динамической командой INSERT:
FORALL indx IN INDICES OF l_top_employees 
   EXECUTE IMMEDIATE
      'INSERT INTO ' || l_table || ' VALUES (:emp_pky, :new_salary)'
      USING l_new_salaries(indx).employee_id,
            l_new_salaries(indx).salary;

 

Атрибуты курсоров для FORALL

Вы можете использовать атрибуты курсоров после выполнения команды FORALL для получения информации об операции DML, выполняемой в FORALL. Oracle также предоставляет дополнительный атрибут %BULK_ROWCOUNT для получения более детализированной информации о результатах массовой команды DML.

В табл. 1 описаны значения, возвращаемые этими атрибутами для FORALL.

 

таблица 1. Неявные атрибуты курсоров для команд FORALL

Неявные атрибуты курсоров для команд FORALL

 

Составной атрибут %BULK_ROWCOUNT, созданный специально для FORALL, обладает семантикой ассоциативного массива или коллекции. База данных помещает в N-й элемент коллекции количество строк, обработанных при N-м выполнении INSERT, UPDATE, DELETE или MERGE команды FORALL. Если операция не затронула ни одной строки, N-я строка содержит нуль.

Пример использования %bulk_rowcount (а также общего атрибута %rowcount ):

DECLARE
   TYPE isbn_list IS TABLE OF VARCHAR2(13); 
   my_books isbn_list 
   := isbn_list (
         '1-56592-375-8', '0-596-00121-5', '1-56592-849-0',
         '1-56592-335-9', '1-56592-674-9', '1-56592-675-7',
         '0-596-00180-0', '1-56592-457-6'
      );
BEGIN
   FORALL book_index IN
          my_books.FIRST..my_books.LAST 
      UPDATE books
         SET page_count = page_count / 2
      WHERE isbn = my_books (book_index);

   -- Было ли обновлено ожидаемое число строк?
   IF SQL%ROWCOUNT != 8 
   THEN
      DBMS_OUTPUT.PUT_LINE (
         'We are missing a book!');
   END IF;

   -- 4-я команда UPDATE изменила какие-либо строки?
   IF SQL%BULK_ROWCOUNT(4) = 0 
   THEN
      DBMS_OUTPUT.PUT_LINE (
         'What happened to Oracle PL/SQL Programming?');
   END IF;
END;

Несколько замечаний по поводу работы этого атрибута:

Команды FORALL и %BULK_ROWCOUNT используют одинаковые значения индексов или номера строк коллекций. Например, если коллекция, переданная FORALL, содержит данные в строках с 10 по 200, то псевдоколлекция %BULK_ROWCOUNT также будет содержать определенные и заполненные строки с 10 по 200. Все остальные строки будут неопределенными. Если INSERT влияет только на одну строку (например, при указании списка VALUES), значение строки в %bulk_rowcount будет равно 1. При этом для команд insert...select значение %bulk_rowcount может быть больше 1.

Значение строки в псевдомассиве %BULK_ROWCOUNT для операций удаления, обновления и вставки с выборкой может быть любым натуральным числом (0 или положительным); эти команды могут изменять более одной строки в зависимости от их условий WHERE.

 

Поведение ROLLBACK для FORALL

Команда FORALL позволяет передать ядру SQL несколько команд SQL. Это означает, что переключение контекста всего одно — но каждая команда выполняется ядром SQL отдельно от других.

Что случится, если в одной из этих команд SQL произойдет ошибка?

  1. Команда DML, инициировавшая исключение, откатывается от неявной точки сохранения, созданной ядром PL/SQL перед выполнением команды. Изменения во всех строках, модифицированных этой командой, отменяются.
  2. Все предшествующие операции DML в этой команде forall, уже завершенные без ошибок, не отменяются.
  3. Если вы не приняли специальных мер (добавив секцию save exceptions в forall — см. далее), выполнение forall останавливается, и остальные команды вообще не выполняются.

 

Продолжение после исключений и секция SAVE EXCEPTIONS

Добавляя в заголовок FORALL секцию SAVE EXCEPTIONS, вы приказываете Oracle продолжить обработку даже при возникновении ошибки. База данных «сохраняет исключение» (или несколько исключений, если ошибок было несколько). При завершении команды DML инициируется исключение ORA-24381. Далее в разделе исключений можно обратиться к псевдоколлекции SQL%BULK_EXCEPTIONS для получения информации об ошибке.

Пример с пояснениями:


1 DECLARE
2   bulk_errors EXCEPTION;
3   PRAGMA EXCEPTION_INIT (bulk_errors, ?24381);
4   TYPE namelist_t IS TABLE OF VARCHAR2(32767);
5
6   enames_with_errors namelist_t
7      := namelist_t ('ABC',
8             'DEF',
9             NULL, /* Фамилия должна быть отлична от NULL */
10            'LITTLE',
11            RPAD ('BIGBIGGERBIGGEST', 250, 'ABC'), /* Слишком длинное */
12            'SMITHIE'
13           );
14 BEGIN
15   FORALL indx IN enames_with_errors.FIRST .. enames_with_errors.LAST
16      SAVE EXCEPTIONS
17      UPDATE EMPLOYEES
18         SET last_name = enames_with_errors (indx);
19
20 EXCEPTION
21   WHEN bulk_errors
22   THEN
23      DBMS_OUTPUT.put_line ('Updated ' || SQL%ROWCOUNT || ' rows.');
24
25      FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
26      LOOP
27         DBMS_OUTPUT.PUT_LINE ('Error '
28           || indx
29           || ' occurred during '
30           || 'iteration '
31           || SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX
32           || ' updating name to '
33           || enames_with_errors (SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX);
34         DBMS_OUTPUT.PUT_LINE ('Oracle error is '
35                || SQLERRM ( ?1 * SQL%BULK_EXCEPTIONS (indx).ERROR_CODE)
36               );
37         END LOOP;
38 END;

Если выполнить этот код в режиме SERVEROUTPUT, будут получены следующие результаты:

SQL> EXEC bulk_exceptions

Error 1 occurred during iteration 3 updating name to BIGBIGGERBIGGEST Oracle error is ORA-01407: cannot update () to NULL

Error 2 occurred during iteration 5 updating name to
Oracle error is ORA-01401: inserted value too large for column

Другими словами, база данных обнаружила два исключения при обработке команд DML для коллекции. Она не прервала выполнение при первом исключении, а продолжила работу и зарегистрировала второе исключение.

Функциональность обработки ошибок в этом коде описана в следующей таблице.

 

Функциональность обработки ошибок

 

Управление FORALL для непоследовательных массивов

До выхода Oracle Database 10g коллекция, используемая в команде FORALL, должна была быть плотной или последовательно заполненной. При наличии пропусков между первым и последним значениями в диапазоне из заголовка FORALL, как в следующем коде, выдавалась ошибка:

1   DECLARE
2      TYPE employee_aat IS TABLE OF employees.employee_id%TYPE
3         INDEX	BY	PLS_INTEGER;
4      l_employees	employee_aat;
5   BEGIN
6      l_employees	(1) := 100;
7      l_employees	(100) := 1000;
8      FORALL l_index IN l_employees.FIRST .. l_employees.LAST
9         UPDATE	employees SET salary = 10000
10         WHERE employee_id = l_employees (l_index);
11   END;
12   /

Сообщение об ошибке выглядит так:

DECLARE
*
ERROR at line 1:
ORA-22160: element at index [2] does not exist

Кроме того, в массиве нельзя пропустить те строки, которые не должны обрабатываться командой FORALL. Эти ограничения часто приводят к написанию дополнительного кода сжатия коллекций, чтобы те удовлетворяли ограничениям FORALL. Чтобы избавить разработчиков от этой раздражающей необходимости, в Oracle Database 10g язык PL/SQL был дополнен конструкциями INDICES OF и VALUES OF для задания части массива, которая должна обрабатываться FORALL.

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

INDICES OF — эта секция используется в том случае, если у вас имеется коллекция (назовем ее индексным массивом), строки которой определяют, какие строки основного массива (из команды DML FORALL) должны обрабатываться. Иначе говоря, если элемент в позиции N не определен в индексном массиве, то команда FORALL должна проигнорировать элемент в позиции N основного массива.

VALUES OF — секция используется в том случае, если у вас имеется коллекция целых чисел (также называемая индексным массивом), содержимое которой (значения элементов) определяет обрабатываемые позиции основного массива.

 

Пример использования INDICES OF

Допустим, я хочу обновить оклады некоторых работников значением 10 000. В настоящее время таких значений в таблице нет:

SQL> SELECT employee_id FROM employees WHERE salary = 10000;
no rows selected

Я пишу следующую программу:


1   DECLARE
2      TYPE employee_aat IS TABLE OF employees.employee_id%TYPE
3         INDEX BY PLS_INTEGER;
4
5      l_employees            employee_aat;
6
7      TYPE boolean_aat IS TABLE OF BOOLEAN
8         INDEX BY PLS_INTEGER;
9
10     l_employee_indices boolean_aat;
11   BEGIN
12     l_employees (1) := 7839;
13     l_employees (100) := 7654;
14     l_employees (500) := 7950;
15     --
16     l_employee_indices (1) := TRUE;
17     l_employee_indices (500) := TRUE;
18     l_employee_indices (799) := TRUE;
19
20    FORALL l_index IN INDICES OF l_employee_indices
21          BETWEEN 1 AND 500
22       UPDATE employees23 SET salary = 10000
24          WHERE employee_id = l_employees (l_index);
25    END;

Логика программы кратко описана в следующей таблице.

 

Логика программы

 

После выполнения кода я выдаю запрос, который показывает, что в таблице были обновлены всего две строки — строка работника с идентификатором 7654 была пропущена, потому что в позиции 100 таблицы индексов элемент не определен:

SQL> SELECT employee_id FROM employee WHERE salary = 10000;

EMPLOYEE_ID
-----------
       7839
       7950

С конструкцией INDICES OF (строка 20) содержимое индексного массива игнорируется. Важны лишь позиции или номера строк, определенных в коллекции.

 

Пример использования VALUES OF

В этом примере, как и в предыдущем, оклад некоторых работников обновляется значением 10 000 — на этот раз с секцией VALUES OF. В данный момент строк с таким значением в таблице нет:

SQL> SELECT employee_id FROM employee WHERE salary = 10000;
no rows selected

Программа выглядит так:


1   DECLARE
2      TYPE employee_aat IS TABLE OF employees.employee_id%TYPE
3         INDEX BY PLS_INTEGER;
4
5      l_employees           employee_aat;
6
7      TYPE indices_aat IS TABLE OF PLS_INTEGER
8         INDEX BY PLS_INTEGER;
9
10     l_employee_indices indices_aat;
11   BEGIN
12     l_employees (-77) := 7820;
13     l_employees (13067) := 7799;
14     l_employees (99999999) := 7369;
15     --
16     l_employee_indices (100) := ?77;
17     l_employee_indices (200) := 99999999;
18     --
19     FORALL l_index IN VALUES OF l_employee_indices
20        UPDATE employees
21           SET salary = 10000
22         WHERE employee_id = l_employees (l_index);
23   END;

Логика программы кратко описана в следующей таблице.

 

Логика программы

 

После выполнения кода я снова выдаю запрос, который показывает, что в таблице были обновлены всего две строки — строка работника с идентификатором 7799 была пропущена, потому что «коллекция значений» не содержит элемента, значение которого равно 13067:

SQL> SELECT employee_id FROM employees WHERE salary = 10000;

EMPLOYEE_ID
-----------
       7369
       7820

  

Вас заинтересует / Intresting for you:

Встроенные методы коллекций PL...
Встроенные методы коллекций PL... 3761 просмотров sepia Tue, 29 Oct 2019, 09:54:01
Управление приложениями PL/SQL...
Управление приложениями PL/SQL... 1978 просмотров Rasen Fasenger Mon, 22 Oct 2018, 04:44:08
Символьные функции и аргументы...
Символьные функции и аргументы... 6176 просмотров Анатолий Wed, 23 May 2018, 18:54:01
Тип данных RAW в PL/SQL
Тип данных RAW в PL/SQL 2962 просмотров Doctor Thu, 12 Jul 2018, 08:41:33

Войдите чтобы комментировать