PLSQL

PL/SQL ARRAYS

PL/SQL ARRAYS

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:

  1. Varray_type_name is said to be the valid attribute name.
  2. n will be the maximum number of elements in the array.
  3. 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.