insert into (<select clause> WITH CHECK OPTION) values (...)
例如：
SQL> insert into (select object_id,object_name,object_type from xxx where object_id<1000 WITH CHECK OPTION) 2 values(999,‘testbyhao‘,‘testtype‘);
这样的语法看起来很特殊，其实是insert进subquery里的这张表里，只不过如果不满足subquery里的where条件的话，就不允许插入。
如果插入的列有不在subquery作为检查的where条件里，那么也会不允许插入。
如果不加WITH CHECK OPTION则在插入时不会检查。
这里注意，subquery其实是不会实际执行的。
例如：
SQL> insert into (select object_id,object_name,object_type from xxx where object_id<1000) 2 values(1001,‘testbyhao‘,‘testtype‘); 1 row created. SQL> insert into (select object_id,object_name,object_type from xxx where object_id<1000with check option) 2 values(1001,‘testbyhao‘,‘testtype‘); insert into (select object_id,object_name,object_type from xxx where object_id<1000 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_id<1000 with check option) 2 values(‘testbyhao‘,‘testtype‘); insert into (select object_name,object_type from xxx where object_id<1000 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_id<1000; 955 rows selected. 97 consistent gets SQL> insert into (select object_id,object_name,object_type from xxx where object_id<1000) 2 values(999,‘testbyhao‘,‘testtype‘); 1 row created. 1 consistent gets