Oracle建立檢視

2019-10-16 22:05:37

在本教學中,您將學習如何使用Oracle CREATE VIEW語句在資料庫中建立新檢視。

Oracle CREATE VIEW語法

要在資料庫中建立新檢視,請使用以下Oracle CREATE VIEW語句:

CREATE [OR REPLACE] VIEW view_name [(column_aliases)] AS
    defining-query
[WITH READ ONLY]
[WITH CHECK OPTION]

下面來看看各個引數的說明 -

  • OR REPLACE - OR REPLACE選項取代了現有檢視的定義。如果已授予該檢視的各種許可權,則非常方便。因為當使用DROP VIEWCREATE VIEW來更改檢視的定義時,Oracle會刪除檢視特權,這可能不是您想要的。 為了避免這種情況,可以使用保留檢視特權的CREATE OR REPLACE子句。

  • FORCE - 通常,基於現有的表建立一個新的檢視。 但是,有時可能希望根據稍後建立的表建立檢視,或者在建立檢視時沒有足夠的許可權存取表。在這些情況下,可以使用FORCE選項。

  • column_aliases - 通常,從定義查詢的選擇列表派生的檢視的列名稱。 但是,定義查詢的列名可能包含不能用於檢視定義的函式或表示式。
    要解決這個問題,有兩個選擇:

    • 使用符合定義查詢的SELECT子句中的命名規則的列別名。
    • CREATE VIEWAS子句之間的檢視列顯式指定列別名。
  • AS defining-query - 定義的查詢是一個SELECT語句,它定義了檢視的列和行。

  • WITH READ ONLY - WITH READ ONLY子句防止底層表通過檢視進行更改。
  • WITH CHECK OPTION - WITH CHECK OPTION子句保護檢視免受對基礎表的任何更改,這些更改將生成未包含在定義查詢中的行。

Oracle CREATE VIEW範例

我們來看一些基於範例資料庫中的表建立新檢視的範例。

1. 建立一個檢視的例子

請參閱範例資料庫中的以下employees表 -

以下語句根據employees表建立一個名為employee_yos的檢視。 該檢視顯示了員工ID,姓名和服務年限:

CREATE VIEW employee_yos AS
SELECT
    employee_id,
    first_name || ' ' || last_name full_name,
    FLOOR( months_between( CURRENT_DATE, hire_date )/ 12 ) yos
FROM
    employees;

在這個例子中,沒有定義檢視的列名,因為定義的查詢使用列別名來表達,比如full_name列為:first_name || ' ' || last_nameyos列為: FLOOR( months_between( CURRENT_DATE, hire_date )/ 12 )

如果您不想在查詢中使用列別名,則必須在CREATE VIEW 子句中定義它們:

CREATE VIEW employee_yos (employee_id, full_name, yos) AS
SELECT
    employee_id,
    first_name || ' ' || last_name,
    FLOOR( months_between( CURRENT_DATE, hire_date )/ 12 )
FROM
    employees;

以下查詢返回服務年數為15的員工:

SELECT
    *
FROM
    employee_yos
WHERE
    yos = 15
ORDER BY
    full_name;

執行上面查詢語句,得到以下結果 -

2. 建立唯讀檢視範例

考慮以下customers表,結構如下:

創建只讀視圖示例

以下範例將建立一個名為customer_credits的唯讀檢視,該檢視基於customers表。 該檢視包含三列:customer_idnamecredit

CREATE OR REPLACE VIEW customer_credits(
        customer_id,
        name,
        credit
    ) AS 
SELECT
        customer_id,
        name,
        credit_limit
    FROM
        customers WITH READ ONLY;

3. 建立連線檢視範例

連線檢視是其定義查詢包含連線(例如,內連線或左連線)的檢視。 以下語句建立一個名為backlog的檢視,其定義查詢包含連線三個表的聯接子句:ordersorder_itemsproducts。三個表之間的關係如下所示 -

創建連接視圖示例3

參考以下語句 -

CREATE OR REPLACE VIEW backlogs AS
SELECT
    product_name,
    EXTRACT(
        YEAR
    FROM
        order_date
    ) YEAR,
    SUM( quantity * unit_price ) amount
FROM
    orders
INNER JOIN order_items
        USING(order_id)
INNER JOIN products
        USING(product_id)
WHERE
    status = 'Pending'
GROUP BY
    EXTRACT(
        YEAR
    FROM
        order_date
    ),
    product_name;

在本教學中,您已學習如何使用Oracle CREATE VIEW語句在資料庫中建立新檢視。