Oracle PL/SQL BULK COLLECT: FORALL Esimerkki

Mikä on BULK COLLECT?

BULK COLLECT vähentää kontekstin vaihtoa SQL- ja PL/SQL-moottorin välillä ja sallii SQL-moottorin hakea tietueet kerralla.

Oracle PL/SQL tarjoaa toiminnon tietueiden noutamiseen joukkona sen sijaan, että niitä haettaisiin yksitellen. Tätä BULK COLLECT -toimintoa voidaan käyttää SELECT-käskyssä tietueiden täyttämiseen joukkona tai kohdistimen noutamiseen joukkona. Koska BULK COLLECT hakee tietueen BULK-muodossa, INTO-lauseen tulee aina sisältää kokoelmatyyppimuuttuja. BULK COLLECTin käytön tärkein etu on se, että se lisää suorituskykyä vähentämällä tietokannan ja PL/SQL-moottorin välistä vuorovaikutusta.

Syntaksi:

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

Yllä olevassa syntaksissa BULK COLLECTia käytetään tietojen keräämiseen SELECT- ja FETCH-käskyistä.

FORALL-lauseke

FORALL mahdollistaa DML-toimintojen suorittamisen datalle massana. Se on samanlainen kuin FOR-silmukan lauseke paitsi in FOR silmukkaa asiat tapahtuvat ennätystasolla, kun taas FORALLissa ei ole LOOP-konseptia. Sen sijaan koko annetulla alueella oleva data käsitellään samanaikaisesti.

Syntaksi:

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

<DML operations>;

Yllä olevassa syntaksissa annettu DML-toiminto suoritetaan koko datalle, joka on alemman ja korkeamman alueen välillä.

LIMIT-lauseke

Joukkokeräyskonsepti lataa koko datan kohdekeräilymuuttujaan massana eli koko data täytetään kokoelmamuuttujaan yhdellä kertaa. Mutta tämä ei ole suositeltavaa, kun ladattava kokonaistietue on erittäin suuri, koska milloin PL / SQL yrittää ladata koko datan kuluttaa enemmän istuntomuistia. Tästä syystä on aina hyvä rajoittaa tämän bulkkikeräysoperaation kokoa.

Tämä kokorajoitus voidaan kuitenkin saavuttaa helposti ottamalla käyttöön ROWNUM-ehto 'SELECT'-käskyssä, kun taas kursorin tapauksessa tämä ei ole mahdollista.

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

Syntaksi:

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

Yllä olevassa syntaksissa kohdistimen hakulause käyttää BULK COLLECT -lausetta LIMIT-lauseen kanssa.

BULK COLLECT Ominaisuudet

Samanlaisia kohdistin attribuutit BULK COLLECT sisältää %BULK_ROWCOUNT(n), joka palauttaa niiden rivien määrän, joihin n vaikuttaa^th FORALL-käskyn DML-lause, eli se antaa FORALL-käskyssä vaikuttavien tietueiden määrän jokaiselle kokoelmamuuttujan yksittäiselle arvolle. Termi 'n' ilmaisee kokoelman arvosarjan, jota varten rivimäärää tarvitaan.

Esimerkki 1: Tässä esimerkissä projisoimme kaikki työntekijän nimet emp-taulukosta käyttämällä BULK COLLECTia ja aiomme myös nostaa kaikkien työntekijöiden palkkaa 5000:lla FORALLin avulla.

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

ulostulo

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

Koodin selitys:

• Koodirivi 2: Ilmoitetaan kohdistin guru99_det lauseelle 'SELECT emp_name FROM emp'.
• Koodirivi 3: lv_emp_name_tbl:n ilmoittaminen VARCHAR2(50) taulukkotyypiksi
• Koodirivi 4: Ilmoitetaan lv_emp_name lv_emp_name_tbl-tyypiksi.
• Koodirivi 6: Kursorin avaaminen.
• Koodirivi 7: Kohdistimen noutaminen käyttämällä BULK COLLECTia, jonka LIMIT-koko on 5000 intl lv_emp_name -muuttuja.
• Koodirivit 8-11: FOR-silmukan asettaminen tulostamaan kaikki kokoelman lv_emp_name tietueet.
• Koodirivi 12: FORALLin avulla päivitetään kaikkien työntekijöiden palkka 5000:lla.
• Koodirivi 14: Kaupan sitominen.