《Oracle嵌套表使用和存储分析.doc》由会员分享,可在线阅读,更多相关《Oracle嵌套表使用和存储分析.doc(8页珍藏版)》请在三一文库上搜索。
1、Oracle 嵌套表的使用一、嵌套表的定义:嵌套表是表中之表。 一个嵌套表是某些行的集合, 它在主表中表示为其中的 一列。对主表中的每一条记录,嵌套表可以包含多个行。在某种意义上,它是在 一个表中存储一对多关系的一种方法。 考查一个包含部门信息的表, 在任何时间 每个部门会有很多项目正在实施。 在一个严格的关系模型中, 将需要建立两个独 立的表 department 和 project 。嵌套表允许在 department 表中存放关于项目的信息。勿需执行联合操作, 就可以通过 department 表直接访问项目表中的记录。这种不经联合而直接选择 数据的能力使得用户对数据访问更加容易。 甚至
2、在并没有定义方法来访问嵌套表 的情况下, 也能够很清楚地把部门和项目中的数据联系在一起。 在严格的关系模 型中, department 和 project 两个表的联系需要通过外部关键字(外键)关系 才能实现。二、举例说明嵌套表的使用:假设有一个关于动物饲养员的表, 希望其中具有他们饲养的动物的信息。 用 一个嵌套表,就可以在同一个表中存储饲养员和其饲养的全部动物的信息。1、创建类型 animal_ty :此类型中,对于每个动物都包含有一个记录,记载了 其品种、名称和出生日期信息。CREATE TYPE animal_ty AS OBJECT (breed varchar2(25),name
3、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(anima
4、l_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);
5、select name,birthdate fromtable(select animals from breederwhere breedername= mary) where name=dog;三、嵌套表的特点:1、对象复用:如果编写面向对象的代码,就提高了重用以前编写的代码模块的机会。同样, 如果创建面向对象的数据库对象, 也就提高了数据库对象能够被重 用的机会。2、标准支持:如果创建标准的对象,那么它们被重用的机会就会提高。如果有 多个应用或多个表使用同一数据库对象集合, 那么它就是既成事实的数据库对象 标准。3、定义访问路径:对于每一个对象,用户可定义在其上运行的过程和函数,从 而可
6、以使数据和访问此数据的方法联合起来。有了用这种方式定义的访问路径, 就可以标准化数据访问的方法并提高对象的可复用性。以前在做报表的时候会经常用到 oracle的存表(其实是oracle嵌套表的部分功 能,这里在下边介绍)来提高性能。利用oracle存表进行临时运算通过ref cursor 来返回我们想要的结果集。ope n cur for select * from table(fu n_to_table_rb1_1(cur_qc,cur_qm);关于这部分的一些测试可以参-看:最近把oracle嵌套表的其他功能仔细看了看并做了个简单整理。oracle提供两种使用嵌套表的方法:1. PL/SQ
7、L代码中作为扩展PL/SQL语言;(这部分容就是上边所说oracle存表 是oracle嵌套表的部分功能)2. 作为物理存储机制,以持久地存储集合。*/-创建测试表:CREATE TABLE dept(dept no NUMBER(2) PRIMARY KEY,dn ame VARCHAR2(14),loc VARCHAR2(13);CREATE TABLE emp(emp no NUMBER(4) PRIMARY KEY,en ame VARCHAR2(10),job VARCHAR2(9),mgr NUMBER(4) REFERENCES emp,hiredate DATE,sal NUM
8、BER(7,2),comm NUMBER(7,2),dept no NUMBER(2) REFERENCES dept);INSERT INTO dept SELECT * FROM scott.dept;INSERT INTO emp SELECT * FROM scott.emp;-仓U建 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
9、 NUMBER(7,2);CREATE OR REPLACE TYPE emp_tab_type AS 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 CO
10、NSTRAINT emps_empno_unique- 嵌套表不支持参照完整性约束,不能参考任何其他表甚至自己- 给嵌套表增加数据,我们看看这两种方式的结果有何不同 方式 1:INSERT INTOdept_and_emp SELECT dept.*, CAST(MULTISET( SELECT empno, ename, job, mgr, hiredate, sal, comm FROM empWHERE emp.deptno= dept.deptno ) AS emp_tab_type ) FROM dept;-Oracle同样提供方法去掉集合的嵌套,像关系型表一样处理(能够将EMPS当
11、作一个表,并自然连接且不需要连接条件):SELECT d.deptno, d.dname, emp.* FROM dept_and_emp D, TABLE(d.emps) emp; - 这里执行看到结果是 14 条数据delete from dept_and_emp;方式 2:INSERT INTO dept_and_empSELECT dept.*, CAST(MULTISET( SELECT empno, ename, job, mgr, hiredate, sal, commFROMemp,deptWHERE emp.deptno= dept.deptno ) AS emp_tab_t
12、ype ) from dept;SELECT d.deptno, d.dname, emp.* FROM dept_and_emp D, TABLE(d.emps) emp;- 这里执行看到结果是 56 条数据,显然是错误的-第一个是按照where等连接条件符合的某一个dept的emp表的数据作为一个 集合存储,而第二个没有任何关联条件,就是把所有emp的数据- 全部作为一个 dept 的数据存储,这个写法显然是错误的,如果我们把刚才讲 的约束给嵌套表加上,就可以起到防止这种错误的功效了。- 增加约束再执行我们上边的第二个 insert 语句将会报错- 我们按照上边第一个 insert 语句插
13、入数据,继续我们下边的测试。- 按照“每行实际是一表”的思想来更新:UPDATE TABLE( SELECT emps FROM dept_and_emp WHERE deptno = 10) SET comm = 100;- 插入与删除的语法:INSERT INTO TABLE(SELECT emps FROM dept_and_emp WHERE deptno=10) VALUES (1234,NewEmp,Clerk,7782,SYSDATE,1200,NULL);DELETE FROM TABLE(SELECT emps FROM dept_and_emp WHERE deptno=2
14、0) WHERE ename=SCOTT;-一般而言,必须总是连接,而不能单独查询嵌套表(如emp_nest)中的数据,但是如果确实需要,是可以的。-hint NESTED_TABLE_GET_REF被用于 EXP和 IMP处理嵌套表。SELECT /*+NESTED_TABLE_GET_REFS+*/ NESTED_TABLE_ID, SYS_NC_ROWINFO$ FROM emps_nest;-而察看 EMPS_NES的结构看不至U NESTED_TABLE_ID,SYS_NC_ROW两列对 父表 DEPT_AND_EMP说 NESTED_TABLEJ是一个外键。- 使用这个 hint
15、就可以直接操作嵌套表了:UPDATE /*+NESTED_TABLE_GET_REFS+*/ emps_nest SET ename=INITCAP(ename);- 嵌套表的存储 :- 上例中,现实产生了两表:/*DEPT_AND_EMP (deptnob NUMBER(2), dname VARCHAR2(14), loc VARCHAR2(13), SYS_NC0000400005$,RAW(16)EMPS_NEST (SYS_NC_ROWINFO$, NESTED_TABLE_ID,RAW(16),empno NUMBER(4), ename VARCHAR2(10), job VAR
16、CHAR2(9), mgr NUMBER(4), hiredate DATE, sal NUMBER(7,2), comm NUMBER(7,2)*/-默认情况下,每个嵌套表列都产生一个额外的RAW(16隐藏列,并在其上创建了唯一约束,用以指向嵌套表。而嵌套表中有两个-隐藏列:SYS_NC_ROWIN是作为一个对象返回所有标量元素的一个伪列;另 一个NESTED_TABLEJ的外键回指向父表。- 可以看到真实代码:/*CREATE TABLE DEPT_AND_EMP (DEPTNO NUMBER(2,0), DNAME VARCHAR2(14), LOC VARCHAR2(13),EMPS
17、EMP_TAB_TYPE)PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255LOGGING STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 4096PCTINCREASE 0 FREELISTS 1 FREELIST GROUP 1 BUFFER_POOL DEFAULT)TABLESPACE USER NESTED TABLE EMPS STORE AS EMPS_NEST RETURN BY VALUE;RETURN BY VALUE*描述嵌套表如何返回到客户应用程序中。NESTE
18、D_TABLEJD必须是索引的,那么较好的解决办法就是使用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 MAXTRANS 255LOGGING STORAGE(INITIAL 131072 NEXT 131072MINEXTENTS 1 MAXEXTENTS 4096PCTINCREASE 0 FREELISTS 1 FREELIST GROUP 1BUFFE
19、R_POOL DEFAULT) TABLESPACE USERNESTED TABLE EMPSSTORE AS EMPS_NEST(emp no NOT NULL,UNIQUE(emp no),PRIMARY KEY( nested_table_id,emp no)ORGANIZATIONINDEX COMPRESS 1)RETURN BY VALUE;这样与最初默认的嵌套表相比,使用了较少的存储空间并有最需要的索引。 不使用嵌套表作为永久存储机制的原因1 增加了 RAW(16列的额外开销,父表和子表都将增加这个额外的列;2. 当通常已经有唯一约束时,父表上的唯一约束是额外开销;3. 没有使
20、用不支持的结构(NESTED_TABLE_GET_REJF嵌套表不容易使用一般推荐在编程结构和视图中使用嵌套表。如果要使用嵌套表作为存储机 制,确保嵌套表是IOT,以避免NESTED_TABLE_I和嵌套表本身中索引的额外开销。以上参考oracle高级专家编程。本文来自CSDN#客,请标明出处:blog.csd n.n et/wxf82610/archive/2007/09/18/1789513.aspx可变数组一、可变数组的定义:可变数组与嵌套表相似,也是一种集合。一个可变数组是对象的一个集合, 其中每个对象都具有相同的数据类型。可变数组的大小由创建时决定。在表中建 立可变数组后,可变数组在
21、主表中作为一个列对待。从概念上讲,可变数组是一 个限制了行集合的嵌套表。可变数组,允许用户在表中存储重复的属性。 例如:假设用户有一个 project 表,并在项目中指定了工作人员, 一个项目可以有多个工人, 而一个工人也可以 为多个项目工作。在严格的关系模型中,用户可以创建一个 project 表,一个 worker 表和存储它们之间关系的交叉表 project_worker 。用户可使用可变数组在 project 表中存储工人的名字。 如果项目限定的工人 数不超过 10 人,可以建立一个以 10 个数据项为限的可变数组。 接下来就可处理 此可变数组, 从而对于每一个项目, 可以选取其中所有
22、工人的名字, 而勿需查询 表 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 ASVARRAY(50) OF comm_info;3、创建表create table user_info(user_id number(6),
23、/* 用户 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, 呼机 ,1281234567);insert into user_infovalues(2,carl,comm_info_list(comm_info(1, 手机 ,),comm_info(2, 呼机 ,1281234567);commit;5、查询可变
24、数组select user_comm from user_infowhere user_id=1;select comm_type,comm_nofrom table(select user_comm from user_infowhere user_id=1)where no=1;与一位用户联系的方式有很多种,比如:手机、呼机、座机等。在一个严格 的关系模型中,将需要两个独立的表:用户信息和通讯方式,而在可变数组中, 允许在表 user_info 中直接访问用户的联系方式, 这种不经联合而直接选择数据 的能力使得用户对数据的访问更加容易。三、可变数组的特点:1、对象复用:如果编写面向对象的代码,就提高了重用以前编写的代码模块的 机会。同样, 如果创建面向对象的数据库对象, 也就提高了数据库对象能够被重 用的机会。2、标准支持:如果创建标准的对象,那么它们被重用的机会就会提高。如果有 多个应用或多个表使用同一数据库对象集合, 那么它就是既成事实的数据库对象 标准。3定义访问路径:对于每一个对象,用户可定义在其上运行的过程和函数,从而 可以使数据和访问此数据的方法联合起来。 有了用这种方式定义的访问路径, 就 可以标准化数据访问的方法并提高对象的可复用性