Oracle PL/SQL BULK COLLECT: FORALL Приклад

Що таке BULK COLLECT?

ПАСОВЕ ЗБІРАННЯ зменшує перемикання контексту між 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

Оператор FORALL виконує Операції DML для даних масово. Це нагадує оператор циклу FOR, за винятком того, що в циклі FOR дії відбуваються на рівні запису, тоді як у FORALL немає концепції ЦИКЛУ. Натомість усі дані, присутні в заданому діапазоні, обробляються одночасно.

Синтаксис:

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

<DML operations>;

У наведеному вище синтаксисі задана операція DML буде виконана для всіх даних, що знаходяться між нижнім та вищим діапазоном.

Речення LIMIT

Концепція масового збору завантажує всі дані в цільову змінну колекції як масове навантаження, тобто всі дані будуть заповнені в змінну колекції за один раз. Але це не рекомендується, коли загальна кількість записів, які потрібно завантажити, дуже велика, оскільки коли PL/SQL намагається завантажити всі дані, він споживає більше пам'яті сеансу. Отже, завжди добре обмежувати розмір цієї операції масового збору.

Цього обмеження розміру можна легко досягти, ввівши умову ROWNUM в оператор SELECT, тоді як у випадку курсора це неможливо.

Щоб подолати це, Oracle надав умову LIMIT, яка визначає кількість записів, які потрібно включити до масового оброблення.

Синтаксис:

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

У наведеному вище синтаксисі оператор вибірки курсора використовує оператор BULK COLLECT разом із реченням LIMIT.

Атрибути BULK COLLECT

Подібно до атрибутів курсора, 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 з розміром LIMIT, рівним 5000, у змінну lv_emp_name.
• Code рядки 8-11: Налаштування циклу FOR для виведення всіх записів у колекції lv_emp_name.
• Code рядок 12: Використання FORALL для оновлення зарплати всіх співробітників на 5000.
• Code рядок 14: Здійснення угода.

Поширені запитання