Beinahe jeder Quellcode enthält sowohl PL/SQL als auch SQL Statements. PL/SQL Statements werden von der PL/SQL engine ausgeführt, SQL Statements von der SQL engine. Folgendes Beispiel:
Angenommen es gäbe 1000 customers mit der account_mgr_id 145, dann würde die PL/SQL engine 1000 Mal zwischen PL/SQL engine und SQL engine hin und her wechseln. Und das kostet bei einer großen Datenmenge Performance, getreu dem Motto „Row by Row = Slow by Slow“.
Eine Möglichkeit, um dies zu verhindern, wäre die Verwendung von Bulk Collect. Anstatt Datensatz für Datensatz zu holen, ist es möglich, ein komplettes Datenset auf einmal zu holen. Hierfür kann man alle drei Collection Types verwenden: Assoziative Arrays, Nested Tables und VARRAYs. Das Beispiel von oben kann wie folgt verbessert werden:
Wir haben nun eine sogenannte nested table deklariert und darauf basierend eine Variable. Danach werden alle Daten auf einmal mittels BULK COLLECT in die Variable v_cust_ids gespeichert. Nun wird im FORALL Statement das angegebene DML Statement abgearbeitet. FORALL ist keine Schleife. Alle entsprechenden Datensätze werden auf einmal bearbeitet, somit entsteht nur ein Wechsel zwischen PL/SQL engine und SQL engine. Jedes FORALL Statement darf nur ein DML Statement enthalten. Müssen n DML Statements abgearbeitet werden, braucht man dementsprechend n FORALL Statements.
Mit dem Attribut SQL%ROWCOUNT kann die Anzahl der abgearbeiteten Datensätze ausgegeben werden.
FORALL und DML Errors
Angenommen, wir wollen 10.000 Datensätze in eine Tabelle speichern. Tritt beim 9.001. Datensatz ein Fehler auf, leitet die SQL engine den Fehler zurück an die PL/SQL engine und diese terminiert das ganze FORALL Statement. Die restlichen Datensätze werden nicht mehr gespeichert.
Mittels SAVE EXCEPTION zwingt man die PL/SQL engine dazu, alle validen Datensätze zu speichern. Somit werden die Fehler ignoriert und das Satement wird zu Ende gebracht. Die PL/SQL engine wirft anschließend einen ORA-24381 Fehler. Mit dem Attribut SQL%BULK_EXCEPTIONS können die fehlerhaften Datensätze ausgelesen und beispielsweise in eine Log-Tabelle gespeichert werden.
Manuel ist Software Developer bei DBConcepts.
