個人主頁：Guiat
歸屬專欄：Oracle

正文

1. 視圖基礎概述

視圖是Oracle數據庫中的虛擬表，它是基于一個或多個表的查詢結果集。視圖不存儲實際數據，而是存儲查詢定義，當訪問視圖時動態執行查詢。

1.1 視圖的概念與特點

1.2 視圖的工作原理

1.3 視圖的分類

2. 簡單視圖

簡單視圖基于單個表，通常可以進行DML操作。

2.1 創建簡單視圖

2.1.1 基本簡單視圖

-- 創建基本的簡單視圖
CREATE VIEW emp_basic_info AS
SELECT employee_id, first_name, last_name, email, hire_date
FROM employees;-- 創建帶WHERE條件的簡單視圖
CREATE VIEW active_employees AS
SELECT employee_id, first_name, last_name, email, phone_number, hire_date, salary
FROM employees
WHERE hire_date >= DATE '2020-01-01';-- 創建帶列別名的簡單視圖
CREATE VIEW emp_summary AS
SELECT employee_id AS emp_id,first_name || ' ' || last_name AS full_name,email AS email_address,salary AS monthly_salary,salary * 12 AS annual_salary,hire_date
FROM employees;-- 查看視圖結構
DESCRIBE emp_basic_info;-- 查詢視圖數據
SELECT * FROM emp_basic_info WHERE employee_id < 110;

2.1.2 帶計算列的簡單視圖

-- 創建包含計算列的視圖
CREATE VIEW emp_salary_analysis AS
SELECT employee_id,first_name || ' ' || last_name AS employee_name,salary,salary * 12 AS annual_salary,CASE WHEN salary < 5000 THEN 'Low'WHEN salary BETWEEN 5000 AND 10000 THEN 'Medium'WHEN salary > 10000 THEN 'High'END AS salary_grade,ROUND(MONTHS_BETWEEN(SYSDATE, hire_date)) AS months_employed,ROUND(MONTHS_BETWEEN(SYSDATE, hire_date) / 12, 1) AS years_employed
FROM employees;-- 查詢計算列視圖
SELECT employee_name, salary_grade, annual_salary, years_employed
FROM emp_salary_analysis
WHERE salary_grade = 'High'
ORDER BY annual_salary DESC;

2.2 簡單視圖的DML操作

2.2.1 通過視圖進行INSERT操作

-- 創建可插入的視圖
CREATE VIEW emp_insert_view AS
SELECT employee_id, first_name, last_name, email, hire_date, job_id, department_id
FROM employees;-- 通過視圖插入數據
INSERT INTO emp_insert_view (employee_id, first_name, last_name, email, hire_date, job_id, department_id
) VALUES (999, 'John', 'Doe', 'john.doe@company.com', SYSDATE, 'IT_PROG', 60
);-- 驗證插入結果
SELECT * FROM emp_insert_view WHERE employee_id = 999;
SELECT * FROM employees WHERE employee_id = 999;

2.2.2 通過視圖進行UPDATE操作

-- 通過視圖更新數據
UPDATE emp_insert_view 
SET email = 'john.doe.updated@company.com',job_id = 'IT_ADMIN'
WHERE employee_id = 999;-- 批量更新
UPDATE emp_salary_analysis 
SET salary = salary * 1.05
WHERE salary_grade = 'Low';-- 驗證更新結果
SELECT employee_id, employee_name, salary, salary_grade
FROM emp_salary_analysis
WHERE employee_id = 999;

2.2.3 通過視圖進行DELETE操作

-- 通過視圖刪除數據
DELETE FROM emp_insert_view WHERE employee_id = 999;-- 驗證刪除結果
SELECT COUNT(*) FROM employees WHERE employee_id = 999;

3. 復雜視圖

復雜視圖基于多個表或包含函數、分組等復雜操作，通常是只讀的。

3.1 多表連接視圖

3.1.1 員工部門視圖

-- 創建員工部門完整信息視圖
CREATE VIEW emp_dept_detail AS
SELECT e.employee_id,e.first_name || ' ' || e.last_name AS employee_name,e.email,e.salary,e.hire_date,j.job_title,d.department_name,d.manager_id AS dept_manager_id,dm.first_name || ' ' || dm.last_name AS dept_manager_name,l.city,l.state_province,c.country_name
FROM employees e
JOIN jobs j ON e.job_id = j.job_id
JOIN departments d ON e.department_id = d.department_id
LEFT JOIN employees dm ON d.manager_id = dm.employee_id
JOIN locations l ON d.location_id = l.location_id
JOIN countries c ON l.country_id = c.country_id;-- 查詢員工部門詳細信息
SELECT employee_name, job_title, department_name, city, country_name
FROM emp_dept_detail
WHERE country_name = 'United States'
ORDER BY department_name, employee_name;

3.1.2 員工層級關系視圖

-- 創建員工管理層級視圖
CREATE VIEW emp_hierarchy AS
SELECT e.employee_id,e.first_name || ' ' || e.last_name AS employee_name,e.job_id,e.salary,e.hire_date,e.manager_id,m.first_name || ' ' || m.last_name AS manager_name,m.job_id AS manager_job_id,d.department_name,LEVEL AS hierarchy_level,SYS_CONNECT_BY_PATH(e.first_name || ' ' || e.last_name, ' -> ') AS hierarchy_path
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id
JOIN departments d ON e.department_id = d.department_id
START WITH e.manager_id IS NULL
CONNECT BY PRIOR e.employee_id = e.manager_id;-- 查詢組織層級結構
SELECT LPAD(' ', (hierarchy_level - 1) * 2) || employee_name AS org_structure,job_id,manager_name,department_name
FROM emp_hierarchy
WHERE department_name = 'Executive'
ORDER BY hierarchy_level, employee_name;

3.2 聚合統計視圖

3.2.1 部門統計視圖

-- 創建部門統計匯總視圖
CREATE VIEW dept_statistics AS
SELECT d.department_id,d.department_name,d.location_id,l.city,COUNT(e.employee_id) AS employee_count,ROUND(AVG(e.salary), 2) AS avg_salary,MIN(e.salary) AS min_salary,MAX(e.salary) AS max_salary,SUM(e.salary) AS total_salary,ROUND(STDDEV(e.salary), 2) AS salary_stddev,MIN(e.hire_date) AS earliest_hire_date,MAX(e.hire_date) AS latest_hire_date
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
LEFT JOIN locations l ON d.location_id = l.location_id
GROUP BY d.department_id, d.department_name, d.location_id, l.city;-- 查詢部門統計信息
SELECT department_name, employee_count,avg_salary,total_salary,city
FROM dept_statistics
WHERE employee_count > 0
ORDER BY avg_salary DESC;

3.2.2 職位薪資分析視圖

-- 創建職位薪資分析視圖
CREATE VIEW job_salary_analysis AS
SELECT j.job_id,j.job_title,COUNT(e.employee_id) AS position_count,ROUND(AVG(e.salary), 2) AS avg_salary,ROUND(MEDIAN(e.salary), 2) AS median_salary,MIN(e.salary) AS min_salary,MAX(e.salary) AS max_salary,j.min_salary AS job_min_salary,j.max_salary AS job_max_salary,ROUND(AVG(e.salary) - j.min_salary, 2) AS avg_above_min,ROUND(j.max_salary - AVG(e.salary), 2) AS avg_below_max,ROUND((AVG(e.salary) - j.min_salary) / (j.max_salary - j.min_salary) * 100, 1) AS salary_position_pct
FROM jobs j
LEFT JOIN employees e ON j.job_id = e.job_id
GROUP BY j.job_id, j.job_title, j.min_salary, j.max_salary;-- 查詢職位薪資分析
SELECT job_title,position_count,avg_salary,median_salary,salary_position_pct || '%' AS position_in_range
FROM job_salary_analysis
WHERE position_count > 0
ORDER BY avg_salary DESC;

3.3 時間序列分析視圖

3.3.1 年度招聘趨勢視圖

-- 創建年度招聘趨勢分析視圖
CREATE VIEW yearly_hiring_trends AS
SELECT hire_year,total_hired,LAG(total_hired, 1) OVER (ORDER BY hire_year) AS prev_year_hired,total_hired - LAG(total_hired, 1) OVER (ORDER BY hire_year) AS year_over_year_change,ROUND((total_hired - LAG(total_hired, 1) OVER (ORDER BY hire_year)) / LAG(total_hired, 1) OVER (ORDER BY hire_year) * 100, 1) AS yoy_change_pct,avg_starting_salary,LAG(avg_starting_salary, 1) OVER (ORDER BY hire_year) AS prev_avg_salary,ROUND(avg_starting_salary - LAG(avg_starting_salary, 1) OVER (ORDER BY hire_year), 2) AS salary_change
FROM (SELECT EXTRACT(YEAR FROM hire_date) AS hire_year,COUNT(*) AS total_hired,ROUND(AVG(salary), 2) AS avg_starting_salaryFROM employeesGROUP BY EXTRACT(YEAR FROM hire_date)
);-- 查詢招聘趨勢
SELECT hire_year,total_hired,CASE WHEN yoy_change_pct > 0 THEN '+' || yoy_change_pct || '%'WHEN yoy_change_pct < 0 THEN yoy_change_pct || '%'ELSE 'N/A'END AS growth_rate,avg_starting_salary,salary_change
FROM yearly_hiring_trends
ORDER BY hire_year;

4. 視圖的更新控制

4.1 WITH CHECK OPTION

WITH CHECK OPTION確保通過視圖進行的DML操作符合視圖的WHERE條件。

4.1.1 基本CHECK OPTION

-- 創建帶CHECK OPTION的視圖
CREATE VIEW high_salary_employees AS
SELECT employee_id, first_name, last_name, salary, department_id
FROM employees
WHERE salary > 8000
WITH CHECK OPTION;-- 嘗試插入符合條件的記錄（成功）
INSERT INTO high_salary_employees 
VALUES (998, 'Jane', 'Smith', 9000, 60);-- 嘗試插入不符合條件的記錄（失敗）
-- 以下操作會產生錯誤：ORA-01402: view WITH CHECK OPTION where-clause violation
BEGININSERT INTO high_salary_employees VALUES (997, 'Bob', 'Johnson', 5000, 60);
EXCEPTIONWHEN OTHERS THENDBMS_OUTPUT.PUT_LINE('錯誤: ' || SQLERRM);
END;
/-- 嘗試更新為不符合條件的值（失敗）
BEGINUPDATE high_salary_employees SET salary = 3000 WHERE employee_id = 998;
EXCEPTIONWHEN OTHERS THENDBMS_OUTPUT.PUT_LINE('更新錯誤: ' || SQLERRM);
END;
/

4.1.2 分級CHECK OPTION

-- 創建基礎視圖
CREATE VIEW dept_employees AS
SELECT employee_id, first_name, last_name, salary, department_id
FROM employees
WHERE department_id = 60;-- 創建基于視圖的視圖，帶CHECK OPTION
CREATE VIEW senior_dept_employees AS
SELECT employee_id, first_name, last_name, salary, department_id
FROM dept_employees
WHERE salary > 7000
WITH CHECK OPTION;-- 測試分級檢查
INSERT INTO senior_dept_employees 
VALUES (996, 'Alice', 'Brown', 8500, 60); -- 成功-- 清理測試數據
DELETE FROM employees WHERE employee_id IN (996, 998);

4.2 WITH READ ONLY

WITH READ ONLY選項創建只讀視圖，禁止任何DML操作。

-- 創建只讀視圖
CREATE VIEW emp_salary_report AS
SELECT d.department_name,e.employee_id,e.first_name || ' ' || e.last_name AS employee_name,e.salary,e.hire_date,RANK() OVER (PARTITION BY d.department_id ORDER BY e.salary DESC) AS salary_rank
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WITH READ ONLY;-- 查詢只讀視圖
SELECT department_name, employee_name, salary, salary_rank
FROM emp_salary_report
WHERE salary_rank <= 3
ORDER BY department_name, salary_rank;-- 嘗試更新只讀視圖（會失敗）
BEGINUPDATE emp_salary_report SET salary = 10000 WHERE employee_id = 100;
EXCEPTIONWHEN OTHERS THENDBMS_OUTPUT.PUT_LINE('只讀視圖錯誤: ' || SQLERRM);
END;
/

5. 物化視圖

物化視圖是將查詢結果物理存儲的視圖，可以顯著提高復雜查詢的性能。

5.1 基本物化視圖

5.1.1 創建物化視圖

-- 創建基本物化視圖
CREATE MATERIALIZED VIEW mv_dept_summary
BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND
AS
SELECT d.department_id,d.department_name,COUNT(e.employee_id) AS emp_count,ROUND(AVG(e.salary), 2) AS avg_salary,SUM(e.salary) AS total_salary
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name;-- 查詢物化視圖
SELECT * FROM mv_dept_summary 
WHERE emp_count > 0 
ORDER BY avg_salary DESC;-- 手動刷新物化視圖
EXEC DBMS_MVIEW.REFRESH('mv_dept_summary');

5.1.2 自動刷新物化視圖

-- 創建自動刷新的物化視圖
CREATE MATERIALIZED VIEW mv_monthly_stats
BUILD IMMEDIATE
REFRESH COMPLETE ON COMMIT
AS
SELECT EXTRACT(YEAR FROM hire_date) AS hire_year,EXTRACT(MONTH FROM hire_date) AS hire_month,COUNT(*) AS employees_hired,ROUND(AVG(salary), 2) AS avg_starting_salary
FROM employees
GROUP BY EXTRACT(YEAR FROM hire_date), EXTRACT(MONTH FROM hire_date);-- 當基礎表發生變化時，物化視圖會自動刷新
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id, salary, department_id)
VALUES (995, 'Test', 'Employee', 'test@company.com', SYSDATE, 'IT_PROG', 6000, 60);COMMIT;-- 查看更新后的物化視圖
SELECT * FROM mv_monthly_stats 
WHERE hire_year = EXTRACT(YEAR FROM SYSDATE)
ORDER BY hire_year, hire_month;-- 清理測試數據
DELETE FROM employees WHERE employee_id = 995;
COMMIT;

5.2 快速刷新物化視圖

快速刷新只更新發生變化的部分，需要物化視圖日志。

5.2.1 創建物化視圖日志

-- 為基礎表創建物化視圖日志
CREATE MATERIALIZED VIEW LOG ON employees
WITH ROWID, SEQUENCE (employee_id, department_id, salary)
INCLUDING NEW VALUES;CREATE MATERIALIZED VIEW LOG ON departments
WITH ROWID, SEQUENCE (department_id, department_name)
INCLUDING NEW VALUES;

5.2.2 創建快速刷新物化視圖

-- 創建支持快速刷新的物化視圖
CREATE MATERIALIZED VIEW mv_dept_summary_fast
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
AS
SELECT d.department_id,d.department_name,COUNT(e.employee_id) AS emp_count,SUM(e.salary) AS total_salary,COUNT(*) AS row_count
FROM departments d, employees e
WHERE d.department_id = e.department_id(+)
GROUP BY d.department_id, d.department_name;-- 進行一些更改
UPDATE employees SET salary = salary + 100 WHERE employee_id = 100;-- 快速刷新物化視圖
EXEC DBMS_MVIEW.REFRESH('mv_dept_summary_fast', 'F');-- 查看刷新后的結果
SELECT * FROM mv_dept_summary_fast ORDER BY department_id;

6. 視圖管理與維護

6.1 查看視圖信息

6.1.1 視圖元數據查詢

-- 查看用戶擁有的所有視圖
SELECT view_name, text_length, read_only
FROM user_views
ORDER BY view_name;-- 查看視圖的詳細定義
SELECT view_name, text
FROM user_views
WHERE view_name = 'EMP_DEPT_DETAIL';-- 查看視圖的列信息
SELECT column_name, data_type, nullable
FROM user_tab_columns
WHERE table_name = 'EMP_DEPT_DETAIL'
ORDER BY column_id;-- 查看視圖的依賴關系
SELECT name, type, referenced_name, referenced_type
FROM user_dependencies
WHERE name = 'EMP_DEPT_DETAIL';

6.1.2 物化視圖狀態查詢

-- 查看物化視圖信息
SELECT mview_name, refresh_mode, refresh_method, build_mode, last_refresh_date, compile_state
FROM user_mviews;-- 查看物化視圖刷新歷史
SELECT mview_name, refresh_method, start_time, end_time, elapsed_time
FROM user_mview_refresh_times
WHERE mview_name = 'MV_DEPT_SUMMARY'
ORDER BY start_time DESC;-- 查看物化視圖日志信息
SELECT log_table, master, log_trigger, rowids, primary_key, object_id
FROM user_mview_logs;

6.2 視圖性能優化

6.2.1 執行計劃分析

-- 分析視圖查詢的執行計劃
EXPLAIN PLAN FOR
SELECT employee_name, department_name, salary
FROM emp_dept_detail
WHERE salary > 8000
ORDER BY salary DESC;SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);-- 比較直接查詢和視圖查詢的性能
EXPLAIN PLAN FOR
SELECT e.first_name || ' ' || e.last_name AS employee_name,d.department_name,e.salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > 8000
ORDER BY e.salary DESC;SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

6.2.2 視圖性能優化建議

-- 創建優化的視圖，包含提示
CREATE OR REPLACE VIEW emp_dept_optimized AS
SELECT /*+ USE_NL(e d) INDEX(e emp_department_ix) */e.employee_id,e.first_name || ' ' || e.last_name AS employee_name,e.salary,d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;-- 為經常查詢的列創建索引
CREATE INDEX emp_salary_idx ON employees(salary);
CREATE INDEX emp_dept_idx ON employees(department_id);

6.3 視圖的修改和刪除

6.3.1 修改視圖

-- 使用OR REPLACE修改視圖定義
CREATE OR REPLACE VIEW emp_basic_info AS
SELECT employee_id, first_name, last_name, email, hire_date,salary,  -- 新增列department_id  -- 新增列
FROM employees
WHERE hire_date >= DATE '2010-01-01';  -- 新增條件-- 驗證視圖修改
DESCRIBE emp_basic_info;
SELECT * FROM emp_basic_info WHERE rownum <= 5;

6.3.2 刪除視圖

-- 刪除普通視圖
DROP VIEW emp_summary;-- 刪除物化視圖
DROP MATERIALIZED VIEW mv_dept_summary;-- 刪除物化視圖日志
DROP MATERIALIZED VIEW LOG ON employees;
DROP MATERIALIZED VIEW LOG ON departments;-- 驗證刪除結果
SELECT view_name FROM user_views WHERE view_name IN ('EMP_SUMMARY');
SELECT mview_name FROM user_mviews WHERE mview_name = 'MV_DEPT_SUMMARY';

7. 視圖安全與權限

7.1 視圖權限管理

7.1.1 授予視圖權限

-- 創建用于演示的視圖
CREATE VIEW public_emp_info AS
SELECT employee_id, first_name, last_name, email, hire_date
FROM employees;-- 授予其他用戶查詢權限
GRANT SELECT ON public_emp_info TO other_user;-- 授予更新權限
GRANT UPDATE ON public_emp_info TO other_user;-- 授予所有權限
GRANT ALL ON public_emp_info TO privileged_user;-- 查看授予的權限
SELECT grantee, privilege, grantable
FROM user_tab_privs
WHERE table_name = 'PUBLIC_EMP_INFO';

7.1.2 回收視圖權限

-- 回收特定權限
REVOKE UPDATE ON public_emp_info FROM other_user;-- 回收所有權限
REVOKE ALL ON public_emp_info FROM other_user;

7.2 行級安全(RLS)視圖

7.2.1 創建安全視圖

-- 創建部門級安全視圖
CREATE VIEW secure_emp_view AS
SELECT employee_id, first_name, last_name, email, salary, department_id
FROM employees
WHERE department_id IN (SELECT department_id FROM user_dept_access WHERE username = USER
);-- 創建基于角色的視圖
CREATE VIEW role_based_emp_view AS
SELECT employee_id, first_name, last_name, email,CASE WHEN USER IN (SELECT username FROM hr_managers) THEN salaryELSE NULLEND AS salary,department_id
FROM employees;

7.3 視圖最佳實踐

7.3.1 命名規范

-- 推薦的視圖命名規范
CREATE VIEW v_employee_summary AS ...;     -- 前綴 v_
CREATE VIEW emp_dept_vw AS ...;             -- 后綴 _vw
CREATE MATERIALIZED VIEW mv_sales_summary AS ...;  -- 物化視圖前綴 mv_-- 業務相關的命名
CREATE VIEW finance_employee_view AS ...;   -- 財務部門員工視圖
CREATE VIEW active_projects_view AS ...;    -- 活躍項目視圖

7.3.2 設計原則

8. 實際應用案例

8.1 企業報表視圖系統

8.1.1 銷售業績報表視圖

-- 創建銷售業績綜合報表視圖
CREATE VIEW sales_performance_report AS
SELECT s.salesperson_id,e.first_name || ' ' || e.last_name AS salesperson_name,d.department_name,EXTRACT(YEAR FROM s.sale_date) AS sale_year,EXTRACT(QUARTER FROM s.sale_date) AS sale_quarter,COUNT(s.sale_id) AS total_sales,SUM(s.amount) AS total_revenue,ROUND(AVG(s.amount), 2) AS avg_sale_amount,RANK() OVER (PARTITION BY EXTRACT(YEAR FROM s.sale_date), EXTRACT(QUARTER FROM s.sale_date)ORDER BY SUM(s.amount) DESC) AS performance_rank
FROM sales s
JOIN employees e ON s.salesperson_id = e.employee_id
JOIN departments d ON e.department_id = d.department_id
GROUP BY s.salesperson_id, e.first_name, e.last_name, d.department_name,EXTRACT(YEAR FROM s.sale_date), EXTRACT(QUARTER FROM s.sale_date);

8.1.2 客戶分析視圖

-- 創建客戶價值分析視圖
CREATE VIEW customer_value_analysis AS
SELECT c.customer_id,c.customer_name,c.registration_date,COUNT(o.order_id) AS total_orders,SUM(o.order_amount) AS total_spent,ROUND(AVG(o.order_amount), 2) AS avg_order_value,MAX(o.order_date) AS last_order_date,ROUND(MONTHS_BETWEEN(SYSDATE, MAX(o.order_date)), 1) AS months_since_last_order,CASE WHEN SUM(o.order_amount) > 10000 THEN 'VIP'WHEN SUM(o.order_amount) > 5000 THEN 'Premium'WHEN SUM(o.order_amount) > 1000 THEN 'Regular'ELSE 'Basic'END AS customer_tier,CASE WHEN MONTHS_BETWEEN(SYSDATE, MAX(o.order_date)) <= 3 THEN 'Active'WHEN MONTHS_BETWEEN(SYSDATE, MAX(o.order_date)) <= 12 THEN 'At Risk'ELSE 'Inactive'END AS customer_status
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name, c.registration_date;

8.2 數據倉庫視圖層

8.2.1 維度視圖

-- 時間維度視圖
CREATE VIEW dim_time AS
SELECT DISTINCTTRUNC(date_column) AS date_key,EXTRACT(YEAR FROM date_column) AS year,EXTRACT(QUARTER FROM date_column) AS quarter,EXTRACT(MONTH FROM date_column) AS month,EXTRACT(DAY FROM date_column) AS day,TO_CHAR(date_column, 'Day') AS day_name,TO_CHAR(date_column, 'Month') AS month_name,CASE WHEN TO_CHAR(date_column, 'D') IN ('1', '7') THEN 'Y' ELSE 'N' END AS is_weekend
FROM (SELECT hire_date AS date_column FROM employeesUNIONSELECT order_date FROM orders
);-- 產品維度視圖
CREATE VIEW dim_product AS
SELECT p.product_id,p.product_name,p.product_code,c.category_name,c.category_id,p.unit_price,p.cost_price,p.unit_price - p.cost_price AS profit_margin,CASE WHEN p.unit_price - p.cost_price > 100 THEN 'High Margin'WHEN p.unit_price - p.cost_price > 50 THEN 'Medium Margin'ELSE 'Low Margin'END AS margin_category
FROM products p
JOIN categories c ON p.category_id = c.category_id;

8.2.2 事實視圖

-- 銷售事實視圖
CREATE VIEW fact_sales AS
SELECT s.sale_id,s.sale_date,s.customer_id,s.product_id,s.salesperson_id,s.quantity,s.unit_price,s.total_amount,s.discount_amount,s.total_amount - s.discount_amount AS net_amount,p.cost_price * s.quantity AS total_cost,(s.total_amount - s.discount_amount) - (p.cost_price * s.quantity) AS profit
FROM sales s
JOIN products p ON s.product_id = p.product_id;

通過這個全面的Oracle視圖教程，我們涵蓋了從基礎概念到高級應用的所有重要方面。視圖是Oracle數據庫中非常強大的工具，正確使用可以顯著提高數據安全性、查詢效率和系統維護性。在實際應用中，應該根據具體需求選擇合適的視圖類型，并遵循最佳實踐來設計和維護視圖。

結語
感謝您的閱讀！期待您的一鍵三連！歡迎指正！