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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- insert all和insert first語句的用法
- mysql insert的特殊用法MySql
- mysql中last_insert_id()用法MySqlAST
- select into from 和 insert into select 的用法和區別
- Oracle批量插入資料insert all into用法Oracle
- Python中insert用法及實戰案例!Python
- Oracle中的insert/insert all/insert firstOracle
- 過往記憶的專欄文章轉載:Hive insert into語句用法Hive
- jquery獲得option的值和對option進行操作jQuery
- onclick="return check()" 和 onclick="check()" 區別
- 蘋果電腦上神奇的Option鍵,巧用option鍵提升效率蘋果
- SAP WM中階Storage Type的Capacity Check – Usage check based on SUT
- option新增空白
- SAP WM中階儲存型別的Capacity Check – Usage check based on material型別
- post請求帶來的option
- Check if String is HappyAPP
- The option-critic architecture
- insert into select
- 在SAP C4C裡觸發SAP ERP的ATP check和Credit check
- 怎樣提高insert的效能
- Jquery 動態增加option及獲取值 遍歷option相關方法jQuery
- CHECK_INTERFACE作用
- check_document_position
- Kotlin Type? vs Scala OptionKotlin
- SQL__INSERTSQL
- E - Insert or Erase
- insert images to a plot
- MTK Camera相關的Makefile Option詳解
- [20181120]奇怪的insert語句.txt
- mysql insert into ... select的鎖問題MySql
- DYLD_INSERT_LIBRARIES的那些事
- 解析MySQL中INSERT INTO SELECT的使用MySql
- [LintCode] Check Full Binary Tree
- Mysql replication check指令碼MySql指令碼
- Check connection related issue of mysqlMySql
- 括展actuator health check
- check memcached process and restart if downREST
- leetcode–57–Insert IntervalLeetCode
- SQLite Insert 語句SQLite