深入解析：【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数据库中非常强大的工具，正确使用可以显著提高数据安全性、查询效率和系统维护性。在实际应用中，应该根据具体需求选择合适的视图类型，并遵循最佳实践来设计和维护视图。
结语
感谢您的阅读！期待您的一键三连！欢迎指正！
浙公网安备 33010602011771号