oracle教程13 Oracle数据库对象---视图详解和举例

Oracle的视图介绍与创建

--创建简单视图
--建立用于查询员工号、姓名、工资的视图。
create view emp_view
as
select empno,ename,sal from emp;


--查询视图
select * from emp_view;

--创建视图时指定视图的列的别名
create view emp_view2(员工号,姓名,工资)
as
select empno,ename,sal from emp;

--查询视图
select * from emp_view2;

--连接视图
--建立用于获得部门号为10的部门号、部门名称及员工信息。
create view dept_emp_view
as
select d.deptno,d.dname,e.empno,e.ename,e.job
from dept d, emp e
where d.deptno = e.deptno and d.deptno = 10;

--查询视图
select * from dept_emp_view;

--只读视图
--建立查看10号部门员工信息的视图。
create view emp_view3
as
select * from emp where deptno = 10
with read only;

--查询视图
select * from emp_view3;
--测试
update emp_view3 set sal = sal +50;

Oracle视图的查询修改与删除

--视图上的DML操作

--创建视图
create view empnew_view
as
select empno,ename,sal from empnew;

--select
select * from empnew_view;

--insert
insert into empnew_view(empno,ename,sal) values(8888,'LAYNA',6666);
select * from empnew;

--update
update empnew_view set sal = sal + 100 where empno = 8888;

--delete
delete from empnew_view where empno = 8888;
commit;

--在视图上定义check约束
create view empnew_view2
as
select * from empnew where deptno = 20
with check option constraint ck_view;

--查询视图
select * from empnew_view2;

--测试
--insert或update
update empnew_view2 set deptno = 30 where empno = 7566;

--修改视图

--修改前查询
select * from empnew_view;

--修改empnew_view视图
create or replace view empnew_view
as
select * from emp where job = 'SALESMAN';

--修改后查询
select * from empnew_view;

--删除视图
drop view empnew_view;

select * from emp;

Oracle的复杂视图

--创建复杂视图
create view job_view(job, avgsal, sumsal, maxsal, minsal)
as
select job,avg(sal),sum(sal),max(sal),min(sal) from emp group by job;

--查看复杂视图
select * from job_view;

--通过with read only子句为复杂视图屏蔽DML操作
create view job_view
as
select job,avg(sal) avgsal,sum(sal) sumsal,max(sal) maxsal,min(sal) minsal from emp group by job
with read only;