Friday, 21 April 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:

Nlp and machine learning online lectures

Links to Natural Language Processing (NLP) and Data Science online lectures An introduction to Bayes theorem and Hidden Markov model ...