PLSQL Nested Tables

Index-By-Table

An index-by table (also called an 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.
Syntax :
TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY subscript_type;
table_name type_name;
Example :
Following example how to create a table to store integer values along with names and later it prints the same list of names.

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 ' || TO_CHAR(salary_list(name)));
      name := salary_list.NEXT(name);
   END LOOP;
END;
/

When the above code is executed at SQL prompt, it produces following result:

Salary of Rajnish is 62000
Salary of Minakshi is 75000
Salary of Martin is 100000
Salary of James is 78000
PL/SQL procedure successfully completed.

PLSQL Table:

PL/SQL tables are PL/SQL’s way of providing arrays. Arrays are like temporary 

tables in memory and thus are processed very quickly. It is important for you to

realize that they are not database tables, and DML statements cannot be issued 

against them. This type of table is indexed by a binary integer counter (it cannot 

be indexed by another type of number) whose value can be referenced using the 

number of the index.

Remember that PL/SQL tables exist in memory only, and therefore don’t exist in

any persistent way, disappearing after the session ends. 

Example showing First and Next method of PL/SQL tables

 Declare
 Type my_student_table is table of varchar2(20)
 Index by binary_integer;
 Var_of_student my_student_table;
 Index_value number;
 Begin
 For student_rec in (select * from student) 
 loop
  Var_of_student(student_rec.rollno) := student_rec.student_name;
 End loop;

 Index_value := var_of_student.first;
 Loop
 Exit when index_value is null;
  Dbms_output.put_line (index_value || ' ' ||var_of_student(index_value));
  Index_value := var_of_student.next(index_value);
 End loop;
End;
/ 

second example of PL/SQL Table


--last_name_type TYPE is declared based on the column LAST_NAME of the STUDENT table.
--last_name_tab is the actual index-by table declared as LAST_NAME_TYPE.
DECLARE
CURSOR name_cur IS SELECT last_name FROM student WHERE rownum <=10;
TYPE last_name_type IS TABLE OF student.last_name%TYPE
INDEX BY BINARY_INTEGER;
last_name_tab last_name_type;
v_counter INTEGER := 0;
BEGIN
For name_rec IN name_cur LOOP
v_counter := v_counter + 1;
last_name_tab(v_counter) := name_rec.last_name;
DBMS_OUTPUT.PUT_LINE('last_name('||v_counter||'): '||

last_name_tab(v_counter));
END LOOP;
END;
OUTPUT will be like:
last_name(1): Roy
last_name(2): Brit