Oracle数据库view介绍

在 Oracle 数据库中，视图（View）是一种虚拟表，它基于 SQL 查询语句从一个或多个表（或其他视图）中导出数据。视图并不存储实际数据，而是动态呈现数据的逻辑结构，提供了数据抽象、安全控制和简化查询的强大功能。

一、视图的基本概念

1. 定义与特性

• 虚拟表：视图是存储在数据字典中的 SQL 查询定义，不占用物理存储空间。

• 动态数据：视图的数据随基表（Base Table）的变化而实时更新。

• 安全性：通过视图可以限制用户对敏感数据的访问权限。

• 简化查询：复杂查询可以封装为视图，供用户直接调用。

2. 视图的用途

• 数据抽象：隐藏表的复杂结构，提供简洁的数据接口。

• 权限控制：仅暴露用户需要的数据列，保护敏感信息（如工资、密码）。

• 跨表查询：将多表连接查询封装为视图，简化客户端使用。

• 兼容旧系统：通过视图为表结构变更提供向后兼容能力。

二、视图的分类

Oracle 视图可分为以下几类：

1. 简单视图（Simple View）

• 特点：基于单个表，不包含函数、分组或连接操作。

• 支持操作：可进行 DML 操作（INSERT/UPDATE/DELETE），需满足一定条件。

-- 创建简单视图
CREATE VIEW emp_dept_view AS
SELECT employee_id, first_name, department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

2. 复杂视图（Complex View）

• 特点：包含函数、分组（GROUP BY）、连接（JOIN）或聚合操作（如 SUM、COUNT）。

• 限制：通常不支持 DML 操作。

-- 创建复杂视图（含聚合函数）
CREATE VIEW dept_salary_view AS
SELECT 
    d.department_name,
    COUNT(e.employee_id) AS employee_count,
    AVG(e.salary) AS avg_salary
FROM 
    employees e
JOIN 
    departments d ON e.department_id = d.department_id
GROUP BY 
    d.department_name;

3. 物化视图（Materialized View）

• 特点：预计算并存储视图结果，定期刷新，适用于数据仓库环境。

• 优势：提高复杂查询性能，减少实时计算开销。

-- 创建物化视图
CREATE MATERIALIZED VIEW emp_salary_mv
BUILD IMMEDIATE  -- 立即构建
REFRESH COMPLETE ON DEMAND  -- 按需完全刷新
AS
SELECT 
    department_id,
    SUM(salary) AS total_salary,
    COUNT(*) AS employee_count
FROM 
    employees
GROUP BY 
    department_id;

4. 内嵌视图（Inline View）

• 特点：在 SQL 查询的 FROM 子句中定义的临时视图，也称为子查询。

• 用途：简化复杂查询，提高可读性。

-- 使用内嵌视图过滤数据
SELECT 
    e.employee_id, 
    e.first_name, 
    d.dept_name
FROM 
    employees e
JOIN (
    SELECT department_id, department_name AS dept_name
    FROM departments
) d ON e.department_id = d.department_id;

三、视图的创建与管理

1. 创建视图

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view_name [(column_alias[, ...])]
AS
SELECT_statement
[WITH CHECK OPTION [CONSTRAINT constraint_name]]
[WITH READ ONLY [CONSTRAINT constraint_name]];

关键参数：

• OR REPLACE：如果视图已存在，则覆盖原定义。

• FORCE：即使基表不存在也创建视图（需在基表创建后才能使用）。

• WITH CHECK OPTION：限制通过视图进行的 DML 操作必须满足视图定义的条件。

• WITH READ ONLY：禁止通过视图执行 DML 操作。

2. 修改视图

-- 使用OR REPLACE子句重新创建视图
CREATE OR REPLACE VIEW emp_dept_view AS
SELECT employee_id, first_name, last_name, department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

3. 删除视图

DROP VIEW emp_dept_view;

4. 查看视图定义

-- 查询数据字典获取视图定义
SELECT text FROM dba_views WHERE view_name = 'EMP_DEPT_VIEW';

四、视图的 DML 操作限制

1. 简单视图的 DML 支持

简单视图通常可执行 DML 操作，但需满足以下条件：

• 视图中的每列必须映射到基表的列。

• 基表的主键列必须包含在视图中。

• 视图定义中不包含聚合函数、GROUP BY、DISTINCT 等。

2. WITH CHECK OPTION 约束

-- 创建带CHECK OPTION的视图
CREATE VIEW emp_sales_view AS
SELECT employee_id, first_name, department_id
FROM employees
WHERE department_id = 80  -- 销售部门
WITH CHECK OPTION;

-- 尝试插入非销售部门数据将失败
INSERT INTO emp_sales_view (employee_id, first_name, department_id)
VALUES (1001, 'John', 90);  -- 违反CHECK OPTION约束

3. 复杂视图的 DML 限制

复杂视图通常不支持 DML 操作，但以下情况除外：

• 使用 INSTEAD OF 触发器处理复杂视图的 DML 操作。

• 视图定义满足 Oracle 的 "可更新连接视图" 规则。

五、视图的安全应用

列级权限控制：

-- 创建仅暴露部分列的视图
CREATE VIEW emp_public_view AS
SELECT employee_id, first_name, last_name, job_title
FROM employees;

行级权限控制：

-- 创建基于部门的行级安全视图
CREATE VIEW dept_emp_view AS
SELECT *
FROM employees
WHERE department_id = SYS_CONTEXT('USERENV', 'DEPARTMENT_ID');

敏感数据掩码：

-- 创建隐藏部分信息的视图
CREATE VIEW emp_sensitive_view AS
SELECT 
    employee_id,
    first_name,
    last_name,
    RPAD(SUBSTR(email, 1, 3), LENGTH(email), '*') AS masked_email,  -- 邮箱掩码
    CASE 
        WHEN SYSDATE - hire_date > 365 THEN salary  -- 老员工可见完整工资
        ELSE '*****' 
    END AS salary
FROM employees;