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

Od: Fiona Brownová Fiona Brownová
Hours aktualizováno

Co je BULK COLLECT?

BULK COLLECT snižuje kontextové přepínání mezi SQL a PL/SQL enginem a umožňuje SQL motoru načítat záznamy najednou.

Oracle PL/SQL poskytuje funkci načítání záznamů hromadně, spíše než načítání po jednom. Tento BULK COLLECT lze použít v příkazu 'SELECT' k hromadnému naplnění záznamů nebo k hromadnému načtení kurzoru. Protože BULK COLLECT načítá záznam v BULK, klauzule INTO by měla vždy obsahovat proměnnou typu kolekce. Hlavní výhodou použití BULK COLLECT je zvýšení výkonu snížením interakce mezi databází a PL/SQL enginem.

Syntaxe:

SELECT <columnl> BULK COLLECT INTO bulk_varaible FROM <table name>;
FETCH <cursor_name> BULK COLLECT INTO <bulk_varaible >;

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

Ustanovení FORALL

FORALL umožňuje provádět DML operace s daty hromadně. Je podobný příkazu smyčky FOR kromě in smyčka FOR věci se dějí na rekordní úrovni, zatímco ve FORALL neexistuje koncept LOOP. Místo toho jsou všechna data přítomná v daném rozsahu zpracována 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á jsou přítomna mezi nižším a vyšším rozsahem.

Ustanovení LIMIT

Koncept hromadného sběru načte celá data do proměnné cílového sběru hromadně, tj. celá data budou naplněna do proměnné sběru najednou. Ale to není vhodné, když je celkový záznam, který je třeba načíst, velmi velký, protože když PL / SQL pokouší načíst všechna data, spotřebovává více paměti relace. Proto je vždy dobré omezit velikost tohoto hromadného sběru.

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

Abych to překonal Oracle poskytla klauzuli „LIMIT“, která definuje počet záznamů, které je třeba zahrnout do hromadného souboru.

Syntaxe:

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

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

HROMADNÝ SBĚR Atributů

Podobně jako u kurzor atributy BULK COLLECT má %BULK_ROWCOUNT(n), které vrací počet řádků ovlivněných v nth DML 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. Výraz 'n' označuje posloupnost hodnot v kolekci, pro kterou je potřeba počet řádků.

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

HROMADNÝ SBĚR Atributů

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;
/

SOUVISEJÍCÍ ČLÁNKY

Výstup

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

Vysvětlení kódu:

  • Řádek kódu 2: Deklarace kurzoru guru99_det pro příkaz 'SELECT emp_name FROM emp'.
  • Řádek kódu 3: Deklarování lv_emp_name_tbl jako typ tabulky VARCHAR2(50)
  • Řádek kódu 4: Deklaruje lv_emp_name jako typ lv_emp_name_tbl.
  • Řádek kódu 6: Otevření kurzoru.
  • Řádek kódu 7: Načtení kurzoru pomocí BULK COLLECT s LIMIT velikostí jako 5000 intl proměnné lv_emp_name.
  • Řádek 8–11 kódu: Nastavení smyčky FOR pro tisk všech záznamů v kolekci lv_emp_name.
  • Řádek kódu 12: Pomocí FORALL aktualizujete plat všech zaměstnanců o 5000.
  • Řádek kódu 14: Potvrzení transakce.

Máš rád: