Использование выражения BULK COLLECT
Статьи про Oracle -> Программирование
Использование выражения BULK COLLECT
Предлагаю вам мой перевод части документации Oracle® Database PL/SQL User's Guide and Reference 10g Release 2 (10.2) B14261-01
v:1.0 2008.11.24
Петрелевич Сергей
Если для Вас выборка большого количества данных и помещение их в переменную PL/SQL важнее чем циклический проход по результирующей выборке, то Вы можете использовать выражение BULK COLLECT. Если в Вашей выборке всего несколько колонок, то каждую из них Вы можете сохранить в отдельную переменную - коллекцию. Если Вы выбираете все колонки таблицы, то можете сохранить результат выборки в коллекции записей. Такая коллекция весьма удобна для циклического перебора результирующих записей, поля которых ссылаются на колонки таблицы.
Пример
DECLARE
TYPE IdsTab IS TABLE OF employees.employee_id%TYPE;
TYPE NameTab IS TABLE OF employees.last_name%TYPE;
ids IdsTab;
names NameTab;
CURSOR c1 IS
SELECT employee_id, last_name
FROM employees
WHERE job_id = 'ST_CLERK';
BEGIN
OPEN c1;
FETCH c1 BULK COLLECT INTO ids, names;
CLOsE c1;
-- Обработка элементов коллекции
FOR i IN ids.FIRST..ids.LAST
LOOP
IF ids(i) > 140 THEN
DBMS_OUTPUT.PUT_LINE(ids(i));
END IF;
END LOOP;
FOR i IN names.FIRST..names.LAST
LOOP
IF names(i) LIKE '%Ma%' THEN
DBMS_OUTPUT.PUT_LINE(names(i));
END IF;
END LOOP;
END;
/
Эта технология может быть не только очень быстрой, то и требовательной к памяти.
-
Используя BULK COLLECT, Вы можете улучшить код, выполняя больше работы в SQL:
- Если Вам надо пройти по результирующей выборке только один раз, используйте цикл For. Этот подход позволяет избежать выделение памяти на хранение копии результирующих данных.
- Если из результирующих данных Вам требуется выбрать определенные значения и поместить их в меньшую выборку, используйте фильтрацию в основном выражении. В простом случае используйте условия WHERE. Для сравнения двух и более наборов данных применяйте выражения INTERSECT и MINUS.
- Если Вы циклически проходите по результирующей выборке и для каждого ряда выполняете DML-выражение или делаете другую выборку, используйте более эффективных подход. Попробуйте вложенную выборку переделать в подзапрос основной выборки, если возможно, используйте выражения EXISTS или NOT EXISTS. Для DML, рассмотрите возможность использования выражения FORALL, который значительно более быстрый, чем аналогичное выражение, выполненное внутри цикла.
Еще один пример использования BULK COLLECT
DECLARE
TYPE EmployeeSet IS TABLE OF employees%ROWTYPE;
underpaid EmployeeSet; -- Набор рядов таблицы EMPLOYEES.
CURSOR c1 IS SELECT first_name, last_name FROM employees;
TYPE NameSet IS TABLE OF c1%ROWTYPE;
some_names NameSet; -- Набор неполных рядов таблицы EMPLOYEES
BEGIN
-- С помощью одного запроса мы извлекаем все данные, соответствующие условиям, в коллекцию записей
SELECT * BULK COLLECT
INTO underpaid
FROM employees
WHERE salary < 5000
ORDER BY salary DESC;
-- Сейчас мы можем обработать данные, выбранные запросом, или передать их в отдельную процедуру.
DBMS_OUTPUT.PUT_LINE(underpaid.COUNT || ' people make less than 5000.');
FOR i IN underpaid.FIRST..underpaid.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(underpaid(i).last_name || ' makes ' || underpaid(i).salary);
END LOOP;
-- А сейчас мы сделаем выборку только по некоторым полям таблицы.
-- Получим фамилию и имя десяти случайных сотрудников.
SELECT first_name, last_name BULK COLLECT
INTO some_names
FROM employees
WHERE ROWNUM < 11;
FOR i IN some_names.FIRST..some_names.LAST
LOOP
DBMS_OUTPUT.PUT_LINE('Employee = ' || some_names(i).first_name || ' ' ||
some_names(i).last_name);
END LOOP;
END;
/
Извлечение результатов выборки в коллекции, используя выражение BULK COLLECT.
Использование ключевых слов BULK COLLECT в выборках - очень эффективный способ получения результирующих данных. Вместо циклической обработки каждого ряда, Вы сохраняете результат в одной или нескольких коллекциях, все это делается в рамках одной операцией. Это ключевое слово может использоваться совместно с выражениями SELECT INTO, FETCH INTO и RETURNING INTO.
При использовании ключевых слов BULK COLLECT все переменные в списке INTO должны быть коллекциями. Колонки таблицы могут быть как скалярными значениями так и структурами, включая объектные типы.
Пример
DECLARE
TYPE NumTab IS TABLE OF employees.employee_id%TYPE;
TYPE NameTab IS TABLE OF employees.last_name%TYPE;
enums NumTab; -- Нет необходимости инициализировать коллекцию.
names NameTab; -- Значения будут заполнены выражением SELECT INTO.
PROCEDURE print_results IS
BEGIN
IF enums.COUNT = 0 THEN
DBMS_OUTPUT.PUT_LINE('No results!');
ELSE
DBMS_OUTPUT.PUT_LINE('Results:');
FOR i IN enums.FIRST..enums.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(' Employee #' || enums(i) || ': ' || names(i));
END LOOP;
END IF;
END;
BEGIN
-- Извлечение данных по сотрудникам, идентификатор которых больше 1000
SELECT employee_id, last_name BULK COLLECT
INTO enums, names FROM employees
WHERE employee_id > 1000;
-- Все данные помещены в память выражением BULK COLLECT
-- Нет необходимости выполнять FETCH для каждого ряда результирующих данных
print_results();
-- Выборка приблизительно 20% всех рядов
SELECT employee_id, last_name BULK COLLECT
INTO enums, names
FROM employees SAMPLE (20);
print_results();
END;
/
Коллекции инициализируются автоматически. Вложенные таблицы и ассоциативные массивы расширяются для сохранения необходимого количества элементов. Если Вы используете массивы с фиксированным размером, убедитесь, что декларируемый размер массива соответствует объемам выбираемых данных. Элементы вставляются в коллекции, начиная с индекса 1, при этом все существующие значения перезаписываются.
Т.к. обработка выражения BULK COLLECT INTO подобна циклу FETCH, не генерируется исключение NO_DATA_FOUND, если не выбран ни один ряд. Если требуется, наличие выбранных данных надо проверять вручную.
Чтобы предотвратить переполнение памяти данными выборки, Вы можете использовать выражение LIMIT или псевдоколонку ROWNUM для ограничения числа записей в выборке. Кроме того возможно использование выражения SAMPLE для получения набора случайных записей.
Пример
DECLARE
TYPE SalList IS TABLE OF employees.salary%TYPE;
sals SalList;
BEGIN
-- Ограничение числа выбираемых записей до 50
SELECT salary BULK COLLECT
INTO sals
FROM employees
WHERE ROWNUM <= 50;
-- Получение 10% (приблизительно) записей в таблице
SELECT salary BULK COLLECT
INTO sals
FROM employees SAMPLE (10);
END;
/
Вы можете обрабатывать большие объемы результирующих данных, указав количество записей, которые будут выбраны из курсора за один раз.
Пример
DECLARE
TYPE NameList IS TABLE OF employees.last_name%TYPE;
TYPE SalList IS TABLE OF employees.salary%TYPE;
CURSOR c1 IS
SELECT last_name, salary
FROM employees
WHERE salary > 10000;
names NameList;
sals SalList;
TYPE RecList IS TABLE OF c1%ROWTYPE;
recs RecList;
v_limit PLS_INTEGER := 10;
PROCEDURE print_results IS
BEGIN
IF names IS NULL OR names.COUNT = 0 THEN -- проверка, не пустая ли коллекция
DBMS_OUTPUT.PUT_LINE('No results!');
ELSE
DBMS_OUTPUT.PUT_LINE('Results: ');
FOR i IN names.FIRST..names.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(' Employee ' || names(i) || ': $' || sals(i));
END LOOP;
END IF;
END;
BEGIN
DBMS_OUTPUT.PUT_LINE('--- Обрабатываем все результаты за раз ---');
OPEN c1;
FETCH c1 BULK COLLECT INTO names, sals;
CLOSE c1;
print_results();
DBMS_OUTPUT.PUT_LINE('--- Обрабатываем ' || v_limit || ' рядов за раз ---');
OPEN c1;
LOOP
FETCH c1 BULK COLLECT INTO names, sals LIMIT v_limit;
EXIT WHEN names.COUNT = 0;
print_results();
END LOOP;
CLOSE c1;
DBMS_OUTPUT.PUT_LINE('--- Извлекаем ряды вместо отдельных колонок ---');
OPEN c1;
FETCH c1 BULK COLLECT INTO recs;
FOR i IN recs.FIRST..recs.LAST
LOOP
-- Сейчас все колонки берем сразу из результирующего набора данных
DBMS_OUTPUT.PUT_LINE(' Employee ' || recs(i).last_name || ': $'|| recs(i).salary);
END LOOP;
END;
/
Ограничение числа рядов в выборке с помощью условия Limit
Дополнительное условие LIMIT может использоваться только с выражением FETCH и ограничивает число рядов, выбираемых из баз данных. В следующем примере на каждой итерации цикла извлекается не больше десяти рядов и помещается в таблицу empids. Предыдущие значения перетираются. Обратите внимание на использование empids.count как условия выхода из цикла.
Пример
DECLARE
TYPE numtab IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
CURSOR c1 IS
SELECT employee_id
FROM employees
WHERE department_id = 80;
empids numtab;
rows PLS_INTEGER := 10;
BEGIN
OPEN c1;
LOOP -- следующее выражение извлекает не больше 10 рядов за одну итерацию
FETCH c1 BULK COLLECT INTO empids LIMIT rows;
EXIT WHEN empids.COUNT = 0;
-- EXIT WHEN c1%NOTFOUND; -- это условие некорректно, можно потерять часть данных
DBMS_OUTPUT.PUT_LINE('------- Results from Each Bulk Fetch --------');
FOR i IN 1..empids.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE( 'Employee Id: ' || empids(i));
END LOOP;
END LOOP;
CLOSE c1;
END;
/
Передача результатов операций DML в коллекцию, используя выражение RETURNING INTO
Вы можете использовать BULK COLLECT в условии RETURNING INTO выражений INSERT, UPDATE, DELETE.
Пример
CREATE TABLE emp_temp AS SELECT * FROM employees;
DECLARE
TYPE NumList IS TABLE OF employees.employee_id%TYPE;
enums NumList;
TYPE NameList IS TABLE OF employees.last_name%TYPE;
names NameList;
BEGIN
DELETE FROM emp_temp WHERE department_id = 30
RETURNING employee_id, last_name BULK COLLECT INTO enums, names;
DBMS_OUTPUT.PUT_LINE('Deleted ' || SQL%ROWCOUNT || ' rows:');
FOR i IN enums.FIRST..enums.LAST
LOOP
DBMS_OUTPUT.PUT_LINE('Employee #' || enums(i) || ': ' || names(i));
END LOOP;
END;
/
Совместное использование FORALL и BULK COLLECT
Вы можете объединить условие BULK COLLECT и выражение FORALL. Результирующая коллекция будет заполнена итерациями выражения FORALL. В следующем примере для каждого удаленного ряда значение employee_id сохраняется в коллекцию e_ids. Коллекция depts хранит три элемента, таким образом выражение FORALL выполнит три итерации. Если каждый оператор DELTE выполненный выражением FORALL удалит пять рядов, то в результате коллекция e_ids, которая хранит значения из удаленных рядов, будет содержать 15 элементов.
Пример
CREATE TABLE emp_temp AS SELECT * FROM employees;
DECLARE
TYPE NumList IS TABLE OF NUMBER;
depts NumList := NumList(10,20,30);
TYPE enum_t IS TABLE OF employees.employee_id%TYPE;
TYPE dept_t IS TABLE OF employees.department_id%TYPE;
e_ids enum_t;
d_ids dept_t;
BEGIN
FORALL j IN depts.FIRST..depts.LAST
DELETE FROM emp_temp WHERE department_id = depts(j)
RETURNING employee_id, department_id BULK COLLECT INTO e_ids, d_ids;
DBMS_OUTPUT.PUT_LINE('Deleted ' || SQL%ROWCOUNT || ' rows:');
FOR i IN e_ids.FIRST .. e_ids.LAST
LOOP
DBMS_OUTPUT.PUT_LINE('Employee #' || e_ids(i) || ' from dept #' || d_ids(i));
END LOOP;
END;
/
Значения столбцов, удаленных каждой итерацией, добавляются к ранее полученным значениям коллекций. Если бы использовался цикл FOR вместо выражения FORALL, то набор результирующих значений перетирался бы следующим выполнением выражения DELETE. Не допускается использование конструкции SELECT ... BULK COLLECT в выражении FORALL.
Метки: PL/SQL
Внимание.
Комментировать могут только зарегистрированные пользователи.
Возможно использование следующих HTML тегов: <a>, <b>, <i>, <br>.
