因为在项目中大量地使用了视图，而在视图上的更新上产生了一点儿问题，所以抽时间对可更新视图进行了复习，英文看得多了，也就成了中文

CREATE TABLE Dept_tab (
Deptno NUMBER(4) PRIMARY KEY,
Dname VARCHAR2(14),
Loc VARCHAR2(13));

CREATE TABLE Emp_tab (
Empno NUMBER(4) PRIMARY KEY,
Ename VARCHAR2(10),
Job varchar2(9),
Mgr NUMBER(4),
Hiredate DATE,
Sal NUMBER(7,2),
Comm NUMBER(7,2),
Deptno NUMBER(2),
FOREIGN KEY (Deptno) REFERENCES Dept_tab(Deptno));

CREATE VIEW Emp_dept_view AS
SELECT e.Empno, e.Ename, e.Deptno, e.Sal, d.Dname, d.Loc
FROM Emp_tab e, Dept_tab d /* JOIN operation */
WHERE e.Deptno = d.Deptno
AND d.Loc IN ('DALLAS', 'NEW YORK', 'BOSTON');In this view, EMP_TAB is a key-preserved table, because EMPNO is a key of the EMP_TAB table, and also a key of the result of the join. DEPT_TAB is not a key-preserved table, because although DEPTNO is a key of the DEPT_TAB table, it is not a key of the join.

Rule for DML Statements on Join Views

Any UPDATE, INSERT, or DELETE statement on a join view can modify only one underlying base table.

Updating a Join View

The following example shows an UPDATE statement that successfully modifies the EMP_DEPT_VIEW view:

UPDATE Emp_dept_view

  SET Sal = Sal * 1.10 

    WHERE Deptno = 10;

The following UPDATE statement would be disallowed on the EMP_DEPT_VIEW view:

UPDATE Emp_dept_view

  SET Loc = 'BOSTON'

    WHERE Ename = 'SMITH';

This statement fails with an ORA-01779 error ("cannot modify a column which maps to a non key-preserved table"), because it attempts to modify the underlying DEPT_TAB table, and the DEPT_TAB table is not key preserved in the EMP_DEPT view.

In general, all modifiable columns of a join view must map to columns of a key-preserved table. If the view is defined using the WITH CHECK OPTION clause, then all join columns and all columns of repeated tables are not modifiable.

So, for example, if the EMP_DEPT view were defined using WITH CHECK OPTION, then the following UPDATE statement would fail:

UPDATE Emp_dept_view

    SET Deptno = 10

        WHERE Ename = 'SMITH';

The statement fails because it is trying to update a join column.

总结:更新一个复杂视图的条件是,只能更新主键表(Key-Preserved )的列;如果WITH CHECK OPTION ,则不能更新公共列;

Deleting from a Join View

You can delete from a join view provided there is one and only one key-preserved table in the join.

The following DELETE statement works on the EMP_DEPT view:

DELETE FROM Emp_dept_view

    WHERE Ename = 'SMITH';

This DELETE statement on the EMP_DEPT view is legal because it can be translated to a DELETE operation on the base EMP_TAB table, and because the EMP_TAB table is the only key-preserved table in the join.

In the following view, a DELETE operation cannot be performed on the view because both E1 and E2 are key-preserved tables:

CREATE VIEW emp_emp AS

    SELECT e1.Ename, e2.Empno, e1.Deptno

        FROM Emp_tab e1, Emp_tab e2

        WHERE e1.Empno = e2.Empno;

        WHERE e1.Empno = e2.Empno;

If a view is defined using the WITH CHECK OPTION clause and the key-preserved table is repeated, then rows cannot be deleted from such a view. For example:

CREATE VIEW Emp_mgr AS

    SELECT e1.Ename, e2.Ename Mname

       FROM Emp_tab e1, Emp_tab e2

            WHERE e1.mgr = e2.Empno

            WITH CHECK OPTION;

No deletion can be performed on this view because the view involves a self-join of the table that is key preserved.

一句话总结：You can delete from a join view provided there is one and only one key-preserved table in the join.

Inserting into a Join View

The following INSERT statement on the EMP_DEPT view succeeds, because only one key-preserved base table is being modified (EMP_TAB), and 40 is a valid DEPTNO in the DEPT_TAB table (thus satisfying the FOREIGN KEY integrity constraint on the EMP_TAB table).

INSERT INTO Emp_dept (Ename, Empno, Deptno)

    VALUES ('KURODA', 9010, 40);

The following INSERT statement fails for the same reason: This UPDATE on the base EMP_TAB table would fail: the FOREIGN KEY integrity constraint on the EMP_TAB table is violated.

INSERT INTO Emp_dept (Ename, Empno, Deptno)

    VALUES ('KURODA', 9010, 77);

The following INSERT statement fails with an ORA-01776 error ("cannot modify more than one base table through a view").

INSERT INTO Emp_dept (Ename, Empno, Deptno)

    VALUES (9010, 'KURODA', 'BOSTON');

An INSERT cannot, implicitly or explicitly, refer to columns of a non-key-preserved table. If the join view is defined using the WITH CHECK OPTION clause, then you cannot perform an INSERT to it.

总结：insert成功的条件only one key-preserved base table is being modified并且符合约束；

但如果在join视图中with check option那么你不能再insert 了.

相关视图 Three views you can use for modifying join views :USER_UPDATABLE_COLUMNS

Outer Joins

Views that involve outer joins are modifiable in some cases. For example:

CREATE VIEW Emp_dept_oj1 AS

    SELECT Empno, Ename, e.Deptno, Dname, Loc

    FROM Emp_tab e, Dept_tab d

    WHERE e.Deptno = d.Deptno (+);

Columns in the base EMP_TAB table of EMP_DEPT_OJ1 are modifiable through the view, because EMP_TAB is a key-preserved table in the join.

The following view also contains an outer join:

CREATE VIEW Emp_dept_oj2 AS

SELECT e.Empno, e.Ename, e.Deptno, d.Dname, d.Loc

FROM Emp_tab e, Dept_tab d

WHERE e.Deptno (+) = d.Deptno;

In this view, EMP_TAB is no longer a key-preserved table, because the EMPNO column in the result of the join can have nulls (the last row in the SELECT above). So, UPDATE, DELETE, and INSERT operations cannot be performed on this view.

Consider the following set of views:

CREATE VIEW Emp_v AS

    SELECT Empno, Ename, Deptno

        FROM Emp_tab;

CREATE VIEW Emp_dept_oj1 AS

    SELECT e.*, Loc, d.Dname

        FROM Emp_v e, Dept_tab d

            WHERE e.Deptno = d.Deptno (+);

In these examples, EMP_V is merged into EMP_DEPT_OJ1 because EMP_V is a simple view, and so EMP_TAB is a key-preserved table. But if EMP_V is changed as follows:

CREATE VIEW Emp_v_2 AS

    SELECT Empno, Ename, Deptno

        FROM Emp_tab

            WHERE Sal > 1000;

Then, because of the presence of the WHERE clause, EMP_V_2 cannot be merged into EMP_DEPT_OJ1, and hence EMP_TAB is no longer a key-preserved table.

If you are in doubt whether a view is modifiable, then you can SELECT from the view USER_UPDATABLE_COLUMNS to see if it is