UPDATE 语句都应用 WHERE 子句,则必须使用 WITH CHECK OPTION 定义该视图。这个选项可以
确保数据库中正在修改的数据的完整性。如果在 INSERT 或 UPDATE 操作期间违反了条件,则
返回 SQL 错误。
下面是一个使用了 WITH CHECK OPTION 的视图定义的示例。要确保总是检查条件,WITH CHECK
OPTION 是必需的。在这种情况下,我们希望确保 DEPT 总是 10。这会限制 DEPT 列的输入值。
当使用视图插入新值时,总是强制执行 WITH CHECK OPTION。
CREATE VIEW EMP_VIEW2
(EMPNO,EMPNAME,DEPTNO,JOBTITLE,HIREDATE)
AS SELECT ID,NAME,DEPT,JOB,HIREDATE FROM EMPLOYEE
WHERE DEPT=10 WITH CHECK OPTION;
执行UPDATE EMP_VIEW2 SET DEPT=20 WHERE DEPT=10;
ORA-01402: 视图 WITH CHECK OPTIDN where 子句违规
如果这个子句不存在,那么某个使用该视图的用户就有可能更新某条记录,使其不再属于该
视图。例如,下列 SQL 语句可能会引起一些问题。 UPDATE EMP_VIEW2 SET DEPT=20 WHERE
DEPT=10; 这个语句的结果是该视图现在不包含任何记录,因为部门 10 不再有雇员了。
补充:如果按照上边的定义来定义视图(WITH CHECKOPTION),那么在这个视图上,然后删除记录,是可以删除成功的。eg: delete from EMP_VIEW2;但是如果在视图上执行insert和update会在DEPT上受到限制,如果插入的DEPT等于其它值(非10),SQL报错:ORA-01402;如果修改DEPT成为其它值,SQL也会报错:ORA-01402。下列例子:
C:/Documents and Settings/Admin>sqlplus/nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Nov 14 20:22:132009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> show user
USER is ""
SQL> conn scott/tiger
Connected.
SQL> drop table t1;
Table dropped.
SQL> create table t1(id number,namevarchar2(20));
Table created.
SQL> insert into t1 values(1,'wh')
2 ;
1 row created.
SQL> insert into t1 values(2,'wp');
1 row created.
SQL> insert into t1 values(3,'wr');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t1;
ID NAME
---------- --------------------
1 wh
2 wp
3 wr
SQL> create view v_t1
2 as
3 select * from t1
4 where id=2
5 with check option;
View created.
首先来看一下INSERT
SQL> insert into v_t1 values(1,'haha');
insert into v_t1 values(1,'haha')
*
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation
--这里由于视图中定义了where id=2的条件并且加有with checkoption子句,所以插入id=1的记录就会报错。
SQL> insert into v_t1 values(2,'haha');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t1;
ID NAME
---------- --------------------
1 wh
2 wp
3 wr
2 haha
--喏,看到了吧,插入id=2的记录就成功了。
接下来看看UPDATE
SQL> select * from v_t1;
ID NAME
---------- --------------------
2 wp
2 haha
SQL> update v_t1 set id=1 where name='wp';
update v_t1 set id=1 where name='wp'
*
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation
--这里如果你把name='wp'的记录中id改为1,那么这条记录就不符合视图定义中id=2的条件了,就会从视图中被删去,这样也是不允许的,所以也就报错了。
SQL> update v_t1
2 set name='hehe'
3 where name='haha';
1 row updated.
SQL> select * from v_t1;
ID NAME
---------- --------------------
2 wp
2 hehe
--看,这样更改就可以了,因为只是更改了name字段,id字段仍然为2。
SQL> update v_t1 set id=11 where name='wh';
0 rows updated.
--从这里可以看到,如果某条在表中可是不在视图中的记录,那么你是无法通过视图来修改它的。
SQL> update t1 set id=11 where name='wh';
1 row updated.
SQL> select * from t1;
ID NAME
---------- --------------------
11 wh
2 wp
3 wr
2 haha
SQL> select * from v_t1;
ID NAME
---------- --------------------
2 wp
2 haha
最后我们看看delete是怎样的
SQL> delete fromv_t1 where name='wh';
0 rows deleted.
--这里的道理和上面一样,name='wh'这条记录根本就没在视图中显示,你怎么可以通过视图来删除呢?
SQL> delete fromv_t1 where name='wp';
1 row deleted.
SQL>commit;
Commit complete.
SQL> select *from v_t1;
ID NAME
---------- --------------------
2 haha
SQL> select *from t1;
ID NAME
---------- --------------------
11 wh
3 wr
2 haha
SQL>
补充:当创建视图的SQL中没WITH CHECKOPTION选项,而有WHERE条件的时候,如下:
CREATE VIEW EMP_VIEW2
(EMPNO,EMPNAME,DEPTNO,JOBTITLE,HIREDATE)
AS SELECT ID,NAME,DEPT,JOB,HIREDATE FROM EMPLOYEE
WHERE DEPT=10;
1、通过视图插入数据没有限制了。
2、但在更新视图的时候,同时会更新表EMPLOYEE。它只会更新针对表
EMPLOYEE 条件列DEPT=10的那些行。