Oracle PL/SQL BULK COLLECT: FORALL Eksempel

Hva er BULK COLLECT?

MASSEINNSAMLING reduserer kontekstbytter mellom SQL og PL/SQL-motoren, og lar SQL-motoren hente postene samtidig.

Oracle PL / SQL gir funksjonaliteten til å hente postene i bulk i stedet for å hente dem én etter én. Denne BULK COLLECT-funksjonen kan brukes i en SELECT-setning for å fylle ut postene i bulk, eller for å hente en markør i bulk. Siden BULK COLLECT henter postene i bulk, bør INTO-klausulen alltid inneholde en variabel av samlingstypen. Hovedfordelen med å bruke BULK COLLECT er at den øker ytelsen ved å redusere interaksjonen mellom databasen og PL/SQL-motoren.

Syntaks:

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

I syntaksen ovenfor brukes BULK COLLECT til å samle inn dataene fra SELECT- og FETCH-setningene.

FORALL klausul

FORALL-setningen utfører DML-operasjoner på data i bulk. Det ligner en FOR-løkkesetning, bortsett fra at i en FOR-løkke skjer handlinger på postnivå, mens det i FORALL ikke finnes noe LOOP-konsept. I stedet behandles alle dataene som finnes i det gitte området samtidig.

Syntaks:

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

<DML operations>;

I syntaksen ovenfor vil den gitte DML-operasjonen bli utført for alle dataene som finnes mellom det nedre og øvre området.

LIMIT-klausul

Konseptet med masseinnsamling laster alle dataene inn i målinnsamlingsvariabelen som en bulk, dvs. at alle dataene vil bli fylt inn i innsamlingsvariabelen på én gang. Men dette er ikke tilrådelig når det totale antallet poster som må lastes inn er veldig stort, fordi når PL/SQL prøver å laste inn alle dataene, bruker det mer sesjonsminne. Derfor er det alltid lurt å begrense størrelsen på denne masseinnsamlingsoperasjonen.

Denne størrelsesgrensen kan enkelt oppnås ved å introdusere ROWNUM-betingelsen i SELECT-setningen, mens dette ikke er mulig for en markør.

For å overvinne dette, Oracle har gitt LIMIT-klausulen som definerer antall poster som må inkluderes i bulk.

Syntaks:

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

I syntaksen ovenfor bruker markørhentingssetningen BULK COLLECT-setningen sammen med LIMIT-klausulen.

BULK COLLECT-attributter

I likhet med markørattributter har BULK COLLECT %BULK_ROWCOUNT(n) som returnerer antall rader som er påvirket i den n-te DML-setningen i FORALL-setningen, dvs. den gir antallet poster som er påvirket i FORALL-setningen for hver enkelt verdi fra samlingsvariabelen. Begrepet 'n' indikerer sekvensen av verdien i samlingen som radantallet er nødvendig for.

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

Skjermbildet nedenfor viser dette BULK COLLECT- og FORALL-eksemplet sammen med utdataene i 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;
/

Produksjon

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

Code Forklaring:

• Code linje 2: Deklarerer markøren guru99_det for setningen 'SELECT emp_name FROM emp'.
• Code linje 3: Deklarerer lv_emp_name_tbl som en tabelltype for VARCHAR2(50).
• Code linje 4: Deklarerer lv_emp_name som lv_emp_name_tbl-typen.
• Code linje 6: Åpne markøren.
• Code linje 7: Henter markøren ved hjelp av BULK COLLECT med LIMIT-størrelsen 5000 inn i variabelen lv_emp_name.
• Code linje 8–11: Setter opp en FOR-løkke for å skrive ut alle postene i samlingen lv_emp_name.
• Code linje 12: Bruk FORALL til å oppdatere lønnen til alle ansatte med 5000.
• Code linje 14: Å begå Transaksjonen.

Spørsmål og svar