 |
|
Oracle Tips by Burleson |
The
PL/SQL Bulk Load Operator
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.
SQL> create
table big_one as
2 select object_name, object_id from dba_objects
3 where 2 = 1;
Table
created.
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.
SQL> set
timing on
SQL> declare
2 type name_tab is table of
dba_objects.object_name%type;
3 type id_tab is table of
dba_objects.object_id%type;
4 t_name name_tab;
5 t_id id_tab;
6 cursor c1 is select object_name, object_id
7 from dba_objects;
8 begin
9 open c1;
10 fetch c1 bulk collect into t_name, t_id;
11 close c1;
12 -- bulk insert
13 forall indx in t_name.first..t_name.last
14 insert into big_one values
15 ( t_name(indx), t_id(indx));
16 end;
17 /
Elapsed:
00:00:00.00
SQL> select count(*) from big_one;
COUNT (*)
----------
12062
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
.
SQL> create
table bigger_one as
2 select book_key, quantity, object_id
3 from dba_objects cross join sales;
Table created.
SQL> select
count(*) from bigger_one;
COUNT (*)
----------
1206300
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.
SQL> declare
2 type book_tab is table of
sales.book_key%type;
3
4 t_book_add book_tab :=
book_tab('B103','B112','B114');
5 t_book_del book_tab :=
book_tab('B116','B106','B109');
6 begin
7 forall indx in
t_book_add.first..t_book_add.last
8 update bigger_one
set quantity = quantity + 10
9 where book_key = t_book_add(indx);
10 forall indx in
t_book_del.first..t_book_del.last
11 delete from bigger_one
12 where book_key = t_book_del(indx);
13 end;
14 /
Elapsed:
00:01:46.06
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.
|
Download your Oracle scripts now:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
|
