Oracle提供两种使用嵌套表

简介: racle提供两种使用嵌套表的方法:1. PL/SQL代码中作为扩展PL/SQL语言;(这部分内容就是上边所说oracle内存表是oracle嵌套表的部分功能)2. 作为物理存储机制,以持久地存储集合。

racle提供两种使用嵌套表的方法:
1. PL/SQL代码中作为扩展PL/SQL语言;(这部分内容就是上边所说oracle内存表是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),
   deptno NUMBER(2) REFERENCES dept
  );
   
INSERT INTO dept SELECT * FROM scott.dept;
INSERT INTO emp SELECT * FROM scott.emp;

--创建type

CREATE 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 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 CONSTRAINT emps_empno_unique 
--嵌套表不支持参照完整性约束,不能参考任何其他表甚至自己
--给嵌套表增加数据,我们看看这两种方式的结果有何不同
方式1:INSERT INTO 
  dept_and_emp
  SELECT dept.*,
   CAST( 
  MULTISET( SELECT empno, ename, job, mgr, hiredate, sal, 
  comm
   FROM 
  emp
   WHERE emp.deptno 
  = dept.deptno ) AS emp_tab_type )
   FROM 
  dept;
--Oracle同样提供方法去掉集合的嵌套,像关系型表一样处理(能够将EMPS列当作一个表,并自然连接且不需要连接条件):
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_emp 
SELECT dept.*, CAST(MULTISET( SELECT empno, ename, job, mgr, hiredate, sal, comm
  FROM 
  emp,dept
   WHERE emp.deptno 
  = dept.deptno ) AS emp_tab_type ) 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语句插入数据,继续我们下边的测试。

--按照“每行实际是一张表”的思想来更新:
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=20)
  WHERE ename='SCOTT';

--一般而言,必须总是连接,而不能单独查询嵌套表(如emp_nest)中的数据,但是如果确实需要,是可以的。
--hint NESTED_TABLE_GET_REFS被用于EXP和IMP处理嵌套表。

  SELECT /*+NESTED_TABLE_GET_REFS+*/ NESTED_TABLE_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 VARCHAR2(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$是作为一个对象返回所有标量元素的一个伪列;另一个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 131072
  MINEXTENTS 1 MAXEXTENTS 4096
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUP 1
  BUFFER_POOL DEFAULT)
  TABLESPACE USER
  NESTED TABLE EMPS
  STORE AS EMPS_NEST
  RETURN 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 MAXTRANS 255 
  LOGGING STORAGE(INITIAL 131072 NEXT 131072
  MINEXTENTS 1 MAXEXTENTS 4096
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUP 1
  BUFFER_POOL DEFAULT) TABLESPACE USER
  NESTED TABLE EMPS
  STORE AS EMPS_NEST
  ((empno NOT NULL,
UNIQUE(empno),
PRIMARY KEY(nested_table_id,empno))
  ORGANIZATION 
  INDEX COMPRESS 1)
  RETURN BY VALUE;
   
  这样与最初默认的嵌套表相比,使用了较少的存储空间并有最需要的索引。
  不使用嵌套表作为永久存储机制的原因
  1.增加了RAW(16)列的额外开销,父表和子表都将增加这个额外的列;
  2.当通常已经有唯一约束时,父表上的唯一约束是额外开销;
  3.没有使用不支持的结构(NESTED_TABLE_GET_REFS),嵌套表不容易使用。
  一般推荐在编程结构和视图中使用嵌套表。如果要使用嵌套表作为存储机制,确保嵌套表是IOT,以避免NESTED_TABLE_ID和嵌套表本身中索引的额外开销。

目录
相关文章
|
存储 SQL Oracle
Oracle数据库批量删除表、视图、序列、存储过程、函数脚本
Oracle数据库批量删除表、视图、序列、存储过程、函数脚本
150 0
|
8月前
|
Oracle 关系型数据库 数据库
Oracle查询优化-复制表的定义及数据
【1月更文挑战第5天】【1月更文挑战第14篇】在Oracle数据库中,复制表定义和复制表数据是两个常见的操作。
121 1
|
8月前
|
消息中间件 关系型数据库 Kafka
实时计算 Flink版产品使用合集之oracle cdc 抽取新增一张表 可以从savepoint恢复吗
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
7月前
|
SQL Oracle 关系型数据库
实时计算 Flink版产品使用问题之oracle无主键的表支持同步吗如何实现
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
7月前
|
运维 DataWorks Oracle
DataWorks产品使用合集之在标准模式下,当同步Oracle的表或视图时,是否需要在源端的测试和生产环境中都存在要同步的表或视图
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
63 3
|
8月前
|
资源调度 Oracle 关系型数据库
实时计算 Flink版产品使用合集之同步Oracle数据时,一张表产生了大量的连接数,如何处理
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
8月前
|
SQL 存储 Oracle
Oracle中的Dual表:数据世界的“神奇小盒子”
【4月更文挑战第19天】Oracle的Dual表是一个虚拟表,仅含一行一列,常用于执行SQL函数、数据类型转换、测试语句和动态SQL。它是测试和便捷操作的工具,如获取当前日期(`SELECT SYSDATE FROM DUAL`)、数字转字符串(`SELECT TO_CHAR(12345) FROM DUAL`)。在存储过程、函数和触发器中也发挥重要作用,是数据库管理员的得力助手。
|
8月前
|
存储 Oracle 关系型数据库
Oracle系列之七:表的创建与管理
Oracle系列之七:表的创建与管理
|
Oracle 关系型数据库
Oracle - 表操作语句
Oracle - 表操作语句
51 0
|
Oracle 关系型数据库 数据库
Oracle数据库 查询所有表
Oracle数据库 查询所有表
195 1

推荐镜像

更多