12일차 # 10-16: WITH CHECK OPTION

SQL> create or replace view d20vu

  2  as select empno, ename, sal, job, deptno

  3  from c_emp

  4  where deptno = 20;

View created.

SQL> select * from d20vu;

     EMPNO ENAME             SAL JOB           DEPTNO

---------- ---------- ---------- --------- ----------

      7369 SMITH             800 CLERK             20

      7566 JONES            2975 MANAGER           20

      7788 SCOTT            3000 ANALYST           20

      7876 ADAMS            1100 CLERK             20

      7902 FORD             3000 ANALYST           20

==> d20vu 뷰를 통해 입력한 데이터를 d20vu 뷰로 질의할 수 없습니다. 그래서 문제죠.

    WITH CHECK OPTION을 이용하면 해결할 수 있습니다.

SQL> create or replace view d20vu

  2  as select empno, ename, sal, job, deptno

  3  from c_emp

  4  where deptno = 20

  5* with check option constraint d20vu_ck

View created.

SQL> select * from d20vu;

     EMPNO ENAME             SAL JOB           DEPTNO

---------- ---------- ---------- --------- ----------

      7369 SMITH             800 CLERK             20

      7566 JONES            2975 MANAGER           20

      7788 SCOTT            3000 ANALYST           20

      7876 ADAMS            1100 CLERK             20

      7902 FORD             3000 ANALYST           20

SQL> insert into d20vu values (8888, 'Kimberly', 1100, 'SALESMAN', 30);

insert into d20vu values (8888, 'Kimberly', 1100, 'SALESMAN', 30)

            *

ERROR at line 1:

ORA-01402: view WITH CHECK OPTION where-clause violation

SQL> insert into d20vu values (8888, 'Kimberly', 1100, 'SALESMAN', 20);

1 row created.

------------------------------------

drop table t1 purge;

create table t1

as

select empno, ename, decode(gubun, '정규', sal) as month_sal, decode(gubun, '비정규', round(sal/4)) as week_sal, gubun, job, deptno

from (select empno, ename, sal

      , decode(mod(empno, 3), 0, '정규', 1, '비정규', '비정규') as gubun

      , job, deptno

      from emp);

create or replace view emp_regular

as

select empno, ename, month_sal, gubun, job, deptno

from t1

where gubun = '정규';

create or replace view emp_temporary

as

select empno, ename, week_sal, gubun, job, deptno

from t1

where gubun = '비정규';

insert into emp_regular (empno, ename, gubun, job, deptno)

values (9999, 'Jang', '비정규', 'MANAGER', 20);

select * from t1;

select * from emp_regular;

==> 방금 전 입력한 Jang의 데이터가 t1 테이블에는 있지만 emp_regular 뷰에는 안 보입니다.

create or replace view emp_regular

as

select empno, ename, month_sal, gubun, job, deptno

from t1

where gubun = '정규'

with check option constraint v_emp_regular;

insert into emp_regular (empno, ename, gubun, job, deptno)

values (9998, 'Kim', '비정규', 'MANAGER', 20);

1행에 오류:

ORA-01402: 뷰의 WITH CHECK OPTION의 조건에 위배 됩니다

select * from t1;

commit;

# 뷰 제약조건 확인하기

  SQL> select table_name, constraint_name, constraint_type

       from user_constraints

       where constraint_type = 'V';

TABLE_NAME                     CONSTRAINT_NAME                C

------------------------------ ------------------------------ -

EMP_REGULAR                    V_EMP_REGULAR                  V

D20VU                          D20VU_CK                       V