PL/SQL Collections [Associate array, Nested table, Varray] with Cursor and Table creation., Oracle

PL/SQL Collections [Associate array, Nested table, Varray] with Cursor and Table creation.

--EMPLOYEE TABLE
DROP TABLE EmployeeTab\\
CREATE TABLE EmployeeTab (id NUMBER(3),name VARCHAR2(20),location VARCHAR2(20),city VARCHAR2(20),sal NUMBER(8))\\
TRUNCATE TABLE EmployeeTab\\
INSERT INTO EmployeeTab VALUES (101,'Hennry','Main Road','Mumbai',14000)\\
INSERT INTO EmployeeTab VALUES (102,'Moana','MG Road','Bangalore',23000)\\
INSERT INTO EmployeeTab VALUES (103,'Rahul','Cross Road','Manali',2500)\\
INSERT INTO EmployeeTab VALUES (104,'Hanna','L&T Road','Delhi',50000)\\
INSERT INTO EmployeeTab VALUES (105,'Briana','TTK Road','Bangalore',92000)\\
INSERT INTO EmployeeTab VALUES (106,'Chris','L&T Road','Delhi',60400)\\
SELECT * FROM EmployeeTab\\

--PL/SQL BLOCK FOR COLLECTIONS
DECLARE 
   --common cursor used for all 3 collections.
   CURSOR EmpCur IS SELECT name FROM EmployeeTab;

--declaration for associate array 
   TYPE CharList_1 IS TABLE of EmployeeTab.name%type INDEX BY binary_integer; 
   NameList_1 CharList_1; 
   counter_1 integer :=0; 
   
   --declaration for nested table
   TYPE CharList_2 IS TABLE of EmployeeTab.name%type; 
   NameList_2 CharList_2 := CharList_2(); 
   counter_2 integer :=0; 
   
   --declaration for varray
   TYPE CharList_3 IS VARRAY(6) of EmployeeTab.name%type;  
   NameList_3 CharList_3 := CharList_3(); 
   counter_3 integer :=0; 
   
BEGIN 
   --adding elements and printing associate array  
   dbms_output.put_line('1.ASSOCIATE ARRAY');
   FOR EmpRec IN EmpCur LOOP 
      counter_1 := counter_1 +1; 
      NameList_1(counter_1) := EmpRec.name; 
      dbms_output.put_line('    Employee('||counter_1||'):'||NameList_1(counter_1)); 
   END LOOP; 
   
   --adding elements and printing nested table
   dbms_output.put_line('2.NESTED TABLE');
   FOR EmpRec IN EmpCur LOOP 
      counter_2 := counter_2 +1; 
      NameList_2.extend; 
      NameList_2(counter_2)  := EmpRec.name; 
      dbms_output.put_line('    Employee('||counter_2||'):'||NameList_2(counter_2)); 
   END LOOP; 
   
   --adding elements and printing varray
   dbms_output.put_line('3.VARRAY');
   FOR EmpRec IN EmpCur LOOP  
      counter_3 := counter_3 + 1; 
      NameList_3.extend; 
      NameList_3(counter_3)  := EmpRec.name; 
      dbms_output.put_line('    Employee('||counter_3 ||'):'||NameList_3(counter_3)); 
   END LOOP; 
END;


run \| edit \| history \| help	0

Please log in to post a comment.