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