Nested Tables

A nested table is an unordered set of any number of elements , all of the same data type. A nested table has a single column, and the type of that column may be a built-in database type or an object type that you previously created (object types were covered in the previous chapter). If the column in a nested table is an object type, the table can also be viewed as a multicolumn table, with a column for each attribute of the object type. You can insert, update, and delete individual elements in a nested table.

Creating a Nested Table Type

In this section, I ll show you how to create a nested table type that stores address_typ object types. You saw the use of address_typ in the previous chapter; it is used to represent an address and is defined as follows :

 CREATE TYPE address_typ AS OBJECT (street VARCHAR2(15),  city VARCHAR2(15),  state CHAR(2),  zip VARCHAR2(5)); / 

You create a nested table type using the CREATE TYPE statement, and the following example creates a nested table type named nested_table_address_typ that stores address_typ object types:

 CREATE TYPE nested_table_address_typ AS TABLE OF address_typ; / 

Notice that you don t specify the maximum size of a nested table. That s because you can insert any number of elements in a nested table.

Using a Nested Table Type to Define a Column in a Table

Once you have defined your nested table type, you can use it to define a column in a table. For example, the following table named customers_with_nested_table uses nested_table_address_typ to define a column named addresses :

 CREATE TABLE customers_with_nested_table (id INTEGER PRIMARY KEY,  first_name VARCHAR2(10),  last_name VARCHAR2(10),  addresses nested_table_address_typ) NESTED TABLE  addresses STORE AS  nested_addresses; 

The NESTED TABLE clause identifies the name of the nested table column ( addresses ), and the STORE AS clause specifies the name of the actual nested table ( nested_addresses ). You cannot access the nested table independently of the table in which it is embedded.

Getting Information on Nested Tables

You can use the DESCRIBE command to get information on your nested table types. The following example describes nested_table_address_typ :

  DESCRIBE nested_table_address_typ  nested_table_address_typ TABLE OF ADDRESS_TYP  Name Null? Type  ----------------------------------------- -------- ------------  STREET VARCHAR2(15)  CITY VARCHAR2(15)  STATE CHAR(2)  ZIP VARCHAR2(5) 

The next example describes the table customers_with_nested_table whose addresses column is of type nested_table_address_typ :

  DESCRIBE customers_with_nested_table  Name Null? Type  ---------------------------------- -------- ------------------------  ID NOT NULL NUMBER(38)  FIRST_NAME VARCHAR2(10)  LAST_NAME VARCHAR2(10)  ADDRESSES NESTED_TABLE_ADDRESS_TYP 

If you set the depth to 2 and describe customers_with_nested_table , then you can see the attributes that make up nested_table_address_typ :

  SET DESCRIBE DEPTH 2   DESCRIBE customers_with_nested_table  Name Null? Type  ----------------------------------- -------- ------------------------  ID NOT NULL NUMBER(38)  FIRST_NAME VARCHAR2(10)  LAST_NAME VARCHAR2(10)  ADDRESSES NESTED_TABLE_ADDRESS_TYP  STREET VARCHAR2(15)  CITY VARCHAR2(15)  STATE CHAR(2)  ZIP VARCHAR2(5) 

You can also get information on your nested tables from the user_nested_tables view. Table 13-2 describes some of the more useful columns in user_nested_tables .

Table 13-2: Some Columns in user_nested_tables

Column	Type	Description
table_name	VARCHAR2(30)	Name of the nested table
table_type_owner	VARCHAR2(30)	User who owns the nested table type
table_type_name	VARCHAR2(30)	Name of the nested table type
parent_table_name	VARCHAR2(30)	Name of the parent table that contains the nested table
parent_table_column	VARCHAR2(4000)	Name of the column in the parent table containing the nested table
storage_spec	VARCHAR2(30)	Storage specification for the nested table
return_type	VARCHAR2(20)	Return type of the column

Note  

You can get information on all the tables you have access to using all_nested_tables .

The following example retrieves the columns from user_nested_tables :

  SELECT *   FROM user_nested_tables;  TABLE_NAME TABLE_TYPE_OWNER ------------------------------ --------------------------- TABLE_TYPE_NAME PARENT_TABLE_NAME ------------------------------ --------------------------- PARENT_TABLE_COLUMN ---------------------------------------------------------- STORAGE_SPEC RETURN_TYPE ------------------------------ -------------------- NESTED_ADDRESSES COLLECTION_USER NESTED_TABLE_ADDRESS_TYP CUSTOMERS_WITH_NESTED_TABLE ADDRESSES  DEFAULT VALUE 

Populating a Nested Table with Elements

You initially populate the elements in a nested table using an INSERT statement. The following INSERT statement adds a row to customers_with_nested_table . Notice the use of the nested_table_address_typ and address_typ constructors to specify the addresses:

 INSERT INTO customers_with_nested_table VALUES (1, 'Steve', 'Brown',  nested_table_address_typ(address_typ('2 State Street', 'Beantown', 'MA', '12345'),  address_typ('4 Hill Street', 'Lost Town', 'CA', '54321'))); 

As you can see, this row has two addresses, but any number of addresses can be stored in a nested table. You ll see how to add additional addresses to the nested table shortly in the section Modifying Nested Table Elements.

Selecting Nested Table Elements

You select the elements in a nested table using a SELECT statement. The following SELECT statement selects the row from customers_with_nested_table :

  SELECT *   FROM customers_with_nested_table;  ID FIRST_NAME LAST_NAME ---------- ---------- ---------- ADDRESSES(STREET, CITY, STATE, ZIP) ----------------------------------------------------------  1 Steve Brown NESTED_TABLE_ADDRESS_TYP(ADDRESS_TYP('2 State Street', 'Beantown', 'MA', '12345'),  ADDRESS_TYP('4 Hill Street', 'Lost Town', 'CA', '54321')) 

Modifying Nested Table Elements

Unlike a varray, elements in a nested table can be modified individually: you can insert, update, and delete elements in a nested table. You do this using the TABLE clause in conjunction with a subquery that selects the nested table. The following example inserts an address at the end of the addresses nested table column for customer #1 in customer_with_nested_table :

 INSERT INTO TABLE (SELECT addresses FROM customers_with_nested_table WHERE id = 1) VALUES (address_typ('5 Main Street', 'Uptown', 'NY', '55512')); 

The next example updates the first address of customer #1 in customers_with_nested_table . Notice the use of the alias addr to identify the first address and subsequently set it:

 UPDATE TABLE (SELECT addresses FROM customers_with_nested_table WHERE id = 1) addr SET  VALUE(addr) = address_typ('1 Market Street', 'Main Town', 'MA', '54321') WHERE  VALUE(addr) = address_typ('2 State Street', 'Beantown', 'MA', '12345'); 

The final example deletes the second address for customer #1 in customers_with_nested_table :

 DELETE FROM TABLE (SELECT addresses FROM customers_with_nested_table WHERE id = 1) addr WHERE  VALUE(addr) = address_typ('4 Hill Street', 'Lost Town', 'CA', '54321'); 

The following query verifies the changes:

  SELECT *   FROM customers_with_nested_table;  ID FIRST_NAME LAST_NAME ---------- ---------- ---------- ADDRESSES(STREET, CITY, STATE, ZIP) ------------------------------------------------------------  1 Steve Brown NESTED_TABLE_ADDRESS_TYP(ADDRESS_TYP('1 Market Street', 'Main Town', 'MA', '54321'),  ADDRESS_TYP('5 Main Street', 'Uptown', 'NY', '55512'))