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可更新檢視以及如何通過它更新底層基表。