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