Oracle可更新视图

在本教程中，您将学习Oracle可更新视图以及如何通过视图在基表中插入或更新数据。

视图就像一个表，因为可以像表一样从中查询数据。但是，不能总是通过视图来操作数据。如果针对视图的语句可以被转换成针对基础表的相应语句，则视图是可更新的。

我们来考虑下面的数据库中的表的ER图：

在数据库关系图中，一辆汽车(cars)属于一个品牌(brands)，而一个品牌拥有一辆或多辆汽车。品牌与汽车的关系是一对多的。

以下SQL语句创建cars和brands表; 并将示例数据插入到这些表中。

CREATE TABLE brands(
 brand_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
 brand_name VARCHAR2(50) NOT NULL,
 PRIMARY KEY(brand_id)
);

CREATE TABLE cars (
 car_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
 car_name VARCHAR2(255) NOT NULL,
 brand_id NUMBER NOT NULL,
 PRIMARY KEY(car_id),
 FOREIGN KEY(brand_id) 
 REFERENCES brands(brand_id) ON DELETE CASCADE
);

INSERT INTO brands(brand_name)
VALUES('Audi');

INSERT INTO brands(brand_name)
VALUES('BMW');

INSERT INTO brands(brand_name)
VALUES('Ford');

INSERT INTO brands(brand_name)
VALUES('Honda');

INSERT INTO brands(brand_name)
VALUES('Toyota');

INSERT INTO cars (car_name,brand_id)
VALUES('Audi R8 Coupe',1);

INSERT INTO cars (car_name,brand_id)
VALUES('Audi Q2',1);

INSERT INTO cars (car_name,brand_id)
VALUES('Audi S1',1);

INSERT INTO cars (car_name,brand_id)
VALUES('BMW 2-serie Cabrio', 2);

INSERT INTO cars (car_name,brand_id)
VALUES('BMW i8',2);

INSERT INTO cars (car_name,brand_id)
VALUES('Ford Edge',3);

INSERT INTO cars (car_name,brand_id)
VALUES('Ford Mustang Fastback',3);

INSERT INTO cars (car_name,brand_id)
VALUES('Honda S2000',4);

INSERT INTO cars (car_name,brand_id)
VALUES('Honda Legend',4);

INSERT INTO cars (car_name,brand_id)
VALUES('Toyota GT86',5);

INSERT INTO cars (car_name,brand_id)
VALUES('Toyota C-HR',5);

Oracle可更新视图示例

以下语句创建一个名为cars_master的新视图：

CREATE VIEW cars_master AS 
SELECT
    car_id,
    car_name
FROM
    cars;

可以通过cars_master视图从cars表中删除一行，例如：

DELETE
FROM
    cars_master
WHERE
    car_id = 1;

可以更新任何暴露在cars_master视图中的列值：

UPDATE
    cars_master
SET
    car_name = 'Audi RS7 Sportback'
WHERE
    car_id = 2;

可以通过cars_master视图向cars表插入和更新数据，因为Oracle可以将INSERT和UPDATE语句转换为相应的语句并在cars表中执行它们。

但是，通过cars_master视图插入到cars表中的新行是不可能的。 因为cars表有一个没有默认值的非空列(brand_id)。例如下面语句 -

INSERT INTO cars_master
VALUES('Audi S1 Sportback');

Oracle发出一个错误：

SQL Error: ORA-00947: not enough values

Oracle可更新连接视图示例

我们来创建一个名为all_cars的连接视图，它基于cars和brands表。

CREATE VIEW all_cars AS 
SELECT
    car_id,
    car_name,
    c.brand_id,
    brand_name
FROM
    cars c
INNER JOIN brands b ON
    b.brand_id = c.brand_id;

以下语句通过call_cars视图向cars表中插入一个新行：

INSERT INTO all_cars(car_name, brand_id )
VALUES('Audi A5 Cabriolet', 1);

cars表中插入了一行新的汽车信息。 这个INSERT语句可以工作，因为Oracle可以将它分解为针对cars表的INSERT语句。

以下语句通过all_cars视图删除cars表中所有本田(Honda)汽车：

DELETE
FROM
    all_cars
WHERE
    brand_name = 'Honda';

执行上面语句，将有两行数据被删除了。

Oracle有一些适用于可更新联接视图的规则和限制。 其中之一是键保存完好表的概念。

保存键完好表是与视图中的行通过主键或唯一键具有一对一行关系的基表。 在上面的例子中，cars表是一个保存键完好的表。

以下是可更新连接视图限制的一些示例：

• SQL语句(例如，INSERT，UPDATE和DELETE)仅允许修改单个基表中的数据。
• 对于INSERT语句，INTO子句中列出的所有列必须属于保存键的表。
• 对于UPDATE语句，SET子句中的所有列必须属于保留键的表。
• 对于DELETE语句，如果连接生成多个保留键的表，则Oracle将从FROM子句的第一个表中删除。

除了这些限制之外，Oracle还要求定义查询不包含以下任何元素：

• 聚合函数例如，AVG，COUNT，MAX，MIN和SUM。
• DISTINCT运算符。
• GROUP BY子句。
• HAVING子句。
• 集合运算符，例如UNION，UNION ALL，INTERSECT和MINUS。
• START WITH或者CONNECT BY子句
• ROWNUM伪列

查找联接视图的可更新列

要查找哪个列可以更新，插入或删除，请使用user_updatable_columns视图。 以下示例显示了all_cars视图的哪一列是可更新的，可插入的和可删除的：

SELECT
    *
FROM
    USER_UPDATABLE_COLUMNS
WHERE
    TABLE_NAME = 'ALL_CARS';

执行上面查询语句，得到以下结果 -

在本教程中，您已经了解了Oracle可更新视图以及如何通过它更新底层基表。