**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 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:

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;

## No comments:

Post a Comment