Opening Hours :7AM to 9PM
CREATE OR REPLACE TYPE varray_type_name IS VARRAY(n) of <element_type>Where,
CREATE Or REPLACE TYPE namearray AS VARRAY(3) OF VARCHAR2(10); /Type created.
TYPE namearray IS VARRAY(5) OF VARCHAR2(10); Type grades IS VARRAY(5) OF INTEGER;Let us now work out on a few examples to understand the concept −
The following program illustrates the use of 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; /
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 the 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 the SQL prompt, it produces the following result −