Oracle PL/SQL BULK COLLECT: FORALL Beispiel

Was ist BULK COLLECT?

BULK COLLECT reduziert Kontextwechsel zwischen SQL und PL/SQL-Engine und ermöglicht es der SQL-Engine, die Datensätze auf einmal abzurufen.

Oracle PL/SQL bietet die Funktionalität, die Datensätze in großen Mengen abzurufen, anstatt sie einzeln abzurufen. Dieses BULK COLLECT kann in der SELECT-Anweisung verwendet werden, um die Datensätze in großen Mengen aufzufüllen oder den Cursor in großen Mengen abzurufen. Da BULK COLLECT den Datensatz in BULK abruft, sollte die INTO-Klausel immer eine Variable vom Typ Sammlung enthalten. Der Hauptvorteil der Verwendung von BULK COLLECT besteht darin, dass die Leistung erhöht wird, indem die Interaktion zwischen Datenbank und PL/SQL-Engine reduziert wird.

Syntax:

SELECT <columnl> BULK COLLECT INTO bulk_varaible FROM <table name>;
FETCH <cursor_name> BULK COLLECT INTO <bulk_varaible >;

In der obigen Syntax wird BULK COLLECT zum Sammeln der Daten aus den Anweisungen „SELECT“ und „FETCH“ verwendet.

FORALL-Klausel

Mit FORALL können Sie DML-Operationen auf Daten in großen Mengen ausführen. Es ähnelt der FOR-Schleifenanweisung, außer in FOR-Schleife Dinge passieren auf der Datensatzebene, während es in FORALL kein LOOP-Konzept gibt. Stattdessen werden alle im angegebenen Bereich vorhandenen Daten gleichzeitig verarbeitet.

Syntax:

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

<DML operations>;

In der obigen Syntax wird die angegebene DML-Operation für alle Daten ausgeführt, die zwischen dem unteren und oberen Bereich liegen.

LIMIT-Klausel

Das Bulk-Collect-Konzept lädt die gesamten Daten in großen Mengen in die Ziel-Collection-Variable, d. h. die gesamten Daten werden auf einmal in die Collection-Variable eingetragen. Dies ist jedoch nicht ratsam, wenn der Gesamtdatensatz, der geladen werden muss, sehr groß ist, denn wann PL / SQL versucht, die gesamten Daten zu laden, verbraucht es mehr Sitzungsspeicher. Daher ist es immer gut, die Größe dieses Massenerfassungsvorgangs zu begrenzen.

Diese Größenbeschränkung kann jedoch leicht durch die Einführung der ROWNUM-Bedingung in der SELECT-Anweisung erreicht werden, während dies im Fall des Cursors nicht möglich ist.

Um dies zu überwinden Oracle hat die Klausel „LIMIT“ bereitgestellt, die die Anzahl der Datensätze definiert, die in die Masse aufgenommen werden müssen.

Syntax:

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

In der obigen Syntax verwendet die Cursor-Abrufanweisung die BULK COLLECT-Anweisung zusammen mit der LIMIT-Klausel.

BULK COLLECT-Attribute

Ähnlich Cursor Attribute BULK COLLECT haben %BULK_ROWCOUNT(n), das die Anzahl der betroffenen Zeilen im n zurückgibt^th DML-Anweisung der FORALL-Anweisung, dh sie gibt die Anzahl der betroffenen Datensätze in der FORALL-Anweisung für jeden einzelnen Wert aus der Sammlungsvariablen an. Der Begriff „n“ gibt die Wertefolge in der Sammlung an, für die die Zeilenanzahl benötigt wird.

Beispiel 1: In diesem Beispiel projizieren wir mit BULK COLLECT alle Mitarbeiternamen aus der emp-Tabelle und erhöhen mit FORALL auch das Gehalt aller Mitarbeiter um 5000.

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;
/

Ausgang

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

Code-Erklärung:

• Codezeile 2: Deklarieren des Cursors guru99_det für die Anweisung „SELECT emp_name FROM emp“.
• Codezeile 3: Deklarieren von lv_emp_name_tbl als Tabellentyp von VARCHAR2(50)
• Codezeile 4: lv_emp_name wird als lv_emp_name_tbl-Typ deklariert.
• Codezeile 6: Öffnen des Cursors.
• Codezeile 7: Abrufen des Cursors mit BULK COLLECT mit der LIMIT-Größe als 5000 intl lv_emp_name-Variable.
• Codezeile 8-11: Einrichten einer FOR-Schleife zum Drucken aller Datensätze in der Sammlung lv_emp_name.
• Codezeile 12: Mit FORALL wird das Gehalt aller Mitarbeiter um 5000 aktualisiert.
• Codezeile 14: Bestätigen der Transaktion.