oracle 下WITH CHECK OPTION用法

   如果视图定义包括条件（譬如WHERE 子句）并且其意图是确保任何引用该视图的 INSERT 或
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的那些行。