Arrays in PL/SQL – Oracle PL/SQL Tutorial

Hey!! Friends, in this part of the PL/SQL tutorial, we are going to discuss the Arrays in PL/SQL, which is very important for the Oracle Pl/SQL tutorial. In every language, Arrays are playing a significant role.

If you missed the previous topics of this tutorial, the must-read. Loops in PL/SQL

Let’s get start ….

Here the list of topics that will cover in this article.

• Introduction
• How to creating a Varray Type
• FAQS

Introduction of arrays in PL/SQL

The Oracle’s PL/SQL programming language provides a data structure which is called the VARRAY. A varray uses to store an ordered collection of the data. However, it is often better to think of an array as collecting variables of the same type on the same list.

All varrays consist of adjacent memory locations. The lowest address is placed at the first element and the highest address to the last element.

For accessing the data elements stored in a varray type variable. We use this syntax Variable_name(index) where index starts from 1 for the first element and goes up to the current upper bound of elements, which means that the number of elements exists at that given point(not the maximum size of the array).

Note :The upper bound keeps on changing as we add or remove the elements from an array, but it cannot exceed the maximum size of the array.

Example:

11

22

19

48

40

Here, in the above example, we have a varray cells of size 8 and 5 data elements are stored in it. In this example, if you want to access the second element, then all you need to do it cell(2).

Creating a Varray Type

How to declare or create a VARRAY Type? A varray type is created or communicates with the CREATE TYPE statement. We have to specify the maximum size and the type of elements stored in the varray.

The basic syntax for creating a VARRAY type is −

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

Where,

• varray_type_name is a valid attribute name.
• n is a number of elements (max) in the varray,
• element_type is the data type of the elements of array.

We can be changed the maximum size of a varray using the ALTER TYPE statement.

The syntax for creating a VARRAY in the PL/SQL block is −

TYPE varray_type_name IS VARRAY(n) of <element_type>

For example:

TYPE vnamearray IS VARRAY(10) OF VARCHAR2(20); 
Type vagearray IS VARRAY(10) OF INTEGER;

In the example above, we have initialized the two varray types, the first one can hold 10 elements of type varchar2, and the second one is an integer type and also hold 10 elements.

Example: Array in PL/SQL Block:

DECLARE 
   type vnamesarray IS VARRAY(5) OF VARCHAR2(10); 
   type vmarks IS VARRAY(5) OF INTEGER; 
BEGIN 
   vnames namesarray := namesarray('John', 'Adam', 'Elly', 'Kate', 'Ron'); 
   marks vmarks := vmarks(42, 88, 62, 89, 97); 
   no_of_students := vnames.count; 
   dbms_output.put_line('Total '|| no_of_students || ' Students'); 
   FOR i in 1 .. no_of_students LOOP 
      dbms_output.put_line('Student: ' || vnames(i) || ' 
      Marks: ' || marks(i)); 
   END LOOP; 
END; 
/

Output:

Total 5 Students
Student: John Marks: 42
Student: Adam Marks: 88
Student: Elly Marks: 62
Student: Kate Marks: 89
Student: Ron Marks: 97
PL/SQL procedure successfully completed.

As we can see in the above example, we have declared two varray types variables, one for storing student names and another for storing student’s marks. And then, we have used them in a PL/SQL block for storing student names and marks.

How to Delete elements in VARRAY

For deleting all elements of a VARRAY, we use the DELETE method:

varray_name.DELETE;

For removing one element from the end of a VARRAY, we can use the TRIM method:

varray_name.TRIM;

If you want to remove n elements from the end of a VARRAY then you can use the TRIM(n) method:

varray_name.TRIM(n)

In this tutorial, you have learned about  VARRAY in Pl/SQL and how to manipulate and use the elements of a VARRAY effectively.

FAQs:

1. What is an Associative array in PL/SQL?

Ans: Associative arrays are single-dimensional, unbounded, and sparse collections of homogeneous elements. We can say that an associative array has elements which have the same data type or homogeneous elements.

2. What is the difference between an associative and VARRAY ?

Ans: Varrays are also called variable arrays and Index by tables called associative arrays.
Index by tables can be used in PL/SQL programs only; it cannot be stored in the database.
It can be stored in the database using equivalent SQL types and manipulated through SQL (but with less ease than nested tables)

3. When we can use the index table or associated arrays.

Ans: when your program needs small look up and you feel that, you do not need to store the collection in the database

4. What is the starting index of an Array in Oracle?

Ans: The starting index for any varrays is always 1.

In the above article, we discussed the Arrays in PL/SQL. An associated array will be discussed briefly in the Collection tutorial. After this, we will be moving to the PL/SQL Procedure and Functions. So, Please stay with us, For now

Thank You 🙂