Oracle PL/SQL BULK COLLECT: FORALL Näide

Mis on BULK COLLECT?

BULK COLLECT vähendab kontekstivahetusi SQL ja PL/SQL-mootorit ning võimaldab SQL-mootoril kirjed korraga hankida.

Oracle PL / SQL pakub funktsionaalsust kirjete hulgi toomiseks, mitte ükshaaval toomiseks. Seda BULK COLLECT funktsiooni saab kasutada SELECT-lauses kirjete hulgi täitmiseks või... kursor hulgi. Kuna BULK COLLECT hangib kirjeid hulgi, peaks INTO-klausel alati sisaldama kogumitüübi muutujat. BULK COLLECTi peamine eelis on jõudluse suurendamine, vähendades andmebaasi ja PL/SQL-mootori vahelist interaktsiooni.

süntaksit:

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

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

FORALL klausel

FORALL-lause täidab DML-i toimingud hulgiandmete puhul. See sarnaneb FOR-tsükli lausega, välja arvatud see, et FOR-tsüklis toimuvad toimingud kirje tasandil, samas kui FORALL-is puudub LOOP-kontseptsioon. Selle asemel töödeldakse kõiki antud vahemikus olevaid andmeid samaaegselt.

süntaksit:

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

<DML operations>;

Ülaltoodud süntaksis teostatakse antud DML-toiming kõigi andmete jaoks, mis asuvad alumise ja ülemise vahemiku vahel.

LIMIT klausel

Hulgikogumise kontseptsioon laadib kõik andmed sihtkogumi muutujasse hulgi, st kõik andmed täidetakse kogumi muutujasse korraga. Kuid see pole soovitatav, kui laaditavate kirjete koguarv on väga suur, sest kui PL/SQL proovib kõiki andmeid laadida, tarbib see rohkem seansimälu. Seega on alati hea selle hulgikogumise operatsiooni suurust piirata.

Selle suurusepiirangu saab hõlpsasti saavutada, kui SELECT-lauses lisada tingimus ROWNUM, kursori puhul aga see pole võimalik.

Selle ületamiseks Oracle on esitanud LIMIT-klausli, mis määrab hulgi hulka kuuluvate kirjete arvu.

süntaksit:

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

Ülaltoodud süntaksis kasutab kursori tõmbamise lause BULK COLLECT lauset koos LIMIT klausliga.

BULK COLLECT Atribuudid

Sarnaselt kursori atribuutidega on BULK COLLECT-il %BULK_ROWCOUNT(n), mis tagastab FORALL-lause n-ndas DML-lauses mõjutatud ridade arvu, st see annab FORALL-lauses mõjutatud kirjete arvu iga kollektsioonimuutuja väärtuse kohta. Termin 'n' näitab väärtuse järjestust kollektsioonis, mille jaoks ridade arvu on vaja.

Näide 1: Selles näites projitseerime kõik töötajate nimed emp-tabelist, kasutades funktsiooni BULK COLLECT, ja suurendame kõigi töötajate palka 5000 võrra, kasutades funktsiooni FORALL.

Allolev ekraanipilt näitab seda BULK COLLECT ja FORALL näidet koos selle väljundiga 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äljund

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

Code Selgitus:

• Code rida 2: Kursori guru99_det deklareerimine lause 'SELECT emp_name FROM emp' jaoks.
• Code rida 3: Tabeli lv_emp_name_tbl deklareerimine VARCHAR2(50) tüübi tabelina.
• Code rida 4: Lv_emp_name deklareerimine lv_emp_name_tbl tüübina.
• Code rida 6: Kursori avamine.
• Code rida 7: Kursori toomine käsuga BULK COLLECT, mille LIMIT suurus on 5000, muutujasse lv_emp_name.
• Code rida 8-11: FOR-tsükli seadistamine kõigi kollektsiooni lv_emp_name kirjete printimiseks.
• Code rida 12: FORALL-i kasutamine kõigi töötajate palkade värskendamiseks 5000 võrra.
• Code rida 14: Pühendumine tehing.

KKK