create view with check option

手冊介紹:

WITH CHECK OPTION Specify WITH CHECK OPTION to indicate that Oracle
Database prohibits any changes to the table or view that would produce rows that are
not included in the subquery. When used in the subquery of a DML statement, you
can specify this clause in a subquery in the FROM clause but not in subquery in the
WHERE clause.

with check option可以這麼解釋：透過檢視進行的修改，必須也能透過該檢視看到修改後的結果。比如你insert，那麼加的這條記錄在重新整理檢視後必須可以看到；如果修改，修改完的結果也必須能透過該檢視看到；如果刪除，當然只能刪除檢視裡有顯示的記錄。

SQL> select * from dept;

DEPT_ID DEPT_NAME
---------- --------------------
1 a
2 b

SQL> create view v_dept as select * from dept where dept_id>1 with check option;

檢視已建立。

SQL> insert into v_dept values(1,'a');
insert into v_dept values(1,'a')
*
第 1 行出現錯誤:
ORA-01402: 檢視 WITH CHECK OPTIDN where 子句違規

SQL> insert into v_dept values(2,'a');

已建立 1 行。

SQL> rollback;

回退已完成。

SQL> select * from v_dept;

DEPT_ID DEPT_NAME
---------- --------------------
2 b

SQL> update v_dept set dept_id=1;
update v_dept set dept_id=1
*
第 1 行出現錯誤:
ORA-01402: 檢視 WITH CHECK OPTIDN where 子句違規

SQL> update v_dept set dept_id=3;

已更新 1 行。

SQL> rollback;

回退已完成。

[@more@]