在本教程中,您将学习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可更新视图以及如何通过它更新底层基表。