Oracle PL/SQL BULK COLLECT: FORALL Esimerkki

Mikä on BULK COLLECT?

JOUKKOKERUUS vähentää kontekstinvaihtoja SQL ja PL/SQL-moottori ja sallii SQL-moottorin hakea tietueet kerralla.

Oracle PL / SQL tarjoaa toiminnallisuuden tietueiden joukkonousuun yksitellen noutamisen sijaan. Tätä BULK COLLECT -käskyä voidaan käyttää SELECT-lausekkeessa tietueiden joukkonousuun tai tietueen noutamiseen. kohdistin irtotavarana. Koska BULK COLLECT noutaa tietueet irtotavarana, INTO-lausekkeen tulisi aina sisältää kokoelmatyyppinen muuttuja. BULK COLLECTin käytön tärkein etu on, että se parantaa suorituskykyä vähentämällä tietokannan ja PL/SQL-moottorin välistä vuorovaikutusta.

Syntaksi:

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

Yllä olevassa syntaksissa BULK COLLECT -funktiota käytetään tietojen keräämiseen SELECT- ja FETCH-lausekkeista.

FORALL-lauseke

FORALL-lauseke suorittaa DML-toiminnot massadatalle. Se muistuttaa FOR-silmukkalauseketta, paitsi että FOR-silmukassa toiminnot tapahtuvat tietuetasolla, kun taas FORALLissa ei ole LOOP-käsitettä. Sen sijaan kaikki annetulla alueella olevat tiedot käsitellään samanaikaisesti.

Syntaksi:

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

<DML operations>;

Yllä olevassa syntaksissa annettu DML-operaatio suoritetaan koko datalle, joka on alemman ja ylemmän alueen välillä.

LIMIT-lauseke

Joukkokeräyskonsepti lataa kaikki tiedot kohdekokoelmamuuttujaan kerralla, eli kaikki tiedot täytetään kokoelmamuuttujaan kerralla. Tämä ei kuitenkaan ole suositeltavaa, jos ladattavien tietueiden kokonaismäärä on erittäin suuri, koska PL/SQL kuluttaa enemmän istuntomuistia yrittäessään ladata kaikki tiedot. Siksi on aina hyvä rajoittaa tämän joukkokeräysoperaation kokoa.

Tämä kokorajoitus voidaan helposti saavuttaa lisäämällä ROWNUM-ehto SELECT-lausekkeeseen, kun taas kohdistimen tapauksessa tämä ei ole mahdollista.

Tämän voittamiseksi, Oracle on tarjonnut LIMIT-lausekkeen, joka määrittää joukkoon sisällytettävien tietueiden määrän.

Syntaksi:

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

Yllä olevassa syntaksissa kohdistimen noutolauseke käyttää BULK COLLECT -lauseketta yhdessä LIMIT-lauseen kanssa.

BULK COLLECT Ominaisuudet

Samoin kuin kohdistimen attribuuttien, BULK COLLECT -funktion %BULK_ROWCOUNT(n) palauttaa FORALL-lausekkeen n:nnen DML-lausekkeen vaikuttamien rivien lukumäärän. Tämä tarkoittaa, että se antaa FORALL-lausekkeen vaikuttamien tietueiden lukumäärän jokaiselle kokoelmamuuttujan yksittäiselle arvolle. Termi 'n' osoittaa sen arvon järjestyksen kokoelmassa, jolta rivien lukumäärää tarvitaan.

Esimerkki 1: Tässä esimerkissä projisoimme kaikkien työntekijöiden nimet emp-taulukosta käyttämällä BULK COLLECT -metodia ja korotamme myös kaikkien työntekijöiden palkkoja 5000:lla käyttämällä FORALL-metodia.

Alla oleva kuvakaappaus näyttää tämän BULK COLLECT- ja FORALL-esimerkin sekä sen tulosteen 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;
/

ulostulo

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

Code Selitys:

• Code rivi 2: Kohdistimen guru99_det määrittely lausekkeelle 'SELECT emp_name FROM emp'.
• Code rivi 3: Taulukon lv_emp_name_tbl määrittäminen VARCHAR2(50)-tyyppiseksi taulukkoksi.
• Code rivi 4: Määritellään lv_emp_name lv_emp_name_tbl-tyypiksi.
• Code rivi 6: Kursorin avaaminen.
• Code rivi 7: Kohdistimen hakeminen BULK COLLECT -komennolla, jonka LIMIT-kooksi on asetettu 5000, lv_emp_name-muuttujaan.
• Code rivi 8–11: FOR-silmukan asettaminen tulostamaan kaikki lv_emp_name-kokoelman tietueet.
• Code rivi 12: FORALL-funktion käyttö kaikkien työntekijöiden palkkojen päivittämiseen 5000:lla.
• Code rivi 14: Sitoutuminen kauppa.

UKK