Collection methods (PL/SQL)

Collection methods can be used to obtain information about collections or to modify collections.

The MYDB database has one table, EMP, which has one column, ENAME (defined as VARCHAR(10)):
db2 select * from emp

ENAME
----------
Curly
Larry
Moe
Shemp
Joe

  5 record(s) selected.
Table 1. Collection methods that are supported (or tolerated) by the data server in a PL/SQL context
Collection method Description Example
COUNT Returns the number of elements in a collection. 
CREATE PACKAGE foo
AS
    TYPE sparse_arr_typ IS TABLE OF NUMBER
      INDEX BY BINARY_INTEGER;
END;
/

SET SERVEROUTPUT ON
/

DECLARE
    sparse_arr      foo.sparse_arr_typ;
BEGIN
    sparse_arr(-10)   := -10;
    sparse_arr(0)     := 0;
    sparse_arr(10)    := 10;
    DBMS_OUTPUT.PUT_LINE('COUNT: ' ||
      sparse_arr.COUNT);
END;
/
DELETE Removes all elements from a collection. 
CREATE PACKAGE foo
AS
    TYPE names_typ IS TABLE OF VARCHAR2(10)
      INDEX BY BINARY_INTEGER;
END;
/

SET SERVEROUTPUT ON
/

DECLARE
    actor_names      foo.names_typ;

BEGIN

    actor_names(1) := 'Chris';
    actor_names(2) := 'Steve';
    actor_names(3) := 'Kate';
    actor_names(4) := 'Naomi';
    actor_names(5) := 'Peter';
    actor_names(6) := 'Philip';
    actor_names(7) := 'Michael';
    actor_names(8) := 'Gary';

    DBMS_OUTPUT.PUT_LINE('COUNT: ' ||
      actor_names.COUNT);

    actor_names.DELETE(2);
    DBMS_OUTPUT.PUT_LINE('COUNT: ' ||
      actor_names.COUNT);

    actor_names.DELETE(3, 5);
    DBMS_OUTPUT.PUT_LINE('COUNT: ' ||
      actor_names.COUNT);

    actor_names.DELETE;
    DBMS_OUTPUT.PUT_LINE('COUNT: ' ||
      actor_names.COUNT);

END;
/
DELETE (n) Removes element n from an associative array. You cannot delete individual elements from a VARRAY collection type. See DELETE.
DELETE (n1, n2) Removes all elements from n1 to n2 from an associative array. You cannot delete individual elements from a VARRAY collection type. See DELETE.
EXISTS (n) Returns TRUE if the specified element exists. 
CREATE PACKAGE foo
AS
    TYPE emp_arr_typ IS VARRAY(5) OF VARCHAR2(10);
END;
/

SET SERVEROUTPUT ON
/

DECLARE
    emp_arr         foo.emp_arr_typ;
    CURSOR emp_cur IS SELECT ename FROM emp
      WHERE ROWNUM <= 5;
    i               INTEGER := 0;
BEGIN
    FOR r_emp IN emp_cur LOOP
        i := i + 1;
        emp_arr.EXTEND;
        emp_arr(i) := r_emp.ename;
    END LOOP;
    emp_arr.TRIM;
    FOR j IN 1..5 LOOP
        IF emp_arr.EXISTS(j) = true THEN
            DBMS_OUTPUT.PUT_LINE(emp_arr(j));
        ELSE
            DBMS_OUTPUT.PUT_LINE('THIS ELEMENT
              HAS BEEN DELETED');
        END IF;
    END LOOP;
END;
/
EXTEND Appends a single NULL element to a collection. See EXISTS (n).
EXTEND (n) Appends n NULL elements to a collection. See EXISTS (n).
EXTEND (n1, n2) Appends n1 copies of the n2th element to a collection. See EXISTS (n).
FIRST Returns the smallest index number in a collection. 
CREATE PACKAGE foo
AS
    TYPE emp_arr_typ IS VARRAY(5) OF VARCHAR2(10);
END;
/

SET SERVEROUTPUT ON
/

DECLARE
    emp_arr         foo.emp_arr_typ;
    CURSOR emp_cur IS SELECT ename FROM emp
      WHERE ROWNUM <= 5;
    i               INTEGER := 0;
    k               INTEGER := 0;
    l               INTEGER := 0;
BEGIN
    
    FOR r_emp IN emp_cur LOOP
        i := i + 1;
        emp_arr(i) := r_emp.ename;
    END LOOP;

    -- Use FIRST and LAST to specify the lower and
    --   upper bounds of a loop range:
    FOR j IN emp_arr.FIRST..emp_arr.LAST LOOP
        DBMS_OUTPUT.PUT_LINE(emp_arr(j));
    END LOOP;

    -- Use NEXT(n) to obtain the subscript of
    --   the next element:
    k := emp_arr.FIRST;
    WHILE k IS NOT NULL LOOP
        DBMS_OUTPUT.PUT_LINE(emp_arr(k));
        k := emp_arr.NEXT(k);
    END LOOP;

    -- Use PRIOR(n) to obtain the subscript of
    --   the previous element:
    l := emp_arr.LAST;
    WHILE l IS NOT NULL LOOP
        DBMS_OUTPUT.PUT_LINE(emp_arr(l));
        l := emp_arr.PRIOR(l);
    END LOOP;

    DBMS_OUTPUT.PUT_LINE('COUNT: ' || emp_arr.COUNT);

    emp_arr.TRIM;
    DBMS_OUTPUT.PUT_LINE('COUNT: ' || emp_arr.COUNT);

    emp_arr.TRIM(2);
    DBMS_OUTPUT.PUT_LINE('COUNT: ' || emp_arr.COUNT);

    DBMS_OUTPUT.PUT_LINE('Max. no. elements = ' ||
      emp_arr.LIMIT);

END;
/
LAST Returns the largest index number in a collection. See FIRST.
LIMIT Returns the maximum number of elements for a VARRAY, or NULL for nested tables. See FIRST.
NEXT (n) Returns the index number of the element immediately following the specified index. See FIRST.
PRIOR (n) Returns the index number of the element immediately prior to the specified index. See FIRST.
TRIM Removes a single element from the end of a collection. You cannot trim elements from an associative array collection type. See FIRST.
TRIM (n) Removes n elements from the end of a collection. You cannot trim elements from an associative array collection type. See FIRST.