1、创建类型animal_ty:此类型中,对于每个动物都包含有一个记录,记载了其品种、名称和出生日期信息。
CREATE TYPE animal_ty AS OBJECT (
breed varchar2(25),
name varchar2(25),
birthdate date);
2、创建animals_nt:此类型将用作一个嵌套表的基础类型。
CREATE TYPE animals_nt as table of animal_ty;
3、创建表breeder:饲养员的信息表
create table breeder
(breedername varchar2(25),
animals animals_nt)
nested table animals store as animals_nt_tab;
4、向嵌套表中插入记录
insert into breeder
values
('mary',
animals_nt(animal_ty('dog', 'butch', '2012-3-5 17:13:41'),
animal_ty('dog', 'rover', '2012-3-5 17:13:41'),
animal_ty('dog', 'julio', '2012-3-5 17:13:41')));
insert into breeder
values
('jane',
animals_nt(animal_ty('cat', 'an', '2012-3-5 17:13:41'),
animal_ty('cat', 'jame', '2012-3-5 17:13:41'),
animal_ty('cat', 'killer', '2012-3-5 17:13:41')));
commit;
5、查询嵌套表
select breed,name,birthdate from breeder a, table (a.animals);
select name, birthdate
from table (select animals
from breeder
where breedername = 'mary')
where name = 'rover';[@more@]