Database Interaction with PL/SQL, part 3 - NESTED TABLES and PL/SQL
(Page 4 of 5 )
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.
Next: Using DML Commands on NESTED TABLES Within PL/SQL >>
More Oracle Articles
More By Jagadish Chatarji
