【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', '[e-mail]', 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 = '[e-mail]',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', '[e-mail]', 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数据库中非常强大的工具，正确使用可以显著提高数据安全性、查询效率和系统维护性。在实际应用中，应该根据具体需求选择合适的视图类型，并遵循最佳实践来设计和维护视图。

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