【Oracle】视图

个人主页：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_salary
    FROM employees
    GROUP 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
BEGIN
  INSERT INTO high_salary_employees 
  VALUES (997, 'Bob', 'Johnson', 5000, 60);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('错误: ' || SQLERRM);
END;
/

-- 尝试更新为不符合条件的值（失败）
BEGIN
  UPDATE high_salary_employees 
  SET salary = 3000 
  WHERE employee_id = 998;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_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;

-- 尝试更新只读视图（会失败）
BEGIN
  UPDATE emp_salary_report 
  SET salary = 10000 
  WHERE employee_id = 100;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_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 salary
         ELSE NULL
       END 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 DISTINCT
    TRUNC(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 employees
    UNION
    SELECT 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数据库中非常强大的工具，正确使用可以显著提高数据安全性、查询效率和系统维护性。在实际应用中，应该根据具体需求选择合适的视图类型，并遵循最佳实践来设计和维护视图。

结语
感谢您的阅读！期待您的一键三连！欢迎指正！