How to access data from nested tables using SELECT queries?

Nested table is Oracle, How to access the data from nested table in Oracle, SELECT queries in Oracle to access nested table data, TABLE() function in Oracle, Unnesting the nested table data

How to access data from nested tables using SELECT queries?

Nested tables can be accessed using TABLE( ) function as follows; let us consider the table from the previous post here;  

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;

Note: All words in ALL CAPS are keywords.

In the above table, Billed_Items is the column that is of type Nested table.

To display all the records from bills table, the query is as follows;

Query 1

SELECT * FROM Bills;

The result will be;

Cust_ID               Name                    Phone                   Bill_Date             ----------- ----------- ----------- ---------------- Billed_Items(Item_ID, Quantity, Price) ------------------------------------------------------ C101                      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))

To access individual attributes of nested table, we can write the query as follows;

Query 2

SELECT p.* FROM Bills b, TABLE(b.Billed_Items) p;

In this query, the function TABLE ( ) will unnest (flatten) the nested table column Billed_Items for every record. For example, in our table, we have one record with customer id C101 and that customer has billed three items. Now the result of TABLE (b.Billed_Items) will be as follows (the nested table is flattened);

Item_ID	Quantity	Price
I101	2	300.00
I106	2	30.00
I110	1	500.00

Here, the headings Item_ID, Quantity and Price are the attributes of the type Items_Purchased_Typ. Refer here.

Hence the result of the Query 2 is as follows;

Item_ID               Quantity                              Price ------------                ------------                -------------------- I101                       2                              300.00 I106                       2                              30.00 I110                       1                              500.00

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

 

Go to Collection Types page

Go to Object database systems page

Go to Nested Tables page

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