转自http://askdb.blog.163.com/blog/static/215683163201322711754198/
Hi,everybody这里来介绍另一类可以显示数据逻辑子集或组合的SCHEMA对象,视图。
1.1主要内容
1>描述什么是视图
2>创建,修改,删除视图
3> 通过视图提取数据
4> 插入,更新,删除(可更新)视图的数据
5> 创建和使用INLINE视图
6> 通过视图实现分页功能
1.2什么是视图
视图可以按每个用户不同的视角去纵向或者横向查询和显示数据子集
对于不同行业的业务表,有不同类型的数据,也有不同类型的限制
所以我们会让业务表数据更加规范,ORACLE提供了约束来实现该功能
通过创建表的视图可以显示数据的逻辑子集或组合
视图是基于表或另一个视图的逻辑表,视图没有自己的数据,它自己没有存储的段
但通过它的定义可以查看或者更改表中的数据,视图所基于的表称为基表
视图以SELECT语句的形式存储在数据字典中
我们看上图颜色部分是通过特定的视角查看到的3行6列的记录
所以视图既可以定义横向数据,又可定义纵向数据
1.3什么是视图
任何一个查询,都可以建立一个视图,或者表
为什么要使用视图? 它有如下四个优点
1>可以限制数据的访问
可以将一个大的数据集通过在视图中定义列和WHERE条件分成多个视图,并让只有有权限的人才能查看到自己的数据。
ORACLE能实现应用层的安全
你可以通过USERENV来搜索登陆的用户,如SYS_CONTEXT('userenv', 'session_user')
我们可以控制表访问权限,然后设置该视图查询权限发放给所有分支机构,这样机构用户就只能查到登陆用户自己业务的数据,别的机构用户数据你是查不到的。
2>视图可以用来简化查询,更容易建立复杂的语句
使得复杂的语句逻辑上变的更简单易懂
注意:
但其实使用视图,在某些情况下,不便于系统优化 ,因为如果视图是一个大的结果集,它先计算出来,再去过滤视图的结果势必效率非常低。
我们应该把视图强制拆分成普通子查询或者使用提示语句来实现
这属于优化的知识,先了解到这个程度
3>视图能提供相对独立的数据
4> 同样的数据可以设置不同的视图
1.4视图的分类
视图分简单视图和复杂视图
1> 简单视图
1) 简单视图只有单表
2) 没有包含分组操作和函数
3) 可以在视图上执行更新删除插入操作,这叫可更新视图
2> 复杂视图
1) 包含一个或多个表
2) 可以包含函数和分组数据
3) 有的时候不允许在视图上做DML语句,这种视图称为不可更新视图
1.5创建视图
上图是创建视图的语句
1>我们可以将子查询嵌入到创建视图的语句中
主要的关键字有
1) OR REPLACE :如果视图存在则替换它
2) FORCE:不用关心视图的基表是否存在,默认是NOFORCE
3) SUBQUERY:是一个完整的SELECT语句,定义了视图的行和列
4) WITH CHECK OPTION:用于控制DML视图时不满足视图的数据报错,只能插入或更新那些能访问的行。
WITH READ ONLY:设置只读视图
2> 子查询可以包含复杂的SELECT语句
上图是创建一个EMPVU80的视图,它包含了部门为80的信息,我们可以用DESC 视图名查看视图的结构
也可以VIEW 视图名,查看视图的定义,子查询中不能包含ORDER BY.
3> WITCH CHECK OPTION的CHECK条件上没有指派约束名,那么系统会自动生成一个SYS_Cn的约束名
4> 在子查询中使用列别名来创建视图
如果create table as select 的话,有别名的列,创建出来的表的列是别名这个也是一样的
5> 通过给定的别名从此视图中选择列
可以直接查询视图,就象查表一样,但盖住了内部执行的复杂性
表面上是这个的
但实际后面有一个复杂的过程
1.6实验,视图类型定义
create table t( x varchar2(5), y number(6) ); insert into t values(1,1); |
Desc t |
那我要把它设置成NUMBER(7)以视图的形式展现,注意,这里只是展现
create or replace view vw as select x, sum(y) y_normal, cast( sum(y) as number(7)) y_cast from t group by x; desc vw |
有的时候,我们需要创建临时表,然后在临时表的记录上创建表
比如create table data as (...)
然后create table t as select * from data t1,data t2 where ...
ORACLE提供了一种简单的写发,一个SQL语句就可以完成
那就是WITH语句
with data /* factored subquery */ as ( select rn, (select x from t where x > 0) /* scalar subquery */ from (select rownum rn from tab where rownum<=3 ) /* inline view */ ) select * from data where rn in (select x from t ) /* subquery */ and exists (select null from t where t.x = data.rn ) /* correlated subquery */ / |
红框就是临时表data
with <临时表名>
生成临时表data后,再由临时表来查询数据,这个红框是从前面生成的临时表data中获取数据
这里临时表data只是一个视图,不是一个真实的表,我们看这里涉及多种子查询
with data as 后面的属于因子子查询
SELECT 列表中的子查询是规则子查询,它是一个完全独立的子查询
from后面的是inline视图,inline视图,from后面我们可以理解成视图
我们看EXISTS里的子查询是关联子查询,因为t.x关联了临时表data.rn
1.7修改视图
1.7.1通过OR REPLACE来修改原来视图语句
1> 视图的字段顺序同子查询中的顺序一致
1.8创建复杂的视图
意思是说用到了这些技术, 分组函数,伪列等,当然还有多表,我们可以在子查询中关联多个表
并进行分组合计运算,生成一个复杂的视图
1.9 DML在视图上的删除准则
要想DELETE视图行,需要满足下面的准则:
也就是说从视图里对基表的操作
1> 在简单的视图上能直接执行所有DML语句
2> 子查询中包含下面语句的将不能进行视图的DML行操作
1) 分组函数
2) GROUP BY 语句
3) DISTINCT
4) 伪列 如:ROWNUM
1.10DML在视图上的更新准则
子查询包含下面语句将不允许更新
1> 分组函数
2> GROUP BY
3> DISTINCT
4> 伪列 如ROWNUM
5> 表达式列
这里多了个表达式
1.11 DML在视图上的插入准则
子查询包含下面语句的将不允许插入视图
1> 分组函数
2> GROUP BY
3> DISTINCT
4> 伪列 如ROWNUM
5> 表达式列
6> 视图中没有列出NOT NULL的字段
1.12 With Check option
V view with check option 这是约束的类型
对于违反视图选择范围的数据行被插入,将受WITH CHECK OPTION检查
阻止DML操作,可以用WITH READ ONLY设置视图成为只读视图,任何在视图执行DML的语句将报错误。
只读视图
上图设置一个只读视图
对WITH READ ONLY的视图执行DELETE将报1752错误
1.13删除视图
我们可以用DROP VIEW "视图名"来删除视图.
drop view view_name;
语法是这样的,和删表一样
当然, 视图是逻辑结构,而且视图里的数据存放在基表中
而视图本身不占存储空间,只占数据字典的空间 (物化视图除外)
我们还有一种临时视图,在语句级的
物化视图,理解为表化了的视图,介于表和视图两者之间的,物化视图,就是用空间换时间
以前说过,视图掩盖了查询的复杂性 ,而物化视图,可以将查询变成一张表存在,这样可以提高性能
1.14IN-LINE视图
1> 一个INLINE视图没有专门用CREATE VIEW语句创建的 而是定义在SQL语句里
比如FROM后面由SELECT定义的子查询就是IN-LINE视图
2> INLINE不是一个SCHEMA对象,红色框是一个INLINE视图,它被一个别名b命名
1.15实验,视图,同义词
有这么一个需求,一个公司有很多人员,人员信息都在一张表里
当然人员信息里包括了人员所有的部门号,公司有多个部门,如销售部门 会计部门等
为了安全,我们规定,数据库里的每个部门用户只能查到自己部门里的人员信息
而视图可以实现这个效果
到scott用户下, 里面有一张表emp, dept
一个是部门表,一个是员工表
为简化实验,我们只为sales和accounting两部门建立用户
conn / as sysdba Create user sales identified by sales; Grant connect,resource to sales; Create user accounting identified by accounting; Grant connect,resource to accounting; |
创建两用户,分别授予权限,只查询是sales部门的员工,应该怎样查询?
Conn scott/tiger select * from emp where deptno=(select deptno from dept where dname='SALES'); |
我们用到SYS_CONTEXT('userenv', 'session_user')
例如查看当前用户和show user类似效果
select SYS_CONTEXT('userenv', 'session_user') from dual; |
USERENV(OPTION)返回当前的会话信息.
返回系统会话信息sys_context
这两个函数用来记录连接的session信息,经常用于触发器中,记录客户端的连接信息
我们比较关注的是sessionid和 ip
select utl_inaddr.get_host_address, utl_inaddr.get_host_name from dual; |
select utl_inaddr.get_host_address ('localhost') from dual; |
当然这里localhost可以使比如新浪,百度等地址
Conn scott/tiger CREATE OR REPLACE VIEW v_EMP AS select * from emp where deptno = (SELECT Deptno FROM DEPT WHERE Dname = SYS_CONTEXT('userenv', 'session_user')); |
grant select on v_emp to sales; grant select on v_emp to accounting; |
进入sales 用户
Conn sales/sales select * from scott.v_emp; |
在accounting用户下也执行一下
Conn accounting/accounting select * from scott.v_emp; |
再回到scott里创建一个同义词
Conn scott/tiger CREATE PUBLIC SYNONYM emp FOR scott.v_emp; |
把scott.v_emp创建一个同义词emp
CONNECT accounting/accounting SELECT * FROM EMP; |
CONNECT SALES/sales SELECT * FROM EMP; |
select * from user_views; |
select OWNER,VIEW_NAME from all_views where OWNER='SCOTT'; |
把刚才的环境清理一下
Conn Scott/tiger Drop public SYNONYM emp; Drop view v_emp; Conn / as sysdba Drop user sales cascade; Drop user accounting cascade; |
1.16强制视图
创建视图了后,可以把表删除,并且视图还在 ,但是 再把表重建后,视图报错
这种场景就可以使用强制视图
Conn hr/hr create view force_view as select * from test; |
出错了吧
create force view force_view as select * from test; |
建一个强制视图
desc force_view |
创建视图的基表test还不存在,所以编译错误
select view_name from all_views where view_name = 'FORCE_VIEW'; |
create table test (i int); |
我们把基表建了,再看
desc force_view |
是不是可以了,这个功能有时候很好用
有时候,表是需要后导入的,而创建视图的结构要先搭建,那就需要用强制命令
1.17更新视图原理
Conn hr/hr create table t1 ( a int, b int, c int, d int ); create table t2 ( d int primary key, e int, f int, g int ); |
我们看只有t2有个主键d ,t1上没有任何约束
我是否能将t2的e,f,g更新到a,b,c? 我们说关联的可更新视图,最关键是在哪?
注意:
T2:T1为1:N,才可以实现T2更新T1,用两个数字去更新一个数字,这种是不可以的
相反,用一个数字,去更新多个数字,这个是正常状况了
insert into t1 select null, null, null, rownum from all_users where rownum <= 5; insert into t2 select rownum, rownum+1, rownum+2, rownum+3 from all_users where rownum <= 5; commit; |
select * from t1; select * from t2; |
update ( select a,b,c,e,f,g from t1, t2 where t1.d = t2.d ) set a = e, b = f, c = g; |
t2.d必须是主键或唯一性约束,因为只有1:1或者N:1的时候可以更新,不能1:N去更新
select * from t1; |
set e = a, f = b, g = c; 表示用a更新e,用b去更新f,用c去更新g
t1去更新t2 ,是不是必须保证t1是1 ,但是t1.d没有任何约束
所以不能执行成功 。
谁要主动去更新,就必须保证唯一,这是个原则性的问题
那我们怎么样来判断这个视图是可更新呢?
ORACLE提供了一个视图 user_updatable_columns
1.18 user_updatable_columns
CREATE TABLE m ( i INT ); CREATE TABLE d ( j INT ); |
CREATE VIEW md_upd AS SELECT i,j FROM m,d WHERE i=j; |
创建两个表,并创建一个关联查询的视图md_upd
md_upd是可更新视图吗?
根据刚才理解的,两个都不是主键
也都没有任何唯一性约束、我们查看下数据字典
SELECT column_name, updatable FROM user_updatable_columns WHERE table_name = 'MD_UPD'; |
这个字段updatable就是表示是否可更新的意思
不可更新造成的原因
结果不一定是唯一的,就会造成不可更新,
如没有主键或者唯一约束
我们加个主键可以将视图变成可更新
ALTER TABLE m ADD CONSTRAINT pk_m PRIMARY KEY ( i ); |
SELECT column_name, updatable FROM user_updatable_columns WHERE table_name = 'MD_UPD'; |
还是NO, 那我们说的不对吗?明显不是,因为视图是代码需要重编译
ALTER VIEW md_upd COMPILE; SELECT column_name, updatable FROM user_updatable_columns WHERE table_name = 'MD_UPD'; |
一个yes,一个no了
相关推荐
Oracle数据库系统是世界...而Oracle提供的不同类型的视图,如关系视图、内嵌视图、对象视图和物化视图,为不同的应用场景提供了丰富的选择。在实践中,根据实际情况灵活运用这些视图,将极大地提升你的数据库管理能力。
在实际应用中,开发者通常需要组合使用这些视图,构建出符合需求的用户界面。 在实际项目中,我们还会遇到一些进阶的视图操作,例如:布局管理(LinearLayout、RelativeLayout、ConstraintLayout等)、触摸事件处理...
3、资源分为两类:真实应用场景的存储过程案例61个(真实业务案例,供学习参考)、通用存储过程案例57个(涉及序列、表及列操作、主键唯一索引约束、事务、内存、权限、导出文件、视图、迭代、备份、参数校验等,可...
4. **动态视图的应用场景**:通过实例展示动态视图在数据库管理、性能监控、权限控制等方面的应用,如DBA常用的V$视图系列。 5. **维护和管理动态视图**:讲解如何修改、重命名、删除动态视图,以及处理视图依赖...
### Webdynpro 视图跳转与传值详解 #### 一、引言 随着企业应用的日益复杂,用户界面的设计也需要更为灵活多变。...实际开发过程中应根据应用场景选择合适的传值策略,以实现高效稳定的应用程序开发。
【MySQL8.0 视图详解】 视图是MySQL数据库中的一个重要概念,它是一个虚拟的表,基于一个...通过学习和熟练掌握视图的创建、查看、修改和删除,能够提升数据库管理效率,增强数据安全性,并为复杂的业务场景提供支持。
dorado入门指南经典教程 Dorado是一个基于Java的企业级应用开发平台,旨在...Dorado入门指南经典教程为读者提供了一个系统的Dorado入门指南,涵盖了Dorado的基本概念、应用场景、开发工具和版本更新等方面的知识点。
在实际应用中,视图可以用于报表生成、数据分析、权限管理等多个场景,例如,你可以创建一个显示特定员工工资信息的视图,以便于人力资源部门查看,而无需暴露整个工资表的所有细节。 总之,视图是 MySQL 数据库中...
本书《MySQL数据库应用从入门到精通_第2版》旨在帮助读者从零基础开始,逐步掌握MySQL的使用技巧和高级功能,从而实现数据库管理与开发的精通。 一、MySQL基础知识 在入门阶段,首先会介绍MySQL的安装与配置,包括...
在"QT 图形视图框架入门简单动画示例"中,我们将探讨如何使用`QGraphicsScene`和`QGraphicsObject`来创建一个阀门开闭以及风机转动的动画效果。 首先,`QGraphicsScene`是图形视图框架的核心,它是一个二维画布,...
### iOS应用教程入门知识点 #### 一、介绍与设置 - **目的与适用范围**: - 本教程旨在引导初学者快速上手iOS应用程序开发,适用于希望在Mac上为iPad、iPhone和iPod touch创建应用程序的新手开发者。 - 通过本...
### Toad 使用快速入门知识点详解 #### 一、Toad 功能综述 Toad (Tools of Oracle Application Developers) 是一款专为Oracle开发者设计的强大工具,由Quest Software开发。其核心优势在于简单易用和访问速度,...
标题和描述中提到的“使用unity3d进行游戏开发_从入门到精通_02.pdf”暗示这是一本关于Unity3D游戏开发的教程书籍,其中“第二章 初识cube”指的是本章将介绍Unity3D中基本的立方体(cube)对象的创建与操作。...
- **应用场景**:调整绘制的图形以符合特定标准或要求时使用。 **MA,** *MATCHPROP(属性匹配)* - **用途**:将一个对象的属性复制到另一个或多个对象上。 - **应用场景**:快速统一图纸上的对象属性,比如使所有...
Unity的工作区包括Scene视图(场景视图)、Game视图(游戏视图)、Project视图(项目视图)和Hierarchy视图(层级视图)等。在Scene视图中,你可以构建游戏环境;在Game视图中,你可以预览游戏的实际效果;Project...
1. Unity软件的界面布局:介绍Unity编辑器的工作区域,包括场景视图、游戏视图、层级视图、项目视图和检视视图等部分,以及它们各自的作用和使用方法。 2. Unity项目的基本结构:解释如何创建和管理Unity项目,包括...
- **存储过程和触发器**:介绍如何使用存储过程来封装复杂的SQL逻辑,以及触发器的使用场景和编写方法。 - **事务处理**:讨论事务的概念、ACID特性以及如何在MySQL中实现事务控制。 - **视图和函数**:讲述视图的...
《MySQL数据库应用-从入门到精通》这本书旨在帮助读者系统地学习并掌握MySQL的相关知识,从基础概念到高级应用,涵盖全面,旨在提升读者的数据库管理技能。配套PPT文档提供了直观的视觉辅助,使学习过程更为高效。 ...