oracle nested table

===========================================================

嵌套表测试

===========================================================

作者: tsmld(http://tsmld.itpub.net)
发表于: 2006.12.05 17:14
分类: 数据库
出处: http://tsmld.itpub.net/post/19744/234628
---------------------------------------------------------------

nested table

--建立对象类型

create or replace type emp_type as object(
name varchar2(10),salary number(6,2),
hiredate date);
/

--建立嵌套表类型
create or replace type emp_array is table of emp_type;
/

--建表

create table department(
deptno number(2), dname varchar2(10),
employee emp_array
) nested table employee store as employee;
/
--插入数据
insert into department values(10,'sales',emp_array(
emp_type('scott',1000,sysdate),
emp_type('smith',1200,sysdate),
emp_type('blake',1500,sysdate))
);
--查询
select d.dname,e.name,e.salary
from department d,table(d.employee) e

参考: <oracle 9i pro*c/c++>