Oracle PL/SQL BULK COLLECT: FORALL Exempel

⚡ Smart sammanfattning

MASSUPHÄMTNING i Oracle PL/SQL hämtar många rader samtidigt till en samling, medan FORALL skickar tillbaka bulk-DML till databasen. Båda eliminerar kontextväxlingar mellan SQL- och PL/SQL-motorerna, vilket ökar prestandan.

  • 📦 MASSUPHÄMTNING: Hämtar flera rader i ett enda steg till en samlingsvariabel och ersätter långsam hämtning rad-för-rad.
  • 🔁 FÖR ALLA: Kör en INSERT, UPDATE eller DELETE över en hel samling med en enda kontextväxel.
  • 📏 LIMIT-klausul: Begränsar hur många rader varje BULK COLLECT hämtar läser in, vilket skyddar sessionsminnet på stora tabeller.
  • 📊 Attribut för BULK COLLECT: Attributet %BULK_ROWCOUNT(n) rapporterar hur många rader den n:te FORALL DML-satsen påverkade.
  • ⚙️ Obligatoriska samlingar: INTO-klausulen måste rikta in sig på en samlingstyp, till exempel en kapslad tabell eller associativ array.
  • 🤖 AI-hjälp: AI-assistenter som GitHub Copilot utarbetar BULK COLLECT- och FORALL-block och flaggar en saknad LIMIT-klausul.

Oracle Översikt över PL/SQL BULK COLLECT och FORALL med LIMIT-klausul

Vad är BULK COLLECT?

MASSINSAMLING minskar kontextväxlingar mellan SQL och PL/SQL-motorn och låter SQL-motorn hämta posterna samtidigt.

Oracle PL / SQL ger funktionen att hämta posterna i bulk istället för att hämta dem en i taget. Denna BULK COLLECT-sats kan användas i en SELECT-sats för att fylla i posterna i bulk, eller för att hämta en markören i bulk. Eftersom BULK COLLECT hämtar posterna i bulk, bör INTO-klausulen alltid innehålla en variabel av samlingstypen. Den största fördelen med att använda BULK COLLECT är att det ökar prestandan genom att minska interaktionen mellan databasen och PL/SQL-motorn.

Syntax:

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

I syntaxen ovan används BULK COLLECT för att samla in data från SELECT- och FETCH-satserna.

FORALL Klausul

FORALL-satsen utför DML-operationer på data i bulk. Det liknar en FOR-loop-sats, förutom att i en FOR-loop sker åtgärder på postnivå, medan det i FORALL inte finns något LOOP-koncept. Istället bearbetas all data som finns i det givna området samtidigt.

Syntax:

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

<DML operations>;

I ovanstående syntax kommer den givna DML-operationen att utföras för all data som finns mellan det lägre och högre intervallet.

LIMIT-klausul

Konceptet med bulkinsamling laddar all data i målinsamlingsvariabeln som en bulk, dvs. all data kommer att fyllas i insamlingsvariabeln på en gång. Men detta är inte tillrådligt när det totala antalet poster som behöver laddas är mycket stort, eftersom när PL/SQL försöker ladda all data förbrukar det mer sessionsminne. Därför är det alltid bra att begränsa storleken på denna bulkinsamlingsoperation.

Denna storleksgräns kan enkelt uppnås genom att introducera ROWNUM-villkoret i SELECT-satsen, medan detta inte är möjligt för en markör.

För att övervinna detta, Oracle har tillhandahållit LIMIT-klausulen som definierar antalet poster som måste inkluderas i bulkposten.

Syntax:

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

I syntaxen ovan använder `cursor fetch`-satsen `BULK COLLECT`-satsen tillsammans med `LIMIT`-klausulen.

BULK COLLECT-attribut

I likhet med markörattribut har BULK COLLECT %BULK_ROWCOUNT(n) som returnerar antalet rader som påverkas i den n:te DML-satsen i FORALL-satsen, dvs. den ger antalet poster som påverkas i FORALL-satsen för varje enskilt värde från samlingsvariabeln. Termen 'n' anger sekvensen av värdet i samlingen för vilket radantalet behövs.

Exempel 1: I det här exemplet kommer vi att projicera alla anställdas namn från anställningstabellen med hjälp av BULK COLLECT, och vi kommer också att öka lönen för alla anställda med 5000 med hjälp av FORALL.

Skärmdumpen nedan visar detta BULK COLLECT- och FORALL-exempel tillsammans med dess utdata i Oracle.

BULK COLLECT med LIMIT och FORALL exempel på uppdatering av anställdas lö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 Förklaring:

  • Code rad 2: Deklarerar markören guru99_det för uttrycket 'SELECT anställd_namn FROM anställd'.
  • Code rad 3: Deklarerar lv_emp_name_tbl som en tabelltyp för VARCHAR2(50).
  • Code rad 4: Deklarerar lv_emp_name som typen lv_emp_name_tbl.
  • Code rad 6: Öppnar markören.
  • Code rad 7: Hämtar markören med hjälp av BULK COLLECT med LIMIT-storleken 5000 till variabeln lv_emp_name.
  • Code rad 8-11: Konfigurerar en FOR-loop för att skriva ut alla poster i samlingen lv_emp_name.
  • Code rad 12: Använd FORALL för att uppdatera lönen för alla anställda med 5000.
  • Code rad 14: Att begå transaktion.

Vanliga frågor

Nej. En BULK COLLECT SELECT genererar aldrig NO_DATA_FOUND; istället returnerar den en tom samling. Testa alltid samlingen med .COUNT-metoden innan du tittar.ping, annars kan du bearbeta noll rader tyst.

SAVE EXCEPTIONS låter FORALL fortsätta köras även om enskilda rader misslyckas. Misslyckade rader lagras i SQL%BULK_EXCEPTIONS, och sedan Oracle höjer ORA-24381, som du fångar i en undantag hanteraren för att granska varje fel.

Använd BULK COLLECT när en loop läser många rader. markören FOR-loopen hämtar en rad per switch, så bulkhämtning plus FORALL kan köras många gånger snabbare på stora resultatmängder.

BULK COLLECT returnerar många rader samtidigt, så den behöver en container med flera rader. INTO-målet måste vara ett samling såsom en kapslad tabell, VARRAY eller associativ array, inte en enda skalär variabel.

Nej. En FORALL-header kör exakt en INSERT-, UPDATE-, DELETE- eller MERGE-sats. Endast värdena i dess VALUES- och WHERE-klausuler kan ändras per iteration. För flera satser, använd separata FORALL-satser.

Massbearbetning kan vara flera gånger till över hundra gånger snabbare än rad-för-rad-kod, eftersom BULK COLLECT och FORALL komprimerar tusentals motorkontextväxlar till några få, vilket kraftigt minskar kostnaden för stora datavolymer.

Ja. GitHub Copilot utkastar BULK COLLECT-hämtningar, FORALL DML-loopar och LIMIT-klausuler från en kommentar, och föreslår deklarationer av samlingstyper, även om du bör granska batchstorlekar och felhantering själv.

AI-assistenter skannar loopar som hämtar eller ändrar en rad i taget och rekommenderar att de skrivs om med BULK COLLECT, LIMIT och FORALL. Denna maskininlärningsgranskning upptäcker saknade LIMIT-gränser och prestandaflaskhalsar före produktion.

Sammanfatta detta inlägg med: