Loops, Cursors, and Bulk Operations

Oracle generously provides a list of things developers can do to tune their PL/SQL code. One item from that list is probably the single best tool developers have at their disposal to supercharge their code: bulk processing.

Whenever you need to retrieve and modify more than one row of a database table you have a few options:

• A single SQL statement.

• A cursor FOR loop to iterate through the results one row at a time to insert, update and/or delete records.

• Retrieve and temporarily store all records in a collection (BULK COLLECT), and process the rows

  • one at a time with a FOR loop;

  • in bulk with FORALL.

• Retrieve all records in batches with an explicit cursor and store the results in a collection (BULK COLLECT ... LIMIT), after which the rows are processed

  • one at a time with a FOR loop;

  • in bulk with FORALL.

Even though it is possible to use a single SQL statement to modify data in a table, PL/SQL developers rarely take this approach. One of the primary reasons against such a single statement is that it is an all-or-nothing proposition: if anything goes wrong all modifications are rolled back. With PL/SQL you have more control over what happens when exceptions occur.

The advantage of FORALL is that the statements are sent (in batches) from PL/SQL to the SQL engine in one go, thereby minimizing the number of context switches. A disadvantage is that with FORALL you can only throw one DML statement over the fence to the SQL engine, and the only differences allowed are in the VALUES and WHERE clauses of the modification statement. Beware that when multiple INSERT statements are sent to the SQL statement executor with FORALL, any statement-level triggers will fire only once: either before all statements have been executed or after all statements have completed.

BULK COLLECT does for queries what FORALL does for IUD statements; MERGE is supported from Oracle Database 11g onwards. Since collections are required in BULK COLLECT and FORALL statements, and (pipelined) table functions, where they can greatly improve the runtime performance, we take a moment to go through the fundamentals.