oracle 创建视图用 with check option 子句详解

最新推荐文章于 2025-01-23 15:12:13 发布
最新推荐文章于 2025-01-23 15:12:13 发布
阅读量2.3w 收藏 12
点赞数 5
分类专栏： oracle疑问集 OCP试题 oracle开发SQL语句
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/haiross/article/details/42123627
版权

小结：

如果创建的视图中带有where等条件，则with check option 子句可以保证让你只能在视图的条件之内对视图进行DML。

当创建的视图中带有with check option 子句时，只要DML的结果满足该创建的视图中的where子句条件，DML就可以正常进行。

如果subquery中的SELECT子句上没有where条件里所有出现的列，则DML类型的主查询语句也不会执行成功的。（？）

如果不加WITH CHECK OPTION则在插入时不会检查where条件。即如果你在创建视图时，使用where 子句限制时没有使用到 with check option 子句 时，当你在通过视图插入数据时，oracle 就不会检查 where 子句的限制条件。


疑问：创建视图的语句里可以有group by子句【group by子句可以附带having子句的】（或是orderby）存在吗？不可group by子句吧


with check option 子句用于普通视图和内联视图(in-line view）中。


关于普通视图和内联视图的关系，可以参见：

内联视图和普通视图的关系以及和子查询的区别


WITH CHECK OPTION

The WITH CHECK OPTION clause specifies the level of checking to be done when doingDML against the view. If specified, every row that is inserted, updated or deleted through the view must conform to the definition of the view.

The problem: 

SQL> CREATE VIEW d20 AS SELECT ename, sal, deptno FROM emp2 WHERE deptno = 20;
View created.

SQL> UPDATE d20 SET deptno = 10;
3 rows updated.

The solution:

SQL> CREATE VIEW d20 AS SELECT ename, sal, deptno FROM emp2 WHERE deptno = 20
  2  WITH CHECK OPTION;
View created.

SQL> UPDATE d20 SET deptno = 10;
UPDATE d20 SET deptno = 10
       *
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation


with check option 子句应用于普通视图中的例子，详见下文：


如果创建的视图中带有where等条件，则with check option 子句可以保证让你只能在视图的条件之内对视图进行DML。
 例如，我创建如下视图：
gyj@OCM> create or replace view v_t3 as select id, name,salary from t3 where salary>=7000 with check option;


View created.

视图中的条件是salary>=7000，因此，我插入一个要salary小于7000的行，将会报出错误：


gyj@OCM> insert into v_t3 values(5,'gyj5',1000);
insert into v_t3 values(5,'gyj5',1000)
            *
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation

 而插入一个大于7000的行，可以成功插入：


gyj@OCM> insert into v_t3 values(5,'gyj5',7500);

1 row created.

如果subquery中的SELECT子句上没有where条件里所有出现的列，则DML类型的主查询语句也不会执行成功的。例如，

  1. SQL> INSERT INTO (SELECT order_id,order_date,customer_id FROM ORDERS  
  2.   2  WHERE order_total=1000  
  3.   3  WITH CHECK OPTION) VALUES(13,SYSDATE,101);  
  4. INSERT INTO (SELECT order_id,order_date,customer_id FROM ORDERS  
  5.                                                          *  
  6. ERROR at line 1:  
  7. ORA-01402: view WITH CHECK OPTION where-clause violation  

这里子查询中的SELECT子句上没有order_total列，则是不允许插入的。


如果不加WITH CHECK OPTION则在插入时不会检查where条件。即如果你在创建视图时，使用where 子句限制时没有使用到 with check option 子句 时，当你在通过视图插入数据时，oracle 就不会检查 where 子句的限制条件。



上面是insert语句的例子，下面是update语句的例子。

创建一个显示销售部的雇员的视图 (SQL)：

  1. 键入以下语句：

    CREATE VIEW sales_employeeAS SELECT emp_id, emp_fname, emp_lname, dept_idFROM employeeWHERE dept_id = 200WITH CHECK OPTION

    该视图的内容如下所示：

    SELECT *FROM sales_employee

    它们在 Interactive SQL 中的形式如下所示：

    emp_idemp_fnameemp_lnamedept_id
    129PhilipChin200
    195MarcDill200
    299RollinOverbey200
    467JamesKlobucher200
    ............

  2. 将 Philip Chin 调到市场部      ，即

    UPDATE sales_employeeSET dept_id = 400 WHERE emp_id = 129

当执行 该 UPDATE 语句时，会生成以下错误消息：

在基表'employee' 中插入/ 更新时WITH CHECK OPTION 违规（ORA-01402？）



  删除我就不再试了，总之，当创建的视图中带有with check option 子句时，只要DML的结果满足该创建的视图中的where子句条件，DML就可以正常进行。



with check option 子句应用于内联视图(in-line view）中的例子，详见下文：

Oracle INSERT WITH CHECK OPTION的用法

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则在插入时不会检查where条件。

还有，这里要注意的是，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


如果你在创建视图时，使用where 子句限制时没有使用到 with check option 子句 时，当你在通过视图插入数据时，oracle 就不会检查 where 子句的限制条件。详见下文：

oracle 创建视图用 with check option 子句详解



oracle的视图是一个很有用的工具，在很多应用中的应用到。

在oracle视图中，对于简单视图，oracle 允许进行 dml 操作 ，这就是说明如果我们在一个表上创建了一个视图，前提是没有限制对它 dml 操作，我就可以通过这个视图往表里插入数据。正是由于这种原因，如果我们在创建视图时，用到 where 子句进行了限制。那么我们通过视图插入数据时，又会不会同样受到 where 子句的限制呢?答案是，如果你在创建视图时，使用where 子句限制时没有使用到 with check option 子句 时，当你在通过视图插入数据时，oracle 就不会检查 where 子句的限制条件。 

下面我用例子来说明：

1. 创建表
SQL> create table s_tab ( id number ) ;

Table created.

SQL>

2.创建视图（不用 with check option）
SQL> create or replace view s_view
  2  as
  3  select * from s_tab
  4  where id < 5 ;

View created.

3. 插入数据测试
SQL> insert into s_view values ( 1) ;

1 row created.

SQL> insert into s_view values (10 ) ;

1 row created.
SQL> select * from s_view ;

        ID
----------
         1

SQL>
SQL> select * from s_tab ;

        ID
----------
         1
        10

SQL>
结果没有进行检查

4. 创建视图（用到 with check option）
SQL> create or replace view s_view
  2  as
  3  select * from s_tab
  4  where id < 5
  5  with check option ;

View created.

5. 插入数据
SQL> delete from s_tab ;
2 row deleted.

SQL> insert into s_view values ( 1) ;

1 row created.

SQL> insert into s_view values (10) ;
insert into s_view values (10)
            *
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation
SQL> select * from s_view ;

        ID
----------
         1

SQL> select * from s_tab ;

        ID
----------
         1
oracle添加创建视图权限不足,Oracle Scott创建视图权限不足解决办法
weixin_35986497的博客
04-03 1855
Oracle Scott创建视图权限不足解决办法在PL/SQL Developer数据库管理工具中，使用SCOTT用户帐号进行登录，新建SQL查询窗口，输入一条创建视图的SQL语句，随后弹出一个错误的提示信息：ORA-01031:权限不足。上图所示的错误提示信息已经说的很明白了，这是权限不足导致的！所以，只要我们设置一个scott用户的权限就可以解决了！在网上查了一下，设置用户权限是使用 gran...
创建视图的with check option选项。
aobi6343的博客
05-13 833
对于withcheckoption而言，对delete是没有限制的。 默认情况下，由于行通过视图进行添加或更新，当其不再符合定义视图的查询的条件时，它们即从视图范围中消失。例如，可创建一个查询，从而定义一个视图以在表中检索所有员工薪水低于$30,000的行。如果该员工的薪水涨到了$32,000，则查询视图时该特定员工将不再出现，因其薪水不符合...
把markdown转换为pdf的方法
最新发布
hunter206的博客
01-23 3985
VS Code 是一款流行的代码编辑器，支持通过插件将 Markdown 转换为 PDF。
oracle 创建视图 with check option
duncan
12-13 240
我们来看下面的例子： create or replace view testview as select empno,ename from emp where ename like ‘M%’ with check option; 这里我们创建了一个视图，并使用了with check option来限制了视图。 然后我们来看一下视图包含的结果： select * from testv...
创建视图时with check option问题
Mr_Pang的专栏
11-24 2750
创建视图时，如果带着这个选项，则在插入数据是，必须满足select语句后的where的条件 比如以下语句: create or replace view view_testheader_testtable as select testheader from testtable where testheader > 1 with check option; 当我们要插入数据时，如
Oracle数据库创建视图详解
- `WITH CHECK OPTION`：指定只有对视图可访问的行才能进行插入或更新操作，确保数据一致性。 - `constraint`：为CHECK OPTION约束分配的名称。 - `WITH READ ONLY`：确保无法在这个视图上执行DML（数据操纵语言）...
Oracle视图详解：创建、操作与安全优势
创建视图时，可以设置各种选项如ORREPLACE、FORCE/NOFORCE、WITHCHECKOPTION和WITHREADONLY。删除视图不影响基表数据。" 在Oracle数据库中，视图是一种非常重要的数据抽象工具，它允许用户以定制的方式查看和操作...
Oracle视图创建详解：基础语法与实例
`WITH CHECK OPTION` 是可选的，当启用时，它可以在从视图进行更新操作时检查是否满足原SELECT语句的条件。 2. **视图创建类型**: - **单表视图**：基于单个表的查询结果创建，只显示选定字段。 - **多表视图**：...
Oracle数据库：视图、同义词与序列详解
例如，包含集合操作（如INTERSECT, UNION, 和 MINUS）的视图、带有GROUP BY、CONNECT BY或START WITH子句的视图、使用聚合函数（如AVG、SUM、MAX）的视图以及使用DISTINCT功能的视图，这些视图通常只支持查询，而不...
ORACLE check view
weixin_30532837的博客
05-24 100
select owner as schema_name, view_name from sys.all_views where VIEW_NAME like 'P%' order by owner, view_name; 转载于:https://www.cnblogs.com/kakaisgood/p/10919569.html...
【SQL】视图WITH CHECK OPTION
cout0
11-05 3094
文章目录WITH CHECK OPTION WITH CHECK OPTION 通过有with check option选项的视图操作基表(只是面对单表，对连接多表的视图正在寻找答案)，有以下结论： 首先视图只操作它可以查询出来的数据，对于它查询不出的数据，即使基表有，也不可以通过视图来操作。 对于update,有with check option，要保证update后，数据要被视图查询出...
ORACLE WITH CHECK OPTION子句详解
朝着梦想 渐行前进
11-14 6767
今天一个朋友在问关于创建视图时候WITH CHECK OPTION是什么意思，我当时也没多想，就比较笼统的回答了一下。后来自己想了想，好像自己也记不太清楚了，脑子里只有个大概的印象。好了话不多说，下面还是通过实验来看一下吧。C:/Documents and Settings/Admin>sqlplus /nologSQL*Plus: Release 10.2.0.1.0 - Produ
oracle视图
weixin_40925233的博客
01-13 208
A: 视图 a:概念: 一种虚拟的表,可以封装sql语句,视图的结构和数据就是对数据表进行查询的结果. b:优缺点 优点: 1:简化数据操作  (简化用户处理数据的方式) 2:着重于特定数据  (不必要的数据或敏感数据可以不出现在视图中)  3:视图提供了一个简单而有效的安全机制,可以定制不同用户对数据的访问权限. 4:提供了向后兼容性 c:创建或修改视图 创建语法: c
with check option 和没有with check option的区别
linglutuanxi的博客
05-22 2553
插入： 从上面这个例子可以看出，加了with check option 会提示错误的操作，不能插入错误的数据；而没有with check option的则会忽略这个错误。 这是因为如没有指定关键字，则with check option默认是cascade，即级联检查，等价于with cascaded check option。为了确定检查的范围，mysql提供了两个选项：LOCAL和CASCADED。如果我们没有在WITH CHECK OPTION子句中显式指定关键字，则mysql默认使用CASCADED
oracle 下WITH CHECK OPTION用法
甲骨文可乐的专栏
07-08 4466
如果视图定义包括条件（譬如 WHERE 子句）并且其意图是确保任何引用该视图的 INSERT 或 UPDATE 语句都应用 WHERE 子句，则必须使用 WITH CHECK OPTION 定义该视图。这个选项可以 确保数据库中正在修改的数据的完整性。如果在 INSERT 或 UPDATE 操作期间违反了条件，则 返回 SQL 错误。 下面是一个使用了 WITH CHECK OPTION 的视图
MySQL之理解视图的with check option
好吗好的~的技术博客
10-14 3604
视图定义： 　视图（view）是一种虚拟存在的表，是一个逻辑表，本身并不包含数据。作为一个select语句保存在数据字典中的。   视图创建： CREATE VIEW 视图名（列表名） AS SELECT 查询子句 [WITH CHECK OPTION]   那么问题来了，WITH CHECK OPTION 的作用是什么??? 软考书上的解释：WITH CHECK OPTION 表示...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值