TOPICS (Click to Navigate)

Pages

Friday, April 21, 2017

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





1 comment: