首页 > 代码库 > 视图(学习笔记)
视图(学习笔记)
视图
视图的优点:
- 视图能够简化用户的操作;
- 视图使用户能以多种角度看待同一数据;
- 视图对重构数据库提供了一定程度的逻辑独立性;
- 视图能够对机密数据提供安全保护;
- 适当的利用视图可以更清晰的表达查询;
创建视图语法
CREATE [FORCE | NOFORCE] [OR REPLACE] VIEW 视图名称 [(别名1,别名2,...)]
AS
子查询 ;
授权创建视图的权限
GRANT CREATE VIEW TO tests;
简单视图:
示例一、创建一张基本工资大于2000的视图
CREATE VIEW v_empviewASSELECT * FROM emp WHERE sal>2000;
查询视图是否创建成功
SELECT * FROM tab WHERE tabtype=‘VIEW‘;
查询v_empview视图
SELECT * FROM v_empview;
查询视图的具体信息
SELECT view_name,text_length,text FROM User_Views;
示例二、创建一张只包含20部门员工信息的视图
CREATE OR REPLACE VIEW v_emp20ASSELECT * FROM emp WHERE deptno=20;
--查询是否创建成功SELECT * FROM tab WHERE tabtype=‘VIEW‘;--查询myemponly_dept20视图SELECT * FROM v_myemponly_dept20;
在视图下执行DML操作
示例三、
创建一张只包含20部门员工信息的视图,并增加一条信息
CREATE OR REPLACE VIEW v_emp20ASSELECT empno,ename,job,sal,deptno FROM emp WHERE deptno=20;
--查询v_myview视图SELECT * FROM v_emp20;
--向v_emp20视图中增加一条数据INSERT INTO v_emp20(EMPNO,ENAME,JOB,sal,deptno)VALUES(8888,‘JAVA‘,‘CLERK‘,1000,20);COMMIT;--查询v_myview视图SELECT * FROM v_emp20;--结果增加成功
示例四、对视图执行修改操作
UPDATE v_emp20 SET ename=‘ORACLE‘,job=‘MANAGER‘,sal=2000 WHERE empno=8888;COMMIT;--查询v_myview视图SELECT * FROM v_emp20;
示例五、删除v_emp20视图中的数据
DELETE FROM v_emp20 WHERE empno=8888;COMMIT;--查询v_myview视图SELECT * FROM v_emp20;
复杂视图
包含多个表的映射的视图
示例六、显示每个部门的详细信息
CREATE OR REPLACE VIEW v_myviewASSELECT d.deptno,d.dname,d.loc,COUNT(e.empno) COUNT,NVL(ROUND(AVG(sal),2),0) AVG, NVL(SUM(sal),0) SUM,NVL(MAX(sal),0) MAX ,NVL(MIN(sal),0) MINFROM emp e,dept dWHERE e.deptno(+)=d.deptnoGROUP BY d.deptno,d.dname,d.loc--查询v_myview视图SELECT * FROM v_myview;
示例七、为视图中的查询的列起别名
CREATE OR REPLACE VIEW v_myview(部门编号,部门名称,部门位置,人数,平均工资,总工资,最高工资,最低工资)ASSELECT d.deptno,d.dname,d.loc,COUNT(e.empno) COUNT,NVL(ROUND(AVG(sal),2),0) AVG, NVL(SUM(sal),0) SUM,NVL(MAX(sal),0) MAX ,NVL(MIN(sal),0) MINFROM emp e,dept dWHERE e.deptno(+)=d.deptnoGROUP BY d.deptno,d.dname,d.loc--查询v_myview视图SELECT * FROM v_myview;
对于简单视图,可以直接执行INSERT,UPDATE和DELETE操作
但是对于复杂视图,不允许直接执行INSERT,UPDATE和DELETE操作。当视图符合以下任何一种情况时,都不允许直接执行DML操作。具体情况如下:
1)具有集合操作符(UNION,UNION ALL,INTERSECT,MINUS);
2)具有分组函数(MIN,MAX,SUM,AVG,COUNT等);
3)具有GROUP BY,CONNECT BY或START WITH等子句;
4)具有DISTINCT关键字;
5)具有连接查询;
为了在具有以上情况的复杂视图上执行DML操作需要征用触发器来完成
WITH CHECK OPTION 子句----保证视图的创建条件不被更改
在创建视图的时候有时候需要使用一些WHERE子句做一些条件的限制,
但是默认情况下的视图创建完成之后,是可以通过视图去修改在WHERE子句之中所使用的字段内容的,
而在此时就需要通过WITH CHECK OPTION子句来保证视图的创建条件不被更新。
语法:
WITH CHECK OPTION子句CREATE [FORCE | NOFORCE] [OR REPLACE] VIEW 视图名称 [(别名1,别名2,...)] AS 子查询 [WITH CHECK OPTION [ CONSTRAINT 约束名称] ];
示例八、创建一张只包含20部门人数的视图,添加约束,部门编号不可更改
CREATE OR REPLACE VIEW v_emp20ASSELECT * FROM emp WHERE deptno=20WITH CHECK OPTION CONSTRAINT v_emp20_ck;---constraint后为可选不写也可以--查询v_myview视图SELECT * FROM v_emp20;--如果现在更新数据并将部门编号更改为其它就会报错并提示违返了where条件UPDATE v_emp20 SET deptno=40 WHERE empno=7369;
WITH READ ONLY子句--让视图中所有字段不可更新
语法:
WITH READ ONLY子句CREATE [FORCE | NOFORCE] [OR REPLACE] VIEW 视图名称 [(别名1,别名2,...)] AS 子查询 [WITH CHECK OPTION [ CONSTRAINT 约束名称] ] [WITH READ ONLY] ;
示例八、创建一张只包含20部门人数的视图,添加约束设置为只读
CREATE OR REPLACE VIEW v_emp20ASSELECT * FROM emp WHERE deptno=20WITH READ ONLY; --设置为只读属性--查询v_myview视图SELECT * FROM v_emp20;--如果现在更新数据就会报错并提示违返了where条件UPDATE v_emp20 SET ename=‘TTTT‘,comm=NULL WHERE empno=7369;
删除视图
DROP VIEW 视图名
示例九、删除v_myview视图
DROP VIEW v_myview;
--查询user_views数据字典SELECT view_name,text_length,text,read_only FROM user_views;
视图(学习笔记)