Summarize Oracle view knowledge points

This article brings you relevant knowledge about Oracle, which mainly introduces related issues about views. A view is a database object, which is obtained from one or more data tables or views. Let’s take a look at the virtual table exported from . I hope it will be helpful to everyone.

## Recommended tutorial: "

Oracle Video Tutorial"

1. View

• View is a database object that is a virtual table derived from one or more data tables or views.
The
• data corresponding to the view is not actually stored in the view, but is stored in the referenced data table.
The structure and data of the view are the results of querying the data table.

---

#According to the conditions given when creating the view, the view can be part of a data table or a union of multiple base tables.
• It stores the definition of the
• query statement to be retrieved for use when referencing the view.

---

Advantages of using views:

1. Simplified data operations: Views can Simplify how users work with data.
2. Focus on specific data: Unnecessary data or sensitive data may not appear in the view.
View provides a simple and effective security mechanism that can customize different users' access rights to data.
4. Provide backward compatibility: Views enable users to create backward-compatible interfaces for tables when their schema changes.

---

1. Create a modified view

CREATE [OR REPLACE] [FORCE] VIEW 'view_name'AS 'sub_query'[WITH CHECK OPTION]-- 只读[WITH READ ONLY]

---

ParametersDescription##OR REPLACEOracleFORCEOraclesub_querySELECT##WITH CHECK OPTIONData inserted or modified in the data table Rows must satisfy the constraints defined by the viewWITH READ ONLYNo operations can be performed on this view

---

2. 删除视图

DROP VIEW 'view_name';

---

二、试图案例

---

1. 简单视图

• 如果视图中的语句只是 单表查询，并且 没有聚合函数，我们就称之为 简单视图。

---

• 需求：创建视图，业主类型为 1 的业主信息。

create or replace view `view_owners` asselect * from T_OWNERS where ownertypeid = 1;

• 查询该视图。

select * from `view_owners` where addressid = 1;

• 就像使用表一样，去使用视图就可以了。
• 对于简单视图，不仅可以用查询，还可以增删改记录。

update `view_owners` set name='王刚' where id = 2;select * from `view_owners`;

• 再次查询表数据，发现表的数据也跟着更改了。
• 结论：视图其实是一个虚拟的表，它的数据其实来自于表。

1. 如果更改了视图的数据，表的数据也自然会变化。
2. 更改了表的数据，视图也自然会变化。
3. 一个视图所存储的并不是数据，而是一条 SQL 语句。

---

2. with check option 带检查约束视图

• 需求：根据 T_ADDRESS（地址表）创建视图 VIEW_ADDRESS，内容为 区域ID 为 2 的记录。

create or replace view `view_address` asselect * from T_ADDRESS where areaid = 2 with check option;

• 执行更新语句，报错。

-- 无法修改成功-- 因为该视图的条件是`arreaid = 2`，不能修改为`arreaid = 1`。update `view_address` set areaid = 1 where id = 4;

• 错误信息：
  ORA-01402: view WITH CHECK OPTION where-clause violation
  

---

3. with read only 只读视图

• 如果创建一个视图，并不希望用户能对视图进行修改。
• 就需要创建视图时，指定 WITH READ ONLY 选项，这样创建的视图就是一个 只读视图。

---

• 需求：修改视图为只读视图。

create or replace view `view_owners` asselect * from T_OWNERS where ownertypeid = 1 with read only;

• 执行更新语句，报错。

update `view_owners` set name='王强' where id = 2;

• 错误信息：
  ORA-42399: cannot perform a DML operation on a read-only view
  

---

4. 带错误视图

• 创建一个视图，如果视图的 SQL 语句所设计的表并不存在。

-- 错误视图，创建不成功create or replace view `view_TEMP`as-- `T_TEMP`表不存在select * from `T_TEMP`;

• T_TEMP 表并不存在。
  

• 有的时候，创建视图时的表可能并不存在，但是以后可能会存在。
• 如果此时需要创建这样的视图，需要添加 FORCE 选项。

create or replace FORCE view `view_TEMP` asselect * from `T_TEMP`;

---

5. 复杂视图

• 复杂视图，就是视图的 SQL 语句中，有 聚合函数 或 多表关联查询。

---

5.1 多表关联查询

• 需求：创建视图，查询显示业主编号，业主名称，业主类型名称。

create or replace view `view_owners` asselect o.id '业主编号', o.name '业主名称', ot.name '业主类型' from T_OWNERS o, T_OWNERTYPE ot 
where o.ownertypeid = ot.id;

• 查询该视图。

select * from `view_owners`;

• 修改该视图。

-- 更新成功update view_owners set '业主名称' = '范小冰' where '业主编号' = 1;  -- 更新失败update view_owners set '业主类型' ='普通居民' where '业主编号' = 1;

• 出错误提示：是说所需改的列不属于键保留表的列。
  ORA-01779: cannot modify a column which maps to a non key-preserved table
  

• 什么叫键保留表。

1. 键保留表，是理解连接视图修改限制的一个基本概念。
2. 该表的主键列全部显示在视图中，并且它们的值在视图中都是唯一且非空的。
3. 也就是说，表的键值在一个连接视图中也是键值，那么就称这个表为 键保留表。

• 该视图中存在两个表 T_OWNERS（业主表）和 T_OWNERTYPE（业主类型表）。

1. 其中 T_OWNERS 表就是 键保留表。
2. 因为 T_OWNERS 的主键也是作为视图的主键。
3. 键保留表的字段是可以更新的，而非键保留表是不能更新的。

---

7.2 分组聚合统计查询

• 需求：创建视图，按年月统计水费金额。

create view `view_account_sum` as-- 必须 + `money_sum` 别名select year, month, sum(money) money_sum from `T_ACCOUNT` group by year, monthorder by year, month;

• 修改该视图。

update view_account_sum set money_sum = money_sum - 100 where year = 2012 and month = 12;

• 此例用到聚合函数，没有键保留表，所以无法执行 update。
  ORA-01732: data manipulation operation not legal on this view
  

---

三、物化视图

• 视图是一个 虚拟表（也可以认为是一条语句）。

1. 基于它创建时，指定的查询语句返回的结果集。
2. 每次访问它，都会导致这个查询语句被执行一次。
3. 为了避免每次访问都执行这个查询，可以将这个查询结果集存储到一个 物化视图（也叫实体化视图）。

---

• 物化视图 与普通的视图相比的区别是。

1. 物化视图是建立的副本。
2. 它类似于一张表，需要占用存储空间。
3. 而对一个物化视图查询的执行效率与查询一个表是一样的。

# 物化视图日志（Tables）MLOG$_'表名'# 物化视图（Tables）MV_'表名'

---

1. 创建物化视图

• 生成数据

1. BUILD IMMEDIATE：是在创建物化视图的时候就生成数据（默认）。
2. BUILD DEFERRED：则在创建时不生成数据，以后根据需要再生成数据。

• REFRESH：刷新，指当基表发生了 DML 操作后，物化视图何时采用哪种方式和基表进行同步。
  REFRESH 后跟着指定的刷新方法有三种：FAST、COMPLETE、FORCE。

1. FAST：采用增量刷新，只刷新自上次刷新以后进行的修改。
2. COMPLETE：对整个物化视图进行完全的刷新。
3. FORCE：刷新时去判断是否可以快速刷新，如果可以则采用 FAST 方式，否则采用 COMPLETE 的方式（默认）。

• 刷新的模式。

1. ON DEMAND：指需要手动刷新物化视图（默认）。
2. ON COMMIT：指在基表发生 COMMIT 操作时自动刷新。

CREATE MATERIALIZED VIEW 'view_name'-- 立即显示 | 延迟显示[ 'BUILD IMMEDIATE' | BUILD DEFERRED ] -- 快 | 完全 | 快or完全REFRESH [ FAST | COMPLETE | 'FORCE' ]  [ON [ 'DEMAND' | COMMIT ] | START WITH (start_time) NEXT (next_time)]AS 'sub_query';

---

四、物化试图案例

---

1. 创建 手动刷新 的物化视图

• 需求：查询地址ID、地址名称和所属区域名称。

create materialized view `mv_address`asselect ad.id, ad.name adname, ar.name ar_name 
from t_address ad, t_area ar 
where ad.areaid = ar.id;

---

• 执行查询语句。

select * from `mv_address`;

---

• 向基表 T_ADDRESS（地址表）中插入一条新记录。

insert into `t_address` values (8, '宏福苑小区', 1, 1);

---

• 再次执行查询语句，会发现新插入的数据并没有出现在物化视图中。

1. 需要通过下面的 PL/SQL 语句，手动刷新物化视图。

begin
	-- `C`完全刷新（COMPLETE）
	DBMS_MVIEW.refresh('MV_ADDRESS', 'C'); end;

---

2. 或者通过下面的命令，手动刷新物化视图。

-- 注意：此命令需要在命令窗口中执行。EXEC DBMS_MVIEW.refresh('MV_ADDRESS', 'C');

• DBMS_MVIEW.refresh 是系统内置的存储过程。
• 执行命令后，再次查询物化视图，就可以查询到最新的数据了。

---

2. 创建 自动刷新 的物化视图

• 创建此物化视图后，当 T_ADDRESS 表发生变化时，MV_ADDRESS 自动跟着改变。

create materialized view `mv_address_2`refresh-- 自动刷新on commit asselect ad.id,ad.name adname,ar.name ar_name 
from t_address ad,t_area ar 
where ad.areaid=ar.id;

---

3. 创建时 不生成数据 的物化视图

create materialized view `mv_address_3`-- 创建不生成数据build deferred 
refresh-- 自动刷新on commit asselect ad.id,ad.name adname,ar.name ar_name 
from t_address ad,t_area ar 
where ad.areaid=ar.id;

---

• 创建后执行下列语句查询物化视图，没有数据。

 select * from `mv_address_3`;

---

• 执行下列语句生成数据。

begin
   DBMS_MVIEW.refresh('MV_ADDRESS3', 'C');end;

• 由于创建时指定的 on commit。
• 所以在修改数据后能立刻看到最新数据，无须再次执行 refresh。

---

4. 创建 增量刷新 的物化视图

• 如果创建增量刷新的物化视图，必须首先创建物化视图日志。
• 记录基表发生了哪些变化，用日志去更新物化视图。

-- 根据`rowid`create materialized view log on t_address with rowid; create materialized view log on t_area with rowid;

---

• 创建的物化视图日志名称为 MLOG$_表名称。

create materialized view `mv_address_4`-- 增量刷新refresh fast 
asselect ad.rowid adrowid, ar.rowid arrowid, ad.id, ad.name adname, ar.name ar_name  
-- 需要创建两表日志from t_address ad, t_area ar  
where ad.areaid = ar.id;

---

• 注意：创建增量刷新的物化视图。

1. 创建物化视图中涉及表的物化视图日志。
2. 在查询语句中，必须包含所有表的 rowid（以 rowid 方式建立物化视图日志）

---

• 当向地址表插入数据后，物化视图日志的内容。

If the created view already exists,	will automatically rebuild the view
This view will be automatically created regardless of whether the base table exists
A complete	statement, in which an alias can be defined
DML

参数	说明
SNAPTIME$$	用于表示刷新时间。
DMLTYPE$$	用于表示 DML 操作类型（I 表示 INSERT，D 表示 DELETE，U 表示 UPDATE）。
OLD_NEW$$	用于表示这个值是新值还是旧值（N（EW）表示新值，O（LD）表示旧值，U 表示 UPDATE 操作）。
CHANGE_VECTOR$$	表示修改矢量，用来表示被修改的是哪个或哪几个字段（此列是 RAW 类型）。 其实 Oracle 采用的方式就是用每个 BIT 位去映射一个列。 插入操作显示为：FE，删除显示为：OO，更新操作则根据更新字段的位置而显示不同的值。

---

• 当手动刷新物化视图后，物化视图日志被清空，物化视图更新。

begin
    DBMS_MVIEW.refresh('MV_ADDRESS4', 'C');end;

推荐教程：《Oracle视频教程》

The above is the detailed content of Summarize Oracle view knowledge points. For more information, please follow other related articles on the PHP Chinese website!