PLSQL Varray

Varray is another kind of collection in PLSQL. It can be compared with a PLSQL table where the elements are identified and accessed by a subscript. The subscripts or indexes are automatically assigned continuous values. The subscript or index also defines the position of the element in the collection called PLSQL VARRAY.

Unlike nested table and associative array, the Varray is not unbounded. Its size has to be predefined in a PLSQL block where you need this collection for storing similar kind of data. The data stored in a Varray a can be from a data table or assigned with in PLSQL Block using PLSQL Loop. Once some elements are stored in the Varray can be extended as the need requires storing more elements.

Syntax for Declaration of PLSQL VARRAY

A Varray is declared in the declaration section of the PLSQL Block as given in the syntax below

TYPE type_name IS {VARRAY | VARYING ARRAY}( array_size ) OF element_type [NOT NULL];

varray_name type_name;

TYPE , VARRAY, VARYING ARRAY, OF, NOT NULL are keywords

type_name is the name of the Varray , array_size is the maximum length of the Varrray given as positive integer and  element_type is the inbuilt data type.

In the given example l_name_list is the name of the declared VARRAY  of 20 elements. Its data type is same as that of the last_name field in the customer table.

TYPE
l_name_list IS VARRAY(20) OF customer.last_name%TYPE; 
l_name_var_array l_name_list;// declare a variable of l_name_list VARRAY. 

Just like working of a nested tables,Varray elements are by default NULL after declaration of a Varray.  To use a Varray you must initialize it. Once initialization is done you can access its elements by using its indices for individual elements.  

Code Example

DECLARE 
CURSOR book_cur IS SELECT author FROM books;
TYPE auth_name_list IS VARRAY(10) OF books.author%TYPE; 
an_array auth_name_list  := auth_name_list();
v_cnt INTEGER := 0; 
BEGIN 
FOR an_rec IN book_cur LOOP 
    v_cnt := v_cnt + 1; 
    an_array.EXTEND; 
    an_array(v_cnt) := an_rec.author; 
    DBMS_OUTPUT.PUT_LINE (v_cnt||'. Author Name :'|| an_array(v_cnt)); 
END LOOP; 
END;

Methods used for PLSQL VARRAY and other Collections

Methods are the actions that you can execute on the collections created like associated array, nested table or Varray. These methods are-

EXTEND– This method is called to increase the size of the collection and add more elements to it.

EXISTS – This method checks whether a particular element at an index is existing in a collection. The method returns TRUE if the element index exists and FALSE if the element index is missing. This method is used to avoid the error that occurs if the element is unavailable like SUBSCRIPT_OUTSIDE_LIMIT exception.

COUNT- When you need to use all the elements of the collection, you need the count of elements. COUNT method returns the count of elements.

FIRST- This method returns the index of the first element of a collection.

LAST- This method returns the index of the last element of a collection.

DELETE- This method is used to delete elements of the collection. You can delete all, a range of elements or a single element. The element will only be deleted from the collection. If you have initialized the collection with data from a data table in database, data in this table will remain unaffected. In Varray DELETE works with no arguments and deletes all elements of Varray.

PRIOR- This method returns the index of previous element relative to the current element.

NEXT- This method returns the index of following element relative to the current element.

TRIM– If you want to remove last or a specific number of elements form the end of the collection you can use the TRIM method.

Code Example with Collection Methods

The code example uses the below described Parts Table

DECLARE 
CURSOR part_cur IS SELECT PARTNAME	 FROM PARTS;
TYPE part_name_list IS VARRAY(20) OF PARTS.PARTNAME%TYPE; 
pn_array part_name_list  := part_name_list();
v_cnt INTEGER := 0; 
BEGIN 
FOR pn_rec IN part_cur LOOP 
    v_cnt := v_cnt + 1; 
    pn_array.EXTEND; 
    pn_array(v_cnt) := pn_rec.PARTNAME; 
END LOOP; 
DBMS_OUTPUT.PUT_LINE ('Count of Parts : '|| pn_array.COUNT); 

IF pn_array.EXISTS(3) Then 
        DBMS_OUTPUT.PUT_LINE ('Part at index 3 in Parts collection : '||pn_array(3));
END IF;        

DBMS_OUTPUT.PUT_LINE ('First and Last Parts in Parts collection are : '||pn_array(pn_array.FIRST)||' and '||pn_array(pn_array.LAST));

DBMS_OUTPUT.PUT_LINE ('Part at index 7 in Parts collection : '||pn_array(7));
DBMS_OUTPUT.PUT_LINE ('Part Next to index 7 in Parts collection : '||pn_array(pn_array.NEXT(7)));
DBMS_OUTPUT.PUT_LINE ('Part Previous to index 7 is Parts collection : '||pn_array(pn_array.PRIOR(7)));

pn_array.TRIM(2);
DBMS_OUTPUT.PUT_LINE ('Count of Parts after Trimming 2 elements: '|| pn_array.COUNT); 

pn_array.DELETE();
DBMS_OUTPUT.PUT_LINE ('Count of Parts after DELETION: '|| pn_array.COUNT);
END;

admin