 |
|
Oracle Tips by Burleson |
PL/SQL Tables
As of Oracle9i PL/SQL tables
were officially named ASSOCIATIVE ARRAYS. Most developers call
associative arrays PL/SQL tables because they can not exist in the
database, only in PL/SQL memory structures. The advantage over nested
tables and VARRAYs is that a PL/SQL table does not have to be extended
to add elements, nor does it have to be initialized. Best of all,
elements are added in any order, in any position in the table.
PL/SQL tables before
Oracle9i could only be indexed by BINARY_INTEGER, but from Oracle9i
and beyond they can be indexed either by BINARY_INTEGER or a string
type. You can conceptualize a PL/SQL table as a two-column table, the
first being the index and the second being the data element. Like the
other collection types, the index value is used to locate the data
element.
In the example below, a PL/SQL table is defined
and a cursor is used to load the collection and then read the elements
out of the collection:
SQL> declare
2 type book_tab is table of book.book_title%type
3 index by binary_integer;
4 book_list book_tab;
5 indx number := 1;
6 begin
7 for r_c3 in (select book_title from book) loop
8 book_list(indx) := r_c3.book_title;
9 indx := indx + 5;
10 end loop;
11 for xndx in book_list.first..book_list.last loop
12 if book_list.exists(xndx) then
13 dbms_output.put_line(book_list(xndx));
14 end if;
15 end loop;
16 end;
17 /
windows
success
piano greats
…
cooking light
never eat meat
how to housebreak your horse
There is a lot to see in the example above. In
line 3 the PL/SQL TABLE is defined as being INDEXED BY BINARY_INTEGER
. In line 4 a variable was defined as the collection, but the
collection was not initialized. Next, a cursor is used to load the
current book titles into the collection, and the indexed value was
increased by 5 each time a value was added.
Remember that the elements can be added in any
order, any place in the collection. We see that line 11 initializes a
FOR loop to access each value, and the FOR loop will go to each
element space that is defined in the collection. Since this is a
sparse collection (values can be deleted) element existence must be
tested before accessing an element, and this test is performed on line
12. The example demonstrates that the PL/SQL Table is very similar to
the nested table
except that it does not have to be
initialized, or extended.
Sometimes your data is paired with a string rather
than a number and a data element is more efficiently accessed using
the string. In the PUBS database, the sales table lists order
numbers and the quantity sold. The order numbers are actually
character strings. A PL/SQL TABLE can be created to allow access to
the quantity values using the order_number. Since
order_number is a varchar2 you will create the PL/SQL Table using
the INDEX BY string method.
SQL> declare
2 type orders_tab is table of number
3 index by varchar2(20);
4 orders_list orders_tab;
5 begin
6 for r_c4 in (select order_number,
7 quantity from sales) loop
8 orders_list(r_c4.order_number) := r_c4.quantity;
9 end loop;
10 dbms_output.put_line(orders_list('O102'));
11 dbms_output.put_line(orders_list('O109'));
12 dbms_output.put_line(orders_list('O193'));
13 dbms_output.put_line(orders_list('O156'));
14 end;
15 /
10
1020
5000
100
In the example above the collection is indexed by
a string that will be the order_number. Line 3 defines this
string index. Notice that you can not use a %TYPE in this
definition. Line 8 loads the quantity values in the element
defined by the order_number. The order_number is the
index for the collection. In lines 10 through 13, the elements (quantity)
stored at specified locations (order_number) are printed with
dbms_output.put_line.
As we have noted, PL/SQL collections provide the
developer with powerful tools to hold and process large data sets in
memory, allowing data to be pulled from the database once, and used
repeatedly as needed. If a developer needs to retrieve large numbers
of rows from the database, it can be much more efficient to use a
collection with a cursor and “bulk collect” the
data in one operation.
|
Download your Oracle scripts now:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
|
