NESTED TABLES and PL/SQL - Oracle

NESTED TABLE is an excellent way to store nested information. We can implement ONE to MANY relationships within each record (in the same table) by using this concept. Even though it looks bit confusing at the beginning, it would be very easy to implement once we master it. Best of all, it can integrate with PL/SQL in the same way we do it in SQL. Instead of speaking a lot, let us go through an example.

First of all, execute these scripts from within SQL *Plus

CREATE TYPE SubjectList AS TABLE OF VARCHAR2(20);
/
CREATE TABLE employee (name VARCHAR2(20), Subjects
  SubjectList) NESTED TABLE Subjects STORE AS subjects_tab;

The above script would create a TYPE named 'SubjectList' (which can hold any number of string values) within the database itself! That means, you can use that data type to create columns for any number of tables. And the most wonder is that, it need not be redefined in the PL/SQL program. That is the advantage of NESTED TABLE. Now let us insert some rows.

Insert into employee values ('win', SubjectList('VB','.NET','J2EE'));
Insert into employee values ('suni', SubjectList('VB','MSSQL','.NET'));
Insert into employee values ('jag', SubjectList('Oracle','.NET','Java'));

The above script would insert three rows into the table 'employee' with employee name and the subjects he/she is familiar with. Observe the constructor 'SubjectList' we are using within the INSERT statement. It is very important here, as we are working with TYPE in the form of a NESTED TABLE. How to get all the subjects of a particular employee? The following query provides the result:

select a.* from
table(select subjects from employee where name='jag') a

Now that we prepared data in SQL, we need to access the same in PL/SQL. Now you can go through the following PL/SQL script to access the data inserted above.

declare
    v_subjects SubjectList;
    v_name employee.name%type := '&name';
begin
    select Subjects into v_subjects
    from employee where name=v_name;
    dbms_output.put_line('Subjects of ' || v_name);
    dbms_output.put_line('-----------------------');
    for i in v_subjects.first .. v_subjects.last
    loop
        dbms_output.put_line(v_subjects(i));
    end loop;
end;

If you observe the above program, it doesn't have any definition of 'SubjectList'. Indeed it has been directly acquired from the database. I hope the rest is same.