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;