oracle nested table 查询条件,nested table

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@]