Select a single row from the nested-object table : Nested Object Table « Object Oriented « Oracle PL/SQL Tutorial






SQL>
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  /

Type created.

SQL>
SQL> CREATE Or Replace TYPE PersonType AS OBJECT (
  2    id         NUMBER,
  3    first_name VARCHAR2(10),
  4    last_name  VARCHAR2(10),
  5    dob        DATE,
  6    phone      VARCHAR2(12),
  7    address    AddressType
  8  )
  9  /

Type created.

SQL>
SQL> CREATE TABLE object_customers OF PersonType
  2  /

Table created.

SQL>
SQL> INSERT INTO object_customers VALUES (
  2    PersonType(1, 'John', 'White', '04-FEB-1945', '800-555-5555',
  3      AddressType('2 Street', 'City', 'MA', '12345')
  4    )
  5  );

1 row created.

SQL>
SQL>
SQL> INSERT INTO object_customers (
  2    id, first_name, last_name, dob, phone,
  3    address
  4  ) VALUES (
  5    2, 'James', 'Green', '05-FEB-1968', '800-555-4444',
  6    AddressType('3 Ave', 'City', 'CA', '12345')
  7  );

1 row created.

SQL>
SQL> select * from object_customers;

 ID FIRST_NAME           LAST_NAME            DOB       PHONE             ADDRESS(STREET, CITY, STATE, ZIP)
--------------------------------------------------------------------------
  1 John                 White                04-FEB-45 800-555-5555      ADDRESSTYPE('2 Street', 'City', 'MA', '12345')
  2 James                Green                05-FEB-68 800-555-4444      ADDRESSTYPE('3 Ave', 'City', 'CA', '12345')


SQL>
SQL> SELECT *
  2  FROM object_customers oc
  3  WHERE oc.id = 1;

 ID FIRST_NAME           LAST_NAME            DOB       PHONE           ADDRESS(STREET, CITY, STATE, ZIP)
------------------------------------------------------------------------
  1 John                 White                04-FEB-45 800-555-5555    ADDRESSTYPE('2 Street', 'City', 'MA', '12345')


SQL>
SQL> drop table object_customers;

Table dropped.

SQL> drop type persontype;

Type dropped.

SQL> drop type addresstype;

Type dropped.

SQL>








32.10.Nested Object Table
32.10.1.Object References
32.10.2.Inserting Rows into the nested-object table
32.10.3.Selecting Rows from the nested-object table
32.10.4.Select a single row from the nested-object table
32.10.5.A row is selected based on the inner column object
32.10.6.Stored Nested Tables
32.10.7.DML on whole Nested Tables
32.10.8.UPDATE on whole Nested Tables
32.10.9.DELETE on whole Nested Tables
32.10.10.SELECT on whole Nested Tables