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;
/
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
Comments
Post a Comment