归纳总结Oracle视图知识点

归纳总结Oracle视图知识点,第1张

归纳总结Oracle视图知识点 本篇文章给大家带来了关于Oracle的相关知识,其中主要介绍了关于视图的相关问题,视图 是一种数据库对象,是从一个或者多个数据表或视图中导出的 虚表,下面一起来看一下,希望对大家有帮助。

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

一、视图
  • 视图 是一种数据库对象,是从一个或者多个数据表或视图中导出的 虚表
  • 视图所对应的 数据并不真正地存储在视图中,而是存储在所引用的数据表中。
  • 视图的结构和数据,是对数据表进行查询的结果。

  • 根据创建视图时给定的条件,视图可以是一个数据表的一部分,也可以是多个基表的联合。
  • 它存储了要执行检索的 查询语句的定义,以便在引用该视图时使用。

  • 使用视图的优点:
  1. 简化数据操作:视图可以简化用户处理数据的方式。
  2. 着重于特定数据:不必要的数据或敏感数据,可以不出现在视图中。
  3. 视图提供了一个简单而有效的安全机制,可以定制不同用户对数据的访问权限。
  4. 提供向后兼容性:视图使用户能够在表的架构更改时,为表创建向后兼容接口。

1. 创建修改视图
CREATE [OR REPLACE] [FORCE] VIEW 'view_name'AS 'sub_query'[WITH CHECK OPTION]-- 只读[WITH READ ONLY]

参数说明OR REPLACE若所创建的试图已经存在,Oracle 自动重建该视图FORCE不管基表是否存在 Oracle 都会自动创建该视图sub_query一条完整的 SELECT 语句,可以在该语句中定义别名WITH CHECK OPTION数据表插入或修改的数据行,必须满足视图定义的约束WITH READ ONLY该视图上不能进行任何 DML 操作
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,内容为 区域ID2 的记录。
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 后跟着指定的刷新方法有三种:FASTCOMPLETEFORCE
  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;

  1. 或者通过下面的命令,手动刷新物化视图。
-- 注意:此命令需要在命令窗口中执行。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 方式建立物化视图日志)

  • 当向地址表插入数据后,物化视图日志的内容。
参数说明SNAPTIME$$用于表示刷新时间。DMLTYPE$$用于表示 DML 操作类型(I 表示 INSERTD 表示 DELETEU 表示 UPDATE)。OLD_NEW$$用于表示这个值是新值还是旧值(NEW)表示新值,OLD)表示旧值,U 表示 UPDATE 操作)。CHANGE_VECTOR$$表示修改矢量,用来表示被修改的是哪个或哪几个字段(此列是 RAW 类型)。
其实 Oracle 采用的方式就是用每个 BIT 位去映射一个列。
插入操作显示为:FE,删除显示为:OO,更新操作则根据更新字段的位置而显示不同的值。
  • 当手动刷新物化视图后,物化视图日志被清空,物化视图更新。
begin
    DBMS_MVIEW.refresh('MV_ADDRESS4', 'C');end;

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

以上就是归纳总结Oracle视图知识点的详细内容,

欢迎分享,转载请注明来源:内存溢出

原文地址: https://outofmemory.cn/sjk/1372421.html

(0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
上一篇 2022-06-16
下一篇 2022-06-16

随机推荐

  • 娇兰复原蜜油有保湿效果吗

    娇兰复原蜜的保湿效果很好,吸收能力也是不错的,涂上很快速就被吸收了,因为是液体状的美容液,所以使用起来不会有黏腻感,是很清透的质地,滋润效果是很好的。娇兰复原蜜的保湿效果很好,吸收能力也是不错的,涂上

    2022-09-29
    0 0 0
  • 齐刘海拍证件照怎么梳

    齐刘海拍证件照的时候要进行简单的整理,如果是比较少的空气刘海,直接做内扣处理就可以,整理整齐,然后使用适当的造型产品定型。如果是比较多一点的齐刘海,最好是能够分区,向两边整齐刘海拍证件照的时候要进行简

    2022-09-29
    0 0 0
  • 大连是哪个省哪个市哪个区

    大连市属于辽宁省,别称滨城,是辽宁省副省级城市、计划单列市,国家批复确定的中方沿海重要的中心城市、港口及风景旅游城市。全市下辖7个区、1个县、代管县级市。大连地处辽东半大连市属于辽宁省,别称滨城,是辽

    2022-09-29
    0 0 0
  • 低帮帆布鞋配什么袜子 低帮帆布鞋配什么袜子好看男

    1、白色帆布鞋 黑色袜子。黑白配,简单而经典,黑色袜子搭上小白鞋,很经典,很耐看。如果喜欢单色,搭配也可以黑白配的方法来搭。2、黑色袜子 黑色帆布鞋。如果喜欢同色系的朋友们,大1、白色帆布鞋 黑色袜子

    2022-09-29
    0 0 0
  • 猫咪胡子根部发黑是怎么回事

    猫咪胡子根部发黑,可能是自身基因引起的,生理性导致的色素出现改变,可以不用去管。主人只需要观察猫咪是否有不正常的现象,比如不停的抓挠胡子根部,胡子根部出现结痂或者掉毛的现猫咪胡子根部发黑,可能是自身基

    2022-09-29
    0 0 0
  • 洗衣袋可以洗内衣吗

    不能,内衣必须用手洗。因为洗衣机长期洗衣服,里面的脏东西不能很好的清除掉,容易滋生一些细菌之类的脏东西。所以不能用洗衣机内衣。当然如果有人真的要洗的话,也是没办法的事。不能,内衣必须用手洗。因为洗衣机

    2022-09-29
    0 0 0
  • 荣耀x20se处理器是什么

    演示机型:荣耀X20 SE系统版本:Magic UI4.2 荣耀x20se处理器是联发科天玑700。荣耀X20 SE采用对称式中置打孔设计,档位高屏占比94.2%,机身上边框为1.35mm,两侧边框

    2022-09-29
    0 0 0
  • 不准为商业目的的统方是什么意思

    不准为商业目的的统方是医院对医生用药信息量的统计。所谓为商业目的“统方”,是指医院中个人或部门为医药营销人员提供医生或部门一定时期内临床用药量信息,供其发放药品回扣不准为商业目的的统方是医院对医生用药

  • 巧克力为什么会有虫子

    巧克力会有虫子可能是因为果仁等配料中有虫卵没有被杀光,然后孵化成虫子。或者在包装过程中,生产的环境卫生条件不合格,导致有虫卵混入。也有可能是包装破损或者开封后虫子进入巧克力会有虫子可能是因为果仁等配料

    2022-09-29
    0 0 0

发表评论

登录后才能评论

评论列表(0条)

保存