SQL之Oracle视图学习总结

目录

• 概念
• 视图类别
• • 1、简单视图
  • 2、复杂视图
  • 3、连接视图
  • 4、只读视图
  • 5、check约束视图
• 创建视图
• • 1、基本语法
  • 2、关键字说明
  • 3、一些规则
• 示例
• • 1、基本语法示例
  • 2、关键字示例
  • • （1）force
    • （2）with check option
    • （3）with read only

概念

视图是基于一张表或多张表或者另外一个视图的逻辑表。视图不同于表，视图本身不包含任何数据。表是实际独立存在的实体，是用于存储数据的基本结构。而视图只是一种定义，对应一个查询语句。视图的数据都来自于某些表，这些表被称为基表。数据库中只在数据字典中存储对视图的定义。视图是不包含任何数据的，只是对基表数据的投影。

优点：

• 为用户集中数据，简化用户的数据查询和处理。
• 屏蔽数据库的复杂性，用户不必了解数据库的复杂性。
• 简化用户权限的管理，只授予用户使用视图的权限。
• 可以提高数据访问的安全性，通过视图往往只可以访问数据库中表指定部分，限制了用户访问表的全部行和列。
• 便于数据共享，多个用户不必都定义所需要的数据。

--查询所有视图view
select * from dba_views t;

--删除视图
DROP VIEW 视图名;
           

视图类别

1、简单视图

指基于单个表并且不包含函数或者表达式的视图，在该视图上可以执行DML语句。

2、复杂视图

指基于单个或者多个表或者包含函数、表达式、或者分组数据的视图，在该视图上执行DML语句时必须要符合特定条件。在定义复杂视图时必须为函数或表达式定义别名。

3、连接视图

指基于多个表建立的视图，一般来说不会在该视图上执行INSERT、UPDATE、DELETE操作。

4、只读视图

指只允许进行SELECT操作的视图，在该视图时指定WITH READ ONLY选项。该视图上不能执行INSERT、UPDATE、DELETE操作。

5、check约束视图

WITH CHECK OPTION用于在视图上定义CHECK约束,即在该视图上执行INSERT或UPDATE操作时,数据必须符合查询结果.

创建视图

1、基本语法

CREATE [OR REPLACE] [FORCE] VIEW 视图名
AS(SELECT 查询语句)
[WITH CHECK OPTION]--check约束
[WITH READ ONLY];--只读
           

2、关键字说明

or replace：如果视图名存在，就替换原视图
force：强制执行，即使报错也执行。通常用来处理执行顺序导致的问题。
with check option：插入或修改数据行必须满足视图定义的约束。
with read only：只读视图，不能执行任何dml操作
           

3、一些规则

视图中出现以下情况，不可进行dml操作。

• 运算符（union、insersect、minus）
• DISTINCT关键字
• GROUP BY、ORDER BY、CONNECT BY、START WITH
• 子查询

• 需要更新的列不是由列表达式定义

  -基表中所有NOT NULL列均属于该视图

  个人理解：

• 判断的依据：向视图中插入数据情况，可以转化为向基表中插入数据的情况，然后再考虑 dml 操作的可行性。
• 一般情况下，我们只会对视图进行查询，不做 dml 操作，所以此处了解即可。
• 大致有下列情况（也许你见过别人说的其它情况，但一切要以自己实际动手的案例为主！）

不符合的情况	解释
多个基表连接	规定（扩展：可通过 instead of 触发器进行 dml 操作）
存在虚拟列	基表中没有，通过表达式，rowid、rownum 等计算得到的
存在 not null	列不在视图中 很好理解，若不是，则违反了基表的 非空约束

示例

1、基本语法示例

--对简单视图的操作
drop table t_temp;
create table t_temp;
--简单视图
create op replace view view_temp
as select * from t_temp;

--select
select * from view_temp;

--update
UPDATE VIEW_TEMP SET VIEW_TEMP.NAME = '程大帅' WHERE VIEW_TEMP.ID='1';

--insert
INSERT INTO VIEW_TEMP VALUES('2','程二帅');

--delete
DELETE FROM VIEW_TEMP WHERE VIEW_TEMP.ID='2';
           

--两表相连，不包含分组函数、group by 、distinct等命令
CREATE OR REPLACE VIEW VIEW_AB
AS
SELECT A.SEX,A.NAME,B.ADDRESS,B.AGE FROM
USERS A , INFO B WHERE A.ID=B.ID
WITH CHECK OPTION; 

--select
SELECT * FROM VIEW_AB;

--update
UPDATE VIEW_AB SET VIEW_AB.NAME='成大帅气' WHERE VIEW_AB.AGE = '18';

--delete
DELETE FROM VIEW_AB WHERE VIEW_AB.AGE='18';

--insert
INSERT INTO VIEW_AB(VIEW_AB.SEX,VIEW_AB.NAME,VIEW_AB.ADDRESS,VIEW_AB.AGE) VALUES('女','迪丽热巴','上海','18');
           

-- 复杂视图、不能DML
CREATE OR REPLACE VIEW V_TEMPC
AS 
SELECT MAX(AGE) AGE FROM USES
WITH READ ONLY;

--SELECT
SELECT * FROM V_TEMPC;

--删除视图
DROP VIEW V_TEMPC;
           

2、关键字示例

（1）force

force强制创建视图

比如视图中用到了自定义的函数，但是函数还没有创建，此时创建视图时会报错。可以加上force来强制创建

--没加force
CREATE OR REPLACE VIEW V_TEMP
AS 
SELECT	NAME,
		SEX,
		FUN_TEST(ID) ID 
	FROM USERS;
           

执行报错：ORA-00904："FUN_TEST"：标识符无效。

将语句加上force后，再次执行

CREATE OR REPLACE FORCE VIEW V_TEMP
AS 
SELECT	NAME,
		SEX,
		FUN_TEST(ID) ID 
	FROM USERS;
           

执行成功！注意：此时view无效，当函数fun_test执行后，才是有效的

（2）with check option

视图进行dml操作时，必须符合where的限制条件。

• insert、update：要保证insert后，数据可以被视图查询出来，即符合where条件。
• delete：无限制
• 对于没有where条件的视图，使用with check option是多余的

CREATE OR REPLACE VIEW V_TEMP
AS SELECT NAME FROM USERS WHERE ID <3
WITH CHECK OPTION;

--测试插入一条id为4的数据
INSERT INTO V_TEMP(ID,NAME) VALUES(4,'马儿扎哈');
           

执行插入语句报错：视图 WITH CHECK OPTION where子语句违规

（3）with read only

只读，禁止dml

CREATE OR REPLACE VIEW V_TEMP
AS SELECT ID,NAME FROM USERS WHERE ID <3
WITH READ ONLY;
           

--测试插入语句
INSERT INTO V_TEMP(ID,NAME) VALUES(4,'马儿扎哈');
           

执行报错：无法对只读视图执行DML操作