 |
|
Oracle Tips by Burleson |
Using PL/SQL Arrays and Bulk
Collects
Description
Your task is to take a sub-optimal PL/SQL code
routine and re-write to using the bulk collect and FORALL features. As
we emphasized, using BULK COLLECT and FORALL can make PL/SQL run
faster, often by an order of magnitude.
Procedure
In our example, we create a table called
title_caps, which will store the uppercase title for all titles in the
book table. Run this definition on your system:
drop table
title_caps;
create table title_caps (uppercase_title varchar2(80));
The PL/SQL program below is a simple routine
that:
1 – Defines a PL/SQL table (an array) to hold
the titles
DECLARE
type t_name is table of book.book_title%type index by
binary_integer;
title_rec t_name;
i binary_integer := 1;
2 – Declares a cursor, and fetches the rows
into the array
DECLARE
cursor c1 is select book_title from book;
BEGIN
open c1;
loop
--*****************************************
-- Load the PL/SQL
table, one row at a time
--*****************************************
fetch c1 into title_rec(i);
exit when c1%NOTFOUND;
i := i + 1;
end loop;
3 – Inserts into the new table from the array,
one row at a time
for i in
1..title_rec.COUNT
loop
--****************************************************
-- Add rows to the new title_caps table, one at a time
--****************************************************
insert into title_caps values (upper(title_rec(i)));
end loop;
Here is the whole code snippet. Your
challenge is to rewrite this code using the BULK COLLECT and FORALL
operators. Copy this code to your server as slow_collect.sql in your
c:\Burleson directory
delete from
title_caps;
DECLARE
type t_name is table of book.book_title%type index by
binary_integer;
title_rec t_name;
i binary_integer := 1;
cursor c1 is select
book_title from book;
BEGIN
open c1;
loop
--*****************************************
-- Load the PL/SQL table, one row at a time
--*****************************************
fetch c1 into
title_rec(i);
exit when c1%NOTFOUND;
i := i + 1;
end loop;
close c1;
dbms_output.Put_line('Old Book Titles:');
for i in
1..title_rec.COUNT
loop
--****************************************************
-- Add rows to the new title_caps table, one at a time
--****************************************************
insert into title_caps values (upper(title_rec(i)));
end loop;
end;
/
select *
from title_caps;
Your new code will be called bulk_collect.sql,
and it will have the following features:
* Write a BULK COLLECT statement to load your
PL/SQL table in a single operation.
* Use a FORALL operator to insert the rows
into the title_caps table in a single operation.
Hint: If you have any problems, use Google to
locate PL/SQL syntax. For example, a Google search of PL/SQL FORALL
will instantly give you the forall syntax.
Once completed, e-mail your bulk_collect.sql
code to your instructor.
ANSWER:
drop table
new_title;
create table new_title (book_title varchar2(80));
DECLARE
type t_name is table of book.book_title%type index by
binary_integer;
title_rec t_name;
i binary_integer := 1;
BEGIN
select
book_title
BULK COLLECT
into title_rec
from
book;
FORALL i in
title_rec.first..title_rec.last
insert into new_title
values (upper(title_rec(i)));
end;
/
select * from new_title;
For more details, see the "Easy
Oracle Series" a set of books especially designed by Oracle
experts to get you started fast with Oracle database technology.
|
