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 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:
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:
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:
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.
Contact Us