Oracle视图使用详解

　

oracle视图

-- 创建简单视图
create view view_owners1 as
select * from t_owners where ownertypeid = 1

-- 查询简单视图
select * from view_owners1 where addressid = 1
select * from view_owners1 where id = 1

update view_owners1 set name = '范小兵' where id = 1
commit

select * from t_owners where id = 1

-- 带检查约束的视图
create or replace view view_address as
select * from t_address where areaid = 3
with check option

select * from view_address

update view_address set id = 5 where areaid = 3

-- 只读视图
create or replace view view_owners2
as
select * from t_owners where ownertypeid = 1
with read only

update view_owners2 set name = '仿效' where id = 1

-- 创建带错误的视图

select ow.*, from t_owners ow,t_ownertype ot
where ow.ownertypeid = ot.id

-- 聚合统计的复杂视图

select month,year,sum(money)
from t_account
group by year,month
order by month,year

select * from t_account

-- 创建手动刷新的物化视图
create materialized view mv_address1
as
select ad.id 地址编号,ad.name 地址名称,ar.name 区域名称 from t_address ad,t_area ar
where ad.areaid = ar.id 

drop materialized view view_address1
-- 查询物化视图
select * from mv_address1
-- 向物化视图中插入数据
insert into t_address values (8,'文汇苑',4,4)
commit
-- 查询基表
select * from t_address

-- 执行下列语句来进行刷新
begin
   DBMS_MVIEW.refresh('MV_ADDRESS1','C');
end;

-- 创建物化视图  自动刷新  on commit 基表发生commit操作，自动刷新物化视图
create materialized view mv_address2
refresh
on commit
as
select ad.id 地址编号,ad.name 地址名称,ar.name 区域名称 from t_address ad,t_area ar
where ad.areaid = ar.id 

-- 向物化视图中插入数据
insert into t_address values (9,'文汇苑111',4,4)
commit

select * from mv_address2

-- 创建增量刷新 的物化视图

create materialized view log on t_address with rowid
create materialized view log on t_area with rowid
-- 创建物化视图中的语句中必须有基表的rowid
create materialized view mv_address3
refresh fast
as
select ad.rowid adrowid,ar.rowid arrowid,ad.id 地址编号,ad.name 地址名称,ar.name 区域名称 from t_address ad,t_area ar
where ad.areaid = ar.id 

select * from mv_address3
 insert into t_address values (10,'文汇苑10',4,4)
  insert into t_address values (11,'文汇苑11',4,4)
  delete t_address where id= 11
commit

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

文章最后发布于: 2018-08-11 13:52:18

相关阅读

【Oracle Database 12c新特性】ORACLE_MAINTAINED

Ask Maclean Liu Oracle>正文 【Oracle Database 12c新特性】ORACLE_MAINTAINED 原创maclean_0072013-07-21

oracle最全面的分析函数over(Partition by...)及开窗

大型项目中通常会涉及到统计分析相关的功能，今天笔者把oracle常用的分析函数用法和区别通过详细的案例整理出来，希望对各位同学有帮

oracle 体系结构

oracle数据库系统由两部分组成：数据库和实例数据库：物理文件的集合 实例：  一组oracle后台   进程/线程 以及服务器分配的 共享

oracle角色（role）和权限（privilege）

0 创建测试用户 create user soctt identified by 11; grant dba to scott; create user one identified by 11; 1 角色role --

【Oracle】Oracle安装配置、创建数据库实例及用户和连

参考资料：https://www.cnblogs.com/hoobey/p/6010804.html 一、Oracle下载 注意Oracle分成两个文件，下载完后，将两个文件解压到同一