1. 创建类型comm_info

CREATE TYPE comm_info AS OBJECT ( /*此类型为通讯方式的集合*/
no number(3), /*通讯类型号*/
comm_type varchar2(20), /*通讯类型*/
comm_no varchar2(30)); /*号码*/   

2. 创建可变数组comm_info_list

CREATE TYPE comm_info_list AS  VARRAY(50) OF comm_info;

3. 创建表

create table user_info
(user_id number(6), /*用户ID号*/
user_name varchar2(20), /*用户名称*/
user_comm comm_info_list); /*与用户联系的通讯方式*/

4. 向可变数组插入记录

insert into user_info
values(1,'mary',comm_info_list(comm_info(1,'手机','13651401919'),
comm_info(2,‘呼机’,‘1281234567’)));

insert into user_info
values(2,'carl',comm_info_list(comm_info(1,'手机','13901018888'),
comm_info(2,'呼机','1281234567')));

5. 查询可变数组

select user_comm from user_info 
where user_id=1; 

select comm_type,comm_no 
from table(select user_comm from user_info 
where user_id=1)
where no=1;