PL/SQL VARRAY

VARRAY (Variable-Sized Array) in PL/SQL is a collection type used to store an ordered set of elements of the same data type. It is suitable when the maximum number of elements is known in advance.

• Stores multiple values of the same data type in a single collection.
• Maintains the order of inserted elements.
• Has a fixed maximum size defined during declaration.
• Does not allow gaps between elements.
• Useful for managing small and fixed-size collections of data.

Different Operations on PL/SQL VARRAYS

Different operations can be performed on VARRAYs in Oracle PL/SQL.

1. Declare and Initialize VARRAY variables

To use a VARRAY in PL/SQL, first create a VARRAY type and then declare a variable of that type.

TYPE SampleVARRAY IS VARRAY(3) OF VARCHAR2(10);

• SampleVARRAY: Name of the VARRAY type
• 3: Maximum number of elements allowed
• VARCHAR2(10): Data type of elements stored in the VARRAY

Step 2: Declare and Initialize a VARRAY Variable

After creating the VARRAY type, you can declare a variable of that type and initialize it:

-- To Declare a variable which is of VARRAY type 
DECLARE 
     myVarray SampleVARRAY := SampleVARRAY('Ramesh', 'Kamlesh', 'Shyam');

2. Accessing VARRAY Elements

In VARRAYs, we can access elements easily like simple arrays. We can access single element that we want to access or also we can access all the elements by using loops.

Syntax:

To access a specific element, use the index number within parentheses.

DECLARE 
     myVarray SampleVARRAY := SampleVARRAY('Ramesh', 'Kamlesh', 'Shyam');

Begin
     -- Accessing elements by index
     dbms_output.PUT_LINE('This is First element: ' || myVarray(1));
End;

Output:

Accessing Multiple Elements

You can iterate through all the elements using a loop.

Syntax:

DECLARE 
     myVarray SampleVARRAY := SampleVARRAY('Ramesh', 'Kamlesh', 'Shyam');

Begin
     -- Accessing alll the elements
     FOR i IN 1.. myVarray.COUNT loop
          dbms_output.PUT_LINE('This is element ' || i || ':' || myVarray(i));
     END LOOP;
End;

Output:

3. Deleting Elements from VARRAY

VARRAYs do not support the DELETE method in PL/SQL because they do not allow gaps between elements. To remove elements, use the TRIM method, which deletes elements only from the end.

Syntax:

DECLARE 
     myVarray SampleVARRAY := SampleVARRAY('Ramesh', 'Kamlesh', 'Shyam');
BEGIN
     -- Removes last element
     myVarray.TRIM;
END;
/

Output:

4. Add More Elements to VARRAY

To add more elements in a VARRAY, we use the EXTEND method. EXTEND increases the number of elements in the VARRAY by one, while EXTEND(n) increases it by n elements. The total size cannot exceed the maximum limit defined for the VARRAY.

Syntax:

DECLARE 
     myVarray SampleVARRAY := SampleVARRAY('Ramesh', 'Kamlesh', 'Shyam');
Begin
     -- this will extend VARRAY by one
     myVarray.Extend;
     -- this will add 'Pankaj' at index 4
     myVarray(4) := 'Pankaj';
End;

Output: