te.
现在查看一下基表有没有发生修改，
SQL> select employee_id,last_name,salary from employees where employee_id=169;
EMPLOYEE_ID LAST_NAME SALARY
----------- --------- ---------------- ----------
169 Blooms 10000
基表也修改了。
对视图的修改成功，也是对基表的修改。
对于在创建视图时不加上with check option。
SQL> create table emp as select * from employees;
Table created.
SQL> create or replace view empvu1
2 as select * from emp where department_id=20;
View created.
SQL> select employee_id,last_name,department_id from empvu1;
EMPLOYEE_ID LAST_NAME DEPARTMENT_ID
----------- ------------ ------------- -------------
201 Hartstein 20
202 Fay 20
SQL> update empvu1 set department_id=10 where employee_id=202;
1 row updated.
SQL> select employee_id,last_name,department_id from empvu1;
EMPLOYEE_ID LAST_NAME DEPARTMENT_ID
----------- ------------------------- -------------
201 Hartstein 20
可以将where子句中 department_id=20修改为department_id=10，从而导致了这行的消失。
用with check option来创建视图，可以防止导致行从视图中消失的DML操作。
SQL> create or replace view empvu1
2 as select * from emp where department_id=20 with check option;
View created.
SQL> select employee_id,last_name,department_id from empvu1;
EMPLOYEE_ID LAST_NAME DEPARTMENT_ID
----------- - ---------------------- -- -------------
201 Hartstein 20
SQL> update empvu1 set department_id=10 where employee_id=201;
update empvu1 set department_id=10 where employee_id=201
*
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation
不能修改
可以对其它列进行修改。
SQL> update empvu1 set last_name='sun' where employee_id=201;
1 row updated.
SQL> select employee_id,last_name,department_id from empvu1;
EMPLOYEE_ID LAST_NAME DEPARTMENT_ID
----------- ------------------------- -------------
201 sun 20
SQL> drop view empvu1;
View dropped.
删除视图不会影响基表,这是因为View并不是存储数据，而是查询语句。