oracle 创建视图用 with check option 子句详解

小结： 如果 创建 的视图中带有where等条件，则 with check option 子句可以保证让你只能在视图的条件之内对视图进行DML。 当 创建 的视图中带有 with check option 子句 时，只 要 DML的结果 满足 该 创建 的视图中 的where子句条件 ，DML就可以正常进行。

小结：

如果创建的视图中带有where等条件，则with check option 子句可以保证让你只能在视图的条件之内对视图进行DML。

当创建的视图中带有with check option 子句时，只要DML的结果满足该创建的视图中的where子句条件，DML就可以正常进行。

如果subquery中的SELECT子句上没有where条件里所有出现的列，则DML类型的主查询语句也不会执行成功的。（？）

如果不加WITH CHECK OPTION则在插入时不会检查where条件。即如果你在创建视图时，使用where 子句限制时没有使用到 with check option 子句 时，当你在通过视图插入数据时，oracle 就不会检查 where 子句的限制条件。

疑问：创建视图的语句里可以有group by子句【group by子句可以附带having子句的】（或是orderby）存在吗？不可group by子句吧

with check option 子句用于普通视图和内联视图(in-line view）中。

关于普通视图和内联视图的关系，可以参见：

内联视图和普通视图的关系以及和子查询的区别

WITH CHECK OPTION

The WITH CHECK OPTION clause specifies the level of checking to be done when doingDML against the view. If specified, every row that is inserted, updated or deleted through the view must conform to the definition of the view.

The problem:

SQL> CREATE VIEW d20 AS SELECT ename, sal, deptno FROM emp2 WHERE deptno = 20;
View created.

SQL> UPDATE d20 SET deptno = 10;
3 rows updated.

The solution:

SQL> CREATE VIEW d20 AS SELECT ename, sal, deptno FROM emp2 WHERE deptno = 20
  2  WITH CHECK OPTION;
View created.

SQL> UPDATE d20 SET deptno = 10;
UPDATE d20 SET deptno = 10
       *
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation

with check option 子句应用于普通视图中的例子，详见下文：

如果创建的视图中带有where等条件，则with check option 子句可以保证让你只能在视图的条件之内对视图进行DML。
例如，我创建如下视图：
gyj@OCM> create or replace view v_t3 as select id, name,salary from t3 where salary>=7000 with check option;


View created.

视图中的条件是salary>=7000，因此，我插入一个要salary小于7000的行，将会报出错误：

gyj@OCM> insert into v_t3 values(5,'gyj5',1000);
insert into v_t3 values(5,'gyj5',1000)
*
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation

而插入一个大于7000的行，可以成功插入：

gyj@OCM> insert into v_t3 values(5,'gyj5',7500);

1 row created.

如果subquery中的SELECT子句上没有where条件里所有出现的列，则DML类型的主查询语句也不会执行成功的。例如，

1. SQL> INSERT INTO (SELECT order_id,order_date,customer_id FROM ORDERS
2. 2 WHERE order_total=1000
3. 3 WITH CHECK OPTION) VALUES(13,SYSDATE,101);
4. INSERT INTO (SELECT order_id,order_date,customer_id FROM ORDERS
5. *
6. ERROR at line 1:
7. ORA-01402: view WITH CHECK OPTION where-clause violation

这里子查询中的SELECT子句上没有order_total列，则是不允许插入的。

如果不加WITH CHECK OPTION则在插入时不会检查where条件。即如果你在创建视图时，使用where 子句限制时没有使用到 with check option 子句 时，当你在通过视图插入数据时，oracle 就不会检查 where 子句的限制条件。

上面是insert语句的例子，下面是update语句的例子。

创建一个显示销售部的雇员的视图 (SQL)：

1. 键入以下语句：

   CREATE VIEW sales_employeeAS SELECT emp_id, emp_fname, emp_lname, dept_idFROM employeeWHERE dept_id = 200WITH CHECK OPTION
   

   该视图的内容如下所示：

   SELECT *FROM sales_employee

   它们在 Interactive SQL 中的形式如下所示：

   emp_id	emp_fname	emp_lname	dept_id
   129	Philip	Chin	200
   195	Marc	Dill	200
   299	Rollin	Overbey	200
   467	James	Klobucher	200
   ...	...	...	...

2. 将 Philip Chin 调到市场部 ，即
   

   UPDATE sales_employeeSET dept_id = 400 WHERE emp_id = 129

当执行 该 UPDATE 语句时，会生成以下错误消息：

在基表'employee' 中插入/ 更新时WITH CHECK OPTION 违规（ORA-01402？）

删除我就不再试了，总之，当创建的视图中带有with check option 子句时，只要DML的结果满足该创建的视图中的where子句条件，DML就可以正常进行。

with check option 子句应用于内联视图(in-line view）中的例子，详见下文：

Oracle INSERT WITH CHECK OPTION的用法

insert into ( WITH CHECK OPTION) values (...)

例如：

SQL> insert into (select object_id,object_name,object_type from xxx where object_id1000 WITH CHECK OPTION)
2 values(999,'testbyhao','testtype');

这样的语法看起来很特殊，其实是insert进subquery里的这张表里，只不过如果不满足subquery里的where条件的话，就不允许插入。

如果插入的列有不在subquery作为检查的where条件里，那么也会不允许插入。

如果不加WITH CHECK OPTION则在插入时不会检查where条件。

还有，这里要注意的是，subquery其实是不会实际执行的。

例如：

SQL> insert into (select object_id,object_name,object_type from xxx where object_id1000)
2 values(1001,'testbyhao','testtype');

1 row created.


SQL> insert into (select object_id,object_name,object_type from xxx where object_id1000with check option)
2 values(1001,'testbyhao','testtype');
insert into (select object_id,object_name,object_type from xxx where object_id1000 with check option)
*
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation

这里插入的列中没有object_id，也是不允许插入的：

SQL> insert into (select object_name,object_type from xxx where object_id1000 with check option)
2 values('testbyhao','testtype');
insert into (select object_name,object_type from xxx where object_id1000 with check option)
*
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation

为什么说subquery没有实际执行呢？看统计信息吧：

SQL> set autotrace trace exp stat
SQL> select object_id,object_name,object_type from xxx where object_id1000;

955 rows selected.
97 consistent gets

SQL> insert into (select object_id,object_name,object_type from xxx where object_id1000)
2 values(999,'testbyhao','testtype');

1 row created.
1 consistent gets

如果你在创建视图时，使用where 子句限制时没有使用到 with check option 子句 时，当你在通过视图插入数据时，oracle 就不会检查 where 子句的限制条件。详见下文：

oracle 创建视图用 with check option 子句详解

oracle的视图是一个很有用的工具，在很多应用中的应用到。

在oracle视图中，对于简单视图，oracle 允许进行 dml 操作 ，这就是说明如果我们在一个表上创建了一个视图，前提是没有限制对它 dml 操作，我就可以通过这个视图往表里插入数据。正是由于这种原因，如果我们在创建视图时，用到 where 子句进行了限制。那么我们通过视图插入数据时，又会不会同样受到 where 子句的限制呢?答案是，如果你在创建视图时，使用where 子句限制时没有使用到 with check option 子句 时，当你在通过视图插入数据时，oracle 就不会检查 where 子句的限制条件。

下面我用例子来说明：

1. 创建表

SQL> create table s_tab ( id number ) ;

Table created.

SQL>

2.创建视图（不用 with check option）

SQL> create or replace view s_view

2 as

3 select * from s_tab

4 where id

View created.

3. 插入数据测试

SQL> insert into s_view values ( 1) ;

1 row created.

SQL> insert into s_view values (10 ) ;

1 row created.

SQL> select * from s_view ;

ID

----------

1

SQL>

SQL> select * from s_tab ;

ID

----------

1

10

SQL>

结果没有进行检查

4. 创建视图（用到 with check option）

SQL> create or replace view s_view

2 as

3 select * from s_tab

4 where id

5 with check option ;

View created.

5. 插入数据

SQL> delete from s_tab ;

2 row deleted.

SQL> insert into s_view values ( 1) ;

1 row created.

SQL> insert into s_view values (10) ;

insert into s_view values (10)

*

ERROR at line 1:

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

SQL> select * from s_view ;

ID

----------

1

SQL> select * from s_tab ;

ID

----------

1