Home arrow Oracle arrow Page 4 - Database Interaction with PL/SQL, part 3

NESTED TABLES and PL/SQL - Oracle

Jagadish Chatarji has been writing about database interactions with Oracle PL/SQL. The last part started on TYPE, RECORD, and TABLE declarations of PL/SQL. This one now goes further into TABLE, RECORD, and using them together. It will also introduce NESTED TABLES.

TABLE OF CONTENTS:
  1. Database Interaction with PL/SQL, part 3
  2. Using TABLE Without Interacting With Database
  3. Combining TABLE and RECORD
  4. NESTED TABLES and PL/SQL
  5. Using DML Commands on NESTED TABLES Within PL/SQL
By: Jagadish Chatarji
Rating: starstarstarstarstar / 18
June 07, 2005

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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.



 
 
>>> More Oracle Articles          >>> More By Jagadish Chatarji
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: