Oracle PL/SQL by Example, Fifth Edition (2015)

A varray—that is, a variable-size array—is another collection type. Similar to PL/SQL tables, each element of a varray is assigned a consecutive subscript starting at 1. Figure 15.2 shows a varray consisting of five integer numbers, where each number is assigned a unique subscript that corresponds to its position in the varray.

Figure 15.2 Varray

A varray has a maximum size. In other words, a subscript of a varray has a fixed lower bound equal to 1, and an upper bound that is extensible if such a need arises. In Figure 15.2, the upper bound of a varray is 5, but it can be extended to 6, 7, and so on, up to 10. Therefore, a varray can contain a number of elements, varying from zero (empty array) to its maximum size. Recall that PL/SQL tables do not have a maximum size that must be specified explicitly.

The general syntax for creating a varray is shown in Listing 15.7 (the reserved words and phrases surrounded by brackets are optional).

Listing 15.7 Varray

TYPE type_name IS {VARRAY | VARYING ARRAY} (size_limit) OF element_type [NOT NULL];
varray_name TYPE_NAME;

First, a varray structure is defined using the TYPE statement, where type_name is the name of the type that is used in the second step to declare an actual varray. Notice that there are two variations of the type, VARRAY and VARYING ARRAY. A size_limit is a positive integer literal that specifies the upper bound of a varray.

Second, the actual varray is declared based on the type specified in the first step.

Consider the code fragment shown in Listing 15.8.

Listing 15.8 Declaring a Varray

DECLARE
TYPE last_name_type IS VARRAY(10) OF student.last_name%TYPE;
last_name_varray last_name_type;

In this example, type last_name_type is declared as a varray of 10 elements based on the column LAST_NAME of the STUDENT table. Next, the actual varray last_name_varray is declared based on the last_name_type. Similarly to nested tables, a varray may be defined as a stand-alone user-defined type via the CREATE TYPE statement.

Just like a nested table, a varray is automatically NULL when it is declared and must be initialized before its individual elements can be referenced. In the following modified version of an example used in Lab 15.1, ch15_1c.sql, instead of using a nested table, the script version uses varray (modified statements are highlighted in bold).

For Example ch15_1d.sql

DECLARE
CURSOR name_cur IS
SELECT last_name
FROM student
WHERE rownum < 10;

TYPE last_name_type IS VARRAY(10) OF student.last_name%TYPE;
last_name_varray last_name_type := last_name_type();

v_index PLS_INTEGER := 0;
BEGIN
FOR name_rec IN name_cur
LOOP
v_index := v_index + 1;
last_name_varray.EXTEND;
last_name_varray(v_index) := name_rec.last_name;

DBMS_OUTPUT.PUT_LINE ('last_name('||v_index||'): '||last_name_varray(v_index));
END LOOP;
END;

This example produces the following output:

last_name(1): Kocka
last_name(2): Jung
last_name(3): Mulroy
last_name(4): Brendler
last_name(5): Carcia
last_name(6): Tripp
last_name(7): Frost
last_name(8): Snow
last_name(9): Scrittorale

Based on the preceding example, you might realize that collection methods seen in Lab 15.1 can be used with varrays as well. Consider the following example, which illustrates the use of various collection methods when applied to a varray:

For Example ch15_3a.sql

DECLARE
TYPE varray_type IS VARRAY(10) OF NUMBER;
varray varray_type := varray_type(1, 2, 3, 4, 5, 6);

BEGIN
DBMS_OUTPUT.PUT_LINE ('varray.COUNT = '||varray.COUNT);
DBMS_OUTPUT.PUT_LINE ('varray.LIMIT = '||varray.LIMIT);

DBMS_OUTPUT.PUT_LINE ('varray.FIRST = '||varray.FIRST);
DBMS_OUTPUT.PUT_LINE ('varray.LAST = '||varray.LAST);
-- Append two copies of the 4th element to the varray
varray.EXTEND(2, 4);
DBMS_OUTPUT.PUT_LINE ('varray.LAST = '||varray.LAST);
DBMS_OUTPUT.PUT_LINE ('varray('||varray.LAST||') = '||varray(varray.LAST));

-- Trim last two elements
varray.TRIM(2);
DBMS_OUTPUT.PUT_LINE('varray.LAST = '||varray.LAST);
END;

This example returns the following output:

varray.COUNT = 6
varray.LIMIT = 10
varray.FIRST = 1
varray.LAST = 6
varray.LAST = 8
varray(8) = 4
varray.LAST = 6

The first two lines of the output

varray.COUNT = 6
varray.LIMIT = 10

show the results of the COUNT and LIMIT methods, respectively. Recall that the COUNT method returns the number of elements that a collection contains. This collection has been initialized to six elements, so the COUNT method returns a value of 6.

The next line of output corresponds to another collection method, LIMIT. This method returns the maximum number of elements that a collection can contain and is typically used with varrays because varrays have an upper bound specified at the time of declaration. This varray has an upper bound of 10, so the LIMIT method returns a value of 10.