중첩 테이블
- 부모-자식 관계인 관계형 구조에서 자식 테이블과 매우 유사.
- 모두 동일한 데이터타입을 가지는 정렬되지 않는 데이터 구조고, 내장 데이터타입이거나 객체 데이터타입으로 정의.
- 부모 테이블의 각 로우가 자신만의 자식 테이블을 가지고 있다는 관점으로 설계됨.
(부모 로우가 100개, 가상의 중첩 테이블이 100개 -> 물리적으로는 하나의 부모 테이블에는 하나의 자식 테이블이 존재) - PL/SQL 코드에서 PL/SQL을 확장하려는 방법과 중첩 관계에 대한 영구적인 물리적 저장 메커니즘으로 사용.
(필자는 항상 PL/SQL에서 사용)
중첩 테이블 문법
create or replace type emp_type
as object
(empno number(4),
ename varchar2(10),
job varchar2(9),
mgr number,
hiredate date,
sal number,
comm number
);
/
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;
-- dept_and_emp 테이블과 물리적으로 분리된 테이블 emps_nt를 생성.
-- empno 컬럼을 유일하게 만들기 위해 중첩 테이블의 empno 컬럼에 제약 추가
alter table emps_nt add constraint
emps_empno_unique unique(empno)
/
-- 그러나 자기 참조 제약으로 존재하기 때문에 다른 테이블 또는 자신의 테이블을 참조할 수 없다.(FK 생성 않됨)
-- 데이터 삽입
insert into dept_and_emp
select dept.*,
cast(multiset( select empno, ename, job, mgr, hiredate, sal, comm
from scott.emp
where emp.deptno = dept.deptno) as emp_tab_type)
from scott.dept
/
4 rows created.
-- 로우만 4개 생성. 실제로도 dept_and_emp 테이블에는 로우가 4개 존재.
-- multiset 키워드는 하나 이상의 로우를 반환하는 서브쿼리가 예상된다는 것.
col employees for a50
select deptno, dname, loc, d.emps as employees
from dept_and_emp d
where deptno = 10;
DEPTNO DNAME LOC EMPLOYEES(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, C
---------- -------------- ------------- --------------------------------------------------
10 ACCOUNTING NEW YORK EMP_TAB_TYPE(EMP_TYPE(7782, 'CLARK', 'MANAGER', 78
39, '09-JUN-81', 2450, NULL), EMP_TYPE(7839, 'KING
', 'PRESIDENT', NULL, '17-NOV-81', 5000, NULL), EM
P_TYPE(7934, 'MILLER', 'CLERK', 7782, '23-JAN-82',
1300, NULL))
-- odbc는 중첩 테이블을 다룰 방법이 없다.
-- 집합을 풀어내는 방법을 제공, 중첩 테이블을 마치 관계형 테이블 처럼 취급
select d.deptno, d.dname, emp.*
from dept_and_emp d, table(d.emps) emp;
DEPTNO DNAME EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- -------------- ---------- ---------- --------- ---------- ------------ ---------- ----------
10 ACCOUNTING 7782 CLARK MANAGER 7839 09-JUN-81 2450
10 ACCOUNTING 7839 KING PRESIDENT 17-NOV-81 5000
10 ACCOUNTING 7934 MILLER CLERK 7782 23-JAN-82 1300
20 RESEARCH 7369 SMITH CLERK 7902 17-DEC-80 800
20 RESEARCH 7566 JONES MANAGER 7839 02-APR-81 2975
20 RESEARCH 7788 SCOTT ANALYST 7566 19-APR-87 3000
20 RESEARCH 7876 ADAMS CLERK 7788 23-MAY-87 1100
20 RESEARCH 7902 FORD ANALYST 7566 03-DEC-81 3000
30 SALES 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30 SALES 7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30 SALES 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30 SALES 7698 BLAKE MANAGER 7839 01-MAY-81 2850
30 SALES 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30 SALES 7900 JAMES CLERK 7698 03-DEC-81 950
-- 10번 부서에 100달러 보너스 지급
update
table (select emps
from dept_and_emp
where deptno = 10)
set comm = 100;
3 rows updated.
-- 정확하게 한 개의 테이블을 select 하지 않을 경우 오류 발생.
update
table (select emps
from dept_and_emp
where deptno = 1)
set comm = 100;
update
*
ERROR at line 1:
ORA-22908: reference to NULL table value
update
table (select emps
from dept_and_emp
where deptno > 1)
set comm = 100;
table (select emps
*
ERROR at line 2:
ORA-01427: single-row subquery returns more than one row
-- 데이터 추가
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';
- emps_nt의 모든 로우를 수정하기 위해서는 update를 4번 해야만 한다. 각 로우에 관계된 가상 테이블을 수정하기 위해서 dept_and_emp 테이블의 각 로우를 수정.
- 부서 테이블을 전혀 수정하지 않는다고 하더라고 수정되는 중첩 테이블이 포함된 로우는 lock 발생.
- 비 공식적 nested_table_get_refs 힌트를 사용하여 일반 테이블 처럼 사용 가능
(완벽하게 문서에서 공식화한 것도, 공식적으로 지원하는 내용도 아니니 테스트 용도로만 사용 할 것)
- 추가되는 raw(16) 컬럼을 저장하기 위한 불필요한 저장공간 발생. (부모, 자식 테이블 모두 발생)
- 부모테이블에 불필요한 유일성 제약 추가