Oracle PL/SQL BULK COLLECT: пример FORALL

Что такое МАССОВЫЙ СБОР?

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

Oracle PL/SQL Предоставляет возможность получения записей пакетами, а не по одной. Этот BULK COLLECT можно использовать в операторе SELECT для массового заполнения записей или для получения данных по одной. курсор пакетно. Поскольку BULK COLLECT извлекает записи пакетами, предложение INTO всегда должно содержать переменную типа коллекции. Главное преимущество использования BULK COLLECT заключается в повышении производительности за счет уменьшения взаимодействия между базой данных и механизмом PL/SQL.

Синтаксис:

SELECT <column1> BULK COLLECT INTO bulk_variable FROM <table name>;
FETCH <cursor_name> BULK COLLECT INTO <bulk_variable>;

В приведенном выше синтаксисе команда BULK COLLECT используется для сбора данных из операторов SELECT и FETCH.

Оговорка ФОРАЛЛ

Заявление FORALL выполняет Операции DML Обработка больших объемов данных. Это похоже на оператор цикла FOR, за исключением того, что в цикле FOR действия происходят на уровне записей, тогда как в FORALL отсутствует понятие цикла. Вместо этого, обрабатываются все данные, находящиеся в заданном диапазоне, одновременно.

Синтаксис:

FORALL <loop_variable> in <lower range> .. <higher range>

<DML operations>;

В приведенном выше синтаксисе указанная операция DML будет выполнена для всех данных, находящихся в диапазоне от нижнего до верхнего предела.

ОГРАНИЧИТЕЛЬНАЯ оговорка

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

Это ограничение по размеру легко достигается путем добавления условия ROWNUM в оператор SELECT, тогда как в случае курсора это невозможно.

Чтобы преодолеть это, Oracle Предусмотрен параметр LIMIT, определяющий количество записей, которые необходимо включить в пакетную обработку.

Синтаксис:

FETCH <cursor_name> BULK COLLECT INTO <bulk_variable> LIMIT <size>;

В приведенном выше синтаксисе оператор выборки курсора использует оператор BULK COLLECT вместе с предложением LIMIT.

МАССОВЫЙ СБОР атрибутов

Подобно атрибутам курсора, функция BULK COLLECT имеет параметр %BULK_ROWCOUNT(n), который возвращает количество строк, затронутых в n-м операторе DML оператора FORALL, то есть он показывает количество записей, затронутых в операторе FORALL для каждого значения из переменной коллекции. Термин «n» указывает последовательность значений в коллекции, для которых требуется подсчет строк.

Пример 1: В этом примере мы спроецируем все имена сотрудников из таблицы emp с помощью функции BULK COLLECT, а также увеличим зарплату всех сотрудников на 5000 с помощью функции FORALL.

На скриншоте ниже показан пример использования команд BULK COLLECT и FORALL, а также результат их выполнения. Oracle.

DECLARE
CURSOR guru99_det IS SELECT emp_name FROM emp;
TYPE lv_emp_name_tbl IS TABLE OF VARCHAR2(50);
lv_emp_name lv_emp_name_tbl;
BEGIN
OPEN guru99_det;
FETCH guru99_det BULK COLLECT INTO lv_emp_name LIMIT 5000;
FOR c_emp_name IN lv_emp_name.FIRST .. lv_emp_name.LAST
LOOP
Dbms_output.put_line('Employee Fetched:'||c_emp_name);
END LOOP;
FORALL i IN lv_emp_name.FIRST .. lv_emp_name.LAST
UPDATE emp SET salary=salary+5000 WHERE emp_name=lv_emp_name(i);
COMMIT;
Dbms_output.put_line('Salary Updated');
CLOSE guru99_det;
END;
/

Результат

Employee Fetched:BBB
Employee Fetched:XXX
Employee Fetched:YYY
Salary Updated

Code Объяснение:

• Code строка 2: Объявляем курсор guru99_det для оператора 'SELECT emp_name FROM emp'.
• Code строка 3: Объявление lv_emp_name_tbl как таблицы типа VARCHAR2(50).
• Code строка 4: Объявление переменной lv_emp_name как типа lv_emp_name_tbl.
• Code строка 6: Открытие курсора.
• Code строка 7: Извлечение курсора с помощью команды BULK COLLECT с ограничением размера в 5000 в переменную lv_emp_name.
• Code строки 8-11: Создание цикла FOR для вывода всех записей из коллекции lv_emp_name.
• Code строка 12: Использование функции FORALL для обновления заработной платы всех сотрудников на 5000.
• Code строка 14: Совершение сделка.

Часто задаваемые вопросы (FAQ)