Oracle PL/SQL BULK COLLECT: FORALL Příklad

Co je BULK COLLECT?

HROMADNÝ SBĚR snižuje přepínání kontextu mezi SQL a PL/SQL engine a umožňuje SQL enginu načítat záznamy najednou.

Oracle PL / SQL poskytuje funkci hromadného načítání záznamů, nikoli jejich načítání po jednom. Funkce BULK COLLECT může být použita v příkazu SELECT k hromadnému naplnění záznamů nebo k načtení kurzor hromadně. Protože BULK COLLECT načítá záznamy hromadně, klauzule INTO by měla vždy obsahovat proměnnou typu kolekce. Hlavní výhodou použití BULK COLLECT je, že zvyšuje výkon snížením interakce mezi databází a enginem PL/SQL.

Syntaxe:

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

Ve výše uvedené syntaxi se příkaz BULK COLLECT používá ke shromažďování dat z příkazů SELECT a FETCH.

Ustanovení FORALL

Příkaz FORALL provádí Operace DML na hromadných datech. Podobá se příkazu smyčky FOR, až na to, že ve smyčce FOR probíhají akce na úrovni záznamů, zatímco ve FORALL neexistuje koncept smyčky. Místo toho se všechna data v daném rozsahu zpracovávají současně.

Syntaxe:

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

<DML operations>;

Ve výše uvedené syntaxi bude daná operace DML provedena pro všechna data, která se nacházejí mezi dolním a horním rozsahem.

Ustanovení LIMIT

Koncept hromadného sběru načítá všechna data do cílové proměnné kolekce hromadně, tj. všechna data budou do proměnné kolekce naplněna najednou. To se však nedoporučuje, pokud je celkový počet záznamů, které je třeba načíst, velmi velký, protože když se PL/SQL pokusí načíst všechna data, spotřebuje to více paměti relace. Proto je vždy dobré omezit velikost této operace hromadného sběru.

Tohoto omezení velikosti lze snadno dosáhnout zavedením podmínky ROWNUM v příkazu SELECT, zatímco v případě kurzoru to možné není.

Chcete-li to překonat, Oracle poskytl klauzuli LIMIT, která definuje počet záznamů, které je třeba zahrnout do hromadné datové sady.

Syntaxe:

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

Ve výše uvedené syntaxi používá příkaz pro načtení kurzoru příkaz BULK COLLECT spolu s klauzulí LIMIT.

HROMADNÝ SBĚR Atributů

Podobně jako atributy kurzoru má funkce BULK COLLECT funkci %BULK_ROWCOUNT(n), která vrací počet řádků ovlivněných n-tým DML příkazem příkazu FORALL, tj. udává počet záznamů ovlivněných příkazem FORALL pro každou jednotlivou hodnotu z proměnné kolekce. Termín 'n' označuje pořadí hodnot v kolekci, pro které je potřeba znát počet řádků.

Příklad 1: V tomto příkladu promítneme všechna jména zaměstnanců z dočasné tabulky pomocí příkazu BULK COLLECT a také zvýšíme plat všech zaměstnanců o 5000 pomocí příkazu FORALL.

Níže uvedený snímek obrazovky ukazuje tento příklad BULK COLLECT a FORALL spolu s jeho výstupem v 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;
/

Výstup

Employee Fetched:BBB
Employee Fetched:XXX
Employee Fetched:YYY
Salary Updated

Code Vysvětlení:

• Code řádek 2: Deklarace kurzoru guru99_det pro příkaz 'SELECT emp_name FROM emp'.
• Code řádek 3: Deklarace lv_emp_name_tbl jako tabulkového typu VARCHAR2(50).
• Code řádek 4: Deklarace lv_emp_name jako typu lv_emp_name_tbl.
• Code řádek 6: Otevření kurzoru.
• Code řádek 7: Načítání kurzoru pomocí BULK COLLECT s velikostí LIMIT na 5000 do proměnné lv_emp_name.
• Code řádek 8–11: Nastavení smyčky FOR pro výpis všech záznamů v kolekci lv_emp_name.
• Code řádek 12: Použití FORALL k aktualizaci platu všech zaměstnanců o 5000.
• Code řádek 14: Zavázání se k transakce.

Nejčastější dotazy