Oracle PL/SQL BULK COLLECT: FORALL Exempel

Vad är BULK COLLECT?

BULK COLLECT minskar kontextväxlingarna mellan SQL och PL/SQL-motorn och tillåter SQL-motorn att hämta posterna på en gång.

Oracle PL/SQL tillhandahåller funktionen att hämta posterna i bulk istället för att hämta en efter en. Denna BULK COLLECT kan användas i 'SELECT'-satsen för att fylla i posterna i bulk eller för att hämta markören i bulk. Eftersom BULK COLLECT hämtar posten i BULK, bör INTO-satsen alltid innehålla en samlingsvariabel. 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 <columnl> BULK COLLECT INTO bulk_varaible FROM <table name>;
FETCH <cursor_name> BULK COLLECT INTO <bulk_varaible >;

I ovanstående syntax används BULK COLLECT för att samla in data från "SELECT" och "FETCH"-satsen.

FORALL Klausul

FORALL gör det möjligt att utföra DML-operationer på data i bulk. Det liknar det för FOR loop-satsen förutom i FÖR slinga saker händer på rekordnivå medan det i FORALL inte finns något LOOP-koncept. Istället bearbetas hela data som finns i det givna intervallet samtidigt.

Syntax:

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

<DML operations>;

I ovanstående syntax kommer den givna DML-operationen att exekveras för hela data som finns mellan lägre och högre intervall.

LIMIT-klausul

Massinsamlingskonceptet läser in hela data i målinsamlingsvariabeln som en bulk, dvs hela data kommer att fyllas i insamlingsvariabeln på en gång. Men detta är inte tillrådligt när den totala posten som behöver laddas är mycket stor, för när PL / SQL försöker ladda hela data det förbrukar mer sessionsminne. Därför är det alltid bra att begränsa storleken på denna bulkuppsamlingsoperation.

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

För att övervinna detta Oracle har tillhandahållit 'LIMIT'-klausul som definierar antalet poster som måste inkluderas i bulken.

Syntax:

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

I ovanstående syntax använder cursor fetch-satsen BULK COLLECT-satsen tillsammans med LIMIT-satsen.

BULK COLLECT-attribut

Liknar markören attributen BULK COLLECT har %BULK_ROWCOUNT(n) som returnerar antalet påverkade rader i n^th DML-satsen för FORALL-satsen, dvs den kommer att ge antalet poster som påverkas i FORALL-satsen för varje enskilt värde från samlingsvariabeln. Termen 'n' indikerar värdesekvensen i samlingen, för vilken radräkningen behövs.

Exempelvis 1: I det här exemplet kommer vi att projicera alla anställdas namn från emp-tabellen med BULK COLLECT och vi kommer också att öka lönen för alla anställda med 5000 med FORALL.

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 salaiy=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

Kodförklaring:

• Kodrad 2: Deklarerar markören guru99_det för satsen 'SELECT emp_name FROM emp'.
• Kodrad 3: Deklarerar lv_emp_name_tbl som tabelltyp för VARCHAR2(50)
• Kodrad 4: Deklarerar lv_emp_name som lv_emp_name_tbl-typ.
• Kodrad 6: Öppnar markören.
• Kodrad 7: Hämta markören med BULK COLLECT med LIMIT-storleken som 5000 intl lv_emp_name-variabel.
• Kodrad 8-11: Ställa in FOR-loop för att skriva ut alla poster i samlingen lv_emp_name.
• Kodrad 12: Använder FORALL för att uppdatera lönen för alla anställda med 5000.
• Kodrad 14: Begå transaktionen.