중첩 테이블

• 중첩 테이블(nested table)은 객체 관계(object-relationaI)가 확장된 것이다.
• 중첩 테이블은 두 집합 유형의 한부분으로, 전통적인 부모-자식 관계인 관계형 구조에서 자식테이블과 매우유사하다.
• 모두동일한 데이터타입을 가지는 정렬되지 않는 데이터 구조고 내장 (builtt-in) 데이터타입이거나 객체 데이터타입으로 정의될 수 있다.
• 그러나 부모 테이블의 각 로우가 자신만의 자식 테이블을 가지고 있다는 착각을 주기 위해 설계되었기 때문에, 기존의 부모/자식 테이블보다는 한 단계 더 진보한 것이다.
• 만약 부모 테이블에 로우가 100 개 존재한다면 가상의 중첩 태이 블이 100 개 존재하게 된다.
• 그러나 물리적으로, 하나의 부모 테이 블에는 하나의 자식 테이블이 존재한다.

-- 관계형 문법
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(empno)
  , hiredate DATE
  , sal      NUMBER(7, 2)
  , comm     NUMBER(7, 2)
  , deptno   NUMBER(2)    REFERENCES dept(deptno)
  )
;

-- 중첩테이블 문법
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_nt
;

ALTER TABLE emps_nt ADD CONSTRAINT emps_empno_unique UNIQUE(empno);

-- 중첩테이블 데이터 삽입
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
;

-- 중첩테이블 조회
SELECT deptno, emps
  FROM dept_and_emp
 WHERE deptno = 10
;

-- 중첩테이블을 풀어내는 방법
SELECT d.deptno, e.empno, e.ename
  FROM dept_and_emp d
     , TABLE(emps) e
 WHERE d.deptno = 10
;