Oracle 创建和使用视图

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view_name   

[(alias[, alias]...)]   

AS subquery  

[WITH CHECK OPTION [CONSTRAINT constraint]]   

[WITH READ ONLY] 

OR REPLACE    ：若所创建的试图已经存在，ORACLE自动重建该视图；
FORCE  ：不管基表是否存在ORACLE都会自动创建该视图；
NOFORCE   ：只有基表都存在ORACLE才会创建该视图：
alias ：为视图产生的列定义的别名；
subquery     ：一条完整的SELECT语句，可以在该语句中定义别名；
WITH CHECK  OPTION ：插入或修改的数据行必须满足视图定义的约束；
WITH READ ONLY ：该视图上不能进行任何DML操作。

CREATE  OR  REPLACE  VIEW  dept_sum_vw 
(name,minsal,maxsal,avgsal) 
AS 
SELECT d.dname,min(e.sal),max(e.sal),avg(e.sal) 
FROM emp e,dept d 
WHERE  e.deptno=d.deptno 
GROUP  BY  d.dname; 

--给用户授权创建视图
-- grant create all view to zdgshr;      所有数据库都能创建视图的用户
grant create view to zdgshr;

--创建简单视图
create view temp 
as 
select * from zd_member_basic_info;
--测试
select * from temp where rownum=1;

--创建简单视图：只读
create or replace view temp1
as 
select id,job_number,name,dept_id from zd_member_basic_info 
with read only;
--测试
insert into temp1(id,job_number,name,dept_id) values(1,0,‘张三‘,1300);

create or replace view temp1
as 
select distinct name,job_number,dept_id from zd_member_basic_info 
with read only;
delete from zd_member_basic_info where name=‘而过‘;

create or replace view temp 
as 
select * from zd_member_basic_info order by name;