Counting the Elements in a Collection (COUNT Method)COUNT returns the number of elements that a collection currently contains:
DECLARE TYPE NumList IS TABLE OF NUMBER; n NumList := NumList(2,4,6,8); -- Collection starts with 4 elements.BEGIN dbms_output.put_line('There are ' || n.COUNT || ' elements in N.'); n.EXTEND(3); -- Add 3 new elements at the end. dbms_output.put_line('Now there are ' || n.COUNT || ' elements in N.'); n := NumList(86,99); -- Assign a completely new value with 2 elements. dbms_output.put_line('Now there are ' || n.COUNT || ' elements in N.'); n.TRIM(2); -- Remove the last 2 elements, leaving none. dbms_output.put_line('Now there are ' || n.COUNT || ' elements in N.');END;/COUNT is useful because the current size of a collection is not always known. For example, you can fetch a column of Oracle data into a nested table, where the number of elements depends on the size of the result set.
For varrays, COUNT always equals LAST. You can increase or decrease the size of a varray using the EXTEND and TRIM methods, so the value of COUNT can change, up to the value of the LIMIT method.
For nested tables, COUNT normally equals LAST. But, if you delete elements from the middle of a nested table, COUNT becomes smaller than LAST. When tallying elements, COUNT ignores 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:
You cannot use EXTEND to add elements to an uninitialized.
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.
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.
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.
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 rangeEND;/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.