Varray in PL/SQL to manipulate elements in Program

PL/SQL Arrays in Oracle.

VARRAY is known as Variable sized array , A VARRAY is single-dimensional collections of elements with the same data type. A VARRAY always has a fixed number of elements(bounded) and never has gaps between the elements (not sparse). In other word we can say PL/SQL programming Language provides a data structure that is known as VARRAY, which is used to store a fixed size sequential collection of elements of the same type. All vaarays consist of contigious memory location . The lowest address represent the first element and the highest address represent to the last element.

Note : Array in PL/SQL is a part of Collection data type , we will explain PL/SQL Collection , Nested Table and it’s types in the next blog.

In Vaaray each element has an index associated with it and also maximum size can be changed dynamically.

Let’s create a Varray Type to understand how the type of element stored in the varray.

Syntax for creating Vaaray in schema is as below:

CREATE OR REPLACE TYPE varray_type_name is VARRAY(n) of <element_type>

 

Here varray_type_name is valid attribute name and n is the number of elements(maximum) in varray  . element_type is the data type of the elements in the Array.We can change the Maximum size of a varray by using Alter Command .

Example of creating varray by using Syntax :

 

Create Or Replace  TYPE dotcarray as VARRAY(3) OF VARCHAR2(15);

TYPE marks is VARRAY(5) OF INTEGER;

We can understand the use of VARRAY with below program.

DECLARE

Type dotcarray IS VARRAY(5) of VARCHAR2(15);

TYPE MARKS IS VARRAY(5) OF INTEGER;

Student_name dotcarray;

Total_mark       marks;

Total                 integer;

BEGIN

Student_name := dotcarray( ‘Prashant’, ’Deepak’ ,’ Gyan’ ,’Krishna’,’Ashish’);

total_mark := marks (90,91,92,95,96);

total := student_name.count;

dbms_out.put_line(‘Number ‘ || total || ‘students’);

For I in 1 .. total

LOOP

dbms_output.put_line(‘Student: ‘ || student_name(i) || ‘ Marks obtained : ‘ || total_marks(i));

END LOOP;

END;

/

 As we put Hard Coded values so when we execute above code , it will give the 5 result as below :

 Total 5 Students

Student: Prashant: Marks Obtained: 90

Student: Deepak:  Marks Obtained: 91

Student: Gyan  Marks: Obtained: 92

Student: Krishna  Marks: Obtained: 95

Student: Ashish: Marks: Obtained :96

 

Note : Index for Starting  varray is 1 , we can intialialize the varray elements by using constructor method of the varray type which has the same name as the vaaray. Varray is 1dimensional array.

 

Let’s Take one example of Varray by using Table and different data types in PL/SQL Block. We can have elements of a varray of %ROWTPE of any tables in Database table field.

 

Suppose we have one table employee with below columns and Records.

Select * from customers;  

We use Varray to write one program by using Cursor For Loop to retrieve the employee name with it’s element.

 

DECLARE

   CURSOR c1 is

   SELECT employee_name FROM employee;

   type dotc_list is varray (6) of customers.name%type;

   employee_name_list dotc_list := dotc_list();

   counter integer :=0;

BEGIN

   FOR i IN c1 LOOP

      counter := counter + 1;

      employee_name_list.extend;

      employee_name_list(counter)  := i.employee_name;

      dbms_output.put_line('Employee('||counter ||'):'||employee_name_list(counter));

   END LOOP;

END;

/

Output of the Above Program will be as below with elements.

 

Employee(1): Ashish 

Employee(2): Prashant

Employee(3): Deepak    

Employee(4): Krishna

Employee(5): Gyan

If we want to select other column like age for output in same program we can do by declaring one more Varray of Integer Types or by simply using %type to automatically get the data type of that column.

This is all All about VARRAY in next blog we will explain Collection and Nested Table .

Note : Advantage of VARRAY is that it's occupies less space in database as VARRAY is stored with the other columns in a row.

Please go through this Blog and let me know if you have any doubts.

Thanks