Oracle嵌套表使用和存储分析.docx
《Oracle嵌套表使用和存储分析.docx》由会员分享，可在线阅读，更多相关《Oracle嵌套表使用和存储分析.docx（10页珍藏版）》请在冰豆网上搜索。
Oracle嵌套表使用和存储分析
Oracle嵌套表的使用
一、嵌套表的定义：
嵌套表是表中之表。
一个嵌套表是某些行的集合，它在主表中表示为其中的一列。
对主表中的每一条记录，嵌套表可以包含多个行。
在某种意义上，它是在一个表中存储一对多关系的一种方法。
考查一个包含部门信息的表，在任何时间内每个部门会有很多项目正在实施。
在一个严格的关系模型中，将需要建立两个独立的表department和project。
嵌套表允许在department表中存放关于项目的信息。
勿需执行联合操作，就可以通过department表直接访问项目表中的记录。
这种不经联合而直接选择数据的能力使得用户对数据访问更加容易。
甚至在并没有定义方法来访问嵌套表的情况下，也能够很清楚地把部门和项目中的数据联系在一起。
在严格的关系模型中，department和project两个表的联系需要通过外部关键字（外键）关系才能实现。
二、举例说明嵌套表的使用：
假设有一个关于动物饲养员的表，希望其中具有他们饲养的动物的信息。
用一个嵌套表，就可以在同一个表中存储饲养员和其饲养的全部动物的信息。
1、创建类型animal_ty：
此类型中，对于每个动物都包含有一个记录，记载了其品种、名称和出生日期信息。
CREATETYPEanimal_tyASOBJECT（
breedvarchar2（25）,
namevarchar2（25）,
（
birthdatedate）;
2、创建animals_nt：
此类型将用作一个嵌套表的基础类型。
CREATETYPEanimals_ntastableofanimal_ty;
3、创建表breeder：
饲养员的信息表
createtablebreeder
（breedernamevarchar2（25）,
animalsanimal_nt）
nestedtableanimalsstoreasanimals_nt_tab;
4、向嵌套表中插入记录
insertintobreeder
，
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'）））;
insertintobreeder
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、查询嵌套表
selectname,birthdatefrom
；
table（selectanimalsfrombreeder）;
selectname,birthdatefrom
table（selectanimalsfrombreeder
wherebreedername=’mary’）
wherename=’dog’;
三、嵌套表的特点：
1、对象复用：
如果编写面向对象的代码，就提高了重用以前编写的代码模块的机会。
同样，如果创建面向对象的数据库对象，也就提高了数据库对象能够被重用的机会。
2、标准支持：
如果创建标准的对象，那么它们被重用的机会就会提高。
如果有多个应用或多个表使用同一数据库对象集合，那么它就是既成事实的数据库对象标准。
3、定义访问路径：
对于每一个对象，用户可定义在其上运行的过程和函数，从而可以使数据和访问此数据的方法联合起来。
有了用这种方式定义的访问路径，就可以标准化数据访问的方法并提高对象的可复用性。
-----------------------------------------------------------------
`
以前在做报表的时候会经常用到oracle的内存表（其实是oracle嵌套表的部分功能，这里在下边介绍）来提高性能。
利用oracle内存表进行临时运算通过refcursor来返回我们想要的结果集。
opencurforselect*fromtable（fun_to_table_rb1_1（cur_qc,cur_qm））;
关于这部分的一些测试可以参看：
最近把oracle嵌套表的其他功能仔细看了看并做了个简单整理。
oracle提供两种使用嵌套表的方法：
1．PL/SQL代码中作为扩展PL/SQL语言；（这部分内容就是上边所说oracle内存表是oracle嵌套表的部分功能）
2．作为物理存储机制，以持久地存储集合。
*/
--创建测试表：
CREATETABLEdept
（deptnoNUMBER
（2）PRIMARYKEY,
dnameVARCHAR2（14）,
locVARCHAR2（13）
）;
CREATETABLEemp
（empnoNUMBER（4）PRIMARYKEY,
enameVARCHAR2（10）,
jobVARCHAR2（9）,
mgrNUMBER（4）REFERENCESemp,
hiredateDATE,
salNUMBER（7,2）,
commNUMBER（7,2）,
deptnoNUMBER
（2）REFERENCESdept
）;
INSERTINTOdeptSELECT*FROM;
INSERTINTOempSELECT*FROM;
--创建type
CREATEORREPLACETYPEemp_typeASOBJECT
（empnoNUMBER（4）,
enameVARCHAR2（10）,
jobVARCHAR2（9）,
mgrNUMBER（4）,
hiredateDATE,
salNUMBER（7,2）,
commNUMBER（7,2）
）;
CREATEORREPLACETYPEemp_tab_typeASTABLEOFemp_type;
--使用嵌套表
CREATETABLEdept_and_emp
（deptnoNUMBER
（2）PRIMARYKEY,
dnameVARCHAR2（14）,
locVARCHAR2（13）,
empsemp_tab_type
）
NESTEDTABLEempsSTOREASemps_nest;
--可以在嵌套表上增加约束（这里我们先不执行此步骤，等做完下一步测试我们再创建约束）
--ALTERTABLEemps_ntADDCONSTRAINTemps_empno_unique
--嵌套表不支持参照完整性约束，不能参考任何其他表甚至自己
--给嵌套表增加数据，我们看看这两种方式的结果有何不同
方式1：
INSERTINTO
dept_and_emp
SELECTdept.*,
CAST（
MULTISET（SELECTempno,ename,job,mgr,hiredate,sal,
comm
FROM
emp
WHERE
=）ASemp_tab_type）
FROM
dept;
--Oracle同样提供方法去掉集合的嵌套，像关系型表一样处理（能够将EMPS列当作一个表，并自然连接且不需要连接条件）：
SELECT,,emp.*FROMdept_and_empD,TABLEemp;
--这里执行看到结果是14条数据
—
deletefromdept_and_emp;
方式2：
INSERTINTOdept_and_emp
SELECTdept.*,CAST（MULTISET（SELECTempno,ename,job,mgr,hiredate,sal,comm
FROM
emp,dept
WHERE
=）ASemp_tab_type）fromdept;
SELECT,,emp.*FROMdept_and_empD,TABLEemp;
--这里执行看到结果是56条数据，显然是错误的
--第一个是按照where等连接条件符合的某一个dept的emp表的数据作为一个集合存储，而第二个没有任何关联条件，就是把所有emp的数据
--全部作为一个dept的数据存储，这个写法显然是错误的，如果我们把刚才讲的约束给嵌套表加上，就可以起到防止这种错误的功效了。
--增加约束再执行我们上边的第二个insert语句将会报错
--我们按照上边第一个insert语句插入数据，继续我们下边的测试。
--按照“每行实际是一张表”的思想来更新：
UPDATETABLE（SELECTempsFROMdept_and_empWHEREdeptno=10）SETcomm=100;
--插入与删除的语法：
INSERTINTOTABLE（SELECTempsFROMdept_and_empWHEREdeptno=10）
VALUES（1234,'NewEmp','Clerk',7782,SYSDATE,1200,NULL）;
DELETEFROMTABLE（SELECTempsFROMdept_and_empWHEREdeptno=20）
WHEREename='SCOTT';
--一般而言，必须总是连接，而不能单独查询嵌套表（如emp_nest）中的数据，但是如果确实需要，是可以的。
--hintNESTED_TABLE_GET_REFS被用于EXP和IMP处理嵌套表。
SELECT/*+NESTED_TABLE_GET_REFS+*/NESTED_TABLE_ID,SYS_NC_ROWINFO$FROMemps_nest;
--而察看EMPS_NEST的结构看不到NESTED_TABLE_ID,SYS_NC_ROWINFO$两列。
对父表DEPT_AND_EMP来说NESTED_TABLE_ID是一个外键。
--使用这个hint就可以直接操作嵌套表了：
UPDATE/*+NESTED_TABLE_GET_REFS+*/emps_nestSETename=INITCAP（ename）;
--嵌套表的存储:
--上例中，现实产生了两张表：
/*
DEPT_AND_EMP
（deptnobNUMBER
（2）,
dnameVARCHAR2（14）,
locVARCHAR2（13）,
SYS_NC0000400005$,
RAW（16））
EMPS_NEST
（SYS_NC_ROWINFO$,
NESTED_TABLE_ID,
RAW（16）,
empnoNUMBER（4）,
enameVARCHAR2（10）,
jobVARCHAR2（9）,
mgrNUMBER（4）,
hiredateDATE,
salNUMBER（7,2）,
commNUMBER（7,2））
*/
--默认情况下，每个嵌套表列都产生一个额外的RAW（16）隐藏列，并在其上创建了唯一约束，用以指向嵌套表。
而嵌套表中有两个
--隐藏列：
SYS_NC_ROWINFO$是作为一个对象返回所有标量元素的一个伪列；另一个NESTED_TABLE_ID的外键回指向父表。
--可以看到真实代码：
/*
CREATETABLEDEPT_AND_EMP
（DEPTNONUMBER（2,0）,
DNAMEVARCHAR2（14）,
LOCVARCHAR2（13）,
EMPSEMP_TAB_TYPE）
PCTFREE10PCTUSED40INITRANS1MAXTRANS255
LOGGINGSTORAGE（INITIAL131072NEXT131072
MINEXTENTS1MAXEXTENTS4096
PCTINCREASE0FREELISTS1FREELISTGROUP1
BUFFER_POOLDEFAULT）
TABLESPACEUSER
NESTEDTABLEEMPS
STOREASEMPS_NEST
RETURNBYVALUE;
RETURNBYVALUE用来描述嵌套表如何返回到客户应用程序中。
NESTED_TABLE_ID列必须是索引的，那么较好的解决办法就是使用IOT存储嵌套表。
CREATETABLEDEPT_AND_EMP
（DEPTNONUMBER（2,0）,
DNAMEVARCHAR2（14）,
LOCVARCHAR2（13）,
EMPSEMP_TAB_TYPE）
PCTFREE10PCTUSED40INITRANS1MAXTRANS255
LOGGINGSTORAGE（INITIAL131072NEXT131072
MINEXTENTS1MAXEXTENTS4096
PCTINCREASE0FREELISTS1FREELISTGROUP1
BUFFER_POOLDEFAULT）TABLESPACEUSER
NESTEDTABLEEMPS
STOREASEMPS_NEST
（（empnoNOTNULL,
UNIQUE（empno）,
PRIMARYKEY（nested_table_id,empno））
ORGANIZATION
INDEXCOMPRESS1）
RETURNBYVALUE;
这样与最初默认的嵌套表相比，使用了较少的存储空间并有最需要的索引。
不使用嵌套表作为永久存储机制的原因
1．增加了RAW（16）列的额外开销，父表和子表都将增加这个额外的列；
2．当通常已经有唯一约束时，父表上的唯一约束是额外开销；
3．没有使用不支持的结构（NESTED_TABLE_GET_REFS），嵌套表不容易使用。
一般推荐在编程结构和视图中使用嵌套表。
如果要使用嵌套表作为存储机制，
确保嵌套表是IOT，以避免NESTED_TABLE_ID和嵌套表本身中索引的额外开销。
<
以上参考oracle高级专家编程。
本文来自CSDN博客，转载请标明出处：
可变数组
一、可变数组的定义：
可变数组与嵌套表相似，也是一种集合。
一个可变数组是对象的一个集合，其中每个对象都具有相同的数据类型。
可变数组的大小由创建时决定。
在表中建立可变数组后，可变数组在主表中作为一个列对待。
从概念上讲，可变数组是一个限制了行集合的嵌套表。
可变数组，允许用户在表中存储重复的属性。
例如：
假设用户有一个project表，并在项目中指定了工作人员，一个项目可以有多个工人，而一个工人也可以为多个项目工作。
在严格的关系模型中，用户可以创建一个project表，一个worker表和存储它们之间关系的交叉表project_worker。
用户可使用可变数组在project表中存储工人的名字。
如果项目限定的工人数不超过10人，可以建立一个以10个数据项为限的可变数组。
接下来就可处理此可变数组，从而对于每一个项目，可以选取其中所有工人的名字，而勿需查询表worker。
二、举例说明可变数组的使用：
1、创建类型comm_info
CREATETYPEcomm_infoASOBJECT（/*此类型为通讯方式的集合
nonumber（3）,/*通讯类型号
】
comm_typevarchar2（20）,/*通讯类型
comm_novarchar2（30））;/*号码
2、创建可变数组comm_info_list
CREATETYPEcomm_info_listAS
VARRAY（50）OFcomm_info;
3、创建表
createtableuser_info
（user_idnumber（6）,/*用户ID号
user_namevarchar2（20）,/*用户名称
user_commcomm_info_list）;/*与用户联系的通讯方式
-
4、向可变数组插入记录
insertintouser_info
values（1,'mary',comm_info_list（comm_info（1,'手机',''）,
comm_info（2,'呼机','67'）））;
insertintouser_info
values（2,'carl',comm_info_list（comm_info（1,'手机',''）,
comm_info（2,'呼机','67'）））;
commit;
5、查询可变数组
selectuser_commfromuser_info
<
whereuser_id=1;
selectcomm_type,comm_no
fromtable（selectuser_commfromuser_info
whereuser_id=1）
whereno=1;
与一位用户联系的方式有很多种，比如：
手机、呼机、座机等。
在一个严格的关系模型中，将需要两个独立的表：
用户信息和通讯方式，而在可变数组中，允许在表user_info中直接访问用户的联系方式，这种不经联合而直接选择数据的能力使得用户对数据的访问更加容易。
三、可变数组的特点：
1、对象复用：
如果编写面向对象的代码，就提高了重用以前编写的代码模块的机会。
同样，如果创建面向对象的数据库对象，也就提高了数据库对象能够被重用的机会。
2、标准支持：
如果创建标准的对象，那么它们被重用的机会就会提高。
如果有多个应用或多个表使用同一数据库对象集合，那么它就是既成事实的数据库对象标准。
3定义访问路径：
对于每一个对象，用户可定义在其上运行的过程和函数，从而可以使数据和访问此数据的方法联合起来。
有了用这种方式定义的访问路径，就可以标准化数据访问的方法并提高对象的可复用性
升级会员 