For All

For the sake of completeness, I'm going to mention the FORALL statement here. It's kind of like the inverse of the BULK COLLECT statement. Given that you have a populated collection, like an associative array, you can bulk execute the same SQL statement for every entry or for selected entries in your collection(s).

For example, if you have 26 entries in your PL/SQL table, you can write a FORALL statement that will execute the same SQL statement 26 times, once for each row in your PL/SQL table. The assumption here is that you'll use the values from your PL/SQL table in each SQL statement.

The problem I have with FORALL is that the data for a collection usually comes from a table in the database in the first place. If that's the case, then a complex SQL statement can do everything a FORALL statement can do, with one context switch, just like FORALL, but using less memory. So why does FORALL exist? Frankly, I don't know. Perhaps it's syntactic sugar for PL/SQL programmers that have weak SQL skills. Or maybe I've just never run across a need for it. That's always possible. However, in practice, I never use it. I always seem to find a better solution using a complex SQL statement.