Like other programming languages, PL/SQL provides a data structure called VARRAY, which can store collection of elements of the same type.
Varray stands for variable-size array. When you create varrays you must provide the maximum size for them.
All varrays consist of contiguous memory locations. The lowest address corresponds to the first element and the highest address to the last element.
Each element in a varray has an index associated with it. It also has a maximum size that can be changed dynamically.
Creating a VARRAY type.
Syntax :
TYPE varray_type_name IS VARRAY(n) of
Example :
TYPE names IS VARRAY(5) OF VARCHAR2(20);
Type percentage IS VARRAY(5) OF INTEGER;
SET SERVEROUTPUT ON;
DECLARE
TYPE v_array is varray(5) OF VARCHAR2(20);
messages v_array := v_array();
BEGIN
messages.extend;
messages(1) := 'Hello';
messages.extend;
messages(2) := 'PL/SQL';
messages.extend;
messages(3) := 'Learner';
dbms_output.put_line( messages(1) || ' ' || messages(2) || ' ' || messages(3));
END;
/
Hello PL/SQL Learner
PL/SQL procedure successfully completed.
In PL/SQL, the starting index for varrays is always 1. Varrays are one-dimensional arrays.
A varray is automatically NULL when it is declared. We must be initialized before its elements can be referenced.
Once the array is initialized you can extend it and add elements.
We access each element using the VARRAY variable and the index number. When the PL/SQL block ends, the memory used by the array is recovered automatically by the PL/SQL engine.
Another example with loop statement.
SET SERVEROUTPUT ON;
DECLARE
TYPE names IS VARRAY(5) OF VARCHAR2(20);
Type percentage IS VARRAY(5) OF INTEGER;
students names;
marks percentage;
total integer;
BEGIN
students := names('AAA', 'BBB', 'CCC', 'DDD', 'EEE');
marks:= percentage(99, 79, 78, 75, 91);
total := students.count;
dbms_output.put_line('Total '|| total || ' Students');
FOR i in 1 .. total
LOOP
dbms_output.put_line('Student: ' || students(i) || ' Marks: ' || marks(i));
END LOOP;
END;
/
Total 5 Students
Student: AAA Marks: 99
Student: BBB Marks: 79
Student: CCC Marks: 78
Student: DDD Marks: 75
Student: EEE Marks: 91
PL/SQL procedure successfully completed.
Here, we added all elements like above and printed one by one with looping statement.
Elements of a varray could also be a %ROWTYPE of any database table or %TYPE of any database table field.
SET SERVEROUTPUT ON;
DECLARE
CURSOR c_customer is
select name from customers;
TYPE v_names IS VARRAY(10) OF CUSTOMERS.NAME%TYPE;
names v_names := v_names();
counter integer :=0;
BEGIN
FOR n in c_customer
LOOP
counter := counter+1;
names.extend;
names(counter) := n.name;
dbms_output.put_line('Name('||counter ||'):'||names(counter));
END LOOP;
END;
/
Name(1):Sriniva
Name(2):Ahkil
Name(3):Gowtham
Name(4):Shiva
Name(5):Jagath
Name(6):Raju
Name(7):Bujji
Name(8):Ammulu
PL/SQL procedure successfully completed.
Take look into below examples as well.
SET SERVEROUTPUT ON;
DECLARE
TYPE my_varray IS VARRAY(10) OF NUMBER;
list_varray my_varray := my_varray(1,2,3,4,5,6);
BEGIN
dbms_output.put_line('list_varray.count is ' ||list_varray.count );
dbms_output.put_line('list_varray.limit is ' ||list_varray.limit );
dbms_output.put_line('list_varray.first is ' ||list_varray.first );
dbms_output.put_line('list_varray.last is ' ||list_varray.last );
list_varray.extend(2,4);
dbms_output.put_line('list_varray.last is ' ||list_varray.last );
dbms_output.put_line('list_varray(' || list_varray.last || ')= ' ||list_varray(list_varray.last) );
list_varray.trim(2);
dbms_output.put_line('list_varray.last is ' ||list_varray.last );
END;
/
list_varray.count is 6
list_varray.limit is 10
list_varray.first is 1
list_varray.last is 6
list_varray.last is 8
list_varray(8)= 4
list_varray.last is 6
PL/SQL procedure successfully completed.
Here, list_varray.extend(2,4) appends two copies on the fourth element to the collection like below.
list_varray(1):1
list_varray(2):2
list_varray(3):3
list_varray(4):4
list_varray(5):5
list_varray(6):6
list_varray(7):4
list_varray(8):4
list_varray.trim(2) will remove last two elements.
list_varray(1):1 list_varray(2):2 list_varray(3):3 list_varray(4):4 list_varray(5):5 list_varray(6):6
