sql - 为什么我不能从此 View 中删除在 select 子句 (ORACLE) 中具有聚合函数的行？

Question

我已经阅读了有关在 ORACLE 中使用 View 进行 DML 操作的规则。他们中的大多数对我来说似乎很清楚，但我对聚合函数感到困惑。

例如，我有两个表。

               EMPLOYEES
╔═══════════╦═════════════╦══════════╗
║   emp_id  ║   salary    ║  dept_id ║
╠═══════════╬═════════════╬══════════╣
║   2134    ║   2200      ║    10    ║
║   2327    ║   3100      ║    10    ║
║   2428    ║   4100      ║    20    ║
║   2637    ║   1700      ║    30    ║
╚═══════════╩═════════════╩══════════╝
      \ | /
       \|/
        |
        |    DEPARTMENTS
╔═══════════╦═════════════╦══════════╗
║ dept_id   ║   dname     ║ location ║
╠═══════════╬═════════════╬══════════╣
║    10     ║             ║          ║
║    20     ║             ║          ║
║    30     ║             ║          ║
╚═══════════╩═════════════╩══════════╝

我想要一个按部门显示平均员工工资的 View 。
所以，我运行这个 SQL:

CREATE OR REPLACE VIEW dept_sals AS
    SELECT d.dept_id, round(avg(e.salary)) AS avg_salary
    FROM employees e
      JOIN
         departments d
      ON (e.dept_id = d.dept_id)
    GROUP BY d.dept_id;

现在我有一个看起来像这样的 View :

╔═══════════╦════════════╗
║ dept_id   ║ avg_salary ║
╠═══════════╬════════════╣
║    10     ║    2650    ║
║    20     ║    4100    ║
║    30     ║    1700    ║
╚═══════════╩════════════╝

我明白为什么我不能针对这个 View 运行更新语句。列“avg_salary”不是数据，而是动态生成的信息。但是为什么我不能从这个 View 中删除一行？

如果我尝试运行:

delete from dept_sals where dept_id = 10;

我得到:

ORA-01732: data manipulation operation not legal on this view

我在想什么:

基表 DEPARTMENTS 与此 View 中的行具有一对一关系。
我猜 Oracle 可以从 View 中获取 {dept_id} 并生成从 DEPARTMENTS 表中删除相应行的 SQL。这不会破坏表的一致性，因为“avg_salary”中的信息不是数据，而是我们可以丢弃的计算，因为 {dept_id} 的部门不再存在。

Answer 1

您指定的是 Oracle 中的不可更新 View 。此处从 Oracle 文档中指定了区分可更新和不可更新 View 的规则。

“如果 View 查询包含以下任何构造，则不能通过 UPDATE、INSERT 或 DELETE 语句修改 View :

A set operator

A DISTINCT operator

An aggregate or analytic function

A GROUP BY, ORDER BY, MODEL, CONNECT BY, or START WITH clause

A collection expression in a SELECT list

A subquery in a SELECT list

A subquery designated WITH READ ONLY

Joins, with some exceptions, as documented in Oracle Database Administrator's Guide"

链接 --> https://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_triggers.htm

使用 Oracle INSTEAD OF 触发器可以解决您想要的问题。