Data Structures   «Prev 

Oracle Nested Table SQL

Nested Table Code Nested Table Code
  1. We create type empobj to contain 3 data elements.
  2. We then create type empobj_tab table as a table of this data type.
  3. We then create the deptnest table with the nested structure empobj_tab.
CREATE TYPE 
empobj AS 
OBJECT 
(
  emp_name NUMBER,
  emp_phone VARCHAR (20),
  sal NUMBER
);
CREATE TYPE 
empobj_tab AS
TABLE OF 
empobj;
CREAT TABLE 
deptnest
(  
  deptno NUMBER,
  dname VARCHAR(20),
  location VARCHAR2(20),
) 
NESTED TABLE
emp
STORE AS emp_tab;

Object Relational Features

You can implement object-relational features such as abstract datatypes and object views in your applications. In this module, you will see how to use collectors—sets of elements that are treated as part of a single row. The two types of collectors available are nested tables and varying arrays. This module explains the differences between these two types of collectors, as well as how to implement and manage them. Collectors make use of abstract datatypes. You should be familiar with the creation and implementation of abstract datatypes before attempting to use varying arrays and nested tables.

Varying Arrays

A varying array allows you to store repeating attributes of a record in a single row.For example, suppose Dora Talbot wants to track which of her tools were borrowed by which of her workers. You could model this in a relational database by creating a BORROWER table: 

create table BORROWER
(Name VARCHAR2(25),
Tool VARCHAR2(25),
constraint BORROWER_PK primary key (Name, Tool));

The primary key of the BORROWER table is the combination of the Name column and the Tool column. Thus, if a single worker borrowed three tools, the worker’s name would be repeated in each of the three records.Even though the worker's Name value does not change, it is repeated in each record because it is part of the primary key. Collectors such as varying arrays allow you to repeat only those column values that change, potentially saving storage space. You can use collectors to accurately represent relationships between datatypes in your database objects.