Nested table

In PL SQL exists an efficient and adaptable collection of data: Nested table.
Nested table is similar to one dimensional array but with some differences :
An array has declare number of element – Nested table not. The size of nested table can increase using extend method.
An array has always consecutive elements – Nested table has consecutive elements initially but it can become sparse when elements are deleted.
To create a nested table you have to use this syntax :
type type_name is table of element_type[size element];
table_name type_name;
Here below an example to creation and use of a nested table.
declare
type disney_table is table of varchar2(30);
disney:= disney_table (null,null,null);
begin
disney(1):=’goofy’;
disney(2):=’donald duck’;
disney(3):= ‘mickey mouse;
end;
Explanation :
This code create a nested table of varchar2 using instruction type ….
Next instruction show how initialize it
finally there are three assignament instructions about nested table.
It’s also possible to extend the nested table using extend instruction. Follow an example :
declare
type disney_table is table of varchar2(30);
begin
extend disney;
disney(1):=’goofy’;
extend disney;
disney(2):=’donald duck’;
extend disney;
disney(3):= ‘mickey mouse’;
end;
or also :
declare
type disney_table is table of varchar2(30);
disney:= disney_table (null,null,null);
begin
disney(1):=’goofy’;
disney(2):=’donald duck’;
disney(3):= ‘mickey mouse’;
extend disney;
disney(4):=’pluto’;
end;
Explanation :
This code is the same of first but has an instruction extend in addition. This instruction increase the elements number of our nested table.
Besides extend Pl Sql provides a lot of methods to manage a Collection of data ( Nested table, varray, etc). Here I show the list of this methods:
1 EXISTS(n)
Returns TRUE if the nth element in a collection exists; otherwise returns FALSE.
2 COUNT
Returns the number of elements that a collection currently contains.
3 LIMIT
Checks the Maximum Size of a Collection.
4 FIRST
Returns the first (smallest) index numbers in a collection that uses integer subscripts.
5 LAST
Returns the last (largest) index numbers in a collection that uses integer subscripts.
6 PRIOR(n)
Returns the index number that precedes index n in a collection.
7 NEXT(n)
Returns the index number that succeeds index n.
8 EXTEND
Appends one null element to a collection.
9 EXTEND(n)
Appends n null elements to a collection.
10 EXTEND(n,i)
Appends n copies of the ith element to a collection.
11 TRIM
Removes one element from the end of a collection.
12 TRIM(n)
Removes n elements from the end of a collection.
13 DELETE
Removes all elements from a collection, setting COUNT to 0.
14 DELETE(n)
Removes the nth element from an associative array with a numeric key or a nested table. If the associative array has a string key, the element corresponding to the key value is deleted. If n is null, DELETE(n) does nothing.
15 DELETE(m,n)
Removes all elements in the range m..n from an associative array or nested table. If m is larger than n or if m or n is null, DELETE(m,n) does nothing.