Oracle bulk collect это

Обновлено: 07.07.2024

Предложение BULK COLLECT извлекает результаты в пакетном режиме, то есть сразу привязывает набор результатов к переменной коллекции и отправляет его из механизма SQL в механизм PL / SQL. Обычно доступно в SELECT INTO,
Используйте BULK COLLECT в предложениях FETCH INTO и RETURNING INTO. Эта статья опишет использование BULK COLLECT в этих ситуациях один за другим.
Для использования оператора FORALL, пожалуйста, обратитесь к:FORALL оператор пакетного SQL

1. Пример связывания партии BULK COLLECT

Во-вторых, используйте LIMIT, чтобы ограничить объем данных FETCH.
При использовании предложения BULK COLLECT для типов коллекций, таких как вложенные таблицы, объединенные массивы и т. д., оно будет автоматически инициализировано и расширено (как показано в следующем примере). Так что если вы используете BULK
Предложение COLLECT работает с коллекцией, поэтому нет необходимости инициализировать и расширять коллекцию. Из-за пакетного характера BULK COLLECT, если объем данных велик, и в это время коллекция автоматически расширяется, чтобы избежать
Избегайте чрезмерно больших наборов данных, вызывающих снижение производительности, поэтому используйте условие limit, чтобы ограничить объем данных, извлекаемых за один раз. Предложение limit допускается только в пакете операторов операции выборки.

Использование:
FETCH . BULK COLLECT INTO . [LIMIT rows]

3. Пакетное связывание пункта ВОЗВРАТ
В дополнение к групповой привязке с помощью SELECT и FETCH, BULK COLLECT также можно использовать в сочетании с операторами INSERT, DELETE и UPDATE. В сочетании с этими заявлениями DML мы
Предложение RETURNING требуется для достижения связывания пакетов.

4. ВСЕГДА И ОБЪЕМ СОБРАТЬ
FORALL и BULK COLLECT - два важных способа достижения пакетного SQL, мы можем использовать его в сочетании для повышения производительности. Следующий пример - сумма двух.

5. Ограничение ОБЪЕМНОГО КОЛЛЕКТА
1. Вы не можете использовать предложение BULK COLLECT для ассоциативного массива, который использует строковый тип в качестве ключа.
2. BULK COLLECT можно использовать только в программе на стороне сервера. Если она используется на клиенте, произойдет ошибка, не поддерживающая эту функцию.
3. Целевой объект BULK COLLECT INTO должен иметь тип коллекции.
4. Сложные цели (например, типы объектов) нельзя использовать в предложении RETURNING INTO.
5. Если существует несколько неявных преобразований типов данных, несколько составных целей не могут использоваться в предложении BULK COLLECT INTO.
6. При неявном преобразовании типов данных составную целевую коллекцию (например, коллекцию типов объектов) нельзя использовать в предложении BULK COLLECTINTO.

Коллекции присутствую в том или ином виде в большинстве языков программирования и везде имеют схожую суть в плане использования. А именно – позволяют хранить набор объектов одного типа и проводить над всем набором какие-либо действия, либо в цикле проводить однотипные действия со всеми элементами набора.

Таким же образом коллекции используются и в Oracle.

Содержание статьи

Общие сведения о коллекциях в pl/sql

Типы коллекций

Тип коллекции	Количество элементов	Тип индекса	Плотная или разреженная	Без инициализации	Где объявляется	Использование в SQL
Ассоциативный массив (index by table)	Не задано	String Pls_integer	Плотная и разреженная	Empty	PL/SQL block Package	Нет
Varray (variable-size array)	Задано	Integer	Только плотная	Null	PL/SQL block Package Schema level	Только определенные на уровне схемы
Nested table	Не задано	Integer	При создании плотная, может стать разреженной	Null	PL/SQL block Package Schema level	Только определенные на уровне схемы

Плотность коллекции означает, что между элементами коллекции нет пропусков, пустых мест. Некоторые коллекции, как видно из таблицы, могут быть разреженными – т.е. могут иметь разрывы между элементами. Это значит, что в коллекции, например, могут быть элементы с индексом 1 и 4, а с индексом 2 и 3 элементов нет. При этом слоты памяти под 2-й и 3-й элементы будут существовать и будут принадлежать коллекции (в случае nested table), но не содержать при этом объектов и попытка прочитать содержимое этих элементов вызовет ошибку no_data_found.
Подробности можно узнать из видео-лекции в конце статьи.

Ассоциативный массив

Набор пар ключ-значение
Данные хранятся в отсортированном по ключу порядке
Не поддерживает DML-операции (не может участвовать в селектах, не может храниться в таблицах)
При объявлении как константа должен быть сразу инициализирован функцией
Порядок элементов в ассоциативном массиве с строковым индексом зависит от параметров NLS_SORT и NLS_COMP
Нельзя объявить тип на уровне схемы, но можно в пакете
Не имеет конструктора
Индекс не может принимать значение null (но допускает пустую строку — подробности и ссылка на пример в первом комментарии)
Datatype – это любой тип данных, кроме ref cursor

Для помещения в память небольших таблиц-справочников
Для передачи в качестве параметра коллекции

Restrictions:
При изменении параметров NLS_SORT и NLS_COMP во время сессии после заполнения ассоциативного массива, можем получать неожиданные результаты вызовов методов first, last, next, previous. Также могут возникнуть проблемы при передаче ассоциативного массива в качестве параметра на другую БД с иными настройками NLS_SORT и NLS_COMP

Varray

Представляет собой массив последовательно хранящихся элементов

Тип описывается следующим образом (varay_type_def):

Размер задается при создании
Индексируется с 1
Инициализируется конструктором
Если параметры в конструктор не передаются, возвращается пустая коллекция
Datatype – это любой тип данных, кроме ref cursor

Знаем максимально возможное количество элементов
Доступ к элементам последовательный

Restrictions:
Максимальный размер – 2 147 483 647 элементов

Nested table

Тип описывается следующим образом (nested_table_type_def):

Размер коллекции изменяется динамически
Может быть в разряженном состоянии, как показано на картинке
<
Инициализируется конструктором
Если параметры в конструктор не передаются, возвращается пустая коллекция
Datatype – это любой тип данных, кроме ref cursor
Если содержит только одно скалярное значение, то имя колонки – Column_Value

Set operations с nested tables

Операции возможны только с коллекциями nested table. Обе коллекции, участвующие в операции, должны быть одного типа.
Результатом операции также является коллекция nested table.

Операция	Описание
MULTISET UNION	Возвращает объединение двух коллекций
MULTISET UNION DISTINCT	Возвращает объединение двух коллекций с дистинктом (убирает дубли)
MULTISET INTERSECT	Возвращает пересечение двух коллекций
MULTISET INTERSECT DISTINCT	Возвращает пересечение двух коллекций с дистинктом (убирает дубли)
SET	Возвращает коллекцию с дистинктом (т.е. коллекцию без дублей)
MULTISET EXCEPT	Возвращает разницу двух коллекций
MULTISET EXCEPT DISTINCT	Возвращает разницу двух коллекций с дистинктом (убирает дубли)

Небольшой пример (обратите внимание на результат операции MULTISET EXCEPT DISTINCT)

Работа со сложными типами данных BULK– массивами и коллекциями повышает производительность за счет минимизации количества переключений контекста между PL / SQL и SQL. То есть целые коллекции, а не только отдельные элементы, передаются туда и обратно.

С помощью следующих команд, и атрибутов курсоров, можно построить динамический SQL таким образом, чтобы корректно осуществлялась работа с массивами и коллекциям .

Синтаксис для динамического связывания с использованием инструкции BULK

Инструкция BULK позволяет Oracle работать переменной в SQL , так , чтобы обрабатывать множества значений. Тип множества может быть любой допустимы в PL / SQL (индекс-таблицы, вложенные таблицы, и массивы переменного размера). Однако элементы должны иметь строго заданный тип данных SQL, CHAR , DATE или NUMBER . Динамические коллекции могут применяться в командах: EXECUTE IMMEDIATE , FETCH и FORALL .

EXECUTE IMMEDIATE и BULK

Эти операторы позволяет вам позволяет связывать заданные переменные или OUT аргументы и передавать их, как параметры, для динамического оператора SQL. Синтаксис имеет следующий вид:

Благодаря инструкции BULK можно возвращать данные в коллекции из динамического SQL.

BULK и FETCH
BULK позволяет выбрать данные из динамического курсора так же, как выборку из статического курсора. Синтаксис имеет следующий вид:

Если число определяемых переменных в BULK COLLECT INTO , превышает количество столбцов в запросе, Oracle выдает ошибку.

Использование FORALL

Этот синтаксис позволяет связать входные переменные в динамическом SQL. Кроме того, вы можете использовать EXECUTE

Динамическая строка может быть или INSERT , UPDATE или DELETE (но не SELECT).
Примеры динамического SQL с использованием BULK

Следующий пример демонстрирует использование BULK для вывода данных в открытые массивы с помощью FETCH

Использование BULK массовом обновлении записей совместно с returning результат помещается в коллекцию enames.

Если для Вас выборка большого количества данных и помещение их в переменную PL/SQL важнее чем циклический проход по результирующей выборке, то Вы можете использовать выражение BULK COLLECT. Если в Вашей выборке всего несколько колонок, то каждую из них Вы можете сохранить в отдельную переменную - коллекцию. Если Вы выбираете все колонки таблицы, то можете сохранить результат выборки в коллекции записей. Такая коллекция весьма удобна для циклического перебора результирующих записей, поля которых ссылаются на колонки таблицы.

Если Вам надо пройти по результирующей выборке только один раз, используйте цикл 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

-- С помощью одного запроса мы извлекаем все данные, соответствующие условиям, в коллекцию записей

-- Сейчас мы можем обработать данные, выбранные запросом, или передать их в отдельную процедуру.

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;

-- А сейчас мы сделаем выборку только по некоторым полям таблицы.
-- Получим фамилию и имя десяти случайных сотрудников.

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 должны быть коллекциями. Колонки таблицы могут быть как скалярными значениями так и структурами, включая объектные типы.

TYPE NumTab IS TABLE OF employees.employee_id%TYPE;
TYPE NameTab IS TABLE OF employees.last_name%TYPE;
enums NumTab; -- Нет необходимости инициализировать коллекцию.
names NameTab; -- Значения будут заполнены выражением SELECT INTO.

-- Извлечение данных по сотрудникам, идентификатор которых больше 1000

-- Все данные помещены в память выражением BULK COLLECT
-- Нет необходимости выполнять FETCH для каждого ряда результирующих данных

-- Выборка приблизительно 20% всех рядов

SELECT employee_id, last_name BULK COLLECT
INTO enums, names
FROM employees SAMPLE (20);

Коллекции инициализируются автоматически. Вложенные таблицы и ассоциативные массивы расширяются для сохранения необходимого количества элементов. Если Вы используете массивы с фиксированным размером, убедитесь, что декларируемый размер массива соответствует объемам выбираемых данных. Элементы вставляются в коллекции, начиная с индекса 1, при этом все существующие значения перезаписываются.

Т.к. обработка выражения BULK COLLECT INTO подобна циклу FETCH, не генерируется исключение NO_DATA_FOUND, если не выбран ни один ряд. Если требуется, наличие выбранных данных надо проверять вручную.

Чтобы предотвратить переполнение памяти данными выборки, Вы можете использовать выражение LIMIT или псевдоколонку ROWNUM для ограничения числа записей в выборке. Кроме того возможно использование выражения SAMPLE для получения набора случайных записей.

-- Получение 10% (приблизительно) записей в таблице
SELECT salary BULK COLLECT
INTO sals
FROM employees SAMPLE (10);
END ;
/

Вы можете обрабатывать большие объемы результирующих данных, указав количество записей, которые будут выбраны из курсора за один раз.

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;

Совместное использование 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;

Значения столбцов, удаленных каждой итерацией, добавляются к ранее полученным значениям коллекций. Если бы использовался цикл FOR вместо выражения FORALL, то набор результирующих значений перетирался бы следующим выполнением выражения DELETE. Не допускается использование конструкции SELECT . BULK COLLECT в выражении FORALL.

BULK COLLECT уменьшает переключение контекста между SQL и механизмом PL / SQL и позволяет механизму SQL получать записи сразу.

Oracle PL / SQL обеспечивает функциональность выборки записей оптом, а не выборки один за другим. Этот BULK COLLECT может использоваться в операторе «SELECT» для заполнения записей в натуральном выражении или для извлечения курсора в натуральном выражении. Поскольку BULK COLLECT извлекает запись в BULK, предложение INTO всегда должно содержать переменную типа коллекции. Основным преимуществом использования BULK COLLECT является повышение производительности за счет снижения взаимодействия между базой данных и механизмом PL / SQL.

Синтаксис:

В приведенном выше синтаксисе BULK COLLECT используется для сбора данных из операторов «SELECT» и «FETCH».

В этом уроке вы узнаете

Пункт FORALL

FORALL позволяет выполнять операции DML с данными в большом количестве. Он аналогичен оператору цикла FOR, за исключением того, что в цикле FOR все происходит на уровне записи, тогда как в FORALL отсутствует концепция LOOP. Вместо этого все данные, представленные в данном диапазоне, обрабатываются одновременно.

Синтаксис:

В приведенном выше синтаксисе данная операция DML будет выполняться для всех данных, которые присутствуют между нижним и верхним диапазоном.

Концепция массового сбора загружает все данные в целевую переменную сбора как массив, т.е. все данные будут заполняться в переменной сбора за один раз. Но это не рекомендуется, когда общее количество записей, которое необходимо загрузить, очень велико, потому что, когда PL / SQL пытается загрузить все данные, он потребляет больше памяти сеанса. Следовательно, всегда полезно ограничить размер этой операции массового сбора.

Тем не менее, это ограничение размера может быть легко достигнуто путем введения условия ROWNUM в операторе SELECT, тогда как в случае курсора это невозможно.

Чтобы преодолеть это, Oracle предоставил условие «LIMIT», которое определяет количество записей, которые должны быть включены в объем.

Синтаксис:

В приведенном выше синтаксисе оператор выбора курсора использует оператор BULK COLLECT вместе с предложением LIMIT.

ОБЪЕМ СОБРАТЬ Атрибуты

Подобно атрибутам курсора, BULK COLLECT имеет% BULK_ROWCOUNT (n), который возвращает количество строк, затронутых в n- м операторе DML инструкции FORALL, т.е. он даст количество записей, затронутых в операторе FORALL, для каждого отдельного значения из коллекции переменная. Термин «n» указывает последовательность значений в коллекции, для которой необходимо количество строк.

Пример 1 : В этом примере мы спроецируем все имя сотрудника из таблицы emp, используя BULK COLLECT, и мы также собираемся увеличить зарплату всех сотрудников на 5000, используя FORALL.

Читайте также: