Oracle PL/SQL Tutorial/Collections/Nested Table Column

Материал из SQL эксперт
Перейти к: навигация, поиск

Delete one record in the nested 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  /

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


Getting Information on Nested Tables

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

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> desc employee;

Name            Null?    Type

ID              NOT NULL NUMBER(38)
FIRST_NAME               VARCHAR2(10)
LAST_NAME                VARCHAR2(10)
ADDRESSES                NESTED_TABLE_ADDRESSTYPE
  STREET                 VARCHAR2(15)
  CITY                   VARCHAR2(15)
  STATE                  CHAR(2)
  ZIP                    VARCHAR2(5)

SQL> SQL> drop table employee; Table dropped. SQL> SQL></source>


Modifying Nested Table Elements

Elements in a nested table can be modified individually: you can insert, update, and delete elements in a nested table.

You do this using the TABLE clause in conjunction with a subquery that selects the nested 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  /

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> 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> drop table employee; Table dropped. SQL> SQL> SQL></source>


Populating a Nested Table with Elements

You populate the elements in a nested table using an INSERT statement.



   <source lang="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> 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"))

SQL> SQL> drop table employee; Table dropped. SQL> SQL> SQL></source>


Selecting Nested Table Elements

You select the elements in a nested table using a SELECT statement.



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

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> 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"))

SQL> SQL> drop table employee; Table dropped. SQL> SQL></source>


Set the depth to 2 and describe a nested 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  /

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> SET DESCRIBE DEPTH 2; SQL> DESCRIBE employee;

Name            Null?    Type

ID              NOT NULL NUMBER(38)
FIRST_NAME               VARCHAR2(10)
LAST_NAME                VARCHAR2(10)
ADDRESSES                NESTED_TABLE_ADDRESSTYPE
  STREET                 VARCHAR2(15)
  CITY                   VARCHAR2(15)
  STATE                  CHAR(2)
  ZIP                    VARCHAR2(5)

SQL> SQL> drop table employee; Table dropped. SQL> SQL> SQL></source>


Update the first record in the nested 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  /

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


Using a Nested Table Type to Define a Column in a Table

Once you have defined your nested table type, you can use it to define a column in a table.

The NESTED TABLE clause identifies the name of the nested table column (addresses).

The STORE AS clause specifies the name of the actual nested table (nested_addresses).

You cannot access the nested table independently of the table in which it is embedded.



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

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> desc employee;

Name           Null?    Type
ID             NOT NULL NUMBER(38)
FIRST_NAME              VARCHAR2(10)
LAST_NAME               VARCHAR2(10)
ADDRESSES               NESTED_TABLE_ADDRESSTYPE
  STREET                VARCHAR2(15)
  CITY                  VARCHAR2(15)
  STATE                 CHAR(2)
  ZIP                   VARCHAR2(5)

SQL> SQL> drop table employee; Table dropped.</source>