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

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

BULK COLLECT уменьшает переключение контекста между механизмом SQL и PL/SQL и позволяет механизму SQL получать записи одновременно.

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

Синтаксис:

SELECT <columnl> BULK COLLECT INTO bulk_varaible FROM <table name>;
FETCH <cursor_name> BULK COLLECT INTO <bulk_varaible >;

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

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

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

Синтаксис:

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^th Оператор DML оператора FORALL, т.е. он выдает количество записей, затронутых оператором FORALL, для каждого отдельного значения из переменной коллекции. Термин «n» указывает последовательность значений в коллекции, для которой требуется количество строк.

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

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 salaiy=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

Пояснение к коду:

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