oracle怎么试图语句,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;