Oracle PL/SQL Tutorial/Collections/Nested Table Column

Материал из SQL эксперт
Версия от 10:04, 26 мая 2010; Admin (обсуждение | вклад) (1 версия)
(разн.) ← Предыдущая | Текущая версия (разн.) | Следующая → (разн.)
Перейти к: навигация, поиск

Delete one record in the nested table

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>


Getting Information on Nested Tables

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>


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.



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>


Populating a Nested Table with Elements

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



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>


Selecting Nested Table Elements

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



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>


Set the depth to 2 and describe a nested table

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>


Update the first record in the nested table.

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>


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.



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.
Источник — «http://sqle.ru/index.php?title=Oracle_PL/SQL_Tutorial/Collections/Nested_Table_Column&oldid=2940»