ORACLE里怎样实现数组的功能呢,嵌套表又是什么呢?
--嵌套表和可变长数组示例:
CREATE TYPE animal_ty AS OBJECT (
breed varchar2(25),
names varchar2(25),
birthdate date);
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//*
create or replace type myTableType as table of varchar2(30);*/
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
CREATE TYPE animals_nt as table of animal_ty;
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
create table breeder
(breedername varchar2(25),
animals animals_nt)
nested table animals store as animals_nt_tab;
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
insert into breeder
values('mary',animals_nt(animal_ty('dog','butch',to_date('3-12-12','dd-mm-yy')),
animal_ty('dog','rover',to_date('03-12-12','dd-mm-yy')),
animal_ty('dog','julio',to_date('03-12-12','dd-mm-yy'))));
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
insert into breeder
values('jane',animals_nt(animal_ty('cat','an',to_date('31-03-97','dd-mm-yy')),
animal_ty('cat','jame',to_date('31-03-97','dd-mm-yy')),
animal_ty('cat','killer',to_date('31-03-97','dd-mm-yy'))));
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
select * from breeder;
--查询嵌套表
select names, birthdate
from table (select animals from breeder);
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
select names, birthdate
from table (select animals from breeder where breedername = 'mary')
where names = 'rover';
delete from table(select animals from breeder) where breed='cat'
drop table breeder;
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--可变长数组
CREATE TYPE comm_info AS OBJECT
( --此类型为通讯方式的集合
no number(3), --通讯类型号
comm_type varchar2(20), --通讯类型
comm_no varchar2(30) --号码
);
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--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'))
);
insert into user_info
values (2, 'badboy', comm_info_list(comm_info(1, '手机', '133546898888'),
comm_info(2, '呼机', '12545541'),
comm_info(3,'EMAIL','changhai-xuri@163.com'))
);
commit;
--5、查询可变数组
select user_comm from user_info where user_id = 2;
select comm_type, comm_no
from table (select user_comm from user_info where user_id = 1)
where no = 1;