PL/SQL Arrays

Creating a Varray Type 

The CREATE TYPE statement creates a varray type. The maximum size and type of elements stored in the varray must be specified.

Let us now look at the syntax to create a varray at the schema level: 

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

where, 

varray_type_name is a valid attribute name, 

n denotes the varray's maximum number of elements, and 

element_type is the data type of the array's elements.

The ALTER TYPE statement can be used to change the maximum size of a varray.

For example: 

CREATE TYPE sname IS VARRAY(30) OF INTEGER;
/ 
Type created.

In the above example, sname is the name of a varray which is of INTEGER type and the maximum number of elements can be 30. 

Let us now look at the syntax to create a varray within a PL/SQL block: 

TYPE varray_type_name IS VARRAY(n) of <element_type>;

For example:

TYPE marks IS VARRAY(30) OF INTEGER;
TYPE employees IS VARRAY(20) OF VARCHAR2(10);

Example of varray using INT data type

The following program illustrates the use of INT datatype: 

DECLARE 
  TYPE numbers IS VARRAY(5) OF INTEGER; 
  set_of_number numbers; 
BEGIN 
  set_of_number:= numbers(10, 20, 30, 40, 50); 
  dbms_output.put_line('Printing the array'); 
  FOR i in 1 .. 5 LOOP 
      dbms_output.put_line(i|| ' : ' ||set_of_number(i)); 
  END LOOP; 
END; 
/

Output:

Example of varray using CHAR data type

CHAR data type is used to store a fixed-length alphanumeric string. It can store up to a range of 1 to 2000 bytes. Even if the data does not use the space, it takes up the entire memory size.

Note: CHAR and VARCHAR both have the same functionalities. The only difference in VARCHAR is the range increases to 1 to 4000 bytes.

The following program illustrates the use of CHAR datatype: 

DECLARE 
  TYPE names IS VARRAY(5) OF CHAR(10); 
  set_of_names names; 
BEGIN 
  set_of_names:= names('Ram','Rahul','Vijay','Dhruv','Amit'); 
  dbms_output.put_line('Printing the array'); 
  FOR i in 1 .. 5 LOOP 
      dbms_output.put_line(i|| ' : ' ||set_of_names(i)); 
  END LOOP; 
  dbms_output.put_line('Size of names array' ||set_of_names.count); 
END; 
/

Output:

Example of varray using VARCHAR2 datatype 

VARCHAR data type is also used to store a fixed-length alphanumeric string. It can store up to a range of 1 to 4000 bytes. Even if the data does not use the space, it releases the unused space in the memory.

The following program demonstrates the use of VARCHAR2 datatype: 

DECLARE 
  TYPE names IS VARRAY(10) OF VARCHAR2(10); 
  set_of_names names; 
BEGIN 
  set_of_names:= names('John','Rahul','Mihir','Dhruv','Jainish'); 
  dbms_output.put_line('Printing the array'); 
  FOR i in 1 .. 5 LOOP 
      dbms_output.put_line(i|| ' : ' ||set_of_names(i)); 
  END LOOP; 
END; 
/

Output:

Example of varray using %ROWTYPE or %TYPE

A varray's elements could also be a %ROWTYPE of any database table or a %TYPE of any database table field. The following example exemplifies the idea.

Consider the students table having the student's data like RollNo, Name, City, Phone, Age.

Query says: 

From the students' table, store the name column data in the varray and display the result. 

Code: 

DECLARE 
  CURSOR c_students is 
  SELECT name FROM students; 
  type c_list is varray (8) of students.name%type; 
  name_list c_list := c_list(); 
  counter integer :=0; 
BEGIN 
  FOR n IN c_students LOOP 
      counter := counter + 1; 
      name_list.extend; 
      name_list(counter)  := n.name; 
      dbms_output.put_line('Student('||counter ||'):'||name_list(counter)); 
  END LOOP; 
END; 
/ 

Output:

Must Read SQL Clauses

FAQs

1. What is a cursor in PL/SQL?
   A SQL cursor is a database object that retrieves data one row at a time from result sets. When data must be updated row by row, the cursor in SQL can be used. A SQL cursor is a database object that is used to retrieve data one row at a time from a result set.
    
2. What is the difference between varray and associative array in PL/SQL?
   A VARRAY is a collection of elements with the same data type that is one dimension in size. In contrast to an associative array and a nested table, a VARRAY always has a fixed number of elements (bounded) and never has gaps between the elements (not sparse).
    
3. What is the major difference between %ROWTYPE and %TYPE?
   When a query returns a whole row of a table or view, %ROWTYPE is used, whereas %TYPE  is used when a query returns a column of different tables or views. 

Key Takeaways 

This article discusses how to declare and use varray in PL/ SQL and various examples using different data_types.

Don't stop here, Ninja; check out the Top 100 SQL Problems to get hands-on experience with frequently asked interview questions and land your dream job.

Check out this problem - Search In Rotated Sorted Array

Furthermore, to get hang on and practice different types of questions on SQL queries, you can visit Database Management Systems FREE Guided path. If you want to learn DBMS from an industry expert, check out a fantastic course on DBMS.