Oracle PL/SQL BULK COLLECT: FORALL Beispiel

Was ist BULK COLLECT?

BULK COLLECT reduziert Kontextwechsel zwischen den 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, Datensätze in großen Mengen anstatt einzeln abzurufen. Diese BULK COLLECT-Anweisung kann in einer SELECT-Anweisung verwendet werden, um Datensätze in großen Mengen abzurufen oder um eine Cursor Da BULK COLLECT die Datensätze gesammelt abruft, muss die INTO-Klausel immer eine Variable vom Typ „Collection“ enthalten. Der Hauptvorteil von BULK COLLECT besteht in der Leistungssteigerung durch die Reduzierung der Interaktion zwischen Datenbank und PL/SQL-Engine.

Syntax:

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

In der obigen Syntax wird BULK COLLECT verwendet, um die Daten aus den SELECT- und FETCH-Anweisungen zu sammeln.

FORALL-Klausel

Die FORALL-Anweisung führt Folgendes aus DML-Operationen Bei großen Datenmengen. Es ähnelt einer FOR-Schleife, mit dem Unterschied, dass Aktionen in einer FOR-Schleife auf Datensatzebene ausgeführt werden, während FORALL kein Schleifenkonzept kennt. Stattdessen werden alle Daten im angegebenen Bereich 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 sich zwischen dem unteren und dem oberen Bereich befinden.

LIMIT-Klausel

Das Konzept der Massensammlung lädt alle Daten als Paket in die Ziel-Sammlungsvariable, d. h. alle Daten werden in einem einzigen Schritt in die Sammlungsvariable eingefügt. Dies ist jedoch nicht empfehlenswert, wenn die Gesamtzahl der zu ladenden Datensätze sehr groß ist, da PL/SQL beim Laden der gesamten Daten mehr Sitzungsspeicher belegt. Daher ist es ratsam, die Größe dieser Massensammlungsoperation zu begrenzen.

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

Um dies zu überwinden, Oracle hat die LIMIT-Klausel bereitgestellt, die die Anzahl der Datensätze definiert, die in den Massenversand einbezogen werden müssen.

Syntax:

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

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

BULK COLLECT-Attribute

Ähnlich wie Cursorattribute verfügt BULK COLLECT über %BULK_ROWCOUNT(n), das die Anzahl der Zeilen zurückgibt, die in der n-ten DML-Anweisung der FORALL-Anweisung betroffen sind. Es gibt also die Anzahl der Datensätze an, die in der FORALL-Anweisung für jeden einzelnen Wert der Sammlungsvariablen betroffen sind. Der Parameter 'n' gibt die Position des Wertes in der Sammlung an, für den die Zeilenanzahl benötigt wird.

Beispiel 1: In diesem Beispiel werden wir alle Mitarbeiternamen aus der Tabelle „emp“ mit BULK COLLECT projizieren und außerdem das Gehalt aller Mitarbeiter mit FORALL um 5000 erhöhen.

Der folgende Screenshot zeigt dieses BULK COLLECT- und FORALL-Beispiel zusammen mit seiner Ausgabe. 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;
/

Ausgang

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

Code Erläuterung:

• Code Zeile 2: Deklaration des Cursors guru99_det für die Anweisung 'SELECT emp_name FROM emp'.
• Code Zeile 3: Deklaration von lv_emp_name_tbl als Tabellentyp VARCHAR2(50).
• Code Zeile 4: Deklaration von lv_emp_name als lv_emp_name_tbl-Typ.
• Code Zeile 6: Öffnen des Cursors.
• Code Zeile 7: Der Cursor wird mit BULK COLLECT und der LIMIT-Größe von 5000 in die Variable lv_emp_name eingelesen.
• Code Zeile 8-11: Einrichten einer FOR-Schleife zum Ausgeben aller Datensätze in der Sammlung lv_emp_name.
• Code Zeile 12: Mit FORALL das Gehalt aller Mitarbeiter um 5000 erhöhen.
• Code Zeile 14: Begehen der Transaktion.

Häufig gestellte Fragen