Oracle 뷰 지식 포인트 요약

이 기사에서는 Oracle에 대한 관련 지식을 제공하며, 주로 뷰와 관련된 문제를 소개합니다. 뷰는 하나 이상의 데이터 테이블이나 뷰에서 파생된 가상 테이블인 데이터베이스 개체입니다. 모든 사람에게 도움이 되기를 바랍니다.

추천 튜토리얼: "Oracle Video Tutorial"

1. View

• View는 하나 이상의 데이터 테이블 또는 뷰가상 테이블에서 파생된 데이터베이스 객체입니다.
• 뷰에 해당하는 데이터는 실제로 뷰에 저장되지 않고 참조된 데이터 테이블에 저장됩니다.
• 뷰의 구조와 데이터는 데이터 테이블을 쿼리한 결과입니다.

• 뷰를 생성할 때 제공된 조건에 따라 뷰는 데이터 테이블의 일부이거나 여러 기본 테이블의 통합일 수 있습니다.
• 뷰를 참조할 때 사용할 검색할 쿼리문의 정의를 저장합니다.

• 뷰 사용의 장점:

1. 데이터 작업 단순화: 뷰는 사용자가 데이터를 처리하는 방식을 단순화할 수 있습니다.
2. 특정 데이터에 집중: 불필요한 데이터나 민감한 데이터는 뷰에 표시될 수 없습니다.
3. 뷰는 데이터에 대한 다양한 사용자의 액세스를 맞춤화할 수 있는 간단하면서도 효과적인 보안 메커니즘을 제공합니다.
4. 이전 버전과의 호환성 제공: 뷰를 사용하면 사용자는 스키마가 변경될 때 테이블에 대해 이전 버전과 호환되는 인터페이스를 만들 수 있습니다.

1. 수정된 뷰를 생성합니다

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

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 方式建立物化视图日志）

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

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

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

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

위 내용은 Oracle 뷰 지식 포인트 요약의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!