Oracle PL/SQL Tutorial/Collections/Nested Table Column
Содержание
- 1 Delete one record in the nested table
- 2 Getting Information on Nested Tables
- 3 Modifying Nested Table Elements
- 4 Populating a Nested Table with Elements
- 5 Selecting Nested Table Elements
- 6 Set the depth to 2 and describe a nested table
- 7 Update the first record in the nested table.
- 8 Using a Nested Table Type to Define a Column in a Table
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.