Учебники

18) Oracle PL / SQL BULK COLLECT

Март 11, 2020

Что такое НАБОР?

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, за исключением того, что в цикле FOR все происходит на уровне записи, тогда как в FORALL отсутствует концепция LOOP. Вместо этого все данные, представленные в данном диапазоне, обрабатываются одновременно.

Синтаксис: 

FORALL <loop_variable>in<lower range> .. <higher range> 

<DML operations>;

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

ОГРАНИЧЕНИЕ

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

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

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

Синтаксис: 

FETCH <cursor_name> BULK COLLECT INTO <bulk_variable> LIMIT <size>;

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

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

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

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

SQL в PL / SQL 

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: фиксация транзакции.

 

75
Share: