insert WITH CHECK OPTION的用法

insert into ( 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<1000 with 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.

        197  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

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15415488/viewspace-673594/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15415488/viewspace-673594/