Oracle PL/SQL BULK COLLECT: FORALL Exempel

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.

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