Oracle PL/SQL BULK COLLECT: FORALL Primjer

Što je BULK COLLECT?

SKUPNO PRIKUPLJANJE smanjuje promjene konteksta između SQL i PL/SQL mehanizam te omogućuje SQL mehanizmu da odjednom dohvati zapise.

Oracle PL / SQL pruža funkcionalnost skupnog dohvaćanja zapisa umjesto pojedinačnog dohvaćanja. Ova naredba BULK COLLECT može se koristiti u naredbi SELECT za skupno popunjavanje zapisa ili za dohvaćanje pokazivač skupno. Budući da BULK COLLECT dohvaća zapise skupno, klauzula INTO uvijek treba sadržavati varijablu tipa kolekcije. Glavna prednost korištenja BULK COLLECT je povećanje performansi smanjenjem interakcije između baze podataka i PL/SQL mehanizma.

Sintaksa:

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

U gornjoj sintaksi, BULK COLLECT se koristi za prikupljanje podataka iz naredbi SELECT i FETCH.

FORALL klauzula

Naredba FORALL izvršava DML operacije na podacima u skupu. Podsjeća na naredbu FOR petlje, osim što se u FOR petlji radnje događaju na razini zapisa, dok u FORALL-u nema koncepta LOOP-a. Umjesto toga, svi podaci prisutni u zadanom rasponu obrađuju se istovremeno.

Sintaksa:

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

<DML operations>;

U gornjoj sintaksi, zadana DML operacija će se izvršiti za sve podatke koji se nalaze između donjeg i gornjeg raspona.

LIMIT klauzula

Koncept skupnog prikupljanja učitava sve podatke u ciljnu varijablu kolekcije kao skupno, tj. svi podaci će se popuniti u varijablu kolekcije odjednom. Ali to se ne preporučuje kada je ukupan broj zapisa koje treba učitati vrlo velik, jer kada PL/SQL pokuša učitati sve podatke, troši više memorije sesije. Stoga je uvijek dobro ograničiti veličinu ove operacije skupnog prikupljanja.

Ovo ograničenje veličine može se lako postići uvođenjem uvjeta ROWNUM u SELECT naredbu, dok u slučaju kursora to nije moguće.

Da bi se ovo prevladalo, Oracle je osigurao klauzulu LIMIT koja definira broj zapisa koji trebaju biti uključeni u skupnu datoteku.

Sintaksa:

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

U gornjoj sintaksi, naredba za dohvaćanje kursora koristi naredbu BULK COLLECT zajedno s klauzulom LIMIT.

BULK COLLECT Atributi

Slično atributima kursora, BULK COLLECT ima %BULK_ROWCOUNT(n) koji vraća broj redaka na koje utječe n-ta DML naredba FORALL naredbe, tj. daje broj zapisa na koje utječe FORALL naredba za svaku pojedinačnu vrijednost iz varijable kolekcije. Izraz 'n' označava redoslijed vrijednosti u kolekciji za koji je potreban broj redaka.

Primjer 1: U ovom primjeru, projicirat ćemo sva imena zaposlenika iz emp tablice koristeći BULK COLLECT, a također ćemo povećati plaću svih zaposlenika za 5000 koristeći FORALL.

Snimka zaslona u nastavku prikazuje ovaj primjer BULK COLLECT i FORALL zajedno s njegovim izlazom u 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;
/

Izlaz

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

Code Objašnjenje:

• Code redak 2: Deklarisanje kursora guru99_det za naredbu 'SELECT emp_name FROM emp'.
• Code redak 3: Deklarisanje lv_emp_name_tbl kao tipa tablice VARCHAR2(50).
• Code redak 4: Deklariranje lv_emp_name kao tipa lv_emp_name_tbl.
• Code redak 6: Otvaranje kursora.
• Code redak 7: Dohvaćanje kursora pomoću BULK COLLECT naredbe s LIMIT veličinom od 5000 u varijablu lv_emp_name.
• Code redak 8-11: Postavljanje FOR petlje za ispis svih zapisa u kolekciji lv_emp_name.
• Code redak 12: Korištenje FORALL-a za ažuriranje plaća svih zaposlenika za 5000.
• Code redak 14: Počinjanje transakcija.

Pitanja i odgovori