While bulk collect retrieves all
the requested data from the database with two context switches, the
bulk load will bulk manipulate the data in the same manner. The term
bulk load does not really describe this capability; a better name
would be bulk change. You can bulk update, delete, or insert. Bulk
loading uses the FORALL clause to tell the
SQL engine to execute the command on all rows meeting the
specification. Although the FORALL clause looks like is FOR loop, it
is different, primarily because there is no LOOP or END LOOP in the
statement.

The example below selects over 12,000 object names
and object ids from the database and then insert them into a table
named big_one. First create the table to hold the data.

Now, bulk collect all the
object names and ID numbers from the dba_objects
view and then bulk load then into the big_one table. If you
are not logged on as a user with DBA privilages the dba_objects
view will not be available. In this case use the user_objects
view, which contains fewer objects.

Wow, this process read and inserted over 12,000
rows in so little time that it did not even register on the timer.
Notice that line 13 through 15 is actually one line of code. The
FORALL clause passes the nested table collections to
the SQL engine, which loads the data into the tables and then returns
to the PL/SQL engine. These changes have not yet been committed and
can still be rolled back if necessary.

The FORALL can also delete or update rows. Here
we create a very large table by joining the dba_objects
view and the sales table using a Cartesian product
.

The bigger_one table has over a million
rows. We want to increase the quantity value by 10 for all
rows with book_key equals B103, B112, and B114. Finally we
want to delete all the rows where the book_key equals B116,
B106, and B109.

Even using bulk loads, the PL/SQL block took
almost 2 minutes to complete. Lines 4 and 5 defined the collection
variables and instantiated the nested tables. Lines 7 through 12
performed the actual work with two bulk loads. Do not forget that the
changes are not permanent until a commit is issued.

A number of the examples provided so far have used
included functions. Local functions are defined in the DECLARE
section of the block. These functions were required because they used
some defined variable or type from the outer block. There is a better
way to share data and definitions within an application and that is by
using PACKAGES.