Arrays in PL/SQL (with its properties, syntaxes and examples)

In this article, we are going to learn an important topic in PL/SQL that is VARRAY DATA STRUCTURE. This data Structure is very important in any programming language. So let’s learn its behaviour in PL/SQL.
Submitted by Yash Kumar, on October 21, 2017

This Procedural programming language provides VARRAY DATA STRUCTURE for us to Store collection of same type of data.

Let's understand some of its properties following with its syntax and implementation.

Properties of VARRAY DATA STRUCTURE

Arrays are used to store more than one value in a single variable.
Values in arrays can be read or write through subscript or index.
In PL/SQL Index starts from 1 not from zero like in C, C++ or Java.
Arrays holds homogenous data.
In PL/SQL there is only one type of array i.e. Single Dimension.
In PL/SQL First we need to create VARRAY TYPE.

Let’s understand

We need to create an Array type.

A varray type is created with the CREATE TYPE statement. You must specify the maximum size and the type of elements stored in the varray.

CREATE OR [REPLACE] TYPE  <array_name >  IS VARRAY(n) of <element_type>;

Let’s create our own VARRAY data type of name height.

CREATE OR REPLACE TYPE Height IS  VARRAY(5) OF INTEGER;

Output of this statement will be like this...

Type created.
0.67 seconds

Let’s use this data type for storing heights of 4 students of our class

Declare          --------declaration section
i  integer;
hos Height;    ----see we are making variable hos of TYPE HEIGHT as we have created before
begin
hos:=Height(22,19,20,15);                ------initialization of varray
for i in 1..hos.count  loop                  ------hos.count will return 4 in this case 
dbms_output.put_line('height of  '||i||'is :'||hos(i));
end loop;
end;

Output

height of 1 is :22
height of 2 is :19
height of 3 is :20
height of 4 is :15

Statement processed.

0.02 seconds

The execution of program is very easy to understand. You might be thinking that why the index is not starting from zero? Let it clear, in PL/SQL the index starts with 1 and also this VARRAY stores data in contiguous memory allocation.

Comments and Discussions!

Load comments ↻

Top MCQs

Top Programs/Examples