insert WITH CHECK OPTION的用法
insert into (
例如:
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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- WITH CHECK OPTION 詳解
- Using the WITH CHECK OPTION
- 【檢視】with check option
- create view with check optionView
- ORACLE WITH CHECK OPTION子句詳解Oracle
- 檢視中with check option的作用是什麼?
- mysql insert的特殊用法MySql
- oracle 建立檢視用 with check option 子句詳解Oracle
- 使用檢視配合With Check Option實現複雜校驗
- Nagios check_http命令用法iOSHTTP
- insert 中append 用法詳解APP
- mysql中last_insert_id()用法MySqlAST
- Oracle批量插入資料insert all into用法Oracle
- Python中insert用法及實戰案例!Python
- select into from 和 insert into select 的用法和區別
- insert into select 與 create table as的用法和區別(轉)
- Oracle中的insert/insert all/insert firstOracle
- oracle中INSERT對應db2中的LOCATE函式用法OracleDB2函式
- jquery獲得option的值和對option進行操作jQuery
- with grant option與with admin option區別
- 蘋果電腦上神奇的Option鍵,巧用option鍵提升效率蘋果
- Oracle 的 INSERT ALL和INSERT FIRSTOracle
- Nagios自帶外掛check_http詳解及用法例項iOSHTTP
- 關於GRANT賦權時,WITH GRANT OPTION和WITH ADMIN OPTION的使用
- insert /*+ append */ into 與insert into 的區別APP
- INSERT ALL 和INSERT FIRST 的區別
- oracle with * option 理解Oracle
- Jumping with Option
- onclick="return check()" 和 onclick="check()" 區別
- jq動態修改select 的option值,使option值自動選中
- post請求帶來的option
- JQuery 對 Select option 的操作jQuery
- 關於WITH GRANT OPTION
- SAP WM中階Storage Type的Capacity Check – Usage check based on SUT
- check_postgres.pl 的缺陷
- Jquery 動態增加option及獲取值 遍歷option相關方法jQuery
- The option-critic architecture
- JavaScript select新增optionJavaScript