Oracle的普通视图与物化视图

雷神乐乐

已于 2025-07-07 08:38:03 修改

阅读量1.6k

点赞数 44

CC 4.0 BY-SA版权

分类专栏： # Oracle 文章标签： oracle 数据库 sql 视图物化视图

于 2025-06-03 09:00:00 首次发布

本文链接：https://blog.csdn.net/Helen_1997_1997/article/details/148378988

Oracle 专栏收录该内容

30 篇文章

订阅专栏

一、Oracle数据库视图介绍

二、普通视图

1.创建视图的用户必须有对应的权限：在 SYS 用户授权

2.创建普通视图的语法

3.修改基表，视图数据也会改变

4.通过视图修改基表中没有经过函数加工的字段 [成功]——不建议

5.通过视图修改基表中经过函数加工的字段 [失败]——不建议

1.创建物化视图的用户必须有对应的权限：在 SYS 用户授权

一、Oracle数据库视图介绍

在 Oracle 数据库中，视图(View)是一种虚拟的逻辑表，它本身并不存储真实数据，而是基于一个或多个实际表(或其他视图)的查询结果动态生成的数据集。视图就像一个 “窗口”，通过这个窗口可以选择性地展示表中的数据，或对数据进行过滤、转换和重组。

视图主要分普通视图和物化视图。
创建视图时，在视图的结尾带 _V，用作区分。
视图简单来说就是：将一段SELECT语句封装到视图中去，查询视图的时候就是执行视图里封装的逻辑。
逻辑里用到的表称为“基表”，视图里封装的是一段查询逻辑，当基表的数据发生了变化时，那么查询视图得到的数据也会跟着变化。
也可以通过视图反过来修改基表的的数据，但是有限制条件，不能修改视图经过加工的字段，而且一般不会通过视图去修改基表的数据，只会直接去修改基表的数据。
什么时候要使用视图：对其它系统提供系统数据的时候，要把敏感数据屏蔽；对于一些公用逻辑，可以先写一个视图。

二、普通视图

普通视图：存储的是一段逻辑，查询普通视图的时候就是去执行视图里封装的逻辑。

1.创建视图的用户必须有对应的权限：在 SYS 用户授权

---GRANT CREATE VIEW TO SCOTT;   ---没有权限的需要授权
---GRANT CREATE SEQUENCE TO SCOTT;
GRANT CREATE VIEW TO SCOTT;

2.创建普通视图的语法

CREATE OR REPLACE VIEW 视图名 AS
SELECT XXXX FROM XXX WHERE 条件;

创建基表：

create table emp_bak1 as select * from emp;

select * from emp_bak1;

根据基表创建普通视图：

-- 删除视图
DROP VIEW emp_datail_v;

-- 创建视图
create or replace view EMP_DETAIL_V 
as
select T.EMPNO
       ,T.ENAME NAME
       ,T.DEPTNO
       ,T.JOB
       ,T.HIREDATE
      ,TO_CHAR(T.HIREDATE,'YYYYMM') YYYYMM
from emp_bak1 T; -- 基表

SELECT * FROM EMP_DETAIL_V;

3.修改基表，视图数据也会改变

如果基表的数据发生了变化，视图呈现的数据也会随之变化

UPDATE emp_bak1 T SET T.ENAME = 'YYDS' WHERE T.EMPNO = 7369;
COMMIT;
SELECT * FROM EMP_DETAIL_V;

4.通过视图修改基表中没有经过函数加工的字段 [成功]——不建议

可以通过视图修改基表的数据，但是一般不会这么做，视图封装单表的时候,表中原有字段可以

UPDATE EMP_DETAIL_V T SET T.NAME = 'SMITH' WHERE T.EMPNO = 7369;
COMMIT;
SELECT * FROM EMP_DETAIL_V;

5.通过视图修改基表中经过函数加工的字段 [失败]——不建议

UPDATE EMP_DETAIL_V T SET T.YYYYMM = '201001' WHERE T.EMPNO = 7369;

COMMIT;
SELECT * FROM EMP_DETAIL_V;
SELECT * FROM emp_bak1;

6. 向某些特定类型的视图插入数据

可以向某些特定类型的视图插入数据：要使视图可更新，其必须基于一个单一的表，且不能包含聚合函数、DISTINCT关键字、GROUP BY子句、ORDER BY子句、HAVING子句、UNION运算符。

INSERT INTO EMP_DETAIL_V(empno) VALUES(1111);

7.删除视图

DROP VIEW EMP_DETAIL_V;

8.练习

7.1创建一个视图，查询每个部门的部门编号，这个部门的人数，平均工资

create or replace view EMP_DETAIL2_V 
as
select
       T.DEPTNO
       ,COUNT(1) CT
       ,AVG(SAL+NVL(COMM,0)) AVG_SAL
from emp_bak1 T
group by T.deptno; -- 基表

SELECT * FROM EMP_DETAIL2_V;

7.2创建一个视图查询每个员工的姓名，以及员工对应领导的工号，领导姓名

create or replace view EMP_DETAIL3_V 
as
select
       e1.ename ename
       ,e1.mgr
       ,e2.ename leader_name
from emp_bak1 e1
left join emp_bak1 e2 on e1.mgr=e2.empno; -- 基表

SELECT * FROM EMP_DETAIL3_V;

三、物化视图

物化视图：存储的是真实的数据，查询物化视图的时候就是去查询视图里提前计算好的数据。

创建物化视图的目的是为了考虑性能。

1.创建物化视图的用户必须有对应的权限：在 SYS 用户授权

GRANT CREATE MATERIALIZED VIEW TO SCOTT;

2.创建物化视图

语法：

CREATE MATERIALIZED VIEW [schema.]view_name
[BUILD {IMMEDIATE | DEFERRED}]          
--BUILD IMMEDIATE：在创建物化视图时立即构建并填充数据。
--BUILD DEFERRED：在创建物化视图时不立即构建，数据会在第一次查询时填充。
[REFRESH {FAST | COMPLETE | FORCE}]   
--REFRESH：指定物化视图的刷新方式，
FAST使用尽可能快的方式刷新，
COMPLETE使用完整查询刷新，
FORCE强制刷新。
[ON {DEMAND | NEVER}]        
--ON：指定物化视图的刷新时机，
COMMIT每次提交事务后刷新，
DEMAND按需刷新，
NEVER永不刷新。
AS
SELECT ... ;

CREATE MATERIALIZED VIEW TEST_M_V -- 创建一个新的物化视图，名称为TEST_M_V。
BUILD IMMEDIATE        -- 立即构建物化视图，即在创建物化视图时立即执行查询并填充数据。
REFRESH FORCE          -- 强制刷新物化视图，即使没有检测到底层表的更改，
                       -- 也会重新执行查询并更新视图的数据。
ON COMMIT              -- 指定物化视图的刷新策略，这里设置为每次提交事务后刷新视图。
AS SELECT T.EMPNO, T.ENAME, T.JOB FROM EMP T;
-- 定义物化视图的查询，选择EMP表中的EMPNO、ENAME和JOB字段，并将EMP表别名为T。

3.查询物化视图的数据

4.修改基表数据，物化视图的数据也会更新

-- 修改基表数据
UPDATE EMP_bak T SET T.ENAME = 'YYDS' WHERE T.EMPNO = 7369;
COMMIT;

-- 重新查询物化视图
SELECT * FROM TEST_M_V;

5.关于物化视图的刷新机制

5.1FAST

快速刷新：这种模式下，数据库系统会尝试使用最快的方法来刷新物化视图。这可能意味着使用增量刷新，只更新自上次刷新以来已更改的数据，而不是重新计算整个查询。

5.2COMPLETE

完整/全量刷新：这种模式下，每次刷新物化视图时，都会重新执行完整的SELECT查询，确保物化视图包含最新的数据。

5.3FORCE

强制刷新：这种模式下，每次刷新都会强制执行完整的查询，即使数据库认为可以使用快速刷新。

6.创建可以定时刷新数据的视图

CREATE MATERIALIZED VIEW TEST_M_V1
BUILD IMMEDIATE
REFRESH FORCE 
ON DEMAND 
---第一次刷新时间是当前系统时间点
START WITH SYSDATE 
---2 分钟刷新一次数据
NEXT SYSDATE + 2/1440 
AS 
SELECT T.EMPNO
      ,T.ENAME
      ,T.JOB
FROM emp_bak T ;

----查询物化视图的数据
SELECT * FROM TEST_M_V1;

时间单位转换逻辑：

1 天 = 1
1 小时 = 1/24
1 分钟 = 1/(24×60) = 1/1440
1 秒 = 1/(24×60×60) = 1/86400

示例：

SYSDATE + 1：明天同一时间。
SYSDATE + 1/24：1 小时后。
SYSDATE + 30/86400：30 秒后。

修改基表数据，2分钟后物化视图数据会自动刷新。

7.修改基表数据后，手动刷新物化视图数据

UPDATE emp_bak T SET T.ENAME = 'SMITH' WHERE T.EMPNO = 7369;
COMMIT;

SELECT * FROM emp_bak T;

----立即查询物化视图的数据 没有更新
SELECT * FROM TEST_M_V1;

---手动刷新物化视图的数据
begin
  DBMS_MVIEW.refresh('TEST_M_V1','C'); -- 'C'=完全刷新，'F'=快速刷新
end;

-- 再次立即查询物化视图的数据，已经更新成功
SELECT * FROM TEST_M_V1;