DB2 Version 10.1 for Linux, UNIX, and Windows

Collection methods (PL/SQL)

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

The following commands should be executed before attempting to run the examples in Table 1.
db2set DB2_COMPATIBILITY_VECTOR=ORA
db2stop
db2start
db2 connect to mydb
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 DB2 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".