Использование выражения 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.

Петрелевич Сергей
petrelevich@yandex.ru

Метки: Oracle   PL/SQL   СУБД  

Внимание.
Комментировать могут только зарегистрированные пользователи.
Возможно использование следующих HTML тегов: <a>, <b>, <i>, <br>.

Пожалуйста авторизуйтесь или зарегистрируйтесь.