PL/SQL Arrays

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
Advertisements

Post your Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s