VIEW(视图的定义/作用、创建/修改VIEW(FORCE强制创建视图、视图的DML操作、不可更新的视图、WITH CHECK OPTION、WITH READ ONLY)、删除 VIEW)

---

常见的数据库对象(DDL是对数据库对象进行操作的)

数据库对象	描述
TABLE	表是存储数据的逻辑单元，以行和列的形式存在，列就是字段，行就是记录
CONSTRAINT	执行数据校验的规则，用于保证数据完整性的规则
SEQUENCE
SYNONYM
VIEW	一个或者多个数据表里的数据的逻辑显示，视图并不存储数据
MATERIALIZED VIEW
INDEX	用于提高查询性能，相当于书的目录
PROCEDURE	用于完成一次完整的业务处理，没有返回值，但可通过传出参数将多个值传给调用环境
FUNCTION	用于完成一次特定的计算，具有一个返回值
TRIGGER	相当于一个事件监听器，当数据库发生特定事件后，触发器被触发，完成相应的处理
TYPE
PACKAGE
PACKAGE BODY

1.查看用户定义的表：SELECT * FROM user_tables;
2.查看用户定义的各种数据库对象：SELECT DISTINCT object_type FROM user_objects;
3.查看用户定义的表，视图，同义词和序列：SELECT * FROM user_catalog;

VIEW

视图的定义

• 视图是一种虚拟表，本身是不存储数据的。
• 视图本身的定义语句是要存储在数据字典里的。视图只有逻辑定义。每次使用的时候，只是重新执行SQL。
• 视图建立在已有表的基础上，视图赖以建立的这些表称为基表。视图的创建和删除只影响视图本身，不影响对应的基表。但是当对视图中的数据进行增加、删除和修改操作时，数据表中的数据会相应地发生变化。
• 视图，是向用户提供基表数据的另一种表现形式。通常情况下，小型项目的数据库可以不使用视图，但是在大型项目中，以及数据表比较复杂的情况下，视图的价值就凸显出来了，它可以帮助我们把经常查询的结果集放到虚拟表中，提升使用效率。

视图的作用

• 隐藏数据的逻辑复杂性并简化查询语句，多表查询语句一般是比较复杂的, 而且用户需要了解表之间的关系，否则容易写错; 如果基于这样的查询语句创建一个视图，用户就可以直接对这个视图进行"简单查询"而获得结果。这样就隐藏了数据的复杂性并简化了查询语句。这也是ORACLE提供各种"数据字典视图"的原因之一，all_constraints就是一个含有2个子查询并连接了9个表的视图(在catalog.sql中定义)
• 执行某些必须使用视图的查询。某些查询必须借助视图的帮助才能完成。比如, 有些查询需要连接一个分组统计后的表和另一表，这时就可以先基于分组统计的结果创建一个视图， 然后在查询中连接这个视图和另一个表就可以了
• 提供某些安全性保证。视图提供了一种可以控制的方式，即可以让不同的用户看见不同的列，而不允许访问那些敏感的列，这样就可以保证敏感数据不被用户看见
• 简化用户权限的管理。可以将视图的权限授予用户，而不必将基表中某些列的权限授予用户，这样就简化了用户权限的定义。

创建/修改 VIEW

权限：要在当前方案中创建视图，用户必须具有create view系统权限；要在其他方案中创建视图，用户必须具有create any view系统权限。视图的功能取决于视图拥有者的权限。

语法:

CREATE [OR REPLACE] [FORCE] VIEW [SCHEMA.]<view_name>
(col1,col2, ..., coln) -- 可省略
AS
  SELECT 语句
[WITH CHECK OPTION]
[WITH READ ONLY];

1、OR REPLACE：如果存在同名的视图, 则替代
2、FORCE："强制"创建视图，不考虑基表是否存在，也不考虑是否具有使用基表的权限
3、WITH CHECK OPTION：插入或修改的数据行必须满足视图定义的约束
4、WITH READ ONLY：创建的视图只能用于查询数据, 而不能用于更改数据。

FORCE 强制创建视图

实际应用场景：DB 部署时，顺序是：DDL 语句 -> DML 语句 -> PKG

若 DDL 语句中的 view 调用了 pkg，则部署时会产生报错(因为此时 pkg 还未部署)，但此报错在 pkg 部署后会消失，此时便用到了 force。

创建视图语句:

CREATE OR REPLACE VIEW vw_stu_info AS 
	SELECT si.id, si.name, si.sex, f_get_class_no(si.id) class_no   -- 函数定义在pkg中，还未被执行(简单演示，就没写到pkg里面，但效果是一样的)
 	FROM stu_info si;

执行结果:

然而加上FORCE后，执行成功。但是会报：success with compilation error

CREATE OR REPLACE FORCE VIEW vw_stu_info AS (
	SELECT si.id, si.name, si.sex, f_get_class_no(si.id) class_no -- 假如有这个方法
	FROM stu_info si
);

虽然成功，但是此时的view还是无效的哦，只有当f_get_class_no函数执行后，才是有效的

SELECT t.status, t.* FROM all_objects t WHERE t.OBJECT_NAME = UPPER('vw_stu_info'); --INVALID: 无效

按顺序部署 pkg 中的方法：f_get_class_no（实际开发中，函数是放在 pkg 里面的，此处仅作为测试使用）

CREATE OR REPLACE FUNCTION f_get_class_no(v_id stu_info.id%TYPE)
   RETURN VARCHAR2 AS
   v_class_no VARCHAR2(10);
BEGIN
   IF v_id < 5 THEN
      v_class_no := 'A001';
   ELSIF v_id >= 5 THEN
      v_class_no := 'A002';
   ELSE
      v_class_no := 'A000';
   END IF;   
   RETURN v_class_no;
END;

查询视图：

SELECT * FROM vw_stu_info;	

视图的 DML 操作

通常情况下，我们都只会对视图只会进行查询操作，但是视图也是可以进行 INSERT、UPDATE、DELETE的，只是限制条件很多。

符合情况举例：“简单的视图” -> 视图中仅含一张基表

创建视图语句：

CREATE OR REPLACE VIEW vw_stu_info AS 
	SELECT si.id, si.name, si.sex
	FROM stu_info si;

向基表中插入数据的情况：

TRUNCATE TABLE stu_info;    --清除历史数据，如果有
INSERT INTO vw_stu_info (id, name, sex) VALUES (1, '瑶瑶', '女');    --插入成功  

不可更新的视图

要使视图可更新，视图中的行和底层基本表中的行之间必须存在 一对一 的关系。另外当视图定义出现如下情况时，视图不支持更新操作：

• 视图中不包含基表中所有被定义为非空又未指定默认值的列，视图将不支持INSERT操作；
• 在定义视图的SELECT语句中使用了JOIN联合查询 ，视图将不支持INSERT和DELETE操作；
• 在定义视图的SELECT语句中包含了关联子查询，视图将不支持 INSERT、UPDATE、DELETE；
• 在定义视图的SELECT语句后的字段列表中使用虚拟列(基表中没有，比如rowid、rownum)
• 在定义视图的SELECT语句后的字段列表中使用数学表达式或子查询，视图将不支持INSERT。也不支持UPDATE使用了数学表达式、子查询的字段值；
• 在定义视图的SELECT语句后的字段列表中使用 DISTINCT、聚合函数、GROUP BY、HAVING 、UNION等，视图将不支持INSERT、UPDATE、DELETE；
• 视图定义基于一个不可更新视图；
• 只读视图。

WITH CHECK OPTION

1、对于没有WHERE子句的视图，使用WITH CHECK OPTION是多余的。
2、对于有WHERE子句的视图，进行INSERT和UPDATE操作时，必须要符合WHERE子句的限制条件。进行SELECT和DELETE操作时，因为不需要更改数据，不需要在意约束条件。

创建视图语句：

CREATE OR REPLACE VIEW vw_stu_info AS 
    SELECT si.id, si.name, si.sex
    FROM stu_info si WHERE si.id < 3;
WITH CHECK OPTION

向基表中插入/删除数据的情况：

--如果不符合约束，报错：视图WITH CHECK OPTION违反WHERE子句
INSERT INTO vw_stu_info (id, name, sex) VALUES (5, '阿瑶', '女');    --插入失败
UPDATE vw_stu_info SET id = 9 WHERE id =1;    --更新失败

WITH READ ONLY

只读视图，只允许SELECT操作。

创建视图语句：

CREATE OR REPLACE VIEW vw_stu_info AS 
	SELECT si.id, si.name, si.sex
	FROM stu_info si WHERE si.id < 3;
WITH READ ONLY

向基表中插入/更新/删除数据的情况：

INSERT INTO vw_stu_info (id, name, sex) VALUES(2, '阿瑶', '女');    --报错：此处不允许虚拟列
UPDATE vw_stu_info SET id = 2 WHERE id =1;    --报错：此处不允许虚拟列
DELETE FROM vw_stu_info WHERE id =2;    --报错：不能从没有一个键值保存表的视图中删除

删除 VIEW

DROP VIEW vw_stu_info ;

1、只是删除视图的定义，并不会删除基表的数据。
2、基于视图a、b创建了新的视图c，如果将视图a或者视图b删除，会导致视图c的查询失败。这 样的视图c需要手动删除或修改，否则影响使用。

不可更新视图

1、在定义视图的SELECT语句后的字段列表中使用虚拟列（比如数值函数、数学表达式、子查询、聚合函数–当然聚合函数肯定是和GROUP BY或HAVING一起使用的、ROWNUM、ROWID），在进行INSERT、DELETE、UPDATE时候，可以对某一个基表数据更改，不要对对虚拟列进行更改。

--例一：数值函数
CREATE OR REPLACE VIEW vw_stu_info AS 
  SELECT si.id, UPPER(si.name) name
  FROM stu_info si;

SELECT id, name FROM vw_stu_info;

INSERT INTO vw_stu_info (id) VALUES('5');    --没有报错
UPDATE vw_stu_info SET id = '1' WHERE id = '2';     --没有报错
INSERT INTO vw_stu_info (id, name) VALUES('5','wf');    --报错:ORA-01733: 此处不允许虚拟列

--例二：聚合函数
CREATE OR REPLACE VIEW vw_stu_info AS 
  SELECT si.id, COUNT(si.name) num
  FROM stu_info si GROUP BY si.id HAVING COUNT (si.name) > 1;

SELECT * FROM vw_stu_info;

INSERT INTO vw_stu_info (id) VALUES('5');    --没有报错
UPDATE vw_stu_info SET id = '1' WHERE id = '2';     --没有报错
INSERT INTO vw_stu_info (id, num) VALUES('5',1);    --报错:ORA-01733: 此处不允许虚拟列

2、如果是使用了join联合查询在，视图将不支持INSERT、UPDATE、DELETE操作。

CREATE OR REPLACE VIEW vw_stu_info AS 
  SELECT si.id, ts.time
  FROM stu_info si, wfwf_test ts;
  
SELECT id, time FROM vw_stu_info;

INSERT INTO vw_stu_info (id) VALUES('5');    --报错:ORA-01779: 无法修改与非键值保存表对应的列
UPDATE vw_stu_info SET id = '3'  WHERE id = '2';     --报错:ORA-01779: 无法修改与非键值保存表对应的列
DELETE FROM vw_stu_info WHERE id = '1';    --报错:ORA-01752: 不能从没有一个键值保存表的视图中删除