SQL>
SQL> CREATE Or Replace TYPE AddressType AS OBJECT (
2 street VARCHAR2(15),
3 city VARCHAR2(15),
4 state CHAR(2),
5 zip VARCHAR2(5)
6 );
7 /
SQL>
SQL> CREATE Or Replace TYPE nested_table_AddressType AS TABLE OF AddressType;
2 /
Type created.
SQL>
SQL> CREATE TABLE employee (
2 id INTEGER PRIMARY KEY,
3 first_name VARCHAR2(10),
4 last_name VARCHAR2(10),
5 addresses nested_table_AddressType
6 )
7 NESTED TABLE
8 addresses
9 STORE AS
10 nested_addresses;
Table created.
SQL>
SQL> INSERT INTO employee VALUES (
2 1, 'Steve', 'Brown',
3 nested_table_AddressType(
4 AddressType('2 Ave', 'City', 'MA', '12345'),
5 AddressType('4 Ave', 'City', 'CA', '54321')
6 )
7 );
1 row created.
SQL>
SQL> INSERT INTO TABLE (
2 SELECT addresses FROM employee WHERE id = 1
3 ) VALUES (
4 AddressType('5 Ave', 'Uptown', 'NY', '55512')
5 );
1 row created.
SQL>
SQL> UPDATE TABLE (
2 SELECT addresses FROM employee WHERE id = 1
3 ) addr
4 SET
5 VALUE(addr) = AddressType(
6 '1 Market Street', 'Main Town', 'MA', '54321'
7 )
8 WHERE
9 VALUE(addr) = AddressType(
10 '2 Ave', 'City', 'MA', '12345'
11 );
1 row updated.
SQL>
SQL> select * from employee;
ID FIRST_NAME LAST_NAME ADDRESSES(STREET, CITY, STATE, ZIP)
-----------------------------------------------------------------------------------------------------------
1 Steve Brown NESTED_TABLE_ADDRESSTYPE(ADDRESSTYPE('1 Market Street', 'Main Town', 'MA', '54321'), ADDRESSTYPE('4Ave', 'City', 'CA', '54321'), ADDRESSTYPE('5 Ave', 'Uptown', 'NY', '55512'))
SQL>
SQL> drop table employee;
Table dropped.
SQL>