Nested Tables

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

Posted in UncategorizedTagged