Oracle PL/SQL BULK COLLECT: FORALL Eksempel

Hvad er BULK COLLECT?

MASSEINDSAMLING reducerer kontekstskift mellem SQL og PL/SQL-motoren og tillader SQL-motoren at hente posterne på én gang.

Oracle PL / SQL giver mulighed for at hente posterne i bulk i stedet for at hente dem én efter én. Denne BULK COLLECT kan bruges i en SELECT-sætning til at udfylde posterne i bulk eller til at hente en markøren i bulk. Da BULK COLLECT henter posterne i bulk, skal INTO-klausulen altid indeholde en variabel af samlingstypen. Den største fordel ved at bruge BULK COLLECT er, at det øger ydeevnen ved at reducere interaktionen mellem 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 ovenstående syntaks bruges BULK COLLECT til at indsamle data fra SELECT- og FETCH-sætningerne.

FORALL klausul

FORALL-sætningen udfører DML-operationer på data i bulk. Det ligner en FOR-løkkesætning, bortset fra at handlinger i en FOR-løkke sker på recordniveau, hvorimod der i FORALL ikke er noget LOOP-koncept. I stedet behandles alle data i det givne område på samme tid.

Syntaks:

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

<DML operations>;

I ovenstående syntaks vil den givne DML-operation blive udført for alle data, der er til stede mellem det nedre og øvre område.

LIMIT klausul

Bulk collect-konceptet indlæser alle data i målindsamlingsvariablen som en bulk, dvs. alle dataene vil blive udfyldt i indsamlingsvariablen på én gang. Men dette er ikke tilrådeligt, når det samlede antal poster, der skal indlæses, er meget stort, fordi når PL/SQL forsøger at indlæse alle dataene, bruger det mere sessionshukommelse. Derfor er det altid godt at begrænse størrelsen af ​​denne bulkindsamlingsoperation.

Denne størrelsesgrænse kan nemt opnås ved at indføre ROWNUM-betingelsen i SELECT-sætningen, hvorimod dette ikke er muligt i tilfælde af en cursor.

For at overvinde dette, Oracle har leveret LIMIT-klausulen, der definerer antallet af poster, der skal inkluderes i bulk-samlingen.

Syntaks:

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

I ovenstående syntaks bruger cursor fetch-sætningen BULK COLLECT-sætningen sammen med LIMIT-klausulen.

BULK COLLECT-attributter

I lighed med cursorattributter har BULK COLLECT %BULK_ROWCOUNT(n), der returnerer antallet af rækker, der er påvirket i den n'te DML-sætning i FORALL-sætningen, dvs. den angiver antallet af poster, der er påvirket i FORALL-sætningen for hver enkelt værdi fra samlingsvariablen. Termen 'n' angiver rækkefølgen af ​​værdien i samlingen, for hvilken rækketællingen er nødvendig.

Eksempel 1: I dette eksempel vil vi projicere alle medarbejdernavnene fra medarbejdertabellen ved hjælp af BULK COLLECT, og vi vil også øge lønnen for alle medarbejdere med 5000 ved hjælp af FORALL.

Skærmbilledet nedenfor viser dette BULK COLLECT- og FORALL-eksempel sammen med dets output 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;
/

Produktion

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

Code Forklaring:

• Code linje 2: Deklarerer markøren guru99_det for sætningen 'SELECT emp_name FROM emp'.
• Code linje 3: Deklarerer lv_emp_name_tbl som en tabeltype for VARCHAR2(50).
• Code linje 4: Deklarerer lv_emp_name som typen lv_emp_name_tbl.
• Code linje 6: Åbning af markøren.
• Code linje 7: Henter markøren ved hjælp af BULK COLLECT med LIMIT-størrelsen 5000 i variablen lv_emp_name.
• Code linje 8-11: Opsætning af en FOR-løkke til at udskrive alle poster i samlingen lv_emp_name.
• Code linje 12: Brug FORALL til at opdatere lønnen for alle medarbejdere med 5000.
• Code linje 14: At begå transaktion.

Ofte Stillede Spørgsmål