PL/SQL VARRAY

VARRAY stands for variable-sized array. VARRAY is used to store an ordered collection of data. VARRAY is a one-dimensional collection that will allow you to store an ordered set of different elements of similar data types. VARRAY always contains a fixed number of elements and they do not contain any gap in between them (also called nonsparse). The fixed size of VARRAY distinguishes it from other collections like associative arrays and nested tables in PL/SQL.

Different Operations on VARRAYS

Declare and Initialize VARRAY variables

The syntax to declare VARRAY is:

-- To Create a VARRAY type here
 TYPE SampleVARRAY IS VARRAY (3) of VARCHAR (10);

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

Accessing the 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 for accessing VARRAY elements is:

Accessing Single Element

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 first element

Accessing Multiple Elements

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:

Accessing all elements by using loop

Deleting Elements from VARRAY

To delete elements in VARRAYs we simply need to mention the element that we want to delete from VARRAY in DELETE(_). Syntax to delete elements from VARRAY is:

DECLARE 
     myVarray SampleVARRAY := SampleVARRAY('Ramesh', 'Kamlesh', 'Shyam');
Begin
     -- this will delete3rd element
     myVarray.DELETE(3); 
End;

Output:

After deletion

Add More Elements to VARRAY

Consider we have created a VARRAY and now we want to add some more elements in it so this can be done by using the EXTEND keyword. Which will extend the VARRAY size by one. If we want to extend VARRAY size by more than one then we can simply mention that size in it as EXTEND(4). This will extend VARRAY by four. The syntax to add an element is:

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:

Adding one more element

Conclusion

In conclusion, we can say that VARRAY in PL/SQL is a fixed-sized collection that stores an ordered set of elements of the same data type. VARRAY has pre declared limit of a maximum number of elements in it. They are well suited for cases requiring a structured and precisely sized array within Oracle database programms.