如何正确操作Oracle视图：查询与DML限制

1. 视图的查询与DML操作：

• 对视图的访问包括查询和受限制的DML操作。访问视图的方法与访问表的基本相同。可以使用SELECT语句从视图中查询数据：

  SELECT * FROM view_1;   -- 代码编号 [000141]

• 如果需要向视图中插入数据，也可以执行INSERT语句：

  INSERT INTO view_1 VALUES ('MARY', 1000, 200);   -- 代码编号 [000142]

• 在对视图进行DML操作时，这种访问被转化为对基表的访问，因此必须遵守基表上的约束。如果插入的数据不满足基表的约束条件，系统会抛出错误。例如，尝试插入上述数据时，如果view_1的基表emp要求empno列不为空且唯一，但插入的empno值为空或与现有记录冲突，就会出现类似如下的错误：

  ORA-01400：无法将NULL插入（SCOTT.EMP.EMPNO）

2. 视图上的DML操作限制：

• 在某些情况下，视图上的DML操作会受到限制。例如，一个视图可能只允许进行查询而不允许进行插入或更新操作。下面创建的view_2，将查询员工表中部门号为30或20且工资大于2000的员工姓名、工资和奖金：

  CREATE OR REPLACE VIEW view_2 AS SELECT ename, sal, comm FROM emp WHERE deptno=30 or deptno=20 AND sal>2000;  -- 代码编号 [000143]

• 对该视图执行查询操作，可以得到相应的结果：

  SELECT * FROM VIEW_2;  -- 代码编号 [000144]

• 如果对视图进行UPDATE操作，例如：

  UPDATE view_2 SET sal=sal- 900;  -- 代码编号 [000145]

  可能导致部分数据不再满足视图的WHERE条件，从而使这些数据从视图中消失。

3. 视图中的CHECK OPTION：

• 为了防止视图中的数据因更新操作不再满足视图的条件，可以在创建视图时使用WITH CHECK OPTION。该选项会强制所有对视图的DML操作必须满足视图的WHERE子句条件：

  CREATE OR REPLACE VIEW view_2 AS SELECT ename, sal, comm FROM emp WHERE deptno=30 or deptno=20 AND sal>2000 WITH CHECK OPTION;  -- 代码编号 [000146]

• 现在如果执行更新操作，使sal列的值减少900，将会触发WITH CHECK OPTION，系统会返回错误信息：

  ORA-01402：视图WITH CHECK OPTION违反WHERE子句

4. 复杂视图的定义与使用：

• 复杂视图是指在创建视图时，SELECT子句涉及多个表的操作，或者对表的列进行了表达式运算。对于复杂视图，可以执行查询操作，有时也可以执行DML操作。创建复杂视图时，必须为每一列指定列名。例如，以下语句创建了一个包含工资最大值、最小值和总和的视图：

  CREATE OR REPLACE VIEW view_3 (deptno, max_sal, min_sal, sum_sal) AS SELECT deptno, max(sal), min(sal), sum(sal) FROM emp GROUP BY deptno;  -- 代码编号 [000148]

• 另一个例子是涉及两个表的视图：

  CREATE OR REPLACE VIEW view_4 (empno, ename, dname, sal, comm) AS SELECT emp.empno, emp.ename, dept.dname, sal*1.1, comm FROM emp, dept WHERE emp.deptno=dept.deptno;  -- 代码编号 [000149]

• 对于复杂视图，通常不允许进行DML操作。例如，尝试在view_4中插入数据将导致系统出错：

  INSERT INTO view_4 VALUES (9999, 'Kate', 'daddress', 2000, 200);  -- 代码编号 [000150]

5. 视图上DML操作的总结：

• 对于简单视图，如果基表的所有列都包含在视图中，或者至少主键列和所有不允许为空的列都包含在视图中，且没有使用复杂的SELECT语句（如DISTINCT、GROUP BY等），可以在此视图上执行DML操作，如插入、删除、修改。
• 对于涉及多个基表的复杂视图，如果视图中的列是从多个表中选择或进行复杂运算的，通常不允许执行DML操作。

非常感谢您读到这里！如果您觉得这篇文章对您有帮助，可以关注一下博主。关注后，您将第一时间获得最新的AI、云计算、运维（Linux、数据库，容器等）技术，以及更多实用的技能干货。
​
​

​
学习成功人士的经验，提供全面的学习资源和社群支持，多种副业选择，总有一个适合你。
​

本文由mdnice多平台发布