 |
|
Oracle Tips by Burleson |
PL/SQL NESTED Tables
A “nested” table can be thought of as a
single-column table that can either be in memory, or as a column in a
database table. A nested table is very similar
to a VARRAY except that the order of the elements is not static.
Elements can be deleted or added anywhere in the nested table where as
a VARRAY can only add or delete elements from the end of the array.
Because a nested table can contain empty elements, it is know as a
sparse collection. Nested tables can be declared in the PL/SQL block
or in the database. If the nested table will be used in the database,
the type definition must be in the database as shown below.
create or
replace type auth_table as
table of author.author_key%type;
type
number_tab as table of number;
Notice that no boundary is defined for a nested
table. A nested table has no set bounds other than the memory
available to the database user.
As with a VARRAY, the nested
table must also be initialized.
numb_list
number_tab := number_tab();
numb_list2 number_tab := number_tab(23,56,34,890,21);
The first line of the above example initializes an
empty table while the second line initializes the table and loads the
listed numbers into the table. Once initialized, the nested table is
manipulated in the same manner as the VARRAY
SQL> declare
2 type number_tab is table of number;
3 numb_list number_tab := number_tab(23,56,34,890,21);
4 begin
5 for indx in numb_list.first..numb_list.last loop
6 dbms_output.put_line(numb_list(indx));
7 end loop;
8 numb_list.delete(2);
9 numb_list.delete(4);
10 for indx in numb_list.first..numb_list.last loop
11 dbms_output.put_line(numb_list(indx));
12 end loop;
13 end;
14 /
23
56
34
890
21
23
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 11
Using a sparse collection is causing us a few
problems during execution as shown by the “no data found” error
message. In the example above, the first FOR loop demonstrates
retrieving the values in the nested table. In
lines 8 and 9, elements inside the nested table are deleted, as
opposed to deleting them from the end. When the second loop is
attempted, an exception is raised when the code uses the second
value. Remember that the DELETE method
removes the element at that location but leaves the space. With a
sparse collection, the existence of an element at each location must
be checked. Below is the same example, except that the second loop
verifies that the location contains an object.
SQL> declare
2 type number_tab is table of number;
3 numb_list number_tab := number_tab(23,56,34,890,21);
4 begin
5 for indx in numb_list.first..numb_list.last loop
6 dbms_output.put_line(numb_list(indx));
7 end loop;
8 numb_list.delete(2);
9 numb_list.delete(4);
10 for indx in numb_list.first..numb_list.last loop
11 if numb_list.exists(indx) then
12 dbms_output.put_line(numb_list(indx));
13 end if;
14 end loop;
15 end;
16 /
23
56
34
890
21
23
34
21
One reason to implement nested tables in your code
is the ability to load the entire nested table into
the database as column values. This is a powerful feature but if you
only need the collection in your PL/SQL code, you can skip some of the
overhead imposed by nested tables and VARRAYs but using the memory
only collection called PL/SQL tables.
|
Download your Oracle scripts now:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
|
