Oracle PL/SQL BULK COLLECT: FORALL Primjer

Što je BULK COLLECT?

BULK COLLECT smanjuje prebacivanje konteksta između SQL i PL/SQL mehanizma i omogućuje SQL mašini da dohvati zapise odjednom.

Oracle PL/SQL pruža funkcionalnost skupnog dohvaćanja zapisa umjesto dohvaćanja jednog po jednog. Ovaj BULK COLLECT može se koristiti u izjavi 'SELECT' za skupno popunjavanje zapisa ili skupno dohvaćanje kursora. Budući da BULK COLLECT dohvaća zapis u BULK-u, klauzula INTO uvijek treba sadržavati varijablu tipa zbirke. Glavna prednost korištenja BULK COLLECT-a je povećanje performansi smanjenjem interakcije između baze podataka i PL/SQL mehanizma.

Sintaksa:

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

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

FORALL klauzula

FORALL omogućuje izvođenje DML operacija na skupnim podacima. Slično je iskazu FOR petlje osim u FOR petlja stvari se događaju na rekordnoj razini, dok u FORALL-u nema koncepta LOOP. Umjesto toga, svi podaci prisutni u zadanom rasponu obrađuju se u isto vrijeme.

Sintaksa:

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

<DML operations>;

U gornjoj sintaksi, navedena DML operacija će se izvršiti za sve podatke koji su prisutni između nižeg i višeg raspona.

LIMIT klauzula

Koncept skupnog prikupljanja učitava cjelokupne podatke u ciljnu varijablu prikupljanja kao skup, tj. cijeli će podaci biti popunjeni u varijablu prikupljanja u jednom potezu. Ali to nije preporučljivo kada je ukupni zapis koji treba učitati jako velik, jer kada PL / SQL pokušava učitati sve podatke i troši više memorije sesije. Stoga je uvijek dobro ograničiti veličinu ove operacije skupnog prikupljanja.

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

Da ovo prevladamo Oracle je osigurao klauzulu 'LIMIT' koja definira broj zapisa koji moraju biti uključeni u skupno.

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 pokazivač atributi BULK COLLECT ima %BULK_ROWCOUNT(n) koji vraća broj redaka zahvaćenih u nth DML naredba FORALL naredbe, tj. dat će broj zapisa na koje utječe naredba FORALL za svaku pojedinačnu vrijednost iz varijable zbirke. Izraz 'n' označava slijed 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 i također ćemo povećati plaću svih zaposlenika za 5000 koristeći FORALL.

BULK COLLECT Atributi

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

Izlaz

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

Objašnjenje koda:

  • Kodna linija 2: Deklaracija kursora guru99_det za izjavu 'SELECT emp_name FROM emp'.
  • Kodna linija 3: Deklaracija lv_emp_name_tbl kao tipa tablice VARCHAR2(50)
  • Kodna linija 4: Deklariranje lv_emp_name kao tipa lv_emp_name_tbl.
  • Redak koda 6: Otvaranje kursora.
  • Redak koda 7: Dohvaćanje kursora korištenjem BULK COLLECT s LIMIT veličinom kao varijablom 5000 intl lv_emp_name.
  • Redak koda 8-11: Postavljanje FOR petlje za ispis svih zapisa u kolekciji lv_emp_name.
  • Redak koda 12: Korištenje FORALL ažuriranje plaće svih zaposlenika za 5000.
  • Redak koda 14: Izvršavanje transakcije.