Oracle PL/SQL BULK COLLECT: FORALL Приклад
Що таке BULK COLLECT?
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
FORALL дозволяє виконувати операції DML над даними масово. Він подібний до оператора циклу FOR, за винятком in Цикл FOR все відбувається на рекордному рівні, тоді як у FORALL немає концепції LOOP. Натомість усі дані, присутні в заданому діапазоні, обробляються одночасно.
Синтаксис:
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), який повертає кількість рядків, на які впливає nth Оператор 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 розміром як 5000 intl змінної lv_emp_name.
- Рядок коду 8-11: Налаштування циклу FOR для друку всіх записів у колекції lv_emp_name.
- Рядок коду 12: Використання FORALL для оновлення зарплати всіх співробітників на 5000.
- Рядок коду 14: Здійснення транзакції.