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