Bulk processing is the term to describe BULK COLLECT and FORALL. The big idea is that there is a PL/SQL engine and a SQL engine in the database. Switching between the two costs you speed due to overhead. So it would be best to make one handoff from the PL/SQL engine to the SQL one.
BULK COLLECT will cause a query to make a single switch into the SQL engine and fill up your colection. FORALL works the other way around, allowing you to do one DML operation for all the items in your collection with just one context switch into the SQL engine.
There is a warning though. Don't do unbounded BULK COLLECT operations. That will eat up a lot of memory in the PGA (Program Global Area). Collections are stored in memory there. You don't want a huge one taking up too much memory. Use the LIMIT statement to cap how many records are grabbed at once in a BULK COLLECT. Experience says you should use a LIMIT of at least 25. It would be preferable to use a LIMIT of around 100. Oracle uses 100 as the default optimization for cursor loops in Oracle 10g and above.
After you do the BULK COLLECT, if there are records, they will start with index 1 in your collection. The rest of the records will be densely populated in the collection. If there are no records, the collection will be empty. The COUNT on your cursor will be zero. There will not be a NO_DATA_FOUND exception raised in this scenario. Similarly you cannot count on %NOTFOUND. Use the COUNT.
Here is a tip for the FORALL. It is not a loop. You don't need a loop command. By default the collection you are doing DML on must be dense. If not, you must use the IN INDICES OF with the FORALL. That only works with Oracle 10g and above. Normally you would not want exceptions in the middle of your FORALL to stop the flow. To prevent that, use the SAVE EXCEPTIONS clause. Then errors will be stored up in the SQL%BULK_EXCEPTIONS pseudo collection.
