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
  DBMS_MVIEW.refresh('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
DBMS_MVIEW.refresh('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 
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 view_address4
--插入数据
select * from T_address
insert into t_address values (10,'居宁花园',2,2)
commit;
delete * from t_address where id = 9


--手动刷新
begin 
  Dbms_Mview.refresh('view_address4','C');
end; 

  • 0
    点赞
  • 11
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值