Oracle嵌套表使用和存储分析.docx

1、Oracle嵌套表使用和存储分析Oracle嵌套表的使用一、嵌套表的定义： 嵌套表是表中之表。一个嵌套表是某些行的集合，它在主表中表示为其中的一列。对主表中的每一条记录，嵌套表可以包含多个行。在某种意义上，它是在一个表中存储一对多关系的一种方法。考查一个包含部门信息的表，在任何时间内每个部门会有很多项目正在实施。在一个严格的关系模型中，将需要建立两个独立的表department和project。嵌套表允许在department表中存放关于项目的信息。勿需执行联合操作，就可以通过department表直接访问项目表中的记录。这种不经联合而直接选择数据的能力使得用户对数据访问更加容易。甚至在并没有

2、定义方法来访问嵌套表的情况下，也能够很清楚地把部门和项目中的数据联系在一起。在严格的关系模型中，department和project两个表的联系需要通过外部关键字（外键）关系才能实现。二、举例说明嵌套表的使用：假设有一个关于动物饲养员的表，希望其中具有他们饲养的动物的信息。用一个嵌套表，就可以在同一个表中存储饲养员和其饲养的全部动物的信息。1、创建类型animal_ty：此类型中，对于每个动物都包含有一个记录，记载了其品种、名称和出生日期信息。CREATE TYPE animal_ty AS OBJECT (breed varchar2(25), name varchar2(25), (bir

3、thdate 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

4、-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 fr

5、om table(select animals from breeder where breedername=mary)where name=dog;三、嵌套表的特点： 1、对象复用：如果编写面向对象的代码，就提高了重用以前编写的代码模块的机会。同样，如果创建面向对象的数据库对象，也就提高了数据库对象能够被重用的机会。2、标准支持：如果创建标准的对象，那么它们被重用的机会就会提高。如果有多个应用或多个表使用同一数据库对象集合，那么它就是既成事实的数据库对象标准。3、定义访问路径：对于每一个对象，用户可定义在其上运行的过程和函数，从而可以使数据和访问此数据的方法联合起来。有了用这种方式定义的访问

6、路径，就可以标准化数据访问的方法并提高对象的可复用性。-以前在做报表的时候会经常用到oracle的内存表(其实是oracle嵌套表的部分功能，这里在下边介绍)来提高性能。利用oracle内存表进行临时运算通过ref cursor来返回我们想要的结果集。open cur for select * from table(fun_to_table_rb1_1(cur_qc,cur_qm);关于这部分的一些测试可以参看：最近把oracle嵌套表的其他功能仔细看了看并做了个简单整理。oracle提供两种使用嵌套表的方法：1 PL/SQL代码中作为扩展PL/SQL语言；(这部分内容就是上边所说oracle

7、内存表是oracle嵌套表的部分功能)2 作为物理存储机制，以持久地存储集合。*/-创建测试表：CREATE TABLE dept(deptno NUMBER(2) PRIMARY KEY, dname VARCHAR2(14), loc VARCHAR2(13); CREATE TABLE emp(empno NUMBER(4) PRIMARY KEY, ename VARCHAR2(10), job VARCHAR2(9), mgr NUMBER(4) REFERENCES emp, hiredate DATE, sal NUMBER(7,2), comm NUMBER(7,2), dep

8、tno NUMBER(2) REFERENCES dept); INSERT INTO dept SELECT * FROM ;INSERT INTO emp SELECT * FROM ;-创建typeCREATE OR REPLACE TYPE emp_type AS OBJECT(empno NUMBER(4), ename VARCHAR2(10), job VARCHAR2(9), mgr NUMBER(4), hiredate DATE, sal NUMBER(7,2), comm NUMBER(7,2); CREATE OR REPLACE TYPE emp_tab_type A

9、S TABLE OF emp_type;-使用嵌套表CREATE TABLE dept_and_emp(deptno NUMBER(2) PRIMARY KEY, dname VARCHAR2(14), loc VARCHAR2(13), emps emp_tab_type)NESTED TABLE emps STORE AS emps_nest;-可以在嵌套表上增加约束(这里我们先不执行此步骤，等做完下一步测试我们再创建约束)-ALTER TABLE emps_nt ADD CONSTRAINT emps_empno_unique -嵌套表不支持参照完整性约束，不能参考任何其他表甚至自己-给

10、嵌套表增加数据，我们看看这两种方式的结果有何不同方式1：INSERT INTO dept_and_empSELECT dept.*, CAST( MULTISET( SELECT empno, ename, job, mgr, hiredate, sal, comm FROM emp WHERE = ) AS emp_tab_type ) FROM dept;-Oracle同样提供方法去掉集合的嵌套，像关系型表一样处理（能够将EMPS列当作一个表，并自然连接且不需要连接条件）：SELECT , , emp.* FROM dept_and_emp D, TABLE emp;-这里执行看到结果是1

11、4条数据delete from dept_and_emp;方式2：INSERT INTO dept_and_emp SELECT dept.*, CAST(MULTISET( SELECT empno, ename, job, mgr, hiredate, sal, commFROM emp,dept WHERE = ) AS emp_tab_type ) from dept;SELECT , , emp.* FROM dept_and_emp D, TABLE emp;-这里执行看到结果是56条数据，显然是错误的-第一个是按照where等连接条件符合的某一个dept的emp表的数据作为一个集

12、合存储，而第二个没有任何关联条件，就是把所有emp的数据-全部作为一个dept的数据存储，这个写法显然是错误的，如果我们把刚才讲的约束给嵌套表加上，就可以起到防止这种错误的功效了。-增加约束再执行我们上边的第二个insert语句将会报错-我们按照上边第一个insert语句插入数据，继续我们下边的测试。-按照“每行实际是一张表”的思想来更新：UPDATE TABLE( SELECT emps FROM dept_and_emp WHERE deptno = 10) SET comm = 100;-插入与删除的语法：INSERT INTO TABLE(SELECT emps FROM dept_a

13、nd_emp WHERE deptno=10)VALUES (1234,NewEmp,Clerk,7782,SYSDATE,1200,NULL); DELETE FROM TABLE(SELECT emps FROM dept_and_emp WHERE deptno=20)WHERE ename=SCOTT;-一般而言，必须总是连接，而不能单独查询嵌套表（如emp_nest）中的数据，但是如果确实需要，是可以的。-hint NESTED_TABLE_GET_REFS被用于EXP和IMP处理嵌套表。SELECT /*+NESTED_TABLE_GET_REFS+*/ NESTED_TABLE_

14、ID, SYS_NC_ROWINFO$ FROM emps_nest;-而察看EMPS_NEST的结构看不到NESTED_TABLE_ID,SYS_NC_ROWINFO$两列。对父表DEPT_AND_EMP来说NESTED_TABLE_ID是一个外键。-使用这个hint就可以直接操作嵌套表了：UPDATE /*+NESTED_TABLE_GET_REFS+*/ emps_nest SET ename=INITCAP(ename);-嵌套表的存储:-上例中，现实产生了两张表：/*DEPT_AND_EMP(deptnob NUMBER(2),dname VARCHAR2(14),loc VARCH

15、AR2(13),SYS_NC0000400005$,RAW(16)EMPS_NEST(SYS_NC_ROWINFO$,NESTED_TABLE_ID,RAW(16),empno NUMBER(4),ename VARCHAR2(10),job VARCHAR2(9),mgr NUMBER(4),hiredate DATE,sal NUMBER(7,2),comm NUMBER(7,2)*/ -默认情况下，每个嵌套表列都产生一个额外的RAW(16)隐藏列，并在其上创建了唯一约束，用以指向嵌套表。而嵌套表中有两个-隐藏列：SYS_NC_ROWINFO$是作为一个对象返回所有标量元素的一个伪列；另一

16、个NESTED_TABLE_ID的外键回指向父表。-可以看到真实代码：/*CREATE TABLE DEPT_AND_EMP(DEPTNO NUMBER(2,0), DNAME VARCHAR2(14), LOC VARCHAR2(13), EMPS EMP_TAB_TYPE)PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING STORAGE(INITIAL 131072 NEXT 131072MINEXTENTS 1 MAXEXTENTS 4096PCTINCREASE 0 FREELISTS 1 FREELIST GROUP 1BUF

17、FER_POOL DEFAULT)TABLESPACE USERNESTED TABLE EMPSSTORE AS EMPS_NESTRETURN BY VALUE; RETURN BY VALUE用来描述嵌套表如何返回到客户应用程序中。NESTED_TABLE_ID列必须是索引的，那么较好的解决办法就是使用IOT存储嵌套表。CREATE TABLE DEPT_AND_EMP(DEPTNO NUMBER(2,0), DNAME VARCHAR2(14), LOC VARCHAR2(13), EMPS EMP_TAB_TYPE)PCTFREE 10 PCTUSED 40 INITRANS 1 M

18、AXTRANS 255 LOGGING STORAGE(INITIAL 131072 NEXT 131072MINEXTENTS 1 MAXEXTENTS 4096PCTINCREASE 0 FREELISTS 1 FREELIST GROUP 1BUFFER_POOL DEFAULT) TABLESPACE USERNESTED TABLE EMPSSTORE AS EMPS_NEST(empno NOT NULL,UNIQUE(empno),PRIMARY KEY(nested_table_id,empno)ORGANIZATION INDEX COMPRESS 1)RETURN BY V

19、ALUE; 这样与最初默认的嵌套表相比，使用了较少的存储空间并有最需要的索引。不使用嵌套表作为永久存储机制的原因1增加了RAW(16)列的额外开销，父表和子表都将增加这个额外的列；2当通常已经有唯一约束时，父表上的唯一约束是额外开销；3没有使用不支持的结构（NESTED_TABLE_GET_REFS），嵌套表不容易使用。一般推荐在编程结构和视图中使用嵌套表。如果要使用嵌套表作为存储机制，确保嵌套表是IOT，以避免NESTED_TABLE_ID和嵌套表本身中索引的额外开销。以上参考oracle高级专家编程。本文来自CSDN博客，转载请标明出处：可变数组 一、可变数组的定义： 可变数组与嵌套表相似

20、，也是一种集合。一个可变数组是对象的一个集合，其中每个对象都具有相同的数据类型。可变数组的大小由创建时决定。在表中建立可变数组后，可变数组在主表中作为一个列对待。从概念上讲，可变数组是一个限制了行集合的嵌套表。可变数组，允许用户在表中存储重复的属性。例如：假设用户有一个project表，并在项目中指定了工作人员，一个项目可以有多个工人，而一个工人也可以为多个项目工作。在严格的关系模型中，用户可以创建一个project表，一个worker表和存储它们之间关系的交叉表project_worker。用户可使用可变数组在project表中存储工人的名字。如果项目限定的工人数不超过10人，可以建立一个以

21、10个数据项为限的可变数组。接下来就可处理此可变数组，从而对于每一个项目，可以选取其中所有工人的名字，而勿需查询表worker。二、举例说明可变数组的使用：1、创建类型comm_infoCREATE TYPE comm_info AS OBJECT ( /*此类型为通讯方式的集合no number(3), /*通讯类型号】comm_type varchar2(20), /*通讯类型comm_no varchar2(30); /*号码2、创建可变数组comm_info_listCREATE TYPE comm_info_list AS VARRAY(50) OF comm_info;3、创建表c

22、reate table user_info(user_id number(6), /*用户ID号user_name varchar2(20), /*用户名称user_comm comm_info_list); /*与用户联系的通讯方式-4、向可变数组插入记录insert into user_infovalues(1,mary,comm_info_list(comm_info(1,手机,),comm_info(2,呼机,67);insert into user_infovalues(2,carl,comm_info_list(comm_info(1,手机,),comm_info(2,呼机,67)

23、;commit;5、查询可变数组select user_comm from user_info where user_id=1; select comm_type,comm_no from table(select user_comm from user_info where user_id=1)where no=1;与一位用户联系的方式有很多种，比如：手机、呼机、座机等。在一个严格的关系模型中，将需要两个独立的表：用户信息和通讯方式，而在可变数组中，允许在表user_info中直接访问用户的联系方式，这种不经联合而直接选择数据的能力使得用户对数据的访问更加容易。三、可变数组的特点：1、对象复用：如果编写面向对象的代码，就提高了重用以前编写的代码模块的机会。同样，如果创建面向对象的数据库对象，也就提高了数据库对象能够被重用的机会。2、标准支持：如果创建标准的对象，那么它们被重用的机会就会提高。如果有多个应用或多个表使用同一数据库对象集合，那么它就是既成事实的数据库对象标准。3定义访问路径：对于每一个对象，用户可定义在其上运行的过程和函数，从而可以使数据和访问此数据的方法联合起来。有了用这种方式定义的访问路径，就可以标准化数据访问的方法并提高对象的可复用性