网站导航
> 技术文档 > Oracle 视图

Oracle 视图

网友: 技术文档 2025-07-30 12:01:36

作者：IvanCodes
日期：2025年7月10日
专栏：Oracle教程

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

思维导图

Oracle 视图
Oracle 视图
Oracle 视图
Oracle 视图

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

1.1 完整创建语法

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

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

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

-- 第一次创建视图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 是默认行为。
代码案例：

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

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

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

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

CREATE OR REPLACE VIEW dept_20_employees_v ASSELECT emp_id, emp_name, department_idFROM employeesWHERE department_id = 20WITH 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)。
代码案例：

CREATE OR REPLACE VIEW all_employees_readonly_v ASSELECT * FROM employeesWITH 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操作。
代码案例：

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

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

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

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

-- 使用内联视图找出每个部门薪水最高的人SELECT department_name, emp_name, salaryFROM ( 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。
代码案例：

-- 为快速刷新创建物化视图日志CREATE MATERIALIZED VIEW LOG ON employees WITH ROWID;CREATE MATERIALIZED VIEW LOG ON departments WITH ROWID;-- 创建一个可快速刷新的、按需刷新的物化视图CREATE MATERIALIZED VIEW dept_summary_mvBUILD IMMEDIATEREFRESH FAST ON DEMANDENABLE QUERY REWRITEASSELECT d.department_name, COUNT(e.emp_id) AS emp_count, SUM(e.salary) AS total_salFROM employees e JOIN departments d ON e.department_id = d.department_idGROUP BY d.department_name;

3.2 刷新物化视图
对于 ON DEMAND 的物化视图，需要手动刷新。
代码案例：

-- 手动刷新 (F=FAST, C=COMPLETE, ?=FORCE)EXEC DBMS_MVIEW.REFRESH(\'DEPT_SUMMARY_MV\', \'C\');

四、视图的管理

4.1 查询视图定义
使用数据字典 USER_VIEWS 或 ALL_VIEWS。
代码案例：

SELECT view_name, text FROM user_views WHERE view_name LIKE \'DEPT%\';

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

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语句。

题目：

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

答案与解析：

  1. 创建简单视图 electronics_products_v:
CREATE OR REPLACE VIEW electronics_products_v ASSELECT product_id, product_name, category, price, stock_quantityFROM productsWHERE category = \'Electronics\';
  • 解析： 这是一个基于单表、无聚合/连接的简单视图，可以用于DML操作。
  1. 创建复杂视图 product_sales_summary_v:
CREATE OR REPLACE VIEW product_sales_summary_v ASSELECT p.product_name, SUM(s.quantity_sold) AS total_quantity, SUM(p.price * s.quantity_sold) AS total_revenueFROM products pJOIN sales s ON p.product_id = s.product_idGROUP BY p.product_name;
  • 解析： 包含了连接和聚合函数，是一个典型的只读复杂视图。
  1. 通过视图更新数据：
UPDATE electronics_products_vSET stock_quantity = 50WHERE product_id = 101;COMMIT;
  • 解析： 由于 electronics_products_v 是一个简单视图，可以通过它直接更新基表 products 的数据。
  1. 创建只读视图：
CREATE OR REPLACE VIEW readonly_products_v ASSELECT * FROM productsWITH READ ONLY;
  • 解析： WITH READ ONLY 关键字确保了此视图不能被用于 INSERT, UPDATE, DELETE 操作。
  1. 创建带检查选项的视图：
CREATE OR REPLACE VIEW low_stock_alerts_v ASSELECT * FROM productsWHERE stock_quantity < 10WITH CHECK OPTION;
  • 解析： WITH CHECK OPTION 意味着，如果你尝试 UPDATE low_stock_alerts_v SET stock_quantity = 15 WHERE ...，该操作会失败，因为新值15不满足 stock_quantity < 10 的条件。
  1. 使用内联视图查询价格第二高的产品：
SELECT product_name, price, categoryFROM ( SELECT product_name, price, category, DENSE_RANK() OVER (PARTITION BY category ORDER BY price DESC) as rnk FROM products)WHERE rnk = 2;
  • 解析： 内联视图先计算出每个类别内的价格排名，外层查询再筛选出排名为2的记录。
  1. 创建物化视图 monthly_category_sales_mv:
CREATE MATERIALIZED VIEW monthly_category_sales_mvBUILD IMMEDIATEREFRESH COMPLETE ON DEMANDASSELECT p.category, TO_CHAR(s.sale_date, \'YYYY-MM\') AS sale_month, SUM(p.price * s.quantity_sold) AS monthly_revenueFROM products pJOIN sales s ON p.product_id = s.product_idGROUP BY p.category, TO_CHAR(s.sale_date, \'YYYY-MM\');
  • 解析： 创建了一个存储物理数据的物化视图，用于快速查询每月各品类的销售额。
  1. 手动刷新物化视图：
EXEC DBMS_MVIEW.REFRESH(\'MONTHLY_CATEGORY_SALES_MV\');
  • 解析： DBMS_MVIEW.REFRESH 是刷新物化视图的标准包程序。默认刷新方式是FORCE。
  1. 查询数据字典：
SELECT view_name, text FROM user_views;
  • 解析： user_views 是一个数据字典视图，存储了当前用户拥有的所有视图的定义信息。
  1. 删除视图：
DROP VIEW electronics_products_v;DROP MATERIALIZED VIEW monthly_category_sales_mv;
  • 解析： 删除普通视图使用 DROP VIEW，删除物化视图使用 DROP MATERIALIZED VIEW。
网络标签：

相关问题