oracle嵌套表示例
----嵌套表：就是把一個(gè)表中的字段定義為一個(gè)表，這個(gè)字段表的數(shù)據(jù)存儲(chǔ)在外部的一個(gè)表中，
例如下例嵌套表中的數(shù)據(jù)存儲(chǔ)在required_tab表中。
----嵌套表可以有效地代替多個(gè)表之間的連接
create type bookobj as object(
title varchar2(40),
author varchar2(40),
catalog_number number(4)
);
/
create type booklist as table of bookobj; -----------定義一個(gè)嵌套表，該表可以被嵌套在其他表中，他的字段就是bookobj中的字段
/
create table course_material(
department char(3),
course number(3),
required_reading booklist -----在表中再定義一個(gè)表，即booklist表，他就是嵌套在course_material中的表，也就是嵌套表。
------他和主表course_material之間是主從關(guān)系，即一條主表記錄對(duì)應(yīng)一個(gè)嵌套表中的多條記錄。
) nested table required_reading store as required_tab;
/
-----------------------給表和嵌套表輸入值，commit后被提交到數(shù)據(jù)庫里保存
declare
v_books booklist:=booklist(bookobj('ssss','www',444));-------定義一個(gè)booklist類型的嵌套表變量v_books，并給出初值。
begin
insert into course_material values('cs',101,booklist(bookobj('www','bbb',1),bookobj('aa','dd',33)));
insert into course_material values('his',301,v_books);
end;
或單獨(dú)插入一條記錄：
insert into course_material values('ss',102,booklist(bookobj('w','b',1),bookobj('a','d',3)));
-----------------------更新嵌套表操作，即將required_reading作為一個(gè)字段
declare
v_books booklist:=booklist(bookobj('xyz','bbb',111),bookobj('zq','ccc',222));
begin
update course_material
set required_reading = v_books
where department = 'his' and course = 301;
end;
----執(zhí)行結(jié)果為子記錄全部被刪除，新添加兩個(gè)bookobj記錄
-------------刪除嵌套表中的記錄
delete from course_material where department = 'his';----對(duì)應(yīng)主表his的嵌套表中的記錄將被刪除
＝＝＝＝＝＝＝＝＝＝＝＝＝直接對(duì)嵌套表進(jìn)行插入＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
insert into the(select required_reading from course_material where department='his' )
values('gog','ggg',999)
-----上面紅色的表示一個(gè)表，注意前面必須加上the，表示是嵌套表。
＝＝＝＝＝＝＝＝＝＝＝＝＝直接對(duì)嵌套表進(jìn)行更新＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
update the(select required_reading from course_material where department='his' )
set catalog_number = catalog_number + 10 -----對(duì)嵌套表中的字段進(jìn)行操作
where catalog_number = 111;
＝＝＝＝＝＝＝＝＝＝＝＝＝直接對(duì)嵌套表進(jìn)行刪除＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
delete from the(select required_reading from course_material where department='his' )
where catalog_number = 111;
=========================直接進(jìn)行嵌套表的查詢，只能返回主表的一條記錄的子表，即如果course_material返回多于2條的記錄則報(bào)錯(cuò)
select * from the(select required_reading from course_material where department='his');
或
select * from the(select required_reading from course_material where department='his') where catalog_number=999
報(bào)錯(cuò)：select * from the(select required_reading from course_material where department in ('his','www'))