COLLECTION METHODS

Collection 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:
collection_name.method_name [(parameters)]

• 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.

1.COUNT:

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

DECLARE
--Index by table type
   TYPE etab IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;
   emptab  etab;
--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 declaration
   CURSOR c_emp IS SELECT * FROM emp;
   v_num   NUMBER;
BEGIN
   v_num := 1;
-- allocate the spaces
   tabtype.EXTEND (3);
   tabtype (1) := 'SCOTT';
   tabtype (2) := 'KING';
   tabtype (3) := 'SMITH';
   tabtype.EXTEND (3);
   tabtype (4) := 'SCOTT';
   tabtype (5) := 'KING';
   tabtype (6) := 'SMIT';
   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 ='|| tabtype.COUNT);
-- 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='|| emptab.COUNT);
END;
/
output:
Total spaces allocated by Nested Table =6
Total spaces allocated by Varray=3
Total Count of elements in Index by Table=14


PL/SQL procedure successfully completed.