PL/SQL provides a data structure called VARRAY, that can store fixed-size sequential collection of elements of the same type. It is used for storing an ordered collection of data. VARRAYS consists of contiguous memory location. That means the lowest address corresponds to the first element and highest address corresponds to the last element. Array is said to be a part of collection type of data and it stand for variable-size arrays. And then each element in a VARRAY has an index associated with it. It then has maximum size which can be changed dynamically.
Creating a VARRAY Type
It is created with CREATE TYPE statement. We must also specify the maximum size and the type of elements that is stored in VARRAY.
Syntax:
CREATE OR REPLACE TYPE varray_type_name IS VARRAY(n) of <element_type>
Where:
- Varray_type_name is said to be the valid attribute name.
- n will be the maximum number of elements in the array.
- element_type is said to be the data type of the elements in the array.
Maximum size of a varray can be changed using the ALTER TYPE statement.
Example:
CREATE Or REPLACE TYPE namearray AS VARRAY(5) OF VARCHAR2(20);
/
Output:
Type created.
Basic syntax for creating a VARRAY type within a PL/SQL block is:
TYPE varray_type_name IS VARRAY(n) of <element_type>
Example:
TYPE marks IS VARRAY(10) OF INTEGER.
Let us see an example in arrays:
DECLARE
type namesarray IS VARRAY(5) OF VARCHAR2(10);
type grades IS VARRAY(5) OF INTEGER;
names namesarray;
salary average;
total integer;
BEGIN
names := namesarray('Sai', 'Ranjith', 'Gayathri', 'shakti', 'Rishabh');
salary:= average(9800, 9700, 7800, 8700, 9200);
total := names.count;
dbms_output.put_line('Total '|| total || ' teachers');
FOR i in 1 .. total LOOP
dbms_output.put_line('teacher: ' || names(i) || '
Salary: ' || salary(i));
END LOOP;
END;
/
Output:
Total 5 teachers
teacher: Sai salary: 9800
teacher: Ranjitha salary: 9700
teacher: Gayathri salary: 7800
teacher: shakti salary: 8700
teacher: Rishabh salary: 9200
PL/SQL procedure successfully completed.
Varrays are said to be one dimensional Arrays and there starting index always starts from 1. This is automatically said to be NULL when declared. we can initialize Varrays using the constructor method.