Collection Methods in PLSQL

Collection Methods:

PL/SQL collections contain a number of built-in methods that prove useful when working with them. Here is some of them.

COUNT:
This method returns the number of elements in the collection.
Example:

declare
Type book is VARRAY(2) OF VARCHAR2(60);
book_tab book;
begin
book_tab:=book('The Angel','Hope on your way');
dbms_output.put_line('Number of elements in the Varray is 'book_tab.count());
end;

Output:
Number of elements in the Varray is 2

EXISTS:

Ø Returns Boolean true if element at specified index exists
Ø Returns Boolean false if element at specified index does not exists


Example:

DECLARE
TYPE numlist IS TABLE OF INTEGER;
n numlist := numlist (1, 3, 5, 7);
BEGIN
IF n.EXISTS (1)
THEN
DBMS_OUTPUT.put_line ('First element exists ');
ELSE
DBMS_OUTPUT.put_line ('First element Not exists');
END IF;

IF n.EXISTS (5)
THEN
DBMS_OUTPUT.put_line ('Fifth element exists ');
ELSE
DBMS_OUTPUT.put_line ('Fifth element Not exists');
END IF;
END;


Output:
First element exists
Fifth element Not exists

EXTEND :

Increases size of Collection by 1 or number specified
Note: Cannot use with Associative Array.
Ø 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.

Example:

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.EXTEND; 
DBMS_OUTPUT.PUT_LINE ('Now there are ' n.COUNT ' elements in N.');

END;

Output:
There are 4 elements in N.
Now there are 7 elements in N.
Now there are 8 elements in N.


FIRST and LAST:

Ø FIRST-Navigates to the first element in the Collection.
Ø LAST -Navigates to last element in the Collection.

Example:

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);
END;

Output:
N's first subscript is 1.
N's last subscript is 4.



PRIOR and NEXT:

Ø PRIOR -Navigates to the previous element.
Ø NEXT -Navigates to the next element.

Example:

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));
END;

Output:
The element after #2 is 3
The element before #2 is 1

TRIM:

Removes the last element, or the last n elements if a number is specified.

Ø TRIM removes one element from the end of a collection.
Ø TRIM(n) removes n elements from the end of a collection.

Note: Cannot use with Associative Array.

Example:

DECLARE
TYPE NumList IS TABLE OF NUMBER;
n NumList := NumList(1,2,3,5,7,11);
BEGIN
n.TRIM(2); -- Remove last 2 elements.
DBMS_OUTPUT.PUT_LINE('There are ' n.COUNT ' elements in N.');
n.TRIM; -- Remove last element.
DBMS_OUTPUT.PUT_LINE ('There are ' n.COUNT ' elements in N.');
END;

Output:
There are 4 elements in N.
There are 3 elements in N.


DELETE:

Removes all elements of a Collection, or the nth element, if a parameter is specified.

Ø DELETE removes all elements from a collection.
Ø DELETE(n) removes the nth element from an associative array or nested table.
Ø DELETE(n) does nothing,if n is null,
Ø DELETE(m,n) removes all elements in the range m..n from an associative array or nested table.
Ø DELETE(m,n) does nothing,if m is larger than n or if m or n is null,


Example:

DECLARE
TYPE NumList IS TABLE OF NUMBER;
n NumList := NumList(10,20,30,40,50,60,70,80,90,100);
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
END;


Thanks
Amar Alam