(vii) PL/SQL arrays

The PL/SQL programming language provides a data structure called Varray, which stores a constant-sized contiguous set of elements of the same type. Varray is used to store an ordered data set, but it is often more useful to think of an array as a set of variables of the same type.
In a Varray each element has an index associated with it. It also has the ability to dynamically change a maximum size.

First, create the Varray type
1. One type of Varray is created with the CREATE TYPE statement. You must specify a maximum length and store the type of the Varray element.
Create a basic syntax for a Vrray type:
CREATE OR REPLACE TYPE varray_type_name is Varray (n) of <element_type>

Varray_type_name is a valid property name.
n is the number of Varray elements (maximum value)
Element_type is the data type of the elements of the array.

2. The maximum length of the Varray can be changed using the ALTER TYPE statement.
For example:
CREATE Or REPLACE TYPE NameArray as Varray (3) of VARCHAR2 (10);
/

Type created.

3. PL/SQL blocks create the basic syntax for the Vrray type:
TYPE Varray_type_name is Varray (n) of <element_type>
Example:
TYPE NameArray is Varray (5) of VARCHAR2 (10);
Type Grades is Varray (5) of INTEGER;

Ii. examples
Example 1

DECLARE type Namesarray is Varray (5) of VARCHAR2 (Ten); Type grades is Varray (5) of INTEGER;   Names Namesarray;   Marks grades; Total integer; BEGIN names:= Namesarray ('Kavita','Pritam','Ayan','Rishav','Aziz'); Marks:= Grades (98, the, +, the, the); Total:=Names.count; Dbms_output.put_line (' Total'|| Total | |'Students'); For Iinch 1.. total LOOP Dbms_output.put_line ('Student:'|| Names (i) | |'Marks:'| | marks (i));END LOOP; END;//when the above code is executed at the SQL prompt, it produces the following results:Student:kavita Marks:98Student:pritam Marks: theStudent:ayan Marks: +Student:rishav Marks: theStudent:aziz Marks: thePL/sql procedure successfully completed.

Please note:
In an Oracle environment, the starting index of a variable group is always 1
You can initialize the Varray element by using the Varray type, which has the same name as the variable-length array
A variable group is a one-dimensional array
A varray when it is declared automatically as a null value, it must be initialized before its elements can be referenced

Example 2
The Varray element can also be any field%type any database table or%rowtype database table. The following example illustrates this concept:
We will use the Customers table stored in the database:
Select * from Customers;

+----+----------+-----+-----------+----------+
| ID | NAME | Age | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 |  Ramesh | 32 |  Ahmedabad | 2000.00 |
| 2 |  Khilan | 25 |  Delhi | 1500.00 |
| 3 |  Kaushik | 23 |  Kota | 2000.00 |
| 4 |  Chaitali | 25 |  Mumbai | 6500.00 |
| 5 |  Hardik | 27 |  Bhopal | 8500.00 |
| 6 |  Komal | 22 |  MP | 4500.00 |
+----+----------+-----+-----------+----------+

DECLARE CURSOR c_customers isSELECT name from customers; Type C_list isVarray (6) of Customers.name%type; Name_list c_list:=c_list (); Counter integer:=0; BEGIN for N in c_customers LOOP counter:= Counter +1;      Name_list.extend; Name_list (counter):=N.name; Dbms_output.put_line ('Customer ('|| Counter | |'):'||name_list (counter)); END LOOP; END;//when the above code is executed at the SQL prompt, it produces the following results:Customer (1): Ramesh Customer (2): Khilan Customer (3): Kaushik Customer (4): Chaitali Customer (5): Hardik Customer (6): KOMALPL/sql procedure successfully completed.

(vii) PL/SQL arrays