Nested Tables
Table within a table
A table is represented as a column within another table
There is no limit to the number of rows in the nested table for each row in the main table.
Basically used for mapping master-detail relationships between tables.
i.e. In the parent table there would be one column, which will store the location of the nested table.
Steps for Creating Nested Tables
Create a type which will hold information for a single line item (this represents the nested table details)
Create Type of Table to hold details for the multiple rows of the nested table (this will be the nested details - nested table type)
Create the master table with one of the columns which will hold the nested table type
Creating a Nested Table
SYNTAX
CREATE TYPE
AS TABLE OF ;
Example --
There is main table Fare_Tab1.
It has columns such as Route_Coe,Route_Desc,Origin,Destination, Firts_Fare, Bus_Fare,Eco_Fare and journey_hrs.
There is a type as table Fs_Nst_Type.
This type table holds column such as Flightno, Airbusno,Deprt_Time, Flight_Day1 and Flight_Day2.
Now we are trying to establish the relation between Fare_Tab1 table and this type table Fs_Nst_Type.
One route_code from Fare_Tab1 table can hold multiple Flightno, Airbusno,Deprt_Time, Flight_Day1 and Flight_Day2. For this purpose we will create a column in Fare_Tab1 table which will show details of the Fs_Nst_Type for a particular route_code. So that column actually represents the nested table.
Step 1 -- Create a type Flight_Sch_type to hold details for a single flight.
Create or replace type Flight_Sch_Type as OBJECT
(Flightno char(4),
Airbusno char(4),
Deprt_time char(6),
Flight_day1 number(1),
Flight_day2 number(1));
Step 2 -- Create a type of table which will hold the multiple rows of the nested table.
Create type Fs_Nst_Type
as TABLE of Flight_Sch_Type;
Step3 -- Create the main table which will have the nested table through a column of the main table
Create table Fare_Tab
(
Route_Code char(7),
Route_Desc varchar2(30),
Origin varchar2(15),
Destination Varchar2(15),
First_fare number(5),
Bus_Fare number(5),
Eco_Fare number(5),
Journey_Hrs char(5),
Flight_Sch_Det Fs_Nst_Type
)
Nested Table Flight_Sch_Det store as Fs_Store_Tab;
Fs_Store_Tab is system generated table for oracle's reference.
Inserting records in the nested table through the main table
SYNTAX
INSERT INTO
VALUES(,…
nested_table_type(nested_column(attribute1_value>,...),
nested_column(attribute1_value>,…
)));
Insert into Fare_Tab
Values ('Goa-Ban', 'Goa - Bangalore', 'Goa', 'Bangalore', 450, 300, 200, 3,
Fs_Nst_Type ( Flight_Sch_Type('F2', 'AB01',' 9:00',1,3),
Flight_Sch_Type('F3','AB02', '11:00', 3, 5) ) );
Here in this insert statement for one route_code 'Goa-Ban' 2 nested rows of F2 and F3 are inserted.
Displaying the data of the nested table columns --
SYNTAX in Oracle 8i
SELECT ,
FROM THE (SELECT
FROM
WHERE ) ,
WHERE ;
To display the nested row for the route code 'Goa-Ban' for flighno F1
Select NT.flight_day1, NT.flight_day2 ,Nt.Deprt_Time, route_code
from THE (Select Flight_Sch_Det from
Fare_Tab
where route_code = 'Goa-Ban')
NT, Fare_Tab
where NT.Flightno = 'F2';
In Oracle 9i Table function is used instead of THE clause which makes the query very easy to understand
select route_code, NT.flight_day1, Nt.flight_day2,NT.Deprt_Time,NT.flightno
from Fare_Tab, Table(Fare_Tab.Flight_Sch_Det) NT
where route_code = 'Goa-Ban' And NT.flightno = 'F3'
So Syntax in 9i is
Select master_table_cols, Alias.Neste_Table_Cols
From Master_Table, Table(Master_Table.Nested_Table) Alias
To see all attribute columns of the Nested Table we can use * with alias name of the nested table –
select route_code, NT.*
from Fare_Tab1, Table(Fare_Tab1.Flight_Sch_Det) NT
where route_code = 'Goa-Ban' And NT.flightno = 'F3'
Updating the nested row in 8i
SYNTAX
UPDATE THE (SELECT
FROM
WHERE )
SET . = ,...,
WHERE ;
To change the value of flight_day1 from 3 to 5 for the Flightno F3 on the route_code 'Goa-Ban'
Update THE
(Select Flight_Sch_Det
from Fare_Tab1 F
where F.route_code = 'Goa-Ban')
NT
Set
NT.Flight_day1 = 5
where NT.Flightno = 'F3';
F is the alias name for the master table Fare_Tab
NT is the alias name for the nested table.
In 9i the same Update statement will be as follows –
To change the Flightno of the nested table to F11
update TABLE(select FLIGHT_SCH_DET
from Fare_Tab
where route_code = 'Goa-Ban') NT
Set NT.Flightno = 'F11'
where NT.Flight_Day2 = 5;
Syntax in 9i –
Update TABLE(select nested_table_name)
From Master_Table
Where master_table condition) Alias for Nested_Table
Set Alis.Nested_Col =
Where Alias.Nested_Col Condition;
Deleting a row from the nested table
SYNTAX in 9i
DELETE FROM THE
(SELECT
FROM
WHERE )
WHERE ;
To delete the details of flightno 'F2' for the route_code 'Goa-Ban'
Delete From THE
(Select Flight_Sch_Det
from Fare_Tab1 F
where F.Route_Code = 'Goa-Ban')
NT
where NT.Flightno = 'F2';
Deleting a row in 9i –
To delete a nested row of Flight_day2 having value 5 for a route_code ‘Goa-Ban’
Delete Table(select FLIGHT_SCH_DET
from Fare_Tab
where route_code = 'Goa-Ban') NT
where NT.Flight_Day2 = 5;
Inserting a nested row to route_code ‘Goa-Ban’ – (9i)
Insert into TABLE (Select FLIGHT_SCH_DET
from Fare_Tab
where route_code = 'Goa-Ban')
Values
(Flight_Sch_Type ('F22','AB02','3',3,5))
To see number of records in the nested table
select count(*) from Fare_Tab, Table(Fare_Tab.Flight_Sch_Det) NT;
To display route_code wise count of nested rows.
select f.route_code, Count(*)
from fare_tab f, TABLE(F.Flight_Sch_Det ) v
group by f.route_code
Difference Between VARRAYs and Nested Tables
VARRAYs
Maximum size specified at time of definition
Data is stored in-line
Retain their ordering and subscripts when stored in database
Nested Tables
No maximum size
Data is stored out-of-line in a store table
Do not retain ordering and subscripts when stored in database
When to use Nested Tables instead of VARRAYs
When the order of the data elements is not important
When indexing is required on an attribute of the nested table type
When there is no set limit to the number of entries for the data elements
When the data elements need to be queried
Discussion about this article
| Author | Body |
Rahul
8/29/2009 8:42 PM
|
Please provide feedback about this article here.
To participate in this discussion Sign up for free membership of 24x7code.
To Signup click on Login , Use create user link & the follow the instructions.
Thank you
|