144.Oracle数据库SQL开发之 集合——多级集合类型

144.Oracle数据库SQL开发之 集合——多级集合类型

欢迎转载，转载请标明出处：http://blog.csdn.net/notbaron/article/details/50180863

自9i发布后，可以在数据库中创建一种其元素也是集合类型的集合类型。

集合的集合称为多级集合（multilevel collections）

创建一个包含3个VARCHAR2字符串的变长数组类型v_varray_phone,

CREATE TYPE t_varray_phone AS VARRAY(3) OFVARCHAR2(14);

/

CREATETYPE t_address AS OBJECT (

  street        VARCHAR2(15),

  city          VARCHAR2(15),

  state         CHAR(2),

  zip           VARCHAR2(5),

  phone_numberst_varray_phone

);

/

创建嵌套表类型：

CREATE TYPE t_nested_table_address AS TABLE OFt_address;

/

创建表customers_with_nested_table，包含类型t_nested_table_address的列

CREATETABLE customers_with_nested_table (

  id         INTEGER PRIMARY KEY,

  first_nameVARCHAR2(10),

 last_name  VARCHAR2(10),

 addresses  t_nested_table_address

)

NESTEDTABLE

  addresses

STORE AS

 nested_addresses;

插入如下：

INSERTINTO customers_with_nested_table VALUES (

  1, 'Steve','Brown',

 t_nested_table_address(

   t_address('2 State Street', 'Beantown', 'MA', '12345',

     t_varray_phone(

       '(800)-555-1211',

       '(800)-555-1212',

       '(800)-555-1213'

      )

    ),

   t_address('4 Hill Street', 'Lost Town', 'CA', '54321',

     t_varray_phone(

       '(800)-555-1211',

       '(800)-555-1212'

      )

    )

  )

);

查看如下：

collection_user2@PDB1> select * fromcustomers_with_nested_table;

 

         ID FIRST_NAME LAST_NAME

---------- ---------- ----------

ADDRESSES(STREET,CITY, STATE, ZIP, PHONE_NUMBERS)

----------------------------------------------------------------------------------------------------

          1 Steve     Brown

T_NESTED_TABLE_ADDRESS(T_ADDRESS('2 StateStreet', 'Beantown', 'MA', '12345', T_VARRAY_PHONE('(800)-

555-1211', '(800)-555-1212','(800)-555-1213')), T_ADDRESS('4 Hill Street', 'Lost Town', 'CA', '5432

1', T_VARRAY_PHONE('(800)-555-1211','(800)-555-1212')))

使用TABLE可以将集合中存储的数据视为一系列行

collection_user2@PDB1> selectcn.first_name,cn.last_name,a.street,a.city,a.state,p.* fromcustomers_with_nested_table cn,table(cn.addresses) a,table(a.phone_numbers) p;

 

FIRST_NAME LAST_NAME  STREET             CITY           ST COLUMN_VALUE

---------- ---------- ------------------------------ -- --------------

Steve           Brown     2 State Street  Beantown       MA(800)-555-1211

Steve           Brown     2 State Street  Beantown       MA(800)-555-1212

Steve           Brown     2 State Street  Beantown       MA(800)-555-1213

Steve           Brown     4 Hill Street   Lost Town       CA (800)-555-1211

Steve           Brown     4 Hill Street   Lost Town       CA (800)-555-1212

使用UPDATE语句来更新如下：

collection_user2@PDB1>  update table(

    selectcn.addresses from customers_with_nested_table cn where cn.id=1 ) addrs

     setaddrs.phone_numbers=t_varray_phone('(800)-555-1214','(800)-555-1215')

    whereaddrs.street='2 State Street';

 

1 row updated.

查询如下：

collection_user2@PDB1> selectcn.first_name,cn.last_name,a.street,a.city,a.state,p.* from customers_with_nested_tablecn,

   table(cn.addresses) a,table(a.phone_numbers) p;

 

FIRST_NAME LAST_NAME  STREET             CITY           ST COLUMN_VALUE

---------- ---------- ------------------------------ -- --------------

Steve           Brown     2 State Street  Beantown       MA(800)-555-1214

Steve           Brown     2 State Street  Beantown       MA(800)-555-1215

Steve           Brown     4 Hill Street   Lost Town       CA (800)-555-1211

Steve           Brown     4 Hill Street   Lost Town       CA (800)-555-1212