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;
--視圖上的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;

--創建複雜視圖
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;