《Oracle Database编程指南》19-03：复杂视图(Complex view)
/*
作者：AT阿宝哥
日期：2016年9月18日
愿景：参考官方资料，做最好的课程，成就更多职业人！
邮箱：12264104@qq.com
CSDN：https://blog.csdn.net/goldentec
简书：https://www.jianshu.com/u/8a6075d7a2e0
说明：
注意：
*/
-------------------------------------------------------------------------------
/*
案例编号：Sample5
案例名称：创建复杂的视图
关键知识点：创建视图
说明：
此查询返回部门编号，部门名称，部门的人数，平均工资以及最低工资的雇员姓名。
注意：
关键词/术语表：
参考文献：
*/
--Step1
SELECT deptno,
count(deptno) AS "Ecount" ,
avg(sal) AS "Eavgsal" ,
min(sal) AS "Eminsal"
FROM emp
group by deptno;
--Step2
SELECT dept.dname,
dept.deptno,
deptstat.ecount,
deptstat.eavgsal,
deptstat.Eminsal
FROM dept ,
(
SELECT deptno,
count(deptno) AS Ecount ,
avg(sal) AS Eavgsal ,
min(sal) AS Eminsal
FROM emp
group by deptno
) deptstat
Where dept.deptno = deptstat.deptno
;
--Step3:
SELECT dept.dname,
dept.deptno,
deptstat.ecount,
deptstat.eavgsal,
deptstat.eminsal,
emp.ename
FROM dept ,
(
SELECT deptno,
count(deptno) AS Ecount ,
avg(sal) AS Eavgsal ,
min(sal) AS Eminsal
FROM emp
GROUP BY deptno
) deptstat ,
emp
WHERE dept.deptno = deptstat.deptno and deptstat.eminsal = emp.sal
;
--Step4:创建视图
CREATE OR REPLACE VIEW View_DeptStat01 AS
SELECT
dept.DEPTNO,
dept.dname,
DeptStat.EmpCount,
DeptStat.AvgSal,
DeptStat.MinSal,
emp.ename
FROM dept ,
(SELECT deptno,
COUNT(empno) EmpCount,
ROUND( AVG(sal) ,2) AvgSal,
MIN(sal) MinSal
FROM emp
GROUP BY deptno)
DeptStat,
emp
WHERE dept.deptno = DeptStat.deptno AND emp.sal = DeptStat.MinSal ;
--Step5:查询视图
select * from View_DeptStat01;
--Step6:删除视图
drop view View_DeptStat01;
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
--Sample6:创建不可更新视图,不可更新视图的创建条件
--可更新视图发生的条件:当视图中的源表或者基表是单个表时.
--Step1:
CREATE OR REPLACE VIEW View_Emp21 AS
SELECT empno,ename,JOB,sal,hiredate ,deptno
FROM emp
WHERE deptno = 20 ;
--Step2:
SELECT * FROM view_emp21;
--Step3:
UPDATE view_emp21 SET deptno=30 WHERE empno=7369 ;
SELECT * FROM emp WHERE empno = 7369;
--Step4:
update emp set deptno = 20 where empno = 7369;
CREATE OR REPLACE VIEW View_Emp21
AS
SELECT empno,ename,JOB,sal,hiredate ,deptno FROM emp
WHERE deptno=20 ---------条件列.
WITH CHECK OPTION;
select * from View_Emp21;
--Step5:
UPDATE view_emp21 SET deptno=30 WHERE empno=7369 ;--失败。提示“视图 WITH CHECK OPTION where 子句违规”
UPDATE VIEW_EMP21 SET deptno=30 WHERE empno=7566 ;--失败。提示“视图 WITH CHECK OPTION where 子句违规”
--
UPDATE view_emp21 SET ename='福特' WHERE empno=7902 ;
UPDATE view_emp21 SET sal=9999 WHERE empno=7902 ;
drop view View_Emp21;
-------------------------------------------------------------------------------
--Sqmple7:*************创建只读视图**************推荐使用.
--Step1:创建视图
CREATE OR REPLACE VIEW View_Emp22
AS
SELECT empno,ename,JOB,sal,hiredate ,DEPTNO FROM emp
WHERE deptno=20
WITH READ ONLY;
--查询视图
SELECT * FROM View_Emp22;
--更新视图
UPDATE View_Emp22 SET ename='约翰' WHERE empno=7902 ;--失败。提示“无法对只读视图执行 DML 操作”
UPDATE View_Emp22 SET ename='失迷司' WHERE empno=7902 ;
UPDATE View_Emp22 SET sal=8888 WHERE empno=7902 ;
-------------------------------------------------------------------------------
--Sqmple8：强制视图
--Step1:创建普通视图
CREATE OR REPLACE VIEW View_Emp23 AS
SELECT empno, ename, JOB, sal
FROM emp2 WHERE deptno = 20; --失败.提示"table or view does not exist"
--Step2：创建强制视图
CREATE OR REPLACE FORCE VIEW View_Emp23 AS
SELECT empno, ename, JOB, sal
FROM emp2 WHERE deptno = 20;
--Step3:查询视图
select * from View_Emp23; --失败。提示"%s has errors"
-------------------------------------------------------------------------------
--Sqmple9：非强制视图
CREATE OR REPLACE NOFORCE VIEW View_Emp24 AS
SELECT empno, ename, JOB, sal
FROM emp2 WHERE deptno = 20;
-------------------------------------------------------------------------------
如果您不能静下心来，不精心雕琢和打造自己的知识系统和技能体系，沉迷于点石成金亦或是拔苗助长等等的奇技淫巧之间，那都是事倍功半的瞎忙活！
古往今来，学习，没有捷径，除非出现科幻般的脑机互联…但有拙法，那就是书山有路勤为径，学海无涯苦作舟！更何况，日新月异知识爆炸的今天，态度犹豫和停止学习，那么等待您的，唯有丛林淘汰法则。
书，能读完吗？肯定不能，汗牛充栋！
书，能学完吗？绝对可以，得读经典。
好好学习，天天向上！继续下一章…
感谢您阅读，如果对作者其它文章也很感兴趣，请扫码关注！
查看专栏详情 立即解锁全部专栏作者：AT阿宝哥
相关文章
Emily
2020-05-26
Saadiya
2020-08-14
Cytheria
2021-05-03
Isleta
2023-07-20
Hadara
2023-07-20
Leona
2023-07-20
Carmen
2023-07-20
Dara
2023-07-20
Bonnie
2023-07-20
Tamara
2023-07-20
Fawn
2023-07-20
Nafisa
2023-07-20
Kohana
2023-07-20
Yelena
2023-07-20
Bena
2023-07-20
Yvonne
2023-07-20
Rayna
2023-07-20
Bertha
2023-07-21
Adonia
2023-07-21