Oracle PL/SQL BULK COLLECT: FORALL Eksempel

Hva er BULK COLLECT?

BULK COLLECT reduserer kontekstbytte mellom SQL og PL/SQL-motor og lar SQL-motoren hente postene på en gang.

Oracle PL/SQL gir funksjonaliteten til å hente postene i bulk i stedet for å hente én etter én. Denne BULK COLLECT kan brukes i 'SELECT'-setningen for å fylle ut postene i bulk eller for å hente markøren i bulk. Siden BULK COLLECT henter posten i BULK, bør INTO-leddet alltid inneholde en samlingstypevariabel. Den største fordelen med å bruke BULK COLLECT er at det øker ytelsen ved å redusere interaksjonen mellom database og PL/SQL-motor.

Syntaks:

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

I syntaksen ovenfor brukes BULK COLLECT for å samle inn data fra 'SELECT' og 'FETCH'-setningen.

FORALL klausul

FORALL gjør det mulig å utføre DML-operasjoner på data i bulk. Det ligner på FOR loop-setningen bortsett fra i FOR løkke ting skjer på rekordnivå, mens det i FORALL ikke er noe LOOP-konsept. I stedet blir hele dataen som er tilstede i det gitte området behandlet samtidig.

Syntaks:

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

<DML operations>;

I syntaksen ovenfor vil den gitte DML-operasjonen bli utført for hele dataen som er tilstede mellom lavere og høyere område.

LIMIT-klausul

Masseinnsamlingskonseptet laster hele dataen inn i målinnsamlingsvariabelen som en bulk, dvs. at hele dataen vil fylles inn i innsamlingsvariabelen på én gang. Men dette er ikke tilrådelig når den totale posten som må lastes er veldig stor, fordi når PL / SQL prøver å laste inn hele dataen, det bruker mer øktminne. Derfor er det alltid greit å begrense størrelsen på denne bulkoppsamlingsoperasjonen.

Imidlertid kan denne størrelsesgrensen enkelt oppnås ved å introdusere ROWNUM-betingelsen i 'SELECT'-setningen, mens dette ikke er mulig for markøren.

For å overvinne dette Oracle har gitt 'LIMIT'-klausul som definerer antall poster som må inkluderes i massen.

Syntaks:

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

I syntaksen ovenfor bruker cursor fetch-setningen BULK COLLECT-setningen sammen med LIMIT-leddet.

BULK COLLECT-attributter

Lik markør attributter BULK COLLECT har %BULK_ROWCOUNT(n) som returnerer antall berørte rader i n^th DML-setning av FORALL-setningen, dvs. den vil gi antallet poster som er berørt i FORALL-setningen for hver enkelt verdi fra samlingsvariabelen. Begrepet 'n' angir verdisekvensen i samlingen som radtellingen er nødvendig for.

Eksempel 1: I dette eksemplet vil vi projisere alle ansattes navn fra emp-tabellen ved å bruke BULK COLLECT, og vi skal også øke lønnen til alle ansatte med 5000 ved å bruke FORALL.

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

Produksjon

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

Kodeforklaring:

• Kodelinje 2: Erklærer markøren guru99_det for setningen 'SELECT emp_name FROM emp'.
• Kodelinje 3: Erklærer lv_emp_name_tbl som tabelltype for VARCHAR2(50)
• Kodelinje 4: Erklærer lv_emp_name som lv_emp_name_tbl type.
• Kodelinje 6: Åpne markøren.
• Kodelinje 7: Henter markøren ved å bruke BULK COLLECT med LIMIT-størrelsen som 5000 intl lv_emp_name-variabel.
• Kodelinje 8-11: Sette opp FOR loop for å skrive ut all posten i samlingen lv_emp_name.
• Kodelinje 12: Ved å bruke FORALL oppdaterer lønnen til alle ansatte med 5000.
• Kodelinje 14: Forplikter transaksjonen.