5.2 Nested Tables
A nested table is a set of elements that may be stored, or nested, within another table. There are two advantages that a nested table has over a VARRAY:
-
A nested table doesn't have a fixed size, whereas a VARRAY has an upper bound specified in the CREATE TYPE statement.
-
You can insert, update, and delete individual elements in a nested table, whereas with a VARRAY, you must recreate the entire VARRAY to change even a single element.
However, there are two advantages that a VARRAY has over a nested table:
-
A VARRAY occupies less space in the database than a nested table.
-
A VARRAY is stored with the other columns in a row, whereas a nested table is stored in a separate table. This means that access to the elements in a VARRAY can be faster than for a nested table.
As with VARRAY types, you use the CREATE TYPE statement to create nested table types. The following example creates a nested table type named t_address4 to hold a list of addresses, with each address being stored as a VARCHAR2 string:
CREATE TYPE t_address4 AS TABLE OF VARCHAR2(50); /
Because nested tables are stored separately from the table in which the nested table is contained, you must use the NESTED TABLE clause in your CREATE TABLE statement. The NESTED TABLE clause has the following syntax:
CREATE TABLE table_name ( ... ) NESTED TABLE column_name STORE AS nested_table_name;
The syntax elements are as follows:
- table_name
-
Specifies the name of the table you are creating. This is the outer table name, not the nested table name.
- column_name
-
Specifies the column in the outer table that is used to reference the nested table. The nested table is accessed through this column name, but is stored separately from the outer table.
- nested_table_name
-
Specifies the name to use for the underlying table in which the nested table rows are really stored. You cannot access this nested table independently of the outer table.
The following CREATE TABLE statement uses the NESTED TABLE clause to store a nested table, which is accessed through the addresses column:
CREATE TABLE customers5 ( id NUMBER, first_name VARCHAR2(10), last_name VARCHAR2(10), addresses t_address4 ) NESTED TABLE addresses STORE AS nested_addresses;
The addresses column uses the type t_address4, which was defined earlier as a nested table of VARCHAR2 strings. The NESTED TABLE clause identifies the addresses column as the reference through which the nested table is accessed, and the STORE AS keyword names this nested table as nested_addresses. This nested table is stored separately from the customers5 table.
The method for adding a row to the customers5 table is identical to the method used to add a row containing a VARRAY to the customers3 table. The only differences are the name of the table, customers5, and the use of the t_address4 constructor:
INSERT INTO customers5 VALUES ( 1, 'John', 'Smith', t_address4( '1 Anystreet, Anytown, CA, 12345', '2 Main Street, Big Town, NY, 54321' ) );
After this INSERT is performed, the customers5 table contains one row. The nested_addresses nested table, however, contains two rows, one for each VARCHAR2 string contained in the t_address4 constructor.
5.2.1 Retrieving the Contents of a Nested Table
Even though nested tables are stored in database tables that are separate from their parent tables, you cannot access those nested tables directly. Therefore, with respect to the example involving the customers5 table, you cannot access the nested_addresses nested table directly. Instead, you must access the nested table though the addresses column. Just as with VARRAYs, you can retrieve the elements in a nested table by simply selecting the column containing the nested table. For example:
SQL> SELECT * 2 FROM customers5 3 WHERE id = 1; ID FIRST_NAME LAST_NAME --- ---------- --------- ADDRESSES --------------------------------------------- 1 John Smith T_ADDRESS4('1 Anystreet, Anytown, CA, 12345', '2 Main Street, Big Town, NY, 54321') Note that the elements in the nested table are contained within the constructor for the nested table type. I mentioned earlier that the elements in a nested table can be individually modified; in the next section, I show you how to do this.
5.2.2 Modifying the Contents of a Nested Table
You can add, modify, or delete individual elements in a nested table by using the TABLE clause. The TABLE clause is used in conjunction with a sub-query to select the column containing the nested table, and may be included in INSERT, UPDATE, and DELETE statements. The TABLE clause is best understood by looking at a few examples. The following INSERT statement adds a string to the end of the nested table stored in the addresses column of the row added earlier to the customers5 table:
INSERT INTO TABLE (SELECT addresses FROM customers5 WHERE id = 1) VALUES ('3 A Street, Oldtown, CA, 34512'); As you can see, the TABLE clause is used in conjunction with a sub-query that selects the addresses column from the row for customer #1. The INSERT statement then adds another string to the end of the nested table.
If you wish to modify a row in a nested table, you can use the TABLE clause in an UPDATE statement. For example, the following UPDATE statement modifies the first string in the nested table for customer #1:
UPDATE TABLE (SELECT addresses FROM customers5 WHERE id = 1) a SET VALUE (a) = '1 New Street, Newtown, CA, 34512' WHERE VALUE (a) = '1 Anystreet, Anytown, CA, 12345';
Notice that the UPDATE statement in this example uses the alias a for the nested table selected by the sub-query. This alias is then referenced in the SET and WHERE clauses. In those clauses, the VALUE keyword is used to obtain the actual strings stored in the nested table.
You can also use the TABLE clause in a DELETE statement to remove an element from a nested table. For example, the following DELETE statement removes the second string from the nested table:
DELETE FROM TABLE (SELECT addresses FROM customers5 WHERE id = 1) a WHERE VALUE (a) = '2 Main Street, Big Town, NY, 54321';
