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

Какво е BULK COLLECT?

ГРУПОВОТО СЪБИРАНЕ намалява превключването на контекста между SQL и PL/SQL енджин и позволява на SQL енджинът да извлича записите наведнъж.

Oracle PL / SQL предоставя функционалността за групово извличане на записите, вместо един по един. Тази команда BULK COLLECT може да се използва в SELECT команда за групово попълване на записите или за извличане на курсор групово. Тъй като BULK COLLECT извлича записите групово, клаузата INTO винаги трябва да съдържа променлива от типа колекция. Основното предимство на използването на BULK COLLECT е, че увеличава производителността чрез намаляване на взаимодействието между базата данни и PL/SQL двигателя.

Синтаксис:

SELECT <column1> BULK COLLECT INTO bulk_variable FROM <table name>;
FETCH <cursor_name> BULK COLLECT INTO <bulk_variable>;

В горния синтаксис, BULK COLLECT се използва за събиране на данни от операторите SELECT и FETCH.

Клауза FORALL

Изразът FORALL изпълнява DML операции върху данни на едро. Прилича на оператор на цикъл FOR, с изключение на това, че във 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), който връща броя на засегнатите редове в n-тия DML оператор на FORALL оператора, т.е. дава броя на засегнатите записи в FORALL оператора за всяка отделна стойност от променливата на колекцията. Терминът „n“ показва последователността на стойността в колекцията, за която е необходим броят на редовете.

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

Снимката на екрана по-долу показва този пример за BULK COLLECT и FORALL, заедно с неговия резултат в Oracle.

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 salary=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

Code Обяснение:

• Code ред 2: Деклариране на курсора guru99_det за оператора 'SELECT emp_name FROM emp'.
• Code ред 3: Деклариране на lv_emp_name_tbl като табличен тип VARCHAR2(50).
• Code ред 4: Деклариране на lv_emp_name като тип lv_emp_name_tbl.
• Code ред 6: Отваряне на курсора.
• Code ред 7: Извличане на курсора чрез BULK COLLECT с размер LIMIT, равен на 5000, в променливата lv_emp_name.
• Code ред 8-11: Настройване на FOR цикъл за отпечатване на всички записи в колекцията lv_emp_name.
• Code ред 12: Използване на FORALL за актуализиране на заплатата на всички служители с 5000.
• Code ред 14: Извършване на транзакция.

Въпроси и Отговори