Updatable Join View의 제약 및 사용 방법

Oracle 7.3까지는 join을 포함한 view는 무조건 DML operation이 불가능하였다.
Oracle8에서는 updatable join view로 구분되는 join을 포함한 view들 중 일부는
직접 view를 변경하는 것이 가능해졌다. 이 updatable join view에 대한 제약 사
항 및 사용방법을 예제를 통해서 자세히 살펴본다.

[참고] updatable join view를 제외한 다른 join view를 변경하기 위해서는
INSTEAD OF trigger를 사용하면 된다.

1. 기본적인 제약사항
updatable join view는 FROM절에 두개 이상의 table을 사용하는 join 형태로, 아
래에 나열한 것은 하나도 포함하지 않아야 한다.

- DISTINCT operator
- group functions: AVG, COUNT, MAX, MIN, STDDEV, SUM, VARIANCE등
- set operations: UNION, MINUS 등
- GROUP BY, CONNECT BY, START WITH clauses
- ROWNUM pseudocolumn

2. Key-Preserved Table
join view에 대한 DML은 반드시 key-preserved table에 대해서만 가능하다.
key-preserved table이란, table에서 key (unique 또는 primary key)인 모든 column이
join이후에도 key가 보장되는 table을 의미한다. 즉, 'join을 이후에도 key가 유
지된다'라는 의미이다.

그러나, base table에서 key에 포함된 모든 column이 join view의 column으로 선
택되어야 하는 것은 아니다. join자체에서 key가 보장되기만 하면 되며, view에
서는 key컬럼을 반드시 선택할 필요는 없다.

3. Updatable Join View에 대한 DML operation
[매우 중요] join view에 대한 INSERT/UPDATE/DELETE문장은 오직 하나의 base
            table만을 변경하는 작업만이 가능하다.

updatable join view에 대한 DML을 살펴보기 위해 다음과 같은 table과 view를
생성하여 예를 들어 본다.

     create table dept
     (deptno number(4) primary key,
      dname varchar2(14),
      loc varchar2(13));

     create table emp
     (empno number(4) primary key,
      ename varchar2(10),
      job varchar2(9),
      sal number(7,2),
      mgr number(4),
      deptno number(2) );

     create view emp_dept
     as select empno, ename, sal, emp.deptno, dname
     from emp, dept
     where emp.deptno=dept.deptno;

SQL> select * from emp_dept;

     EMPNO ENAME             SAL     DEPTNO DNAME
---------- ---------- ---------- ---------- --------------
      1000 EYKIM             200         20 RESEARCH
      2000 SCOTT             400         10 SALES
      3000 YOUNKIM           500         10 SALES
      1500 IJYEO             300         30 ACCOUNTING
      2200 EJLEE             350         20 RESEARCH

이 emp_dept join view의 경우 emp의 empno는 key성질을 여전히 유지할 수 있으므로
key preserved table이나, dept의 deptno는 더 이상 unique 성질을 유지하지 못해
key-preserved table이 아니다. 즉 emp table에 속한 empno, ename, deptno만이 변경
가능하다. 만약 view를 생성시 emp.deptno대신 dept.deptno로 기술하였다면 view
를 통한 결과값은 변함이 없지만, empno와 ename만이 변경가능하고 deptno는
변경할 수 없게 된다.
아래에서 insert/update/delete 각각의 경우에 대해서 올바른 문장의 사용과 그렇
지 않은 경우를 예를 들어 설명한다.

(1) INSERT 문장
[성공적인 문장]
insert into emp_dept (ename, empno, deptno) values('WOOKPARK, 4000, 10);

[실패하는 문장 1]
dept table은 key preserved table이 아니므로, dept table에 포함된 column의 값은
다음과 같이 변경할 수 없다.

insert into emp_dept (empno, ename, dname) values(4000, 'WOOKPARK', 'SALES');
ORA-01776: cannot modify more than one base table through a join view

[실패하는 문장 2]
만약에 emp_dept에서 empno컬럼만을 제외하고 emp_dept2라는 새로운 view를 만든
경우도 emp는 여전히 key-preserved table이다. 이러한 경우 empno가 primary
key가 아니고 unique이면 다음 insert는 성공하며, 이 문서에서는 primary key로
선언하였으므로 다음과 같은 오류가 발생하게 된다.

SQL> insert into emp_dept2(ename, sal) values('HANSEO', 700);
ORA-01400: cannot insert NULL into ("EYKIM"."EMP"."EMPNO")

(2) UPDATE
[성공적인 문장] update emp_dept set sal = sal * 1.1 where deptno = 10;

[실패하는 문장 1]
update emp_dept set dname = 'SALES' where ename = 'EYKIM';
ORA-01779: cannot modify a column which maps to a non key-preserved table

[실패하는 문장 2]
WITH CHECK OPTION을 이용하여 emp_dept3 view를 생성시에는 다음과 같은
join column은 변경할 수 없다.

update emp_dept3 set deptno=10 where ename = 'EYKIM';
ORA-01733: virtual column not allowed here

(3) DELETE
delete의 경우에는 join에 포함된 table들 중 오직 하나의 table만이 key-reserved
table이어야 가능하다.
[성공적인 문장] delete from emp_dept where ename = 'EYKIM';

[실패하는 문장 1]
예를 들어 다음과 같은 join view를 생성하게 되면 e1, e2모두 key-preserved
table이 되어 delete가 실패하게 된다.
create view emp_emp as
select e1.ename, e2.empno, e1.deptno
from emp e1, emp e2
where e1.empno = e2.empno;

delete from emp_emp where empno=1000;
ORA-01752: cannot delete from view without exactly one key-preserved table

[실패하는 문장 2] WITH CHECK OPTION을 사용할 때는 하나의 table만의 key-
reserved table이더라도, 그 table을을 key-reserved가 아닌 형태로라도 다시 사용
하면 오류가 발생한다.

create view emp_mgr as
select e1.ename, e2.ename mname
from emp e1, emp e2
where e1.mgr=e2.empno
with check option;

delete from emp_mgr where ename = 'EYKIM';
ORA-01752: cannot delete from view without exactly one key-preserved table

4. 일반적으로 view에서 변경이 가능한 column은 다음과 같이
user_updatable_columns (dba_updatable_columns)를 통해 확인가능하다.

select * from user_updatable_columns where table_name = 'EMP_DEPT'

OWNER   TABLE_NAME   COLUMN_NAM UPD INS DEL
------- ------------ ---------- --- --- ---
EYKIM   EMP_DEPT     EMPNO      YES YES YES
EYKIM   EMP_DEPT     ENAME      YES YES YES
EYKIM   EMP_DEPT     SAL        YES YES YES
EYKIM   EMP_DEPT     DEPTNO     NO  NO  NO
EYKIM   EMP_DEPT     DNAME      NO  NO  NO

 

출처 : http://rudaks.co.kr/bbs/view.jsp?seq=1426&bbs=db