視圖是**儲存的 SELECT 查詢**,不佔用資料空間,每次查詢時即時執行定義 SQL。可簡化複雜 JOIN、隱藏敏感欄位、提供穩定介面給應用程式。
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 current_product_list AS
SELECT product_id, product_name, category
FROM products
WHERE discontinued = 'No';
DROP VIEW current_product_list;
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` | 視圖疊視圖,層層封裝 |
-- 限制透過視圖修改的資料必須符合視圖 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` 突破可更新限制,讓應用程式與資料庫結構解耦。