oracle 视图的增删改查操作举例

oracle 视图的增删改查操作举例

oracle视图创建和操作创建简单复杂的视图创建基表不存在的视图视图增删改查看视图的结构 关键字: oracle 视图 创建 操作 简单 复杂 基表 不存在 增 删 改 插入 修改 删除

查看 结构 视图的概念 视图是基于一张表或多张表或另外一个视图的逻辑表。视图不同于表视图本身不包含任何数据。表是实际独立存在的实体是用于存储数据的基本结构。而视图只是一种定义对应一个查询语句。视图的数据都来自于某些表这些表被称为基表。通过视图来查看表就像是从不同的角度来观察一个或多个表。 视图有如下一些优点 可以提高数据访问的安全性通过视图往往只可以访问数据库中表的特定部分限制了用户访问表的全部行和列。 简化了对数据的查询隐藏了查询的复杂性。视图的数据来自一个复杂的查询用户对视图的检索却很简单。 一个视图可以检索多张表的数据因此用户通过访问一个视图可完成对多个表的访问。 视图是相同数据的不同表示通过为不同的用户创建同一个表的不同视图使用户可分别访问同一个表的不同部分。 视图可以在表能够使用的任何地方使用但在对视图的操作上同表相比有些限制特别是插入和修改操作。对视图的操作将传递到基表所以在表上定义的约束条件和触发器在视图上将同样起作用。 视图的创建 创建视图需要CREAE

VIEW系统权限视图的创建语法如下 CREATE OR REPLACE FORCENOFORCE VIEW 视图名别名1别名2... AS 子查询 WITH CHECK OPTION CONSTRAINT 约束名 WITH

READ ONLY 其中 OR REPLACE 表示替代已经存在的视图。 FORCE表示不管基表是否存在创建视图。 NOFORCE表示只有基表存在时才创建视图是默认值。 别名是为子查询中选中的列新定义的名字替代查询表中原有的列名。 子查询是一个用于定义视图的SELECT查询语句可以包含连接、分组及子查询。 WITH CHECK OPTION表示进行视图插入或修改时必须满足子查询的约束条件。后面的约束名是该约束条件的名字。 WITH READ ONLY

表示视图是只读的。 删除视图的语法如下 DROP VIEW 视图名 删除视图者需要是视图的建立者或者拥有DROP ANY VIEW权限。视图的删除不影响基表不会丢失数据。 1创建简单视图 创建图书作者视图。 步骤1创建图书作者视图 Sql代码 1. CREATE VIEW 图书作者书名作者 2. AS SELECT 图书名称作者 FROM 图书 输出结果 视图已建立。 步骤2查询视图全部内容 Sql代码 1. SELECT FROM 图书作者 输出结果 Sql代码 1. 书名 作者 2.

-------------------------------- -------------------- 3. 计算机原理 刘勇 4. C语言程序设计 马丽 5.

汇编语言程序设计 黄海明 步骤3查询部分视图 Sql代码 1. SELECT 作者 FROM 图书作者 输出结果 Sql代码 1. 作者 2. ---------- 3. 刘勇 4. 马丽 5. 黄海明 说明本训练创建的视图名称为“图书作者”视图只包含两列为“书名”和“作者”对应图书表的“图书名称”和“作者”两列。如果省略了视图名称后面的列名则视图会采用和表一样的列名。对视图查询和对表查询一样但通过视图最多只能看到表的两列可见视图隐藏了表的部分内容。 创建清华大学出版社的图书视图。 步骤1创建清华大学出版社的图书视图 Sql代码 1. CREATE VIEW 清华图书 AS SELECT 图书名称作者单价 FROM 图书 WHERE 出版社编号 01 执行结果 视图已建立。 步骤2查询图书视图 Sql代码 1. SELECT FROM 清华图书 执行结果 Sql代码

1. 图书名称 作者 单价 2. -------------------------------------------- ---------- ----------------------- 3.

计算机原理 刘勇 25.3 步骤3删除视图 Sql代码 1. DROP VIEW 清华图书 执行结果 视图已丢掉。 说明该视图包含了对记录的约束条件。 2创建复杂视图 修改作者视图加入出版社名称。 步骤1重建图书作者视图 Sql代码 1. CREATE OR REPLACE VIEW 图书作者书名作者出版社 2. AS SELECT 图书名称作者出版社名称 FROM 图书出版社 3. WHERE 图书.出版社编号出版社.编号 输出结果 视图已建立。 步骤2查询新视图内容 Sql代码 1.

SELECT FROM 图书作者 输出结果 Sql代码 1. 书名 作者 出版社 2.

-------------------------------------------- ---------- ---------------------------- 3. 计算机原理 刘勇 清华大学出版社 4. C语言程序设计 马丽 电子科技大学出版社 5. 汇编语言程序设计 黄海明

电子科技大学出版社 说明本训练中使用了OR REPLACE选项使新的视图替代了同名的原有视图同时在查询中使用了相等连接使得视图的列来自于两个不同的基表。 创建一个统计视图。 步骤1创建emp表的一个统计视图 Sql代码 1. CREATE VIEW 统计表部门名最大工资最小工资平均工资 2. AS SELECT DNAMEMAXSALMINSALAVGSAL FROM EMP

EDEPT D 3. WHERE GROUP BY DNAME 执行结果 视图已建立。

步骤2查询统计表 Sql代码 1. SELECT FROM 统计表 执行结果 Sql代码 1. 部门名 最大工资 最小工资 平均工资 2. -------------------------- --------------- ----------------- ------------------ 3.

ACCOUNTING 5000 1300 3050 4. RESEARCH 3000 800 2175 5. SALES 2850 950 1566.66667

说明本训练中使用了分组查询和连接查询作为视图的子查询每次查询该视图都可以得到统计结果。 创建只读视图 创建只读视图要用WITH READ ONLY选项。 创建只读视图。

步骤1创建emp表的经理视图 Sql代码 1. CREATE OR REPLACE VIEW manager 2. AS

SELECT FROM emp WHERE job MANAGER 3. WITH READ ONLY 执行结果 视图已建立。

步骤2进行删除 Sql代码 1. DELETE FROM manager 执行结果 ERROR 位于第 1 行:

ORA-01752: 不能从没有一个键值保存表的视图中删除 4创建基表不存在的视图 正常情况下不能创建错误的视图特别是当基表还不存在时。但使用FORCE选项就可以在创建基表前先创建视图。创建的视图是无效视图当访问无效视图时Oracle将重新编译无效的视图。

使用FORCE选项创建带有错误的视图 Sql代码 1. CREATE FORCE VIEW 班干部 AS

SELECT FROM 班级 WHERE 职务 IS NOT NULL 执行结果 警告: 创建的视图带有编译错误。 视图的操作 对视图经常进行的操作是查询操作但也可以在一定条件下对视图进行插入、删除和修改操作。对视图的这些操作最终传递到基表。但是对视图的操作有很多限定。如果视图设置了只读则对视图只能进行查询不能进行修改操作。 1视图的插入 视图插入练习。 步骤1创建清华大学出版社的图书视图 Sql代码 1. CREATE OR REPLACE VIEW 清华图书 2. AS SELECT FROM 图书 WHERE 出版社编号 01 执行结果 视图已建立。 步骤2插入新图书 Sql代码 1. INSERT INTO 清华图书 VALUESA0005软件工程01冯娟527.3

执行结果 已创建 1 行。 步骤3显示视图 Sql代码 1. SELECT FROM 清华图书 执行结果

Sql代码 1. 图书 图书名称 出 作者 数量 单价 2. -------- ----------------------------------------

----------- -------- ------------------------ -------------- 3. A0001 计算机原理 01 刘勇 5 25.3 4.

A0005 软件工程 01 冯娟 5 27.3 步骤4显示基表 Sql代码 1. SELECT FROM 图书 执行结果 Sql代码 1. 图书 图书名称 出 作者 数量 单价 2. --------

------------------------------------------ ------- ---------------- ----------------- --------------- 3. A0001 计算机原理 01 刘勇 5 25.3 4. A0002 C语言程序设计 02 马丽 1 18.75 5. A0003 汇编语言程序设计 02 黄海明 15 20.18 6. A0005 软件工程 01 冯娟 5 27.3 说明通过查看视图可见新图书插入到了视图中。通过查看基表看到该图书也出现在基表中说明成功地进行了插入。新图书的出版社编号为“01”仍然属于“清华大学出版社”。 但是有一个问题就是如果在“清华图书”的视图中插入其他出版社的图书结果会怎么样呢结果是允许插入但是在视图中看不见在基表中可以看见这显然是不合理的。 2使用WITH CHECK OPTION选项 为了避免上述情况的发生可以使用WITH CHECK OPTION选项。使用该选项可以对视图的插入或更新进行限制即该数据必须满足视图定义中的子查询中的WHERE条件否则不允许插入或更新。比如“清华图书”视图的WHERE条件是出版社编号要等于“01”01是清华大学出版社的编号所以如果设置了WITH CHECK OPTION选项那么只有出版社编号为“01”的图书才能通过清华视图进行插入。 使用WITH CHECK OPTION选项限制视图的插入。 步骤1重建清华大学出版社的图书视图带WITH CHECK OPTION选项 Sql代码 1. CREATE OR REPLACE

VIEW 清华图书 2. AS SELECT FROM 图书 WHERE 出版社编号 01 3. WITH CHECK

OPTION 执行结果 视图已建立。 步骤2插入新图书 Sql代码 1. INSERT INTO 清华图书

VALUESA0006Oracle数据库02黄河339.8 执行结果 ERROR 位于第 1 行: ORA-01402:

视图 WITH CHECK OPTIDN 违反 where 子句 说明可见通过设置了WITH CHECK

OPTION选项“02”出版社的图书插入受到了限制。如果修改已有图书的出版社编号情况会如何答案是将同样受到限制。要是删除视图中已有图书结果又将怎样呢答案是可以因为删除并不违反WHERE条件。 3来自基表的限制 除了以上的限制基表本身的限制和约束也必须要考虑。如果生成子查询的语句是一个分组查询或查询中出现计算列这时显然不能对表进行插入。另外主键和NOT NULL列如果没有出现在视图的子查询中也不能对视图进行插入。在视图中插入的数据也必须满足基表的约束条件。 基表本身限制视图的插入。 步骤1重建图书价格视图 Sql代码 1. CREATE OR REPLACE VIEW 图书价格 2. AS SELECT 图书名称单价 FROM 图书 执行结果 视图已建立。 步骤2插入新图书 Sql代码 1. INSERT INTO

图书价格 VALUESOracle数据库39.8 执行结果 ERROR 位于第 1 行: ORA-01400: 无法将 NULL 插入 SCOTT.图书.图书编号 说明在视图中没有出现的基表的列在对视图插入时自动默认为NULL。该视图只有两列可以插入其他列将默认为空。插入出错的原因是在视图中不能插入图书编号而图书编号是图书表的主键是必须插入的列不能为空这就产生了矛盾。

视图的查看 USER_VIEWS字典中包含了视图的定义。 USER_UPDATABLE_COLUMNS字典包含了哪些列可以更新、插入、删除。 USER_OBJECTS字典中包含了用户的对象。 可以通过DESCRIBE命令查看字典的其他列信息。在这里给出一个训练例子。 查看清华图书视图的定义 Sql代码 1. SELECT TEXT FROM USER_VIEWS WHERE VIEW_NAME清华图书 执行结果 Sql代码 1. TEXT 2.

----------------------------------------------------------------------------------------------- 3. SELECT 图书名称作者单价 FROM 图书 WHERE 出版社编号01 查看用户拥有的视图 Sql代码 1.

SELECT object_name FROM user_objects WHERE object_typeVIEW 执行结果 Sql代码 1.

OBJECT_NAME 2.

---------------------------------------------------------------------------------------------- 3. 清华图书 4.

图书作者