Collections in PLSQL with Sample Examples

 

 -------------------COLLECTIONS>> 

 -------------------1. VARRAYS.-----------

DECLARE 

TYPE  VARRY_ENAME IS VARRAY(5) OF VARCHAR2(100);

TYPE VARRAY_EID IS VARRAY(5)  OF INTEGER;

 VAR1_ENAME VARRY_ENAME;

 VAR2_EID  VARRAY_EID;

 V_TOTAL INTEGER ;

 BEGIN

   VAR1_ENAME := VARRY_ENAME('AZEEZ','JUNAED', 'RASHID', 'REHAN');--GROUP OF ELEMENTS OF SIMILAR DATATYPE

   VAR2_EID   := VARRAY_EID (1, 2,3,4);

 V_TOTAL := VAR1_ENAME.COUNT;--4

 FOR I IN 1 ..V_TOTAL LOOP  --4

 DBMS_OUTPUT.PUT_LINE ( 'EMPLOYEE NAME : '|| VAR1_ENAME(I) || ' EMPLOYEE ID IS : '|| VAR2_EID(I));

 END LOOP;

END;

  /

 

 

 --ASSOCIATIVE ARRAY/INDEX BY TABLE

A PL/SQL table is very similar to an array in C or Pascal. Like a record, the PL/SQL table must be declared first as a type declaration and then as a variable of the user-defined type, as shown below.

DECLARE

   TYPE Student_SSN_tabtype IS TABLE OF

       integer (9)

       INDEX BY binary_integer;

   Student_SSN_table      Student_SSN_tabtype;

Like records, the PL/SQL table is a composite datatype. The number of rows that can be held in a PL/SQL table is limited only by the range of values for the INDEX variable. The PL/SQL table is indexed using a signed integer and can be navigated either forward or backward (unlike cursors, which can only be moved forward).

As of Oracle9i PL/SQL tables  were officially named ASSOCIATIVE ARRAYS.  Most developers call associative arrays PL/SQL tables because they can not exist in the database, only in PL/SQL memory structures.  The advantage over nested tables and VARRAYs is that a PL/SQL table does not have to be extended to add elements, nor does it have to be initialized.  Best of all, elements are added in any order, in any position in the table. 

PL/SQL tables before Oracle9i could only be indexed by BINARY_INTEGER, but from Oracle9i and beyond they can be indexed either by BINARY_INTEGER or a string type.  You can conceptualize a PL/SQL table as a two-column table, the first being the index and the second being the data element.  Like the other collection types, the index value is used to locate the data element. 

In the example below, a PL/SQL table is defined and a cursor is used to load the collection and then read the elements out of the collection:

--CREATE OR REPLACE TYPE V_IDXTL_ENAME IS TABLE OF  VARCHAR2   INDEX BY VARCHAR2 (10);

DECLARE 

TYPE V_IDXTL_SAL IS TABLE OF NUMBER INDEX BY VARCHAR2(10);

SALARY_LIST V_IDXTL_SAL;

NAME VARCHAR2(100);

BEGIN

SALARY_LIST('AJAY') :=1000;

SALARY_LIST('SALMAN') :=2000;

SALARY_LIST('VIJAY') :=3000;  

SALARY_LIST('SACHIN') :=4000;

--PRINT

NAME  :=SALARY_LIST.FIRST;

WHILE  SALARY_LIST IS NOT NULL

 LOOP

 DBMS_OUTPUT.PUT_LINE ('SALARY OF ' ||NAME  ||' IS  :'|| SALARY_LIST(NAME) );

 NAME := SALARY_LIST.NEXT(NAME);

 END LOOP;

 END;

 

 

--------------------------------------------------------NESTED TABLES------------------------------

TYPE type_name IS TABLE OF element_type [NOT NULL];  --NESTED TABLE

--NESTED TABLES ARE SIMILAR TO VARRAYS IN FUNCTIONALITY EXCEPT IT IS UNBOUNDED,WHEREAS VARRAYS ARE BOUNDED

--NESTED TABLES ARE SIMILAR TO INDEX BY TABLES IN SYNTAX WISE  EXCEPT THE "INDEX BY CLAUSE" WHICH IS PRESENT IN INDEX BY TABLE DEFINITION.

  

DECLARE 

  --TYPE V_IDXTL_SAL IS TABLE OF NUMBER INDEX BY VARCHAR2(10); --INDEX BY TABLE

   TYPE sname_table IS TABLE OF VARCHAR2(10);                 --NESTED TABLE

   TYPE marks_table IS TABLE OF INTEGER;  

   names sname_table; 

   marks marks_table; 

   total integer; 

BEGIN 

   names := sname_table('Akshay', 'Rohit', 'Sachin', 'virat', 'siraj'); 

   marks:= marks_table(100, 88, 87, 90, 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; 

/  

--------------------------------------------------------------------------------------

DECLARE 

   CURSOR c_customers is   SELECT  Ename,DNAME FROM EMPL E, DEP D WHERE E.DEPNP=D.DEPNO;  

   TYPE c_list IS TABLE of EMPL.ENAME%type;  --NESTED TABLE 

   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.ENAME; 

      dbms_output.put_line('Customer('||counter||'):'||name_list(counter)); 

   END LOOP; 

END; 

/