В 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 тесно интегрированы на синтаксическом уровне, но во внутренней реализации они связаны отнюдь не так тесно, как кажется на первый взгляд.
Рис. 1. Переключения контекста между PL/SQL и SQL
Однако конструкции FORALL и BULK COLLECT позволяют оптимизировать взаимодействие этих двух компонентов; фактически вы приказываете ядру PL/SQL объединить множество переключений контекста в одно переключение, тем самым повышая производительность своего приложения.
Рассмотрим команду FORALL, изображенную на рис. 2. Вместо того чтобы перебирать обновляемые записи в курсорном цикле FOR или в цикле со счетчиком, мы используем заголовок FORALL для определения общего количества итераций. На стадии выполнения ядро PL/SQL «расширяет» команду UPDATE в набор команд для выполнения всех итераций, а затем передает их ядру SQL за одно переключение контекста. Иначе говоря, выполняются те же команды SQL, но за одно обращение к ядру SQL.
Рис. 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илиMERGE(вOraclellgи выше). - Команда
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
Составной атрибут %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 произойдет ошибка?
- Команда
DML, инициировавшая исключение, откатывается от неявной точки сохранения, созданной ядром PL/SQL перед выполнением команды. Изменения во всех строках, модифицированных этой командой, отменяются. - Все предшествующие операции DML в этой команде
forall, уже завершенные без ошибок, не отменяются. - Если вы не приняли специальных мер (добавив секцию
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
