视图

--------------------视图（虚拟表，简化开发）---------------------------

--创建简单视图--
create view VIEW_OWNERS1 as
select*from t_owners where ownertypeid=1
--查询简单视图--
select*from view_owners1
--修改视图数据--
update view_owners1 set name='aaab' where id=1;
commit;
--带检查约束的视图--
create view VIEW_ADDRESS2 as
select*from t_address where areaid=2
with check option
--不能修改条件（该视图的条件是areaid=2）--
update view_address2 set areaid=3 where id=4
--只读视图--
create or replace view VIEW_OWNERS1 as
select*from t_owners where ownertypeid=1
with read only
--创建带错误的视图(可以忽略select语句的错误)--
create force view VIEW_TEST as
select*from t_test
--复杂视图--
--多表关联--
create or replace view view_owners as
select ow.id 业主编号,ow.name 业主名称,ot.name 业主类型 from t_owners ow,t_ownertype ot
where ow.ownertypeid=ot.id
select*from view_owners
--修改复杂视图数据--
update view_owners set 业主名称='aaak' where 业主编号=1;
--只能修改键保留表的数据--
--键保留表：把主键保留下来的那个表
update view_owners set 业主类型='商业' where 业主编号=1;
commit;
--聚合统计的复杂视图--
create view view_accountsum as
select year,month,sum(money) money
from t_account
group by year,month
order by year,month
select*from view_accountsum where year='2012' and month='03'
--------------------物化视图（真实表，提高查询效率（多表关联且经常用））---------------------------
--手动刷新的物化视图--
create materialized view mv_address1 as
select ad.id,ad.name,ar.name arname from t_address ad,t_area ar
where ad.areaid=ar.id
select*from mv_address1
--向基表插入数据--
insert into t_address values(8,'西三旗',2,2);
select*from t_address
--执行下列语句来进行刷新
begin
-- C 完全刷新
DBMS_MVIEW.refresh('mv_address1','C');
end;
--自动刷新物化视图 基表发生commit操作，自动化刷新物化视图（要求实时性强时）--
create materialized view mv_address2
refresh
on commit
as
select ad.id,ad.name,ar.name arname from t_address ad,t_area ar
where ad.areaid=ar.id
select*from mv_address2
insert into t_address values(9,'西四旗',2,2);
--创建时不生成出具的物化视图--
create materialized view mv_address3
build deferred
refresh
on commit
as
select ad.id,ad.name,ar.name arname from t_address ad,t_area ar
where ad.areaid=ar.id
select*from mv_address3
--第一次必须手动执行刷新--
begin
DBMS_MVIEW.refresh('mv_address3','C');
end;
--增量刷新的物化视图--
--前提要有物化视图日志：记录基表发生了什么变化，用这些记录去更新物化视图--
create materialized view log on t_address with rowid
create materialized view log on t_area with rowid
--创建物化视图语句中必须有基表的rowid
create materialized view mv_address4
refresh fast
as
select ad.rowid adrowid,ar.rowid arrowid,ad.id,ad.name,ar.name arname from t_address ad,t_area ar
where ad.areaid=ar.id
select*from mv_address4
insert into t_address values(11,'西6旗',2,2);
begin
DBMS_MVIEW.refresh('mv_address4','C');
end;