Oracle PL/SQL BULK COLLECT: FORALL Näide

Autor: Fiona Brown Fiona Brown
Hours Ajakohastatud

Mis on BULK COLLECT?

BULK COLLECT vähendab kontekstivahetust SQL-i ja PL/SQL-mootori vahel ning võimaldab SQL-mootoril kirjeid korraga tuua.

Oracle PL/SQL pakub kirjete hulgitoomise funktsiooni, mitte ükshaaval toomist. Seda BULK COLLECTi saab kasutada lauses SELECT kirjete hulgi täitmiseks või kursori hulgitoomiseks. Kuna BULK COLLECT hangib kirje BULK-vormingus, peaks INTO-klausel alati sisaldama kogutüübi muutujat. BULK COLLECTi kasutamise peamine eelis on see, et see suurendab jõudlust, vähendades andmebaasi ja PL/SQL-mootori vahelist suhtlust.

süntaksit:

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

Ülaltoodud süntaksis kasutatakse BULK COLLECTi andmete kogumiseks lausetest „SELECT” ja „FETCH”.

FORALL klausel

FORALL võimaldab teha DML-i toiminguid andmetega hulgi. See on sarnane FOR tsükli lausega, välja arvatud in FOR silmus asjad juhtuvad rekordtasemel, samas kui FORALLis pole LOOPi kontseptsiooni. Selle asemel töödeldakse kõiki antud vahemikus olevaid andmeid korraga.

süntaksit:

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

<DML operations>;

Ülaltoodud süntaksis täidetakse antud DML-operatsioon kogu andmete jaoks, mis asuvad madalama ja kõrgema vahemiku vahel.

LIMIT klausel

Hulgikogumise kontseptsioon laadib kogu andmed sihtkogumismuutujasse hulgi, st kogu andmed sisestatakse kogumismuutujasse ühe korraga. Kuid see pole soovitatav, kui laadimist vajav kirje on väga suur, sest millal PL / SQL proovib laadida kogu andmeid, see kulutab rohkem seansimälu. Seetõttu on alati hea selle hulgikogumisoperatsiooni suurust piirata.

Seda suurusepiirangut saab aga hõlpsasti saavutada, lisades lausesse 'SELECT' tingimuse ROWNUM, samas kui kursori puhul pole see võimalik.

Et sellest üle saada Oracle on esitanud klausli LIMIT, mis määrab kirjete arvu, mis tuleb hulgi lisada.

süntaksit:

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

Ülaltoodud süntaksis kasutab kursori toomise lause BULK COLLECT lauset koos klausliga LIMIT.

BULK COLLECT Atribuudid

Sarnaselt kursor atribuudid BULK COLLECT sisaldab %BULK_ROWCOUNT(n), mis tagastab n-s mõjutatud ridade arvuth FORALL-lause DML-lause, st see annab FORALL-lauses mõjutatud kirjete arvu kogumuutuja iga üksiku väärtuse kohta. Mõiste n tähistab väärtuste jada kogus, mille jaoks on vaja ridade arvu.

Näiteks 1: Selles näites projitseerime emp tabelist BULK COLLECTi abil kogu töötajate nimed ja tõstame ka kõigi töötajate palka 5000 võrra kasutades FORALLi.

BULK COLLECT Atribuudid

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

SEOTUD ARTIKLID

Väljund

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

Koodi selgitus:

  • Koodirida 2: Kursori guru99_det deklareerimine lausele 'SELECT emp_name FROM emp'.
  • Koodirida 3: lv_emp_name_tbl deklareerimine VARCHAR2(50) tabelitüübiks
  • Koodirida 4: lv_emp_name deklareerimine lv_emp_name_tbl tüübiks.
  • Koodirida 6: Kursori avamine.
  • Koodirida 7: Kursori toomine BULK COLLECTi abil, mille suurus on LIMIT kui muutuja 5000 intl lv_emp_name.
  • Koodirida 8–11: Silmuse FOR seadistamine kogu lv_emp_name kogu kirje printimiseks.
  • Koodirida 12: Kasutades FORALLi, värskendades kõigi töötajate palka 5000 võrra.
  • Koodirida 14: Tehingu sooritamine.