Collections Methods
A collection method is a built-in function or procedure that operates on collections and is called using
dot notation.
Following are the functions used with collections.
EXISTS
COUNT
LIMIT
FIRST AND LAST
PRIOR AND NEXT
EXTEND
TRIM
DELETE
Syntax:-
[sql]collection_name.method_name [(parameters)][/sql]
Collection methods cannot be called from SQL statements.
Only the EXISTS method can be used on a NULL collection.
All other methods applied on a null collection raise the COLLECTION_IS_NULL error.
[Link]:
COUNT method returns the number of elements with the space allocated in Varrays and
Nested tables.
The COUNT method displays number of elements in associate arrays.
COUNT can be smaller than LIMIT for Varrays.
Example:-
[sql]DECLARE
/*Index by table type*/
TYPE enametab IS TABLE OF emp%ROWTYPE
INDEX BY BINARY_INTEGER;
emptab enametab;
/*Nested Table Type*/
TYPE tab_type IS TABLE OF VARCHAR2 (10);
tabtype tab_type := tab_type ();
/* Varray Type */
TYPE varray_type IS VARRAY (3) OF VARCHAR2 (10);
/* directly assign the values */
v_type varray_type := varray_type (‘MILLER’, ‘FORD’, ‘TURNER’);
CURSOR c_emp
IS
SELECT *
FROM emp;
v_num NUMBER;
BEGIN
v_num := 1;
/*allocate the spaces*/
[Link] (3);
tabtype (1) := ‘SCOTT’;
tabtype (2) := ‘KING’;
tabtype (3) := ‘SMITH’;
[Link] (3);
tabtype (4) := ‘SCOTT’;
tabtype (5) := ‘KING’;
tabtype (6) := ‘SMITH’;
FOR emp_rec IN c_emp
LOOP
emptab (v_num) := emp_rec;
v_num := v_num + 1;
END LOOP;
/*count the number of spaces allocated to nested table*/
DBMS_OUTPUT.put_line ( ‘Total spaces allocated by Nested Table =>’
|| [Link]
);
/*count the number of spaces allocated to Varray */
DBMS_OUTPUT.put_line (‘Total spaces allocated by Varray=>’ || v_type.COUNT);
/*count the number of elements In index by table*/
DBMS_OUTPUT.put_line ( ‘Total Count of elements in Index by Table=>’
|| [Link]
);
END;[/sql]
Output:-
anonymous block completed
Total spaces allocated by Nested Table =>6
Total spaces allocated by Varray=>3
Total Count of elements in Index by Table=>9
[Link]:
EXISTS is a method determines whether or not an element is found in a collection. It takes a
single formal parameter that is overloaded.
The parameter data types are PLS_INTERGER, VARCHAR2, and LONG.
If the collection is a null element structure, the EXISTS method will raise
COLLECTION_IS_NULL exception.
Example:-
[sql]DECLARE
/*Nested Table Type*/
TYPE tab_type IS TABLE OF VARCHAR2 (10);
/*declare the variable and initialize the variable*/
tabtype tab_type := tab_type ();
BEGIN
/*allocate the spaces*/
[Link] (3);
tabtype (1) := ‘SCOTT’;
tabtype (2) := ‘KING’;
tabtype (3) := ‘SMITH’;
[Link] (3);
tabtype (4) := ‘SCOTT’;
tabtype (5) := ‘KING’;
tabtype (6) := ‘SMITH’;
/*checks the 1st space. If the 1st space having element then its return true otherwise false*/
IF ([Link] (1))
THEN
DBMS_OUTPUT.put_line (‘Element found for 1st space’);
END IF;
IF ([Link] (7))
THEN
DBMS_OUTPUT.put_line (‘Element found for 7th space’);
ELSE
DBMS_OUTPUT.put_line (‘Element not found for 7th space’);
END IF;
END;[/sql]
Output:-
anonymous block completed
Element found for 1st space
Element not found for 7th space
[Link]:
The LIMIT method returns the highest allowed subscript value in Varray.
Example:-
[sql]DECLARE
/*declare the Varray */
TYPE varray_type IS VARRAY (3) OF VARCHAR2 (10);
/*Initialize the Varray and assign the values directly to the varray type variable */
v_type varray_type := varray_type (‘MILLER’, ‘FORD’, ‘TURNER’);
BEGIN
/*Print the limit of varray*/
DBMS_OUTPUT.put_line (‘Limit of Varray is=>’ || v_type.LIMIT);
END;[/sql]
Output:-
anonymous block completed
Limit of Varray is=>3
[Link] and LAST:-
The FIRST method will display returns the lowest subscript value in a collection.
If it is a numeric index, it returns a PLS_INTEGER.If it’s an associative array, it returns a
VARCHAR2 or LONG data type.
You can’t use the FIRST method in a range for-loop when the index is non-numeric.
The LAST method will display returns the highest subscript value in a collection.
If it is a numeric index, it returns a PLS_INTEGER.If it’s an associative array, it returns a
VARCHAR2 or LONG data type.
You can’t use the LAST method in a range for-loop when the index is non-numeric.
Example:-
[sql]DECLARE
/*Index by table type*/
TYPE enametab IS TABLE OF emp%ROWTYPE
INDEX BY BINARY_INTEGER;
emptab enametab;
/*Nested Table Type*/
TYPE tab_type IS TABLE OF VARCHAR2 (10);
tabtype tab_type := tab_type ();
/* Varray Type */
TYPE varray_type IS VARRAY (3) OF VARCHAR2 (10);
/* directly assign the values */
v_type varray_type := varray_type (‘MILLER’, ‘FORD’, ‘TURNER’);
CURSOR c_emp
IS
SELECT *
FROM emp;
v_num NUMBER;
BEGIN
v_num := 1;
/*allocate the spaces*/
[Link] (3);
tabtype (1) := ‘SCOTT’;
tabtype (2) := ‘KING’;
tabtype (3) := ‘SMITH’;
[Link] (3);
tabtype (4) := ‘SCOTT’;
tabtype (5) := ‘KING’;
tabtype (6) := ‘SMITH’;
FOR emp_rec IN c_emp
LOOP
emptab (v_num) := emp_rec;
v_num := v_num + 1;
END LOOP;
/*get the lowest subscripted value and high subscripted value in Nested table*/
DBMS_OUTPUT.put_line (‘—Nested Table—‘);
DBMS_OUTPUT.put_line ( ‘the first value (‘|| [Link]|| ‘) =>’|| tabtype ([Link])
);
DBMS_OUTPUT.put_line ( ‘the last value (‘|| [Link]|| ‘) =>’|| tabtype ([Link])
);
/* get the lowest subscripted value and high subscripted value in Varray */
DBMS_OUTPUT.put_line (‘—Varrays—‘);
DBMS_OUTPUT.put_line ( ‘the first value (‘|| v_type.FIRST|| ‘) =>’|| v_type (v_type.FIRST)
);
DBMS_OUTPUT.put_line ( ‘the last value (‘|| v_type.LAST|| ‘) =>’|| v_type (v_type.LAST)
);
/* get the lowest subscripted value and high subscripted value in Index by table */
DBMS_OUTPUT.put_line (‘—Index By Table—‘);
DBMS_OUTPUT.put_line ( ‘the first value of empno(‘|| [Link]|| ‘)=>’|| emptab
([Link]).empno
);
DBMS_OUTPUT.put_line ( ‘the last value of empno(‘|| [Link]|| ‘)=>’|| emptab
([Link]).empno
);
END;[/sql]
Output:-
anonymous block completed
---Nested Table---
the first value (1) =>SCOTT
the last value (6) =>SMITH
---Varrays---
the first value (1) =>MILLER
the last value (3) =>TURNER
---Index By Table---
the first value of empno(1)=>7839
the last value of empno(9)=>7934
[Link] and PRIOR:-
The NEXT method uses the subscript to find the next higher subscript in the collection.
If there is no higher subscript value, NEXT return NULL.
The PRIOR method uses the subscript to find the next lower subscript in the collection.
If there is no higher subscript value, PRIOR return NULL.
Example:-
[sql]DECLARE
/*Index by table type*/
TYPE enametab IS TABLE OF emp%ROWTYPE
INDEX BY BINARY_INTEGER;
emptab enametab;
/*Nested Table Type*/
TYPE tab_type IS TABLE OF VARCHAR2 (10);
tabtype tab_type := tab_type ();
/* Varray Type */
TYPE varray_type IS VARRAY (3) OF VARCHAR2 (10);
/* directly assign the values */
v_type varray_type := varray_type (‘MILLER’, ‘FORD’, ‘TURNER’);
CURSOR c_emp
IS
SELECT *
FROM emp;
v_num NUMBER;
BEGIN
v_num:= 1;
/*allocate the spaces*/
[Link] (3);
tabtype (1) := ‘SCOTT’;
tabtype (2) := ‘KING’;
tabtype (3) := ‘SMITH’;
[Link] (3);
tabtype (4) := ‘SCOTT’;
tabtype (5) := ‘TURNER’;
tabtype (6) := ‘SMITH’;
FOR emp_rec IN c_emp
LOOP
emptab (v_num) := emp_rec;
v_num:= v_num + 1;
END LOOP;
/*we try to find out the next higher subscript value and lower subscript value in Nested Table*/
DBMS_OUTPUT.put_line (‘—Nested Table—‘);
DBMS_OUTPUT.put_line (‘the Next value (‘|| [Link] ([Link])|| ‘) =>’||
tabtype ([Link] ([Link]))
);
DBMS_OUTPUT.put_line(‘the previous value(‘||[Link]([Link])||’)
=>’||tabtype ([Link] ([Link]))
);
/*we try to find out the next higher subscript value and lower subscript value in Varrays*/
DBMS_OUTPUT.put_line (‘—Varrays—‘);
DBMS_OUTPUT.put_line (‘the Next value (‘|| v_type.NEXT (v_type.FIRST)|| ‘) =>’||
v_type (v_type.NEXT (v_type.FIRST))
);
DBMS_OUTPUT.put_line (‘the previous value (‘|| v_type.PRIOR (v_type.LAST)|| ‘) =>’||
v_type (v_type.PRIOR (v_type.LAST))
);
/*we try to find out the next higher subscript value and lower subscript value in Index By table*/
DBMS_OUTPUT.put_line (‘—Index By Table—‘);
DBMS_OUTPUT.put_line (‘the next value of empno(‘|| [Link]([Link])|| ‘)=>’||
emptab ([Link]
([Link])).empno
);
DBMS_OUTPUT.put_line (‘the previous value of empno(‘|| [Link]([Link])|| ‘)=>’||
emptab ([Link]
([Link])).empno
);
END;[/sql]
Output:-
anonymous block completed
---Nested Table---
the Next value (2) =>KING
the previous value(5)
=>TURNER
---Varrays---
the Next value (2) =>FORD
the previous value (2) =>FORD
---Index By Table---
the next value of empno(2)=>7698
the previous value of empno(8)=>7900
[Link]:-
The EXTEND allocates the space for new element in a collection. It is used to allocate space
before adding a value to the collection.
EXTEND will fail it attempts to exceed the LIMIT of a varray.
And it takes a single formal parameter; it is optional (parameter).
Example:-
[sql]DECLARE
/*Nested Table Type*/
TYPE tab_type IS TABLE OF VARCHAR2 (10);
tabtype tab_type := tab_type ();
/* Varray Type */
TYPE varray_type IS VARRAY (3) OF VARCHAR2 (10);
/* directly assign the values */
v_type varray_type:= varray_type (‘MILLER’, ‘FORD’, ‘TURNER’);
BEGIN
/*allocate the spaces for each element individually*/
[Link];
tabtype (1) := ‘SCOTT’;
[Link];
tabtype (2) := ‘KING’;
[Link];
tabtype (3) := ‘SMITH’;
/*we try to allocate the spaces for 3 elements at a time */
[Link] (3);
tabtype (4) := ‘SCOTT’;
tabtype (5) := ‘KING’;
tabtype (6) := ‘SMITH’;
/*count the number of spaces allocated to nested table*/
DBMS_OUTPUT.put_line ( ‘Total spaces allocated by Nested Table =>’
|| [Link]
);
/*count the number of spaces allocated to Varray */
DBMS_OUTPUT.put_line (‘Total spaces allocated by Varray=>’ || v_type.COUNT);
END;[/sql]
Output:-
anonymous block completed
Total spaces allocated by Nested Table =>6
Total spaces allocated by Varray=>3
[Link]:-
The DELETE will remove the element with subscript.
The other version (DELETE (n, m)) will delete a continuous inclusive range of elements from
a collection.
Example:-
[sql]DECLARE
/*Nested Table Type*/
TYPE tab_type IS TABLE OF VARCHAR2 (10);
tabtype tab_type := tab_type ();
/* Varray Type */
TYPE varray_type IS VARRAY (3) OF VARCHAR2 (10);
/* directly assign the values */
v_type varray_type := varray_type (‘MILLER’, ‘FORD’, ‘TURNER’);
BEGIN
/*allocate the spaces*/
[Link];
tabtype (1) := ‘SCOTT’;
[Link];
tabtype (2) := ‘KING’;
[Link];
tabtype (3) := ‘SMITH’;
[Link] (3);
tabtype (4) := ‘SCOTT’;
tabtype (5) := ‘KING’;
tabtype (6) := ‘SMITH’;
/*count the number of spaces allocated to nested table*/
DBMS_OUTPUT.put_line ( ‘Total spaces allocated by Nested Table before delete =>’ ||
[Link]
);
[Link];
DBMS_OUTPUT.put_line ( ‘Total spaces allocated by Nested Table After delete =>’||
[Link]
);
/*count the number of spaces allocated to Varray */
DBMS_OUTPUT.put_line (‘Total spaces allocated by Varray before delete =>’ || v_type.count);
v_type.DELETE;
DBMS_OUTPUT.put_line (‘Total spaces allocated by Varray After delete=>’ || v_type.count);
END;[/sql]
Output:-
anonymous block completed
Total spaces allocated by Nested Table before delete =>6
Total spaces allocated by Nested Table After delete =>0
Total spaces allocated by Varray before delete =>3
Total spaces allocated by Varray After delete=>0