в 13:45, 26 мая 2010

2010-05-26T13:45:46Z

Admin: 1 версия

2010-05-26T10:04:29Z

1 версия

Новая страница

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

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL Tutorial/Collections/Nested Table Column - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия