Полезные Скрипты Oracle - Использование выражения BULK COLLECT

BULK COLLECT извлекает из запроса все данные(строки) в указанную коллекцию записей.
Использование выражения BULK COLLECT может заметно ускорить ваш модуль, но оно очень требовательно к памяти.
Это ключевое слово может использоваться совместно с выражениями SELECT INTO, FETCH INTO и RETURNING INTO.

Дополнительное условие LIMIT может использоваться только с выражением FETCH которое ограничивает число записей.

Синтаксис:

SELECT <колонки> BULK COLLECT INTO <коллекция> FROM <имя таблицы> [WHERE <условия>];
FETCH <имя курсора> BULK COLLECT INTO <коллекция> [LIMIT <число лимита>];
< INSERT или UPDATE оператор> RETURNING <колонки> BULK COLLECT INTO <коллекция>;

Пример 1( SELECT <колонки> BULK COLLECT INTO <коллекция> ):

DECLARE
TYPE  IdsTab IS TABLE OF hr.employees.employee_id%TYPE;
TYPE  NameTab IS TABLE OF hr.employees.last_name%TYPE;
ids   IdsTab;
names NameTab;
BEGIN
SELECT employee_id, last_name BULK COLLECT INTO ids, names FROM hr.employees;

-- Обработка элементов коллекции
FOR i IN ids.FIRST..ids.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(ids(i)||'----'||names(i));
END LOOP;

END;

Пример 2(FETCH <колонки> BULK COLLECT INTO <коллекция>):

DECLARE
TYPE  IdsTab IS TABLE OF hr.employees.employee_id%TYPE;
TYPE  NameTab IS TABLE OF hr.employees.last_name%TYPE;
ids   IdsTab;
names NameTab;
CURSOR c1 IS SELECT employee_id, last_name FROM hr.employees;
BEGIN
OPEN c1;
FETCH c1 BULK COLLECT INTO ids, names;
CLOSE c1;
-- Обработка элементов коллекции
FOR i IN ids.FIRST..ids.LAST
LOOP
   DBMS_OUTPUT.PUT_LINE(ids(i)||'----'||names(i));
END LOOP;

END;

Пример 3( < INSERT или UPDATE оператор> RETURNING <колонки> BULK COLLECT INTO <коллекция> ):

-- создаем копию таблицы hr.employees

CREATE TABLE hr.employees_copy AS
SELECT employee_id, first_name, last_name
FROM hr.employees;

DECLARE
TYPE EmpTab IS TABLE OF hr.employees_copy%ROWTYPE;
emps EmpTab;

BEGIN
INSERT INTO hr.employees_copy VALUES(1000,'John','Smith')
RETURNING employee_id, first_name, last_name BULK COLLECT INTO emps;

DBMS_OUTPUT.PUT_LINE('Added :'||emps(1).employee_id||' '||emps(1).last_name);

UPDATE hr.employees_copy SET last_name ='MR. '|| last_name
WHERE first_name='John'
RETURNING employee_id, first_name, last_name BULK COLLECT INTO emps;

DBMS_OUTPUT.PUT_LINE('Updated :');
FOR i IN emps.FIRST..emps.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(emps(i).employee_id||' '||emps(i).last_name);
END LOOP;

DELETE FROM hr.employees_copy WHERE first_name!='John'
RETURNING employee_id, first_name, last_name BULK COLLECT INTO emps;
DBMS_OUTPUT.PUT_LINE('Deleted :');
FOR i IN emps.FIRST..emps.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(emps(i).employee_id||' '||emps(i).last_name);
END LOOP;
END ;

/

-- удаляем таблицу hr.employees_copy
DROP TABLE hr.employees_copy;