VARRAY (Variable-size array) - Collection in PLSQL

In PL/SQL, a VARRAY (Variable-size array) is a type of collection that can store a fixed number of elements. Each element in the VARRAY is stored in a sequential manner, and all elements are of the same data type.

Characteristics of VARRAY:

1. Fixed Maximum Size: When you define a VARRAY, you specify the maximum number of elements it can hold. You cannot exceed this limit.

2. Sequential: Elements in a VARRAY are stored and accessed in a specific order, starting from index 1.

3. Homogeneous: All elements in a VARRAY must be of the same data type.

4. Densely Indexed: A VARRAY is always densely populated. This means that there are no gaps between indexes.

5. Stored in Oracle Database: When used in database columns, VARRAYs are stored inline with the table row data.

Syntax for VARRAY in PL/SQL

1. Declaring a VARRAY Type

You first define a VARRAY type, specifying the number of elements it can hold and the data type of the elements.

TYPE varray_name IS VARRAY(max_size) OF element_type;

varray_name: Name of the VARRAY type.

max_size: Maximum number of elements that can be stored in the VARRAY.

element_type: Data type of the elements stored in the VARRAY.

1. Defining and Initializing a VARRAY

Once the VARRAY type is declared, you can declare variables of that type and initialize them with values.

DECLARE
TYPE employee_varray IS VARRAY(5) OF VARCHAR2(30); -- VARRAY type declaration with a maximum size of 5
employee_names employee_varray := employee_varray('John', 'Jane'); -- Initializing with 2 elements
BEGIN
-- Statements to use the VARRAY
END;

Operations on VARRAYs:

Here are some common operations you can perform on VARRAYs:

EXTEND(n): Adds n elements to the end of the VARRAY.

COUNT: Returns the current number of elements in the VARRAY.

TRIM(n): Removes n elements from the end of the VARRAY.

FIRST and LAST: Returns the first and last index in the VARRAY.

Example: Declaring and Using a VARRAY

DECLARE
-- Declare a VARRAY type that can hold up to 5 elements of VARCHAR2(50)
TYPE employee_varray IS VARRAY(5) OF VARCHAR2(50);

-- Declare a variable of this VARRAY type and initialize it with two values
employee_names employee_varray := employee_varray('John', 'Jane');

BEGIN
-- Add more elements to the VARRAY
employee_names.EXTEND(3); -- Extend the array by 3 more elements
employee_names(3) := 'Sam';
employee_names(4) := 'Peter';
employee_names(5) := 'Lucy';

-- Print all the names in the VARRAY
FOR i IN 1..employee_names.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE('Employee ' || i || ': ' || employee_names(i));
END LOOP;

-- Trim 1 element from the end of the VARRAY
employee_names.TRIM(1);
DBMS_OUTPUT.PUT_LINE('After trimming 1 element:');

-- Print remaining names in the VARRAY
FOR i IN 1..employee_names.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE('Employee ' || i || ': ' || employee_names(i));
END LOOP;

END;
/

Explanation:

1. VARRAY Type Declaration: We declare a VARRAY type employee_varray that can hold up to 5 elements of type VARCHAR2(50).

2. Initialization: We initialize the employee_names variable with two names: 'John' and 'Jane'.

3. EXTEND: We use the EXTEND(3) method to extend the VARRAY to hold 3 more elements, making room for a total of 5 elements.

4. Accessing Elements: We assign values to the new positions (3 to 5) and print all elements using a loop.

5. TRIM: The TRIM(1) method is used to remove the last element from the VARRAY, reducing the number of elements from 5 to 4.

Output:

Employee 1: John
Employee 2: Jane
Employee 3: Sam
Employee 4: Peter
Employee 5: Lucy
After trimming 1 element:
Employee 1: John
Employee 2: Jane
Employee 3: Sam
Employee 4: Peter

Key Points:

VARRAYs are useful when you know the maximum size of your collection ahead of time.

Sequential Access: Elements in VARRAYs are accessed using their index.

Extending and Trimming: You can dynamically add or remove elements from the VARRAY using EXTEND and TRIM.