Delete one record in the nested table : Nested Table Column « Collections « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Collections
3. Nested Table Column

SQL>
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>
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>
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>
SQL>
SQL> select * from employee;

ID FIRST_NAME LAST_NAME ADDRESSES(STREET, CITY, STATE, ZIP)
-------------------------------------------------------------------------------------------------------
1 Steve       Brown     NESTED_TABLE_ADDRESSTYPE(ADDRESSTYPE('2 Ave', 'City', 'MA', '12345'), ADDRESSTYPE('4 Ave', 'City', 'CA', '54321'), ADDRESSTYPE('5 Ave', 'Uptown', 'NY', '55512'))


SQL>
SQL> DELETE FROM TABLE (
  2    SELECT addresses FROM employee WHERE id = 1
  3  ) addr
  4  WHERE
  5    VALUE(addr) = AddressType(
  6      '4 Ave', 'City', 'CA', '54321'
  7    );

1 row deleted.

SQL>
SQL>
SQL> select * from employee;

ID FIRST_NAME LAST_NAME ADDRESSES(STREET, CITY, STATE, ZIP)
---------------------------------------------------------------------------------------------------------
1 Steve       Brown     NESTED_TABLE_ADDRESSTYPE(ADDRESSTYPE('2 Ave', 'City', 'MA', '12345'), ADDRESSTYPE('5 Ave', 'Uptown', 'NY', '55512'))


SQL>
SQL> drop table employee;

Table dropped.

SQL>
SQL>
SQL>
SQL>