Creating Nested Tables

In this example, you'll see how to nest one table inside another using an object type. When you nest a child table inside its parent table, Oracle automatically manages the built-in relationships among each row of the parent table and the associated nested table rows.

Nested tables are appropriate for master-detail relationships in which detailrows store unique information. For example, consider the typical ORDERS and ITEMS tables in a relational order-entry database. Each line item is a unique collection of data that corresponds to a particular order. This is the kind of masterdetail relationship that is the perfect candidate for a nested table. The following example creates a simplified ITEM-TYPE ( no reference to the part ID is included) and corresponding ITEM-LIST-TYPE that you can then use to nest a typical ITEMS table inside a typical ORDERS table.

CREATE OR REPLACE TYPE sales.item-type AS OBJECT(
item-id INTEGER,
quantity INTEGER);
CREATE OR REPLACE TYPE sales.item-list AS
TABLE OF sales. Item-TYPE;
CREATE TABLE sales.orders (
id INTEGER PRIMARY KEY,
order-date DATE,
ship-date DATE,
line-items sales.Item-List
NESTED TABLE line-items STORE AS items;

As you might expect, manipulating nested tables requires that you use some special DML syntax. For example, an application can insert a new order and corresponding items into the new ORDERS table using a single INSERT statement with constructors to insert ITEMS into the nested table.

INSERT INTO sales.orders VALUES(
1, SYSDATE, NULL,
sales. Item-List ( sales.Item-Type (1, 22),
sales.Item-Type (2, 100)));

Domain Name Search

Creating Nested Tables

Domain Names

SQL

Web Design

Web Hosting

E Commerce

Email