oracle数据库视图相关的语句创建视图：
create or replace force view view_owners
as
select * from T_OWNERS
删除视图语法：
drop view view_owners
创建视图 ：业主类型为 1 的业主信息
create or replace  view view_owners
as
select * from t_Owners where ownertypeid = 1
--利⽤该视图进⾏查询
select * from view_owners where ownertypeid = 1
--对简单视图还可以进⾏增删改的操作
update T_owners  set name = '范冰冰' where id = 1
--利⽤该视图进⾏查询
select * from view_owners where ownertypeid = 1
--对表进⾏查询
select * from T_Owners where ownertypeid = 1
--带检查约束的视图
--根据地址表（T_ADDRESS）创建视图 VIEW_ADDRESS2 ，内容为区域 ID 为 2 的记录。
create or replace view view_address
as
select * from T_ADDRESS where areaid = 2
with check option
--执⾏不成功的语句带约束的视图，不可以修改条件，修改其他的属性可以
update view_address set areaid = 3 where id = 4
--只读视图的创建和使⽤
--将上边的视图修改为只读视图
create or replace view view_address2
as
select * from T_address where areaid = 2
with read only
--查询该视图
select * from view_address2
--修改后会出现错误基本的增删改查语句
update view_address2 set name = '区域' where id = 3
--创建带错误的视图
create force view view_temp
as
select * from T_temp
--复杂视图的创建和使⽤
--查询显⽰业主编号，业主名称，业主类型名称
create or replace view view_owners
as
select ot.id 业主编号,ot.name 业主名称,ow.name 业主类型
from T_Owners ot ,t_Ownertype ow
where  ot.ownertypeid = ow.id
select * from view_owners
--进⾏数据的修改
update view_owners set 业主名称 = '范冰' where 业主编号 = 1--可以修改成功 --在进⾏修改业主类型
update view_owners  set 业主类型='居民' where 业主编号 = 1
---创建视图，按年⽉统计⽔费⾦额，
create or replace view view_account
as
select year 年份 ,month ⽉份 ,sum(money) ⾦额
from T_account group by year ,month
order by year , month
--查询
select * from T_account
--物化视图
--查询地址 ID，地址名称和所属区域名称
create materialized view view_address
as
select ad.id,ad.name 地址 ,ar.name 区域 from T_address ad ,T_area ar where ad.areaid = ar.id
--------------------------------
create  materialized  view view_address
as
select id,name,(select name from T_area where id = areaid) from T_address ---------------------------------------------
select * from view_address
--向表中添加⼀条记录
select * from T_address
insert into T_address values (9,'宏福苑⼩区',1,1)
--没有进⾏刷新，默认⼿动刷新
begin
fresh('view_ADDRESS','C');
end;
--创建⾃动刷新的物化视图
--创建⾃动刷新的物化视图
create materialized view view_address2
refresh
on commit
as
select ad.id,ad.name adname,ar.name arname
from t_address ad ,t_area ar
where ad.areaid = ar.id
select * from view_address2
--创建不⽣成数据的物化视图
create materialized view view_address3
build deferred
refresh
on commit
as
select ad.id,ad.name 地址, ar.name 区域
from t_address ad ,t_area ar
where ad.areaid = ar.id
--查询该视图
select * from view_address3
---执⾏下列语句⽣成数据
begin
fresh('view_ADDRESS3','C');
end;
--创建增量刷新的物化视图
--如果创建增量刷新的物化视图，必须⾸先创建物化视图⽇志
create materialized view log on t_address with rowid;
create materialized view log on t_area with rowid
--创建的物化视图⽇志名称为 MLOG$_表名称
--创建物化视图
create materialized view view_address4
refresh fast
as
wid adrowid ,ar.rowid arrowid ,ad.id,ad.name 地址, ar.name 区域from t_address ad ,t_area ar
where ad.areaid = ar.id
--查询物化视图
select * from view_address4
--插⼊数据
select * from T_address
insert into t_address values (10,'居宁花园',2,2)
commit;
delete * from t_address where id = 9
--⼿动刷新
begin
fresh('view_address4','C');
end;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。