Oracle PL/SQL Tutorial/Object Oriented/Nested Object Table

A row is selected based on the inner column object

   <source lang="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 Ave", "City", "AA", "12345")
 4    )
 5  );

1 row created. 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", "Town", "CC", "12345")
 7  );

1 row created. SQL> 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 Ave", "City", "AA", "12345")
 2 James                Green                05-FEB-68 800-555-4444      ADDRESSTYPE("3 Ave", "Town", "CC", "12345")

SQL> SQL> SELECT *

 2  FROM object_customers oc
 3  WHERE oc.address.state = "MA";

no rows selected SQL> SQL> SQL> drop table object_customers; Table dropped. SQL> drop type persontype; Type dropped. SQL> drop type addresstype; Type dropped. SQL></source>

DELETE on whole Nested Tables

   <source lang="sql">

SQL> SQL> CREATE OR REPLACE TYPE numberTableType AS TABLE OF NUMBER(10);

 2  /

Type created. SQL> SQL> CREATE TABLE address_list (list_id VARCHAR2(6)PRIMARY KEY,

 2                   home_addresses numberTableType )
 3                   NESTED TABLE home_addresses STORE AS home_addreses_tab;

SQL> SQL> SQL> INSERT INTO address_list VALUES("H101",numberTableType(1001,1002,1003,1004));

SQL> SQL> SQL> SQL> DELETE FROM address_list

 2  WHERE list_id ="H102";

SQL> SQL> drop table address_list;</source>

DML on whole Nested Tables

   <source lang="sql">

SQL> -- INSERT SQL> SQL> CREATE OR REPLACE TYPE numberTableType AS TABLE OF NUMBER(10);

 2  /

Type created. SQL> SQL> CREATE TABLE address_list (

 2      list_id VARCHAR2(6)PRIMARY KEY,
 3      home_addresses numberTableType )
 4  NESTED TABLE home_addresses STORE AS home_addreses_tab;

SQL> SQL> SQL> INSERT INTO address_list VALUES("H101",numberTableType(1001,1002,1003,1004));

SQL> SQL> DECLARE

 2    v_numberVarryType numberTableType :=numberTableType(2001,2002);
 3  BEGIN
 4    INSERT INTO address_list VALUES ("H102",v_numberVarryType);
 5  END;
 6  /

SQL> SQL> SQL> SQL> drop table address_list;

SQL> SQL></source>

Inserting Rows into the nested-object table

   <source lang="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; Table created. SQL> SQL> INSERT INTO object_customers VALUES (

 2    PersonType(1, "John", "White", "04-FEB-1945", "800-555-5555",
 3      AddressType("2 Ave", "town", "AA", "12345")
 4    )
 5  );

1 row created. 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", "CC", "12345")
 7  );

1 row created. SQL> 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 Ave", "town", "AA", "12345")
 2 James                Green                05-FEB-68 800-555-4444    ADDRESSTYPE("3 Ave", "City", "CC", "12345")

SQL> SQL> drop table object_customers; Table dropped. SQL> drop type persontype; Type dropped. SQL> drop type addresstype; Type dropped. SQL></source>

Object References

   <source lang="sql">

SQL> SQL> SQL> CREATE OR REPLACE TYPE address AS OBJECT

 2              (line1 VARCHAR2(20),
 3               line2 VARCHAR2(20),
 4               city VARCHAR2(20),
 5               state_code VARCHAR2(2),
 6               zip VARCHAR2(13),
 7    MEMBER FUNCTION get_address RETURN VARCHAR2,
 8    MEMBER PROCEDURE set_address
 9              (addressLine1 VARCHAR2,
10               addressLine2 VARCHAR2,
11               address_city VARCHAR2,
12               address_state VARCHAR2,
13               address_zip VARCHAR2)
14  );
15  /

Type created. SQL> CREATE OR REPLACE TYPE BODY address AS

 2    MEMBER FUNCTION get_address RETURN VARCHAR2
 3    IS
 4    BEGIN
 5      RETURN (SELF.line1||" "||SELF.line2||" "||SELF.city||", "||SELF.state_code||" "||SELF.zip);
 6    END get_address;
 7    MEMBER PROCEDURE set_address (addressLine1 VARCHAR2,
 8                  addressLine2 VARCHAR2,
 9                  address_city VARCHAR2,
10                  address_state VARCHAR2,
11                  address_zip VARCHAR2)
12    IS
13    BEGIN
14      line1 :=addressLine1;
15
16      line2 :=addressLine2;
17
18      city :=address_city;
19
20      state_code :=address_state;
21
22      zip :=address_zip;
23    END set_address;
24  END;
25  /

Type body created. SQL> CREATE TABLE employee

 2  (empid number(10)PRIMARY KEY,
 3   lastname varchar2(30)NOT NULL,
 4   firstname varchar2(30)NOT NULL,
 5   middle_initial varchar2(2),
 6   emp_address REF address);

Table created. SQL> SQL> SQL> DROP TABLE employee; Table dropped. SQL></source>

Select a single row from the nested-object table

   <source lang="sql">

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

Selecting Rows from the nested-object table

   <source lang="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> 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> SQL> CREATE TABLE object_customers OF PersonType; Table created. SQL> SQL> INSERT INTO object_customers VALUES (

 2    PersonType(1, "John", "White", "04-FEB-1999", "800-555-5555",
 3      AddressType("2 Ave", "town", "AA", "12345")
 4    )
 5  );

1 row created. 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-99 800-555-5555     ADDRESSTYPE("2 Ave", "town", "AA", "12345")
 2 James                Green                05-FEB-68 800-555-4444     ADDRESSTYPE("3 Ave", "City", "CA", "12345")

SQL> SQL> drop table object_customers; Table dropped. SQL> drop type persontype; Type dropped. SQL> drop type addresstype; Type dropped. SQL></source>

SELECT on whole Nested Tables

   <source lang="sql">

SQL> SQL> CREATE OR REPLACE TYPE numberTableType AS TABLE OF NUMBER(10);

 2  /

Type created. SQL> CREATE TABLE address_list (list_id VARCHAR2(6)PRIMARY KEY,

 2                   home_addresses numberTableType )
 3                   NESTED TABLE home_addresses STORE AS home_addreses_tab;

SQL> SQL> SQL> INSERT INTO address_list VALUES ("H101",numberTableType(1001,1002,1003,1004));

SQL> SQL> SQL> DECLARE

 2    v_numberVarryType numberTableType :=numberTableType(2001,2002);
 3  BEGIN
 4    INSERT INTO address_list VALUES ("H102",v_numberVarryType);
 5  END;
 6  /

SQL> SQL> drop table address_list;</source>

Stored Nested Tables

   <source lang="sql">

SQL> SQL> CREATE OR REPLACE TYPE numberTableType AS TABLE OF NUMBER(10);

 2  /

Type created. SQL> SQL> CREATE TABLE address_list (

 2      list_id VARCHAR2(6)PRIMARY KEY,
 3      home_addresses numberTableType )
 4  NESTED TABLE home_addresses STORE AS home_addreses_tab;

SQL> SQL> drop table address_list;

SQL> SQL> SQL></source>

UPDATE on whole Nested Tables

   <source lang="sql">

SQL> SQL> SQL> CREATE OR REPLACE TYPE numberTableType AS TABLE OF NUMBER(10);

 2  /

Type created. SQL> SQL> CREATE TABLE address_list (

 2      list_id VARCHAR2(6)PRIMARY KEY,
 3      home_addresses numberTableType )
 4  NESTED TABLE home_addresses STORE AS home_addreses_tab;

SQL> SQL> SQL> INSERT INTO address_list VALUES("H101",numberTableType(1001,1002,1003,1004));

SQL> SQL> SQL> DECLARE

 2    v_numberVarryType numberTableType :=numberTableType(1011,1012,1013);
 3  BEGIN
 4    UPDATE address_list
 5    SET home_addresses =v_numberVarryType
 6    WHERE list_id ="H102";
 7  END;
 8  /

SQL> SQL> drop table address_list;

SQL></source>