Oracle 视图-阿里云开发者社区

Oracle 视图是一个存储在数据库中的命名查询，它本身不包含任何物理数据 (物化视图除外)。你可以把它看作一张“虚拟表”或一个“逻辑窗口”，通过它，用户可以以预设的方式查看一个或多个基表中的数据。

思维导图

一、视图的创建与核心选项

1.1 完整创建语法

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

下面我们将逐一解析每个选项。

1.2 OR REPLACE 选项
此选项允许你修改一个已存在的视图，而无需先删除它。
代码案例：

sql -- 第一次创建视图 CREATE VIEW employee_v AS SELECT emp_id, emp_name FROM employees; -- 修改视图，增加 salary 列 CREATE OR REPLACE VIEW employee_v AS SELECT emp_id, emp_name, salary FROM employees;

1.3 FORCE | NOFORCE 选项
FORCE 可以在基表不存在的情况下强制创建视图。该视图会处于无效(INVALID)状态。 NOFORCE 是默认行为。
代码案例：

sql -- 即使 'non_existent_table' 不存在，也强制创建视图 CREATE FORCE VIEW future_view AS SELECT col1 FROM non_existent_table; -- 查询视图状态，会看到 STATUS 为 INVALID SELECT object_name, status FROM user_objects WHERE object_name = 'FUTURE_VIEW';

1.4 列别名 [(alias1, alias2, ...)]
用于为视图的列指定名称，当 SELECT 语句中包含表达式或函数时特别有用。
代码案例：

sql CREATE OR REPLACE VIEW employee_payroll_v (employee_id, employee_name, annual_salary) AS SELECT emp_id, emp_name, salary * 12 -- 表达式需要别名 FROM employees;

1.5 WITH CHECK OPTION 选项
确保通过视图进行的DML操作产生的新行必须满足视图定义中 WHERE 子句的条件。
代码案例：

sql CREATE OR REPLACE VIEW dept_20_employees_v AS SELECT emp_id, emp_name, department_id FROM employees WHERE department_id = 20 WITH CHECK OPTION; -- 尝试将部门为20的员工更新到部门30，会失败 UPDATE dept_20_employees_v SET department_id = 30 WHERE emp_id = 201; -- ORA-01402: view WITH CHECK OPTION where-clause violation

1.6 WITH READ ONLY 选项
使视图变为只读，禁止通过它进行任何DML操作 ( INSERT, UPDATE, DELETE)。
代码案例：

sql CREATE OR REPLACE VIEW all_employees_readonly_v AS SELECT * FROM employees WITH READ ONLY; -- 尝试通过此视图删除数据，会失败 DELETE FROM all_employees_readonly_v WHERE emp_id = 101; -- ORA-42399: cannot perform a DML operation on a read-only view

## 二、视图的分类与应用 2.1 简单视图
基于单个表，不含 GROUP BY, DISTINCT, 连接, 聚合/窗口函数等。通常可进行DML操作。
代码案例：

sql -- 创建一个简单的、只显示HR部门员工的视图 CREATE OR REPLACE VIEW hr_employees_v AS SELECT emp_id, emp_name, salary FROM employees WHERE department_id = 30; -- 通过简单视图更新数据 UPDATE hr_employees_v SET salary = salary + 1000 WHERE emp_id = 301; COMMIT;

2.2 复杂视图
包含连接、聚合、函数等复杂逻辑。通常只读。
代码案例：

sql -- 创建一个显示各部门员工总数的复杂视图 CREATE OR REPLACE VIEW dept_employee_count_v (department_name, employee_count) AS SELECT d.department_name, COUNT(e.emp_id) FROM employees e JOIN departments d ON e.department_id = d.department_id GROUP BY d.department_name; -- 查询复杂视图 SELECT * FROM dept_employee_count_v;

2.3 内联视图
定义在查询的 FROM 子句中的临时子查询，用完即弃。
代码案例：

sql -- 使用内联视图找出每个部门薪水最高的人 SELECT department_name, emp_name, salary FROM ( SELECT d.department_name, e.emp_name, e.salary, ROW_NUMBER() OVER(PARTITION BY e.department_id ORDER BY e.salary DESC) AS rn FROM employees e JOIN departments d ON e.department_id = d.department_id ) -- 这是内联视图 WHERE rn = 1;

## 三、物化视图

与普通视图不同，物化视图存储查询结果的物理副本，以空间换时间，用于加速复杂查询的性能。

3.1 创建物化视图
语法要点： REFRESH [FAST|COMPLETE|FORCE] ON [COMMIT|DEMAND], BUILD [IMMEDIATE|DEFERRED], ENABLE QUERY REWRITE。
代码案例：

sql -- 为快速刷新创建物化视图日志 CREATE MATERIALIZED VIEW LOG ON employees WITH ROWID; CREATE MATERIALIZED VIEW LOG ON departments WITH ROWID; -- 创建一个可快速刷新的、按需刷新的物化视图 CREATE MATERIALIZED VIEW dept_summary_mv BUILD IMMEDIATE REFRESH FAST ON DEMAND ENABLE QUERY REWRITE AS SELECT d.department_name, COUNT(e.emp_id) AS emp_count, SUM(e.salary) AS total_sal FROM employees e JOIN departments d ON e.department_id = d.department_id GROUP BY d.department_name;

3.2 刷新物化视图
对于 ON DEMAND 的物化视图，需要手动刷新。
代码案例：
sql -- 手动刷新 (F=FAST, C=COMPLETE, ?=FORCE) EXEC DBMS_MVIEW.REFRESH('DEPT_SUMMARY_MV', 'C');

## 四、视图的管理 4.1 查询视图定义
使用数据字典 USER_VIEWS 或 ALL_VIEWS。
代码案例：
sql SELECT view_name, text FROM user_views WHERE view_name LIKE 'DEPT%';

4.2 删除视图
DROP VIEW 用于普通视图， DROP MATERIALIZED VIEW 用于物化视图。
代码案例：
sql DROP VIEW hr_employees_v; DROP MATERIALIZED VIEW dept_summary_mv;

总结：
简单/复杂视图：核心是逻辑抽象，不存数据。 内联视图：查询内部的临时工作台，非常灵活。
物化视图：性能优化的大杀器，核心挑战是数据同步策略。

---

## *练习题

背景表结构：

CREATE TABLE products (
    product_id     NUMBER PRIMARY KEY,
    product_name   VARCHAR2(100),
    category       VARCHAR2(50),
    price          NUMBER(8, 2),
    stock_quantity NUMBER
);
CREATE TABLE sales (
    sale_id       NUMBER PRIMARY KEY,
    product_id    NUMBER,
    customer_id   NUMBER,
    sale_date     DATE,
    quantity_sold NUMBER
);

请为以下每个场景编写相应的SQL语句。

题目：

创建一个名为 electronics_products_v 的简单视图，显示 products 表中所有 category 为 'Electronics' 的产品信息 (所有列)。
创建一个名为 product_sales_summary_v 的复杂视图，显示每个产品 (product_name) 的总销售数量 (total_quantity) 和总销售额 (total_revenue，即 price * quantity_sold 的总和)。
通过 electronics_products_v 视图，将 product_id 为 (假设一个电子产品ID) 101 的产品的 stock_quantity 更新为 50。
创建一个只读视图 readonly_products_v，包含 products 表的所有信息。
创建一个视图 low_stock_alerts_v，显示 stock_quantity 小于10的产品信息，并确保通过此视图更新产品时，stock_quantity 仍然必须小于10。
使用内联视图，查询出每个产品类别中，价格第二高的产品的名称和价格。
创建一个物化视图 monthly_category_sales_mv，按需完全刷新，用于存储每个产品类别每月的总销售额。
写出手动刷新 monthly_category_sales_mv 的命令。
查询数据字典，找出当前用户下所有视图的名称和其定义SQL文本。
删除视图 electronics_products_v 和物化视图 monthly_category_sales_mv。

答案与解析：

创建简单视图 electronics_products_v:

CREATE OR REPLACE VIEW electronics_products_v AS
SELECT product_id, product_name, category, price, stock_quantity
FROM products
WHERE category = 'Electronics';

解析： 这是一个基于单表、无聚合/连接的简单视图，可以用于DML操作。

创建复杂视图 product_sales_summary_v:

CREATE OR REPLACE VIEW product_sales_summary_v AS
SELECT
p.product_name,
SUM(s.quantity_sold) AS total_quantity,
SUM(p.price * s.quantity_sold) AS total_revenue
FROM products p
JOIN sales s ON p.product_id = s.product_id
GROUP BY p.product_name;

解析： 包含了连接和聚合函数，是一个典型的只读复杂视图。

通过视图更新数据：
```
UPDATE electronics_products_v
SET stock_quantity = 50
WHERE product_id = 101;
COMMIT;
```
- 解析： 由于 electronics_products_v 是一个简单视图，可以通过它直接更新基表 products 的数据。
创建只读视图：
```
CREATE OR REPLACE VIEW readonly_products_v AS
SELECT * FROM products
WITH READ ONLY;
```
- 解析： WITH READ ONLY 关键字确保了此视图不能被用于 INSERT, UPDATE, DELETE 操作。
创建带检查选项的视图：
```
CREATE OR REPLACE VIEW low_stock_alerts_v AS
SELECT * FROM products
WHERE stock_quantity < 10
WITH CHECK OPTION;
```
- 解析： WITH CHECK OPTION 意味着，如果你尝试 UPDATE low_stock_alerts_v SET stock_quantity = 15 WHERE ...，该操作会失败，因为新值15不满足 stock_quantity < 10 的条件。

使用内联视图查询价格第二高的产品：

SELECT product_name, price, category
FROM (
SELECT
product_name, price, category,
DENSE_RANK() OVER (PARTITION BY category ORDER BY price DESC) as rnk
FROM products
)
WHERE rnk = 2;

解析： 内联视图先计算出每个类别内的价格排名，外层查询再筛选出排名为2的记录。

创建物化视图 monthly_category_sales_mv:

CREATE MATERIALIZED VIEW monthly_category_sales_mv
BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND
AS
SELECT
p.category,
TO_CHAR(s.sale_date, 'YYYY-MM') AS sale_month,
SUM(p.price * s.quantity_sold) AS monthly_revenue
FROM products p
JOIN sales s ON p.product_id = s.product_id
GROUP BY p.category, TO_CHAR(s.sale_date, 'YYYY-MM');

解析： 创建了一个存储物理数据的物化视图，用于快速查询每月各品类的销售额。

手动刷新物化视图：
```
EXEC DBMS_MVIEW.REFRESH('MONTHLY_CATEGORY_SALES_MV');
```
- 解析： DBMS_MVIEW.REFRESH 是刷新物化视图的标准包程序。默认刷新方式是FORCE。
查询数据字典：
```
SELECT view_name, text FROM user_views;
```
- 解析： user_views 是一个数据字典视图，存储了当前用户拥有的所有视图的定义信息。
删除视图：
```
DROP VIEW electronics_products_v;
DROP MATERIALIZED VIEW monthly_category_sales_mv;
```
- 解析： 删除普通视图使用 DROP VIEW，删除物化视图使用 DROP MATERIALIZED VIEW。

Oracle 视图

思维导图

一、视图的创建与核心选项

数据库

热门文章

最新文章

相关电子书