程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> OracleINSERTWITHCHECKOPTION的用法

OracleINSERTWITHCHECKOPTION的用法

編輯:Oracle教程

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

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved