Oracle嵌套表

一、介绍 

1、定义  

    嵌套表是表中之表。一个嵌套表是某些行的集合，它在主表中表示为其中的一列。对主表中的每一条记录，嵌套表可以包含多个行。在某种意义上，它是在一个表中存储一对多关系的一种方法。
    简单的说：就是把一个表中的字段定义为一个表，这个字段表的数据存储在外部的一个表中。嵌套表可以有效地代替多个表之间的连接。

补充：

当需要存储与表有关的数据时，可选的方案包括：可变数组、嵌套表、独立表。三种方案有什么区别，或者说什么情况下适用呢？

（1）可变数组：可变数组是不能被索引的，当记录量很大时，可变数组就会遇到性能问题，此外，从可变数组中查询数据比较困难，因此，当行数有限时，使用可变数组比较合适。

（2）嵌套表：嵌套表可以被索引，它是对象类型，故有与之关联的方法。所以，当需要将数据与方法联系在一起时，需要考虑使用嵌套表。

（3）关系表：关系表最大的好处是可以灵活地与多表关联，当数据需要与多表关联时，推荐使用标准关系表。

参考：《Oracle Database 12C完全参考手册》

2、Oracle提供两种使用嵌套表的方法

  (1).PL/SQL代码中作为扩展PL/SQL语言；(这部分内容就是上边所说Oracle内存表是oracle嵌套表的部分功能)
  (2).作为物理存储机制，以持久地存储集合(比较少)。

3、嵌套表的特点

    1、对象复用：如果编写面向对象的代码，就提高了重用以前编写的代码模块的机会。同样，如果创建面向对象的数据库对象，也就提高了数据库对象能够被重用的机会。
    2、标准支持：如果创建标准的对象，那么它们被重用的机会就会提高。如果有多个应用或多个表使用同一数据库对象集合，那么它就是既成事实的数据库对象标准。
    3、定义访问路径：对于每一个对象，用户可定义在其上运行的过程和函数，从而可以使数据和访问此数据的方法联合起来。有了用这种方式定义的访问路径，就可以标准化数据访问的方法并提高对象的可复用性。

二、测试

1、创建测试表emp、dept

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) ,
　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;

2、创建类型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)
);

3、创建嵌套表emp_tab_type

　　该表可以被嵌套在其他表中，它的字段就是emp_type中的字段

CREATE OR REPLACE TYPE emp_tab_type AS TABLE OF emp_type;

4、对自定义类型初始化

    如果嵌套表是直接参照一个物理表的结构创建的，则可以直接使用，如果嵌套表是自己定义的一个record，并根据record创建的嵌套表，则在使用时需要为record初始化，否则会报错

create or replace procedure mytest2 is
  CURSOR all_emps IS
    SELECT * FROM emp;
  TYPE emp_table IS TABLE OF  emp_tab_type;
  emps    emp_table;
  I       PLS_INTEGER;
  l_count PLS_INTEGER;
BEGIN
  l_count := 0;
  emps    := emp_table(); --  初始化嵌套表并产生一条空记录 
  FOR c1 IN all_emps LOOP
    l_count := l_count + 1;
    emps.EXTEND;
    emps(l_count) := emp_tab_type(null,null,null,null,null,null,null);
    emps(l_count).empno := c1.empno;
    emps(l_count).ename := c1.ename;
    emps(l_count).job := c1.job;
    emps(l_count).mgr := c1.mgr;
    emps(l_count).hiredate := c1.hiredate;
    emps(l_count).sal := c1.sal;
    emps(l_count).comm := c1.comm;
  END LOOP;

  -- clone the first entry five times 
  emps.EXTEND(5, 1);

  FOR i IN 1 .. l_count + 5 LOOP
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(emps(i).empno) || '      ');
  END LOOP;
END;

5、使用嵌套表

CREATE TABLE dept_and_emp(
　deptno NUMBER(2) PRIMARY KEY,
　dname VARCHAR2(14),
　loc VARCHAR2(13),
　emps emp_tab_type  --在表中再定义一个表，即emp_tab_type表，它就是嵌套在dept_and_emp中的表，也就是嵌套表。
                     --它和主表dept_and_emp之间是主从关系，即一条主表记录对应一个嵌套表中的多条记录。
)
NESTED TABLE emps STORE AS emps_nest;  --emps_nest是嵌套表列的存储表

6、插入数据

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;

注意：只有“4”行被创建。实际上在DEPT_AND_EMP表中只有4行。14个EMP行实际上不能独立存在。

注意提交commit;

7、查询插入结果

    Oracle同样提供方法table()去掉集合的嵌套，像关系型表一样处理（能够将EMPS列当作一个表，并自然连接且不需要连接条件）：

SELECT d.deptno, d.dname, emp.* FROM dept_and_emp D, TABLE(d.emps) emp;

8、更新记录

按照“每行实际是一张表”的思想来更新：

UPDATE TABLE( SELECT emps FROM dept_and_emp WHERE deptno = 10) SET comm = 100;

9、插入

INSERT INTO TABLE
(SELECT emps FROM dept_and_emp WHERE deptno = 10)
VALUES
(1234, 'NewEmp', 'Clerk', 7782, SYSDATE, 1200, NULL);

SELECT a.deptno, a.dname, EMP.* FROM dept_and_emp A, TABLE(A.EMPS) EMP; --查看结果，已经添加记录

10、删除　　 

DELETE FROM TABLE (SELECT emps FROM dept_and_emp WHERE deptno = 10)
WHERE ename = 'NewEmp';

SELECT a.deptno, a.dname, EMP.* FROM dept_and_emp A, TABLE(A.EMPS) EMP;; --查看结果已经删除记录

补充：

一般而言，必须总是连接，而不能单独查询嵌套表（如emp_nest）中的数据，但是如果确实需要，是可以的：

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);

参考材料：

《Oracle专家高级编程》
plsql使用自定义的嵌套表-http://blog.csdn.net/majian_1987/article/details/8557082?locationNum=14
oracle自定义类型-http://www.cnblogs.com/advocate/p/3729998.html
Oracle提供两种使用嵌套表 - http://blog.csdn.net/rudygao/article/details/25240427
oracle 嵌套表- http://www.cnblogs.com/oldcat/archive/2011/09/17/2179928.html
oracle 嵌套表- http://www.cnblogs.com/starzhao/archive/2010/05/19/1739234.html