Examples of Collection

Index-By Table:-An index-by table (also called associative array) is a set of key-value pairs. Each key is unique and is used to locate the 
corresponding value. The key can be either an integer or a string.

PLSQL Declaration Syntax for Index by table:-

declare 

TYPE <type_name> IS TABLE OF element_datatype [NOT NULL] INDEX BY subscript_type;
<variable_name> <type_name>;
begin

executable statement;

end;

/

Example1:

DECLARE
   TYPE salary IS TABLE OF NUMBER INDEX BY VARCHAR2(20);
   salary_list salary;
   name   VARCHAR2(20);
BEGIN
   -- adding elements to the table
   salary_list('Rajnish')  := 62000;
   salary_list('Minakshi')  := 75000;
   salary_list('Martin') := 100000;
   salary_list('James') := 78000;
   -- printing the table
   name := salary_list.FIRST;
   WHILE name IS NOT null LOOP
      dbms_output.put_line('Salary of ' || name || ' is ' || salary_list(name));
      name := salary_list.NEXT(name);
   END LOOP;
END;
/

output :-
Salary of James is 78000
Salary of Martin is 100000
Salary of Minakshi is 75000
Salary of Rajnish is 62000

Example2:

declare
type salary_ty is table of number index by varchar2(20);
salary_list salary_ty;       
name varchar2(20);
begin
--adding elements to the table
salary_list('Mahesh'):=5000;
salary_list('Ganesh'):=4000;
salary_list('Suresh'):=3000;
salary_list('Dinesh'):=2000;
salary_list('Yogesh'):=1000;
--printing the table
name:=salary_list.first;
while name is not null loop
dbms_output.put_line('Salary of :'||name||' is '||to_char(salary_list(name)));
name:=salary_list.next(name);
end loop;
end;
/


Output:-
Salary of :Dinesh is 2000
Salary of :Ganesh is 4000
Salary of :Mahesh is 5000
Salary of :Suresh is 3000
Salary of :Yogesh is 1000


Nested Tables:-A nested table is like a one-dimensional array with an arbitrary number of elements. However, a nested table differs from an 
array in the following aspects 
1) An array has a declared number of elements, but a nested table does not. The size of a nested table can increase dynamically.
2) An array is always dense, i.e., it always has consecutive subscripts.  A nested array is dense initially, but it can become sparse when elements
are deleted from it.

PLSQL Declaration Syntax for Nested table:- 

declare 

TYPE <type_name> IS TABLE OF element_datatype [NOT NULL];
<variable_name> <type_name>;

begin

executable statement;

end;

/

A nested table can be stored in a database column and so it could be used for simplifying SQL operations where you join a single-column table with 
a larger table. An associative array cannot be stored in the database.

Example:

DECLARE
   TYPE names_table IS TABLE OF VARCHAR2(10);
   TYPE grades IS TABLE OF INTEGER;
   names names_table;
   marks grades;
   total integer;
BEGIN
   names := names_table('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;
/

Output:-
Total 5 Students
Student:Kavita, Marks:98
Student:Pritam, Marks:97
Student:Ayan, Marks:78
Student:Rishav, Marks:87
Student:Aziz, Marks:92