萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle VIEW 視圖完整指南:建立、更新、刪除、多表 JOIN、實戰範例

什麼是視圖?

視圖是**儲存的 SELECT 查詢**,不佔用資料空間,每次查詢時即時執行定義 SQL。可簡化複雜 JOIN、隱藏敏感欄位、提供穩定介面給應用程式。

建立視圖 (CREATE VIEW)

CREATE VIEW current_product_list AS
SELECT product_id, product_name
FROM   products
WHERE  discontinued = 'No';

查詢視圖

SELECT * FROM current_product_list;

進階視圖:計算欄位、子查詢、跨視圖引用

-- 單價高於平均的產品
CREATE VIEW products_above_avg AS
SELECT product_name, unit_price
FROM   products
WHERE  unit_price > (SELECT AVG(unit_price) FROM products);

-- 1997 年各類別銷售總額 (引用另一視圖)
CREATE VIEW category_sales_1997 AS
SELECT DISTINCT category_name, SUM(product_sales) AS category_sales
FROM   product_sales_for_1997
GROUP BY category_name;

更新視圖 (CREATE OR REPLACE VIEW)

CREATE OR REPLACE VIEW current_product_list AS
SELECT product_id, product_name, category
FROM   products
WHERE  discontinued = 'No';

刪除視圖 (DROP VIEW)

DROP VIEW current_product_list;

實戰:多表 JOIN 視圖產出「課程座位佔用報表」

CREATE OR REPLACE VIEW vw_occupied_seats_by_class AS
SELECT c.course_designater_fk AS course,
       b.seat_num,
       a.firstname || ' ' || a.lastname AS student
FROM   students a
JOIN   classregistration b ON a.student_id = b.student_id_fk
JOIN   classes c ON c.classes_num = b.classes_num;

視圖應用三種模式

模式SQL 範例說明
單一課程明細`SELECT * FROM vw_occupied_seats_by_class WHERE course='Perl100' AND student<>'1'`直接套用 WHERE,無需重寫 JOIN
各課程空位統計`SELECT course, COUNT(seat_num) AS "# Open Seats" FROM vw_occupied_seats_by_class GROUP BY course`聚合運算直接在視圖上
嵌套視圖`CREATE VIEW vw_class_summary AS SELECT course, COUNT(*) cnt FROM vw_occupied_seats_by_class GROUP BY course`視圖疊視圖,層層封裝

可更新視圖 (Updatable View) 條件

  • 單一基表、無聚合/GROUP BY/DISTINCT/集合運算
  • 所有 NOT NULL 欄位皆在視圖中
  • 可對視圖 `INSERT/UPDATE/DELETE`,效果回寫基表
  • 複雜視圖需改用 `INSTEAD OF TRIGGER` 實現可更新

WITH CHECK OPTION / READ ONLY

-- 限制透過視圖修改的資料必須符合視圖 WHERE 條件
CREATE VIEW vip_customers AS
SELECT * FROM customers WHERE credit_limit > 10000
WITH CHECK OPTION;

-- 唯讀視圖
CREATE VIEW readonly_log AS SELECT * FROM audit_log WITH READ ONLY;

結語

視圖是 **SQL 封裝與抽象層** 的利器:對內隱藏複雜 JOIN/業務規則,對外提供穩定欄位介面。善用 `CREATE OR REPLACE` 熱部署、`WITH CHECK OPTION` 守護資料品質、`INSTEAD OF TRIGGER` 突破可更新限制,讓應用程式與資料庫結構解耦。

copyright © 萬盛學電腦網 all rights reserved