DB2 Version 10.1 for Linux, UNIX, and Windows

VARRAY collection type declaration (PL/SQL)

A VARRAY is a type of collection in which each element is referenced by a positive integer called the array index. The maximum cardinality of the VARRAY is specified in the type definition.

The TYPE IS VARRAY statement is used to define a VARRAY collection type.

Syntax


>>-TYPE--varraytype--IS VARRAY--(--n--)--OF--datatype--;-------><

Description

varraytype: An identifier that is assigned to the array type.
n: The maximum number of elements in the array type.
datatype: A supported data type, such as NUMBER, VARCHAR2, RECORD, VARRAY, or associative array type. The %TYPE attribute and the %ROWTYPE attribute are also supported.

Example

The following example reads employee names from the EMP table, stores the names in an array variable of type VARRAY, and then displays the results. The EMP table contains one column named ENAME. The code is executed from a DB2® script (script.db2). The following commands should be issued from the DB2 command window before executing the script (db2 -tvf script.db2):

db2set DB2_COMPATIBILITY_VECTOR=FFF
db2stop
db2start

The script contains the following code:

SET SQLCOMPAT PLSQL;

connect to mydb
/

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(i) := r_emp.ename;
    END LOOP;
    FOR j IN 1..5 LOOP
        DBMS_OUTPUT.PUT_LINE(emp_arr(j));
    END LOOP;
END;
/

DROP PACKAGE foo
/

connect reset
/

This script produces the following sample output:

Curly
Larry
Moe
Shemp
Joe