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