Oracle基础(十五):视图、数据字典、复杂视图

一、视图

视图 VIEW,是数据库对象之一。

视图也被成为虚表,即虚拟的表,是一组数据逻辑表示。

视图对应于一条SELECT语句,结果集被赋予一个名字,即视图的名字。

视图本身并不包含任何数据,它只包含映射到基表的一个查询语句,当基表数据发生变化,视图数据也随之变化。

使用视图的目的是简化SQL语句的复杂度,重用子查询,限制数据访问。

在SQL语句中体现的角色与表一致,但视图只是对应一个查询语句的结果集。

 

视图创建后,可以像操作表一样操作视图,主要是查询。
SubQuery是SELECT查询语句,对应的表被称为基表。
根据视图所对应的子查询种类分为几种类型:

1.简单视图:SELECT语句是基于单表建立的,且不包含任何函数运算、表达式或分组函数,叫做"简单视图",此时视图是基表的子集。
(只有简单视图可以实现DML操作)
2.复杂视图:SELECT语句同样是基于单表,但包含了单行函数、表达式、分组函数或者GROUP BY子句,叫做"复杂视图"。
3.连接视图:SELECT语句是基于多个表的,叫做"连接视图"。

(1)创建视图

语法:CREATE VIEW 视图名 AS SELECT xxx ....;

①创建简单视图

--简单视图,创建一个简单视图,该视图包含的数据为10号部门员工的编号、名字、薪水、部门号
create view v_emp_10 as
select empno,ename,sal,deptno from emp where deptno=10;
DESC v_emp_10;--查看视图的结构
select * from v_emp_10;--查看视图数据(重用子查询),相当于以下的子查询
--SUBQUERY
SELECT * FROM(SELECT empno,ename,sal,deptno
FROM emp
WHERE deptno=10);

②创建视图时,给列赋予别名。

可以用OR REPLACE短语修改视图对应的SQL查询语句

视图对应的子查询中的字段若含有函数或者表达式,那么该字段必须指定别。
当视图对应的子查询中的字段使用了别名,那么视图中该字段就用别名来命名。

 

修改视图
由于视图仅对应一个SELECT语句,所以修改视图就是替换该SELECT语句而已.
(若存在视图则修改,不存在则创建)
语法:CREATE OR REPALCE VIEW 视图名 AS SELECT xxx....;

create or replace view v_emp_10 as
select empno id,ename name,sal salary,deptno from emp where deptno=10;
select * from v_emp_10;
desc v_emp_10;

视图分为简单视图与复杂视图
简单视图:对应的子查询中不含有关联查询,查询的字段不包含函数,
表达式等,没有分组,没有去重.反之则是复杂视图.

 

③对简单视图进行DML操作

对视图进行DML操作就是对视图数据来源的基础表进行的操作(但不能违反基础表的约束条件)。
只能对简单视图进行DML操作,复杂视图不可以。

--对简单视图v_emp_10执行INSERT语句,插入数据操作
insert into v_emp_10(id,name,salary,deptno)
values(1001,'jack',2000,10);
select * from v_emp_10;
select * from emp;
--操作v_emp_10视图,把jack的工资改为3000
update v_emp_10 set salary=3000 where name='jack';
--操作v_emp_10视图,删除name是jack的记录
delete v_emp_10 where name='jack';
--根据上面对视图进行DML操作就是对基表操作,进行DML操作不当可能会污染基础表数据。
--操作v_emp_10视图,该INSERT语句插入了20号部门数据,所以v_emp_10视图对ROSE不可见。
insert into v_emp_10(id,name,salary,deptno) values(1003,'ROSE',3000,20);
--UPDATE同样存在更新后对数据不可控的情况
UPDATE v_emp_10 SET deptno=20;
rollback;
select * from v_emp_10;
select * from emp;
--从视图中删除20号部门的记录
--删除不会对基表产生数据污染,因为该视图看不到20号部门的数据。
delete from v_emp_10 where deptno=20;--脚本输出:0 行已删除。

④创建具有 CHECK OPTION约束的视图

语法:

CREATE [OR REPLACE] VIEW 视图名 AS subquery [WITH CHECK OPTION];

WITH CHECK OPTION 表示通过视图所作的修改,必须在视图可见的范围内。
假设进行INSERT操作,新增的记录在视图仍可查看(但可能会违反基表的非空约束)。
假设进行UPDATE操作,修改后的结果必须能通过视图查看到。 

可以为视图添加检查选项,来保证对视图进行DML操作时不会对基表数据污染。
当视图添加了检查选项后,视图要求对视图中数据进行DML操作后,视图必须对该记录可见,否则不允许操作。

 

create or replace view v_emp_10 as
select empno id,ename name,sal salary,deptno
from emp where deptno=10
with check option;
--插入20号部门数据,ORA-01402: 视图 WITH CHECK OPTION where 子句违规
--该记录对视图不可见
insert into v_emp_10(id,name,salary,deptno) values(1003,'ROSE',3000,20);
--插入10号部门数据,对视图可见
INSERT INTO v_emp_10(id,name,salary,deptno)
VALUES
(1001,'JACK',2000,10);
--更新为20部门,错误报告 -ORA-01402: 视图 WITH CHECK OPTION where 子句违规,
--因为该记录对视图不可见
UPDATE v_emp_10 SET deptno=20;--出错,该记录对视图不可见
select * from v_emp_10;
select * from emp;
rollback;

⑤创建具有READ ONLY约束的视图

对简单视图进行DML操作时合法的,但这是不安全的。
如果没有在视图上执行DML操作的必要,那么在建立视图时声明为"只读"来避免这种情况,
保证视图对应的基表数据不会被非法修改 。

语法:CREATE [OR REPLACE ] VIEW 视图名 AS subquery [WITH READ ONLY];
create or replace view v_emp_10 as
select empno id,ename name,sal salary,deptno
from emp where deptno=10 with read only;
--为视图添加只读选项,当一个视图添加了只读选项后,该视图不能进行DML操作。
INSERT INTO v_emp_10(id,name,salary,deptno)
VALUES(1001,'JACK',2000,10);--SQL 错误: ORA-42399: 无法对只读视图执行 DML 操作

二、数据字典

查看数据字典(特殊的表),有助于了解曾经创建过的数据库对象。

和视图相关的数据字典:
USER_OBJECTS            数据库对象表 
USER_VIEWS             通过查询user_views获取相关信息
USER_UPDATE_COLUMNS
USER_TABLES

 

--在数据字典USER_OBJECTS中查询所有视图名称
desc USER_OBJECTS;
select object_name from user_objects where object_type='VIEW';
select object_name from user_objects where object_type='TABLE';
desc user_views;
select view_name,text from user_views;--text字段对应的是视图的子查询
desc user_tables;
select table_name from user_tables;

三、复杂视图

复杂视图不能进行DML操作。

SELECT语句同样是基于单表,但包含了单行函数、表达式、分组函数或GROUP BY子句,叫做复杂视图。

--创建一个公司部门工资信息的视图,内容为:部门号,部门名,部门最高、最低、平均以及工资总和信息。
create or replace view v_dept_sal
as select d.deptno,d.dname,
max(e.sal) max_sal,
min(e.sal) min_sal,
avg(e.sal) avg_sal,
sum(e.sal) sum_sal
from emp e join dept d on e.deptno=d.deptno group by d.deptno,d.dname;
desc v_dept_sal;
select * from v_dept_sal;
--通过视图v_dept_sal查看谁的工资高于自己所在部门平均工资?
select e.ename,e.sal,e.deptno,v.deptno,v.avg_sal
from emp e join v_dept_sal v
on e.deptno=v.deptno where e.sal>v.avg_sal;
--删除视图,当不再需要视图定义,可以使用DROP VIEW viewName语句删除视图。
--删除视图本身不会影响基表数据,但是删除视图数据会对应将基表数据删除!
drop view v_dept_sal;
posted @   禾喵  阅读(867)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· DeepSeek “源神”启动!「GitHub 热点速览」
· 我与微信审核的“相爱相杀”看个人小程序副业
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
· 上周热点回顾(2.17-2.23)
点击右上角即可分享
微信分享提示