嵌套表范例:
--------------------
CREATE OR REPLACE TYPE CourseList AS TABLE OF VARCHAR2(64);
/desc courselistSELECT type, text
FROM user_source
WHERE name = 'COURSELIST';CREATE TABLE department (
name VARCHAR2(20),
director VARCHAR2(20),
office VARCHAR2(20),
courses CourseList)
NESTED TABLE courses STORE AS courses_tab;desc departmentdesc courses_tabSELECT table_name, nested
FROM user_tables;set linesize 121
col table_name format a20
col data_type format a30
col table_type_name format a15
col parent_table_column format a10SELECT column_name, data_type, data_length
FROM user_tab_columns
WHERE table_name = 'DEPARTMENT';SELECT table_name, table_type_owner, table_type_name,
parent_table_column
FROM user_nested_tables;--------------------------------
嵌套表插入值范例INSERT INTO department
(name, director, office, courses)
VALUES
('English', 'Lynn Saunders', 'Breakstone Hall 205', CourseList(
'Expository Writing',
'Film and Literature',
'Modern Science Fiction',
'Discursive Writing',
'Modern English Grammar',
'Introduction to Shakespeare',
'Modern Drama',
'The Short Story',
'The American Novel'));SELECT * FROM department;
--------------------------------
更新嵌套表范例DECLARE
new_courses CourseList :=
CourseList('Expository Writing',
'Film and Literature',
'Discursive Writing',
'Modern English Grammar',
'Realism and Naturalism',
'Introduction to Shakespeare',
'Modern Drama',
'The Short Story',
'The American Novel',
'20th-Century Poetry',
'Advanced Workshop in Poetry');
BEGIN
UPDATE department
SET courses = new_courses
WHERE name = 'English';
END;
/SELECT * FROM department;
-----------------------------------
DROP嵌套表范例SELECT table_name
FROM user_tables;DROP TABLE courses_tab;You cannot directly drop the storage table of a nested table. Instead, you must drop the nested table column using the ALTER TABLE ... DROP COLUMN clause.desc departmentALTER TABLE department
DROP COLUMN courses;
-------------------------------
--------------------
CREATE OR REPLACE TYPE CourseList AS TABLE OF VARCHAR2(64);
/desc courselistSELECT type, text
FROM user_source
WHERE name = 'COURSELIST';CREATE TABLE department (
name VARCHAR2(20),
director VARCHAR2(20),
office VARCHAR2(20),
courses CourseList)
NESTED TABLE courses STORE AS courses_tab;desc departmentdesc courses_tabSELECT table_name, nested
FROM user_tables;set linesize 121
col table_name format a20
col data_type format a30
col table_type_name format a15
col parent_table_column format a10SELECT column_name, data_type, data_length
FROM user_tab_columns
WHERE table_name = 'DEPARTMENT';SELECT table_name, table_type_owner, table_type_name,
parent_table_column
FROM user_nested_tables;--------------------------------
嵌套表插入值范例INSERT INTO department
(name, director, office, courses)
VALUES
('English', 'Lynn Saunders', 'Breakstone Hall 205', CourseList(
'Expository Writing',
'Film and Literature',
'Modern Science Fiction',
'Discursive Writing',
'Modern English Grammar',
'Introduction to Shakespeare',
'Modern Drama',
'The Short Story',
'The American Novel'));SELECT * FROM department;
--------------------------------
更新嵌套表范例DECLARE
new_courses CourseList :=
CourseList('Expository Writing',
'Film and Literature',
'Discursive Writing',
'Modern English Grammar',
'Realism and Naturalism',
'Introduction to Shakespeare',
'Modern Drama',
'The Short Story',
'The American Novel',
'20th-Century Poetry',
'Advanced Workshop in Poetry');
BEGIN
UPDATE department
SET courses = new_courses
WHERE name = 'English';
END;
/SELECT * FROM department;
-----------------------------------
DROP嵌套表范例SELECT table_name
FROM user_tables;DROP TABLE courses_tab;You cannot directly drop the storage table of a nested table. Instead, you must drop the nested table column using the ALTER TABLE ... DROP COLUMN clause.desc departmentALTER TABLE department
DROP COLUMN courses;
-------------------------------
假设有一个关于动物饲养员的表，希望其中具有他们饲养的动物的信息。用一个嵌套表，就可以在同一个表中存储饲养员和其饲养的全部动物的信息。
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 animal_nt)
nested table animals store as animals_nt_tab; 4、向嵌套表中插入记录
insert into breeder values
( 'mary',
animal_nt(animal_ty('dog','butch','31-MAR-97'),
animal_ty('dog','rover','31-MAR-97'),
animal_ty('dog','julio','31-MAR-97'))
); insert into breeder values
( 'jane',
animal_nt(animal_ty('cat','an','31-MAR-97'),
animal_ty('cat','jame','31-MAR-97'),
animal_ty('cat','killer','31-MAR-97'))
); commit; 5、查询嵌套表
select name,birthdate
from table(select animals from breeder); select name,birthdate
from table(select animals from breeder where breedername=’mary’)
where name=’dog’;
嵌套表的特点：
1、对象复用：如果编写面向对象的代码，就提高了重用以前编写的代码模块的机会。同样，如果创建面向对象的数据库对象，也就提高了数据库对象能够被重用的机会。2、标准支持：如果创建标准的对象，那么它们被重用的机会就会提高。如果有多个应用或多个表使用同一数据库对象集合，那么它就是既成事实的数据库对象标准。 3、定义访问路径：对于每一个对象，用户可定义在其上运行的过程和函数，从而可以使数据和访问此数据的方法联合起来。有了用这种方式定义的访问路径，就可以标准化数据访问的方法并提高对象的可复用性。