PL/SQL - Array
ARRAYS:
-------------
S1: array declaration syntax:
TYPE arrayname IS VARRAY(size) OF datatype;
-------------------------------------------------------------------
S2: array variable declaration syntax:
arrayvarname arrayname;
-------------------------------------------------------------------
S3: initialize the array variable syntax:
arrayvarname := arrayname(value1, value2, value3, ...)
-------------------------------------------------------------------
get the size of the array syntax:
arrayvarname.count
----------------------------------------------------------------
get an item from the arrayvar using the index:
arrayvarname(index)
Note:
the 1st index i 1
the last index is arrayvarname.count (size)
=====================================
SET ECHO ON;
SET SERVEROUTPUT ON;
DECLARE
/*array declaration*/
TYPE marksarray IS VARRAY(5) OF number(5,2);
/*array variable declaration*/
marks marksarray;
n number(2);
i number(2);
BEGIN
/*initialize the array variable*/
marks := marksarray(55.5, 66.5, 77.5, 88.5, 99.5);
/*get the size*/
n := marks.count;
DBMS_OUTPUT.PUT_LINE('Total size of the array: ' || n);
/*iterate the arrayvar*/
/*DBMS_OUTPUT.PUT_LINE('All Array Items');*/
FOR i IN 1 .. n LOOP
DBMS_OUTPUT.PUT_LINE(marks(i));
END LOOP;
END;
/
=======================================================
SET SERVEROUT ON;
SET ECH ON;
DECLARE
TYPE namearray IS VARRAY(4) OF varchar2(10);
name namearray;
TYPE marksarrray IS VARRAY(4) OF number(3);
marks marksarray;
BEGIN
FOR i IN 1..4 LOOP
name(i) := &name;
marks(i) := &marks;
END LOOP;
FOR i IN 1..4 LOOP
DBMS_OUTPUT.PUTLINE('Name' || name(i) || 'marks' || marks(i));
END LOOP;
END;
/
===========================================
SET ECHO ON;
SET SERVEROUTPUT ON;
DECLARE
TYPE marksarray IS VARRAY(5) OF number(5,2);
TYPE namearray IS VARRAY(5) OF varchar2(5);
marks marksarray;
name namearray;
n number(2);
m number(2);
i number(2);
BEGIN
marks := marksarray(55.5, 66.5, 77.5, 88.5, 99.5);
n := marks.count;
name := namearray( 's' , 'c','d', 'f', 'g');
DBMS_OUTPUT.PUT_LINE('Total size of the name array: ' || m);
DBMS_OUTPUT.PUT_LINE('Total size of the marks array: ' || n);
FOR i IN 1 .. n LOOP
DBMS_OUTPUT.PUT_LINE(marks(i));
DBMS_OUTPUT.PUT_LINE(name(i));
END LOOP;
END;
/
==========================================
Elements of a varray could also be a %ROWTYPE of any database table or %TYPE of any database table field. The following example illustrates the concept.
We will use the CUSTOMERS table stored in our database as −
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 −
Customer(1): Ramesh
Customer(2): Khilan
Customer(3): kaushik
Customer(4): Chaitali
Customer(5): Hardik
Customer(6): Komal
PL/SQL procedure successfully completed.
0 Comments