Nested tables in object relational database systems with example

Nested tables in object relational database, Nested table examples in Oracle, How to create a nested table in oracle? How to insert records into a nested table?

Nested Tables

• Nested table is a table within a table.
• An ordered group of items of type TABLE are called nested tables.
• Nested tables can contain multiple columns.
• Nested tables are unbounded, ie., it can grow in size. In other words, we don’t specify the maximum size as we do for other data types.
• Within the database, nested tables can be considered one-column database tables. Oracle stores the rows of a nested table in no particular order. But, when you retrieve the nested table into a PL/SQL variable, the rows are given consecutive subscripts starting at 1. That gives you array-like access to individual rows.
• A nested table can be either used in a PL/SQL block to declare as a variable temporarily or as a column of a table which can persistently store the data in the database. 

Example:

The following example shows the creation of nested tables and manipulation of data in nested tables in Oracle.

First we create a user defined type Items_Purchased_Typ to model the items that are purchased by a customer in a super market.

CREATE TYPE Items_Purchased_Typ AS OBJECT ( Item_ID CHAR(5), Quantity NUMBER(3), Price NUMBER(6,2) ); /

Then we create a table type Items_Purchased_Tab as a table of the type that we have created earlier.

CREATE TYPE Items_Purchased_Tab AS TABLE OF Items_Purchased_Typ; /

Here, we created a table Bills to store the information regarding purchase of all customers’ bills. Bills table has the attribute Billed_Items as nested table type.

CREATE TABLE Bills ( Cust_ID CHAR(10), Name VARCHAR(30), Phone NUMBER(10), Bill_Date DATE, Billed_Items Items_Purchased_Tab ) NESTED TABLE Billed_Items STORE AS Billed_Items_Tab;

Here, the NESTED TABLE … STORE AS … clause assigns a name for the nested table.

We can insert a record into Bills as follows;

INSERT INTO Bills VALUES ( ‘C1001’, ‘Raghu’, 9878676500, ’04-JUN-2016’, Items_Purchased_Tab ( Items_Purchased_Typ (‘I101’, 2, 300.00), Items_Purchased_Typ (‘I106’, 2, 30.00), Items_Purchased_Typ (‘I110’, 1, 500.00)));

                                                                      ************

Go to Collection Types page

Go to Object database systems page

Go to How to access data from nested table in Oracle page