Oracle:集合(一)

集合

集合有3中类型：

• 变长数组：类似于java中的数组。可存储有序的元素集合，每个元素都有一个索引，记录了元素在数组中的位置。只能作为整体来修改。在创建时创建时可设置最大大小，但是后期可以更改。
• 嵌套表：嵌套在另一个表中的表。可以插入、更新和删除嵌套标表中的单个元素。没有最大大小，可以再嵌套表中存储任意数目的元素。
• 关联数组（以前称为索引表）；类似于java中的散列表。是个键值对集合。关联数组只能用于PL/SQL中，不能存储在数据库中。

一.创建集合类型

1.创建变长数组类型

既可以是内置的数据库类型，也可以是用户定义的对象类型。

crete type t_varray_address as varray(3) of varchar2(50);

修改最大大小,将最大元素个数改为10

alter type t_varray_address modify limit 10 cascade;

2.创建嵌套表类型

create type t_address as object (    street varchar2(15),    city varchar2(15),    state char(2),    zip varchar2(5));

create type t_nested_table_address as table of t_address;

二.使用集合类型定义表列

1.使用变长数组类型定义表列

create table customer_with_varry (    id integer primary key,    first_name varchar(10),    last_name varchar(10),    addresses t_varray_address);

注意：当变长数组的大小小于4000字节时，变长数组的元素直接存储在表中；否则，变长数组存储在表之外。当变长数组存储在表中时，它的元素访问速度比嵌套表的元素访问速度快。

2.使用嵌套表类型定义表列

create table customer_with_nested_tabel (    id integer primary key,    first_name varchar(10),    last_name varchar(10),    addresses t_nested_table_address)nested table    addressesstore as    nested_addresses;

三.获取集合信息

1.获取变长数组信息

describe t_varray_addresst_varray_address varray(3) of varchar2(50);select * from user_varrays where type_name='t_varray_address';

2.获得嵌套表信息

describe t_nested_table_address

select * from user_nested_tables where table_name='nested_addresses';

四.填充集合元素

1.填充变长数组元素

insert into customer_with_varry values (    1, 'Steve', 'Brown',        t_varray_address (            '2 State Street, Beantown, MA, 12345'    ));

2.填充嵌套表元素

insert into customer_with_nested_tabel values(    1, 'Steve', 'Brown',        t_nested_table_address (            t_address('2 State Street, Beantown, MA, 12345')        ));

五.检索集合元素

1.检索变长数组元素

select * from customer_with_varry where id = 1;ID     FIRST_NMAE   LAST_NAME   ASSRESSES-----------------------------------------------------------1      Steve         Brown      t_varray_address('2 State Street, Beantown, MA, 12345')

2.检索嵌套表元素

select * from customer_with_nested_tabel where id = 1;ID   FIRST_NMAE   LAST_NAME   ASSRESSES-----------------------------------------------------------------1    Steve        Brown      t_nested_table_address(t_address('2 State Street, Beantown, MA, 12345'))

六.使用table()函数将集合视为一系列行

1.将table()函数应用于变长数组

select a.* from customer_with_varry c, table(c.addresses) a where id = 1;COLUMN_VALUE-----------------------------------2 State Street, Beantown, MA, 12345

select * from table(    select addresses     where id = 1    );COLUMN_VALUE-----------------------------------2 State Street, Beantown, MA, 12345

2.将table()函数应用于嵌套表

select a.* from customer_with_nested_tabel c, table(c.addresses) a where id = 1;STREET           CITY      ST   ZIP-------------------------------------2 State Street   Beantown  MA   12345

七.更改集合元素

1.更改变长数组元素

注意：只能作为整体来修改

update customer_with_varryset addresses = t_varray_address(    'ShengXia Streest, Shanghai, CA, 3346',    'ZuChongZhi Streest, Shanghai, CA, 8978')where id = 1;

2.更改嵌套表元素

insert into table(    select addresses    from customer_with_nested_tabel    where id = 1) values (    t_address('5 Main Streest', 'Uptonwn', 'NY', '55512'));

update table(    select addresses    from customer_with_nested_tabel    where id = 1) addr    set values(addr) =     t_address('ShengXia Streest', 'Shanghai', 'CA', '3346')    where value(addr) =    t_address('5 Main Streest', 'Uptonwn', 'NY', '55512');

delete from table(    select addresses    from customer_with_nested_tabel    where id = 1)addr    where value(addr) =     t_address('ShengXia Streest', 'Shanghai', 'CA', '3346')

八.使用映射方法比较嵌套表的内容

注意：变长数组的类型不能直接比较

满足以下三个条件，两个嵌套表才相等：

1. 两个嵌套表具有相同的类型
2. 两个嵌套表具有相同的行数
3. 两个嵌套表的所有元素的值都相同

如果嵌套表是内置数据库类型，那么数据库会自动比较嵌套表的内容。如果嵌套表的元素时用户定义的对象类型，那么需要提供映射函数。

create type t_address2 as object (    street varchar2(15),    city varchar2(15),    state char(2),    zip varchar2(5),    map member function get_string return varchar2);

create type body t_address2 as    map member function get_string return varchar2 isbegin    return zip || ' '||state||' '||city||' '||street;    end get_string;end;

使用submultiset of比较是否一个嵌套表的内容是另一个的子集

九.使用CAST()函数将集合从一种类型转换为另一种类型

1.使用CAST()函数将变长数组转换为嵌套表

select cast(cv.addresses as t_nested_table_address)from customer_with_varry cvwhere cv.id = 1;

2.使用CAST()函数将嵌套表转换为变长数组

select cast(cv.addresses as customer_with_varry)from t_nested_table_address cvwhere cv.id = 1;