PLSQL Array

Introduction to PLSQL Array

PL/ SQL array is the provision to store multiple value of same type in a particular variable whose datatype is VARRAY. PL/ SQL provides us with a special datatype named VARRAY to implement the arrays. We can specify the fixed size of the VARRAY which will help us specify the number of elements that can be stored in that array. In this article, we will study about the syntax of using the VARRAY, the usage and internal working and also implementation along with the help of certain examples.

Syntax and Working of PLSQL Array

The arrays in PL/ SQL created by using VARRAY datatype reserves the sequential storage space in the memory which means that all the elements which we are going to store will be kept in memory in contagious format one after the other. The VARRAY datatype can store only one-dimensional arrays which are not sparse that means no gaps in between the two or more elements and are bounded. The syntax of declaring and using the VARRAT datatype for arrays in PL/ SQL programming is as shown below –

TYPE array name IS VARRAY(number of elements) OF type of element [NOT NULL];

In the above syntax,

• Array name: array name is the name of the array which you want to create using which you will refer it further in the programming. Note that in oracle indexing starts from 1. Each element in array is associated with index which helps to access it inside that array. This means that any element in the array can be accessed by using its name and the index value.
• Number of elements: This helps you to specify the size of the datatype VARRAY which will tell about the fixed number of elements that can be stored inside that particular variable whose datatype is VARRAY.
• Type of element: This will tell us about what type of values are going to be stored inside the array which we are creating. That array can have string values, number values, Boolean or any other type. Note that a single array can contain only one type of element in it.
• NOT NULL: This is the optional specification while declaring the array. If we specify the NOT NULL attribute then all the elements which will be stored in the array should not have null value in them.
• Declare global array: We can create an array that can be accessed globally throughout the database and not only in PL/ SQL. For this we can make the use of the following syntax –

CREATE [OR REPLACE ] TYPE name of array AS | IS
VARRAY (number of elements) OF type of element [NOT NULL];

In the above syntax if we specify the OR REPLACE clause and there is any existence of the same-named variable of varray datatype then it is replaced but its granted privileges are still persisting.

• Initializing the VARRAY instance: If creation and declaration of varray type is completed by using above procedure, we can initialize and create the instance of that type by using the following syntax –

Name of varray                 type of the varray              [:=type of varray(values….)];

In the above syntax,

• Type of varray(): This is the constructor which ca n accept the values which we want to store inside our array. Note that the values specified as the arguments to this constructor should be comma separated. In case, if we have not initialized the VARRAY before using it, we will receive an error while using it in code or somewhere else which is as shown below – We can initialize the VARRAY to a blank array by not passing any values in the parenthesis of the type of array constructor.
• Accessing the elements of array: Indexing is used for referencing to each and every individual element inside the array. Indexing starts from 1 and increments by one for each of the sequential element stored in the array. We can simply access a particular element at nth index by using the following syntax –
• Name of varray (n): Where n is the index at which the element is located. If we have specified the value of n such that it does not belongs to range of the index values (1 to number of elements) then PL/ SQL throws an exception named SUBSCRIPT_BEYOND_COUNT.

Deleting particular element from varray: We can delete a particular value inside the array in PL/ SQL by using the following syntax –

Name of varray. DELETE

Which deletes all the elements present inside the array.

In case if you want to delete only one element from the end of the array, you can use the following syntax –

Name of varray. TRIM;

To remove a particular element at nth position with n index, we can make the use of following syntax –

Name of varray. TRIM(n);

Example of PLSQL Array

Let us understand the usage of arrays in PL/SQL with the help of example.

DECLARE
type employeeNames IS VARRAY(6) OF VARCHAR2(10);
type salary IS VARRAY(6) OF INTEGER;
names employeeNames;
salaries salary;
total integer;
BEGIN
names := employeeNames('Mayur', 'Ekta', 'Pari', 'Yashika', 'Payal');
salaries:= salary(9845, 9007, 7813, 8754, 9022);
total := names.count;
dbms_output.put_line('Total '|| total || ' Employees');
FOR i in 1 .. total LOOP
dbms_output.put_line('Employee: ' || names(i) || '
Salaries: ' || salaries(i));
END LOOP;
END;

The output of the above code is as shown in the below image displaying the information of all the employees and their individual salaries stored in our arrays in PL/ SQL  –

Conclusion

We can make the use of VARRAY datatype in PL/ SQL to implement the arrays in it. The arrays store the data in a sequential format in the memory. The values inside a particular array should be of same datatype. The indexing in PL/ SQL for arrays starts from 1. We need to priorly specify the maximum number of elements that will be stored in that particular element. To delete the elements inside the array we can make use of TRIM() or DELETE functions. Arrays are used for storing many elements with same data type in a single variable.

Recommended Articles

This is a guide to PLSQL Array. Here we also discuss the Syntax and Working of PLSQL Array along with example. You may also have a look at the following articles to learn more –

1. PLSQL Interview Questions
2. SQLite Show Tables
3. SQLite connection string
4. SQLite concat