For an associative array with VARCHAR2 key values, the lowest and highest key values are returned. By default, the order is based on the binary values of the characters in the string. If the NLS_COMP initialization parameter is set to ANSI, the order is based on the locale-specific sort order specified by the NLS_SORT initialization parameter.
If the collection is empty, FIRST and LAST return NULL.
If the collection contains only one element, FIRST and LAST return the same index value.
The following example shows how to use FIRST and LAST to iterate through the elements in a collection that has consecutive subscripts:
DECLARE
TYPE NumList IS TABLE OF NUMBER;
n NumList := NumList(1,3,5,7);
counter INTEGER;
BEGIN
dbms_output.put_line('N''s first subscript is ' || n.FIRST);
dbms_output.put_line('N''s last subscript is ' || n.LAST);
-- When the subscripts are consecutive starting at 1, it's simple to loop through
them.
FOR i IN n.FIRST .. n.LAST
LOOP
dbms_output.put_line('Element #' || i || ' = ' || n(i));
END LOOP;
n.DELETE(2); -- Delete second element.
-- When the subscripts have gaps or the collection might be uninitialized,
-- the loop logic is more extensive. We start at the first element, and
-- keep looking for the next element until there are no more.
IF n IS NOT NULL THEN
counter := n.FIRST;
WHILE counter IS NOT NULL
LOOP
dbms_output.put_line('Element #' || counter || ' = ' || n(counter));
counter := n.NEXT(counter);
END LOOP;
ELSE
dbms_output.put_line('N is null, nothing to do.');
END IF;
END;
/
For varrays, FIRST always returns 1 and LAST always equals COUNT.
For nested tables, normally FIRST returns 1 and LAST equals COUNT. But if you delete elements from the beginning of a nested table, FIRST returns a number larger than 1.
If you delete elements from the middle of a nested table, LAST becomes larger than COUNT.
When scanning elements, FIRST and LAST ignore deleted elements.
Looping Through Collection Elements (PRIOR and NEXT Methods)
PRIOR(n) returns the index number that precedes index n in a collection. NEXT(n) returns the index number that succeeds index n. If n has no predecessor, PRIOR(n) returns NULL. If n has no successor, NEXT(n) returns NULL.
For associative arrays with VARCHAR2 keys, these methods return the appropriate key value; ordering is based on the binary values of the characters in the string, unless the NLS_COMP initialization parameter is set to ANSI, in which case the ordering is based on the locale-specific sort order specified by the NLS_SORT initialization parameter.
These methods are more reliable than looping through a fixed set of subscript values, because elements might be inserted or deleted from the collection during the loop.
This is especially true for associative arrays, where the subscripts might not be in consecutive order and so the sequence of subscripts might be (1,2,4,8,16) or ('A','E','I','O','U').
DECLARE
TYPE NumList IS TABLE OF NUMBER;
n NumList := NumList(1966,1971,1984,1989,1999);
BEGIN
dbms_output.put_line('The element after #2 is #' || n.NEXT(2));
dbms_output.put_line('The element before #2 is #' || n.PRIOR(2));
n.DELETE(3); -- Delete an element to show how NEXT can handle gaps.
dbms_output.put_line('Now the element after #2 is #' || n.NEXT(2));
IF n.PRIOR(n.FIRST) IS NULL THEN
dbms_output.put_line('Can''t get PRIOR of the first element or NEXT of the
last.');
END IF;
END;
/
o/p:
The element after #2 is #3
The element before #2 is #1
Now the element after #2 is #4
Can't get PRIOR of the first element or NEXT of the last.
PL/SQL procedure successfully completed.
You can use PRIOR or NEXT to traverse collections indexed by any series of subscripts. The following example uses NEXT to traverse a nested table from which some elements have been deleted:
DECLARE
TYPE NumList IS TABLE OF NUMBER;
n NumList := NumList(1,3,5,7);
counter INTEGER;
BEGIN
n.DELETE(2); -- Delete second element.
-- When the subscripts have gaps, the loop logic is more extensive. We start at
the
-- first element, and keep looking for the next element until there are no more.
counter := n.FIRST;
WHILE counter IS NOT NULL
LOOP
dbms_output.put_line('Counting up: Element #' || counter || ' = ' ||
n(counter));
counter := n.NEXT(counter);
END LOOP;
-- Run the same loop in reverse order.
counter := n.LAST;
WHILE counter IS NOT NULL
LOOP
dbms_output.put_line('Counting down: Element #' || counter || ' = ' ||
n(counter));
counter := n.PRIOR(counter);
END LOOP;
END;
/
When traversing elements, PRIOR and NEXT skip over deleted elements.
Increasing the Size of a Collection (EXTEND Method)
To increase the size of a nested table or varray, use EXTEND.
You cannot use EXTEND with index-by tables.
This procedure has three forms:
■ EXTEND appends one null element to a collection.
■ EXTEND(n) appends n null elements to a collection.
■ EXTEND(n,i) appends n copies of the ith element to a collection.
You cannot use EXTEND to add elements to an uninitialized.
If you impose the NOT NULL constraint on a TABLE or VARRAY type, you cannot apply the first two forms of EXTEND to collections of that type.
EXTEND operates on the internal size of a collection, which includes any deleted elements. If EXTEND encounters deleted elements, it includes them in its tally. PL/SQL keeps placeholders for deleted elements so that you can re-create them by assigning new values.
DECLARE
TYPE NumList IS TABLE OF INTEGER;
n NumList := NumList(2,4,6,8);
x NumList := NumList(1,3);
PROCEDURE print_numlist(the_list NumList) IS
output VARCHAR2(128);
BEGIN
FOR i IN the_list.FIRST .. the_list.LAST
LOOP
output := output || NVL(TO_CHAR(the_list(i)),'NULL') || ' ';
END LOOP;
dbms_output.put_line(output);
END;
BEGIN
dbms_output.put_line('At first, N has ' || n.COUNT || ' elements.');
n.EXTEND(5); -- Add 5 elements at the end.
dbms_output.put_line('Now N has ' || n.COUNT || ' elements.');
-- Elements 5, 6, 7, 8, and 9 are all NULL.
print_numlist(n);
dbms_output.put_line('At first, X has ' || x.COUNT || ' elements.');
x.EXTEND(4,2); -- Add 4 elements at the end.
dbms_output.put_line('Now X has ' || x.COUNT || ' elements.');
-- Elements 3, 4, 5, and 6 are copies of element #2.
print_numlist(x);
END;
/
When it includes deleted elements, the internal size of a nested table differs from the values returned by COUNT and LAST. For instance, if you initialize a nested table with five elements, then delete elements 2 and 5, the internal size is 5, COUNT returns 3, and LAST returns 4. All deleted elements, regardless of position, are treated alike.
Decreasing the Size of a Collection (TRIM Method)
This procedure has two forms:
■ TRIM removes one element from the end of a collection.
■ TRIM(n) removes n elements from the end of a collection.
DECLARE
TYPE NumList IS TABLE OF NUMBER;
n NumList := NumList(1,2,3,5,7,11);
PROCEDURE print_numlist(the_list NumList) IS
output VARCHAR2(128);
BEGIN
IF n.COUNT = 0 THEN
dbms_output.put_line('No elements in collection.');
ELSE
FOR i IN the_list.FIRST .. the_list.LAST
LOOP
output := output || NVL(TO_CHAR(the_list(i)),'NULL') || ' ';
END LOOP;
dbms_output.put_line(output);
END IF;
END;
BEGIN
print_numlist(n);
n.TRIM(2); -- Remove last 2 elements.
print_numlist(n);
n.TRIM; -- Remove last element.
print_numlist(n);
n.TRIM(n.COUNT); -- Remove all remaining elements.
print_numlist(n);
-- If too many elements are specified, TRIM raises the exception SUBSCRIPT_BEYOND_
COUNT.
BEGIN
n := NumList(1,2,3);
n.TRIM(100);
EXCEPTION
WHEN SUBSCRIPT_BEYOND_COUNT THEN
dbms_output.put_line('I guess there weren''t 100 elements that could be trimmed.');
END;
-- When elements are removed by DELETE, placeholders are left behind. TRIM counts
these
-- placeholders as it removes elements from the end.
n := NumList(1,2,3,4);
n.DELETE(3); -- delete element 3
-- At this point, n contains elements (1,2,4).
-- TRIMming the last 2 elements removes the 4 and the placeholder, not 4 and 2.
n.TRIM(2);
print_numlist(n);
END;
/
END;
/
If n is too large, TRIM(n) raises SUBSCRIPT_BEYOND_COUNT.
TRIM operates on the internal size of a collection. If TRIM encounters deleted elements, it includes them in its tally. Consider the following example:
DECLARE
TYPE CourseList IS TABLE OF VARCHAR2(10);
courses CourseList;
BEGIN
courses := CourseList('Biol 4412', 'Psyc 3112', 'Anth 3001');
courses.DELETE(courses.LAST); -- delete element 3
/* At this point, COUNT equals 2, the number of valid
elements remaining. So, you might expect the next
statement to empty the nested table by trimming
elements 1 and 2. Instead, it trims valid element 2
and deleted element 3 because TRIM includes deleted
elements in its tally. */
courses.TRIM(courses.COUNT);
dbms_output.put_line(courses(1)); -- prints 'Biol 4412'
END;
/
In general, do not depend on the interaction between TRIM and DELETE. It is better to treat nested tables like fixed-size arrays and use only DELETE, or to treat them like stacks and use only TRIM and EXTEND.
Because PL/SQL does not keep placeholders for trimmed elements, you cannot replace a trimmed element simply by assigning it a new value.
Deleting Collection Elements (DELETE Method)
This procedure has various forms:
■ DELETE removes all elements from a collection.
■ 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.
■ 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.
For example:
DECLARE
TYPE NumList IS TABLE OF NUMBER;
n NumList := NumList(10,20,30,40,50,60,70,80,90,100);
TYPE NickList IS TABLE OF VARCHAR2(64) INDEX BY VARCHAR2(32);
nicknames NickList;
BEGIN
n.DELETE(2); -- deletes element 2
n.DELETE(3,6); -- deletes elements 3 through 6
n.DELETE(7,7); -- deletes element 7
n.DELETE(6,3); -- does nothing since 6 > 3
n.DELETE; -- deletes all elements
nicknames('Bob') := 'Robert';
nicknames('Buffy') := 'Esmerelda';
nicknames('Chip') := 'Charles';
nicknames('Dan') := 'Daniel';
nicknames('Fluffy') := 'Ernestina';
nicknames('Rob') := 'Robert';
nicknames.DELETE('Chip'); -- deletes element denoted by this key
nicknames.DELETE('Buffy','Fluffy'); -- deletes elements with keys in this
alphabetic range
END;
/
Varrays always have consecutive subscripts, so you cannot delete individual elements except from the end (by using the TRIM method).
If an element to be deleted does not exist, DELETE simply skips it; no exception is raised. PL/SQL keeps placeholders for deleted elements, so you can replace a deleted element by assigning it a new value.
DELETE lets you maintain sparse nested tables. You can store sparse nested tables in the database, just like any other nested tables.
The amount of memory allocated to a nested table can increase or decrease dynamically. As you delete elements, memory is freed page by page. If you delete the entire table, all the memory is freed.
