VARRAY

Example 1

The following program illustrates using varrays:

DECLARE

   type namesarray IS VARRAY(5) OF VARCHAR2(10);

   type grades IS VARRAY(5) OF INTEGER;

   names namesarray;

   marks grades;

   total integer;

BEGIN

   names := namesarray('Kavita', 'Pritam', 'Ayan', 'Rishav', 'Aziz');

   marks:= grades(98, 97, 78, 87, 92);

   total := names.count;

   dbms_output.put_line('Total '|| total || ' Students');

   FOR i in 1 .. total LOOP

      dbms_output.put_line('Student: ' || names(i) || '

      Marks: ' || marks(i));

   END LOOP;

END;

/

When the above code is executed at SQL prompt, it produces the following result:

Student: Kavita  Marks: 98

Student: Pritam  Marks: 97

Student: Ayan  Marks: 78

Student: Rishav  Marks: 87

Student: Aziz  Marks: 92

PL/SQL procedure successfully completed.

Example 2

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 |

+----+----------+-----+-----------+----------+

Following example makes use of cursor, which you will study in detail in a separate chapter.

DECLARE

   CURSOR c_customers is

   SELECT  name FROM customers;

   type c_list is varray (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 SQL prompt, it produces the following result:

Customer(1): Ramesh

Customer(2): Khilan

Customer(3): kaushik   

Customer(4): Chaitali

Customer(5): Hardik

Customer(6): Komal

PL/SQL procedure successfully completed.