Oracle PL/SQL BULK COLLECT: FORALL Eksempel

โšก Smart opsummering

MASSEAFHENTNING i Oracle PL/SQL henter mange rรฆkker pรฅ รฉn gang ind i en samling, mens FORALL skubber bulk-DML tilbage til databasen. Begge dele fjerner kontekstskift mellem SQL- og PL/SQL-motorerne, hvilket รธger ydeevnen.

  • ๐Ÿ“ฆ MASSEINDSAMLING: Henter flere rรฆkker i en enkelt gennemgang ind i en samlingsvariabel og erstatter langsom hentning rรฆkke for rรฆkke.
  • ๐Ÿ” FOR ALLE: Kรธrer รฉn INSERT, UPDATE eller DELETE pรฅ tvรฆrs af en hel samling med en enkelt kontekstknap.
  • ๐Ÿ“ LIMIT-klausul: Begrรฆnser, hvor mange rรฆkker hver BULK COLLECT henter indlรฆses, hvilket beskytter sessionshukommelsen pรฅ store tabeller.
  • ๐Ÿ“Š Attributter for MASSEINDSAMLING: Attributten %BULK_ROWCOUNT(n) rapporterer, hvor mange rรฆkker den n'te FORALL DML-sรฆtning pรฅvirkede.
  • ๐Ÿ‡ง๐Ÿ‡ท Pรฅkrรฆvede samlinger: INTO-klausulen skal vรฆre mรฅlrettet mod en samlingstype, f.eks. en indlejret tabel eller et associativt array.
  • ๐Ÿค– AI Assistance: AI-assistenter som GitHub Copilot udarbejder BULK COLLECT- og FORALL-blokke og markerer en manglende LIMIT-klausul.

Oracle Oversigt over PL/SQL BULK COLLECT og FORALL med LIMIT-klausul

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.

BULK COLLECT med LIMIT og FORALL eksempel pรฅ opdatering af medarbejderlรธn i Oracle PL / SQL

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

Nej. En BULK COLLECT SELECT returnerer aldrig NO_DATA_FOUND; i stedet returnerer den en tom samling. Test altid samlingen med .COUNT-metoden, fรธr du serping, ellers kan du behandle nul rรฆkker lydlรธst.

SAVE EXCEPTIONS lader FORALL fortsรฆtte med at kรธre, nรฅr individuelle rรฆkker fejler. Fejlbehรฆftede rรฆkker gemmes i SQL%BULK_EXCEPTIONS, og derefter Oracle rejser ORA-24381, som du fanger i en undtagelse behandler til at inspicere hver fejl.

Brug BULK COLLECT, nรฅr en lรธkke lรฆser mange rรฆkker. markรธren FOR-lรธkken henter รฉn rรฆkke pr. switch, sรฅ bulk-hentning plus FORALL kan kรธre mange gange hurtigere pรฅ store resultatsรฆt.

BULK COLLECT returnerer mange rรฆkker pรฅ รฉn gang, sรฅ den krรฆver en container med flere rรฆkker. INTO-mรฅlet skal vรฆre et samling sรฅsom en indlejret tabel, VARRAY eller et associativt array, ikke en enkelt skalar variabel.

Nej. En FORALL-header driver prรฆcis รฉn INSERT-, UPDATE-, DELETE- eller MERGE-klausul. Kun vรฆrdierne i dens VALUES- og WHERE-klausuler kan รฆndres pr. iteration. Brug separate FORALL-sรฆtninger for flere sรฆtninger.

Massebehandling kan vรฆre flere gange til over hundrede gange hurtigere end rรฆkke-for-rรฆkke-kode, fordi BULK COLLECT og FORALL kollapser tusindvis af engine-kontekstskift til et par stykker, hvilket kraftigt reducerer overhead pรฅ store datamรฆngder.

Ja. GitHub Copilot laver kladder til BULK COLLECT-hentninger, FORALL DML-lรธkker og LIMIT-klausuler fra en kommentar og foreslรฅr deklarationer af samlingstyper, selvom du selv bรธr gennemgรฅ batchstรธrrelser og fejlhรฅndtering.

AI-assistenter scanner lรธkker, der henter eller รฆndrer รฉn rรฆkke ad gangen, og anbefaler at omskrive dem med BULK COLLECT, LIMIT og FORALL. Denne maskinlรฆringsgennemgang opdager manglende LIMIT-grรฆnser og flaskehalse i ydeevnen fรธr produktion.

Opsummer dette indlรฆg med: