Oracle PL/SQL BULK COLLECT: FORALL Пример

Какво е BULK COLLECT?

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

Синтаксис:

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

<DML operations>;

В горния синтаксис дадената DML операция ще бъде изпълнена за всички данни, които присъстват между по-нисък и по-висок диапазон.

Клауза LIMIT

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

Въпреки това, това ограничение на размера може лесно да бъде постигнато чрез въвеждане на условието ROWNUM в оператора 'SELECT', докато в случай на курсор това не е възможно.

За да се преодолее това Oracle предостави клауза „LIMIT“, която определя броя на записите, които трябва да бъдат включени в груповото.

Синтаксис:

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

В горния синтаксис изразът за извличане на курсора използва оператор BULK COLLECT заедно с клаузата LIMIT.

BULK COLLECT Атрибути

Подобно курсор атрибути BULK COLLECT има %BULK_ROWCOUNT(n), който връща броя на засегнатите редове в nth DML оператор на оператора FORALL, т.е. той ще даде броя на засегнатите записи в оператора FORALL за всяка отделна стойност от променливата за събиране. Терминът 'n' показва последователността от стойности в колекцията, за която е необходим броят на редовете.

Пример 1: В този пример ще проектираме имената на всички служители от таблицата emp с помощта на BULK COLLECT и също така ще увеличим заплатите на всички служители с 5000 с помощта на FORALL.

BULK COLLECT Атрибути

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: Ангажиране на транзакцията.