Oracle第6章视图和索引操作

1、第6章 视图和索引操作主编：刘志成本章学习习导航本章学习习导航本章学习习要点（1）视图的的基本知知识。（2）OEM创建、修修改、查查看和删删除视图。（3）PL/SQL创建、修修改、查查看和删删除视图。（4）使用视视图。（5）索引的的概述。（6）OEM创建、修修改、查查看和删删除索引。（7）创建、修改和和删除聚聚集。建议课时时：4课时6.1视图概述述视图定义义视图（View）是从一一个或多多个表（其他视视图）中中导出的的表，其其结构和和数据是是建立在在对表的的查询基基础之上上的。所所以视图图不是真真实存在在的基础础表，而而是一张张虚表。视图所所对应的的数据并并不实际际地以视视图结构构存储在在数据

2、库库中，而而是存储储在视图图所引用用的表中中。视图一经经定义便便存储在在数据库库中，与与其相对对应的数数据并没没有像表表一样在在数据库库中另外外存储一一份，通通过视图图看到的的数据只只是存放放在基表表中的数数据。对对视图的的操作与与对表的的操作一一样，可可以对其其进行查查询、修修改（有有一定的的限制）和删除除。当对视图图中的数数据进行行修改时时，相应应的基表表的数据据也要发发生变化化，同时时，如果果基表的的数据发发生变化化，则这这种变化化也可以以自动地地反映到到视图中中。6.1视图概述述视图特点点（1）视点集集中，减减少对象象大小视图让用用户能够够着重于于他们所所需要的的特定数数据或所所负责的

3、的特定要要求，如如用户可可以选择择特定行行或特定定列。（2）从异构构源组织织数据可以在连连接两个个或多个个表的复复杂查询询的基础础上创建建视图，这样可可以将单单个表显显示给用用户。（3）隐藏数数据的复复杂性，简化操操作视图向用用户隐藏藏了数据据库设计计的复杂杂性，这这样如果果开发者者改变数数据库设设计，不不会影响响到用户户与数据据库交互互。另外外，用户户可将经经常使用用的连接接查询、嵌套查查询或联联合查询询定义为为视图。（4）简化用用户权限限的管理理可以将视视图的权权限授予予用户，而不必必将基表表中某些些列的权权限授予予用户，这样就就简化了了用户权权限的定定义。6.2.1课堂案例例1使用OEM

4、管理视图图掌握Oracle中应用OEM创建视图图、修改改视图、查看视视图和删删除视图图的方法法和基本本步骤。案例学习习目标视图的定定义、OEM创建视图图、OEM修改视图图、OEM查看视图图和OEM删除视图图。案例知识识要点6.2.1课堂案例例1使用OEM管理视图图案例完成成步骤添加标题题文字1使用OEM创建视图图（1）进入OEM后，依次次选择“方案”、“视视图”，进入“视图” 页面面（2）单击“创建”按钮，进入“创建视视图”页页面添加标题题文字教师演示示讲解6.2.1课堂案例例1使用OEM管理视图图案例完成成步骤添加标题题文字2使用OEM修改视图图（1）重命名名视图重命名视视图在OEM中不能直

5、直接完成成，只能能先删除除原有的的视图，再新建建对应的的视图。（2）修改视视图定义义（a）在指定定方案的的“视图图”页面面中，选选择要编编辑的视视图，单单击“编编辑”按按钮，进进入“编编辑视图图”页面面（b）在“查查询文本本”中可可以完成成视图对对应的SQL语句的修修改操作作，修改改完成后后，单击击“编译译”按钮钮可以进进行编译译（c）在完成成视图编编辑和编编译后，单击“应用”按钮，完成对对指定视视图的修修改操作作。添加标题题文字教师演示示讲解6.2.1课堂案例例1使用OEM管理视图图案例完成成步骤添加标题题文字3使用OEM查看视图图（1）在指定定方案的的“视图图”页面面中，选选择要编编辑的视

6、视图，单单击“查查看”按按钮，进进入“查查看视图图”页面面（2）在视图图的“查查看”页页面中，可以查查看到视视图的名名称、视视图的方方案、别别名、视视图状态态和查询询文本等等添加标题题文字教师演示示讲解6.2.1课堂案例例1使用OEM管理视图图案例完成成步骤添加标题题文字4使用OEM删除视图图（1）在指定定方案的的“视图图”页面面中，选选择要编编辑的视视图，单单击“删删除”按按钮，进进入“确确认删除除”页面面（2）单击“是”按按钮，将将删除指指定的视视图添加标题题文字教师演示示讲解6.2.2课堂案例例2使用PL/SQL管理视图图掌握Oracle中应用PL/SQL创建视图图、修改改视图、查看视视

7、图和删删除视图图的基本本语句和和使用方方法。案例学习习目标PL/SQL创建简单单视图、PL/SQL创建只读读视图、PL/SQL创建检查查视图、PL/SQL创建连接接视图、PL/SQL创建复杂杂视图、PL/SQL创建强制制视图、PL/SQL修改视图图、PL/SQL查看视图图和PL/SQL删除视图图。案例知识识要点6.2.2课堂案例例2使用PL/SQL管理视图图案例完成成步骤添加标题题文字1使用PL/SQL创建视图图（1）创建简简单视图图。【例2-1】经常需要要了解“热点”商品的的商品号号（g_ID）、商品品名称（g_Name）、类别别号（t_ID）、商品品价格（g_Price）、商品品折扣（g_

8、Discount）和商品品数量（g_Number）信息，可以创创建一个个“热点点”商品品的视图图。CREATEORREPLACEVIEW SCOTT.vw_HotGoodsASSELECTg_IDAS商品号, g_NameAS商品名称称, t_IDAS类别号, g_PriceAS价格, g_DiscountAS折扣, g_Number AS数量FROM SCOTT.GoodsWHEREg_Status= 热点;教师演示示讲解6.2.2课堂案例例2使用PL/SQL管理视图图案例完成成步骤添加标题题文字（2）创建只只读视图图。在创建视视图时，可以使使用WITH READONLY选项创建建只读视视图

9、。【例2-2】经常需要要了解员员工的编编号（e_ID）、名称称（e_Name）、家庭庭地址（e_Address）、邮政政编码（e_PostCode）和手机机号码（e_Mobile）信息，要求能能够通过过视图来来查询这这些信息息（但不不能通过过视图更更改这些些信息）。CREATEVIEWSCOTT.vw_emp_readonlyASSELECTe_ID,e_Name,e_Address,e_PostCode,e_MobileFROM SCOTT.employeesWITH READONLY;教师演示示讲解6.2.2课堂案例例2使用PL/SQL管理视图图案例完成成步骤添加标题题文字（3）创建检检查

10、视图图。在创建视视图时，可以使使用WITH CHECK OPTION选项创建建检查视视图。【例2-3】经常需要要了解海海尔公司司商品的的商品号号（g_ID）、商品品名称（g_Name）、商品品价格（g_Price）、商品品折扣（g_Discount）和商品品数量（g_Number）信息，可以创创建一个个关于海海尔商品品的视图图。CREATEORREPLACEVIEW SCOTT.vw_HaierGoodsASSELECTg_ID,g_Name,g_Price,g_Discount,g_NumberFROM SCOTT.GoodsWHEREg_NameLIKE%海尔%WITH CHECK OPT

11、ION;教师演示示讲解6.2.2课堂案例例2使用PL/SQL管理视图图案例完成成步骤添加标题题文字（4）创建连连接视图图连接视图图是指基基于多个个表所创创建的视视图，即即定义视视图的查查询是一一个连接接查询。使用连连接视图图的主要要目的是是为了简简化连接接查询。【例2-4】经常需要要了解商商品的商商品号（g_ID）、商品品名称（g_Name）、类别别名称（t_Name）和商品品价格（g_Price）信息，可以创创建一个个关于这这类商品品的视图图。CREATEORREPLACEVIEW SCOTT.vw_TNameGoodsASSELECTg_ID,g_Name,t_Name, g_PriceF

12、ROM SCOTT.GoodsJOIN TypesONGoods.t_ID=Types.t_IDWITH CHECK OPTION教师演示示讲解6.2.2课堂案例例2使用PL/SQL管理视图图案例完成成步骤添加标题题文字（5）创建复复杂视图图。复杂视图图是指包包含函数数、表达达式或分分组数据据的视图图，主要要目的是是为了简简化查询询。【例2-5】经常需要要了解某某一类商商品的类类别号（t_ID）和该类类商品的的最高价价格信息息，可以以创建一一个关于于这类商商品的视视图。CREATEORREPLACEVIEW SCOTT.vw_MaxPriceGoodsASSELECTt_ID,Max(g_Pr

13、ice) AS MaxPriceFROM SCOTT.GoodsGROUPBYt_ID教师演示示讲解6.2.2课堂案例例2使用PL/SQL管理视图图案例完成成步骤添加标题题文字（6）强制创创建视图图正常情况况下，如如果基表表不存在在，创建建视图就就会失败败。但是是可以使使用FORCE选项强制制创建视视图（前前提是创创建视图图的语句句没有语语法错误误），但但此时该该视图处处于失效效状态。【例2-6】创建并验验证基于于Test表的强制制视图（a）在Test表不存在在的情况况下，创创建基于于该表的的强制视视图vw_TestForce。CREATEFORCEVIEWSCOTT.vw_TestForce

14、ASSELECTc1,c2FROMTest;以上语句句执行后后，视图图创建成成功，但但会出现现警告信信息，视视图状态态为INVALID状态。教师演示示讲解6.2.2课堂案例例2使用PL/SQL管理视图图案例完成成步骤添加标题题文字在实际应应用中，随着查查询要求求的改变变和数据据源的变变化：更改视图图只是改改变数据据字典中中对该视视图的定定义信息息，视图图的所有有基础对对象都不不会受到到任何影影响；更改视图图之后，依赖于于该视图图的所有有视图和和PL/SQL程序都将将变为INVALID(失效)状态；如果以前前的视图图中具有有WITH CHECK OPTION选项，但但是重定定义时没没有使用用该选

15、项项，则以以前的此此选项将将自动删删除。2使用PL/SQL修改视图图（1）重命名名视图【例2-7】重命名用用户方案案SCOTT的视图vw_MaxPriceGoods为vw_MaxPrice。renamevw_MaxPriceGoodsTOvw_MaxPrice;教师演示示讲解6.2.2课堂案例例2使用PL/SQL管理视图图案例完成成步骤添加标题题文字2使用PL/SQL修改视图图（2）修改视视图定义义【例2-8】对于已创创建的视视图vw_HotGoods，现在需需要删除除其中的的折扣（g_Discount）信息，使之仅仅包含商商品的商商品号（g_ID）、商品品名称（g_Name）、类别别号（t_

16、ID）、价格格（g_Price）和数量量（g_Number）信息。CREATEORREPLACEVIEW SCOTT.vw_HotGoodsASSELECTg_IDAS商品号, g_NameAS商品名称称, t_IDAS类别号, g_PriceAS价格, g_Number AS数量FROM SCOTT.GoodsWHEREg_Status= 热点教师演示示讲解6.2.2课堂案例例2使用PL/SQL管理视图图案例完成成步骤添加标题题文字（3）重新编编译视图图可以使用用PL/SQL的ALTERVIEW语句重新新编译视视图。ALTERVIEW视图名COMPILE;当视图依依赖的基基表改变变后，视视图

17、会“失效”。为了了确保这这种改变变“不影影响”视视图和依依赖于该该视图的的其他对对象，应应该使用用ALTERVIEW语句明确确的重新新编译该该视图，从而在在运行视视图前发发现重新新编译的的错误。视图被被重新编编译后，若发现现错误，则依赖赖该视图图的对象象也会失失效；若若没有错错误，视视图会变变为“有有效”。教师演示示讲解6.2.2课堂案例例2使用PL/SQL管理视图图案例完成成步骤添加标题题文字3使用PL/SQL查看视图图Oracle的PL/SQL语句提供供了DESCRIBE命令用来来查看视视图的信信息，其其基本语语法格式式为：DESCRIBE用户方案案.视图名;【例2-9】查看用户户方案SC

18、OTT的视图VW_HOTGOODS的信息。DESC SCOTT.VW_HOTGOODS;教师演示示讲解6.2.2课堂案例例2使用PL/SQL管理视图图案例完成成步骤添加标题题文字4使用PL/SQL删除视图图Oracle的PL/SQL语句提供供了DROP VIEW命令来删删除视图图（必须须保证当当前用户户具有DROP ANYVIEW的系统权权限），其基本本语法格格式为：DROPVIEW用户方案案.视图名;【例2-10】删除用户户方案SCOTT的视图.VW_TNAMEGOODS的信息。DROP VIEWSCOTT.VW_TNAMEGOODS;教师演示示讲解课堂案例例3使用视图图掌握Oracle中通

19、过视视图进行行查询和和DML操作的基基本方法法和注意意事项。案例学习习目标通过视图图查询数数据、通通过视图图添加数数据、通通过视图图修改数数据、通通过视图图删除数数据。案例知识识要点课堂案例例3使用视图图案例完成成步骤添加标题题文字6.3.1视图查询询操作【例3-1】需要了解解价格在在2000元以上的的促销商商品信息息，为了了简化查查询操作作，可以以在视图图vw_SaleGoods中进行查查询。SELECT*FROM SCOTT.vw_SaleGoodsWHERE价格2000;视图上执执行查询询操作的的步骤和原原理：（1）将针对对视图的的SQL语句与视视图的定定义语句句(保存在数数据字典典中)

20、“合并”成成一条SQL语句；（2）在内存存结构的的共享SQL区中“解解析”(并优化)合并后的的SQL语句；（3）“执行行”合并并、解析析后的SQL语句。教师演示示讲解课堂案例例3使用视图图案例完成成步骤添加标题题文字【例3-2】需要统计计每类商商品的平平均价格格（显示示类别名名和该类类别的平平均价格格），为为了简化化查询操操作，可可以在视视图vw_TNameGoods中进行查查询。SELECTt_Name类别名称称, AVG(g_Price)平均价格格FROM SCOTT.vw_TNameGoodsGROUPBYt_Name;教师演示示讲解课堂案例例3使用视图图案例完成成步骤添加标题题文字6.

21、3.2视图DML操作当向视图图中插入入、修改改或者删删除数据据时，实实际上是是对视图图所引用用的基表表执行数数据的插插入、修修改或者者删除操操作对于于这些操操作，Oracle有如下的的一些限制：用户应该该具有操操作视图图的权限限，同时时具有操操作视图图所引用用的基表表或其他他视图的的权限；在一个语语句中，一次不不能修改改一个以以上的视视图基表表；对视图中中所有列列的修改改必须遵遵守视图图基表中中所定义义的各种种数据约约束条件件；不允许对对视图中中的计算算列进行行修改，也不允允许对视视图定义义中包含含有统计计函数或或者GROUPBY子句的视视图进行行更新操操作。教师演示示讲解课堂案例例3使用视图

22、图案例完成成步骤添加标题题文字【例3-3】通过视图图vw_Users向表Users中增加一一个用户户。（1）首先建建立一个个视图vw_Users，要求将将Users表中的英英文的列列名换成成汉字的的列名。CREATEORREPLACEVIEW SCOTT.vw_UsersASSELECTu_IDAS编号, u_NameAS用户名, u_TypeAS用户组, u_PasswordAS用户密码码FROM SCOTT.Users;（2）通过视视图vw_Users实现记录录的添加加。INSERTINTOSCOTT.vw_UsersVALUES(05,view,普通,view);教师演示示讲解课堂案例例

23、3使用视图图案例完成成步骤添加标题题文字【例3-4】通过视图图vw_Users将用户“amy”的所属用用户组修修改为“查询”。UPDATESCOTT.vw_UsersSET用户组=查询WHERE用户名=amy;【例3-5】试着通过过视图vw_emp_readonly（视图定定义见6.2.2）删除名名称为“刘丽丽丽”的员员工。DELETEFROMSCOTT.vw_emp_readonlyWHEREe_Name=刘丽丽;语句执行行后，将将会显示示“无法法对只读读视图进进行DML操作”的的错误提提示教师演示示讲解6.4索索引概概述索引概念念索引（Index）是一个个单独的、物理的数据结构构，在这这个

24、数据据结构中中包括表表中一列列或若干干列的值值以及相相应的指指向表中中物理标标识这些些值的数数据页的的逻辑指指针的集集合。索引提供供了数据据库中编排表中中数据的内部方方法。索索引依赖赖于数据据库的表表，作为为表的一一个组成成部分，一旦创创建后，由数据据库系统统自身进进行维护护。一个个表的存存储是由由两部分分组成的的，一部部分用来来存放表表的数据据页面，另一部部用来分分存放索索引页面面，索引引就存放放在索引引页面上上。索引页面面相对于于数据页页面来说说小得多多。当进进行数据据检索时时，系统统先搜索索索引页页面，从从中找到到所需数数据的指指针，再再直接通通过指针针从数据据页面中中读取数数据。从从某

25、种程程度上可可以把数数据库看看作一本本书，把索引引看作书书的目录，通过目目录查找找书中的的信息，显然比比没有目目录的书书更方便便、快捷捷。6.4索索引概概述索引分类类1B树索引B树索引是是最常见见的索引引结构，默认建建立的索索引就是是这种类类型的索索引。6.4索索引概概述索引分类类2反向索索引反向索引引是B树索引的的一个分分支，它它的设计计是为了了运用在在某些特特定的环环境下。3降序索索引降序索引引是B树的另一一个衍生生物，它它的变化化就是列列在索引引中的存存储方式式从升序序变成了了降序，在某些些场合下下降序索索引将会会起作用用。创建建降序索索引时，Oracle已经把数数据都按按降序排排好了。

26、4位图索索引位图索引引主要用用于决策策支持系系统或静静态数据据，不支支持行级级锁定。5函数索索引基于函数数的索引引有索引引计算列列的能力力，它易易于使用用并且提提供计算算好的值值，在不不修改应应用程序序的逻辑辑上提高高了查询询性能。6.5.1课堂案例例4使用OEM管理索引引掌握Oracle中应用OEM创建索引引、修改改索引、查看索索引和删删除索引引的方法法和基本本步骤。案例学习习目标OEM创建索引引图、索索引的选选择、OEM修改索引引、OEM查看索引引和OEM删除索引引。案例知识识要点6.5.1课堂案例例4使用OEM管理索引引案例完成成步骤添加标题题文字1使用OEM创建索引引（1）进入OEM后

27、，依次次选择“方案”、“索索引”，进入“索引页页面”（2）单击“创建”按钮，进入“创建索索引”页页面，输输入索引引名称（如：ix_GoodName），指定定用户方方案为SCOTT，并指定定索引对对应的表表和顺序序（3）单击“确定”按钮，如果创创建的索索引没有有错误，进入“确认”页面，并显示示“已成成功创建建 索引引SCOTT.IX_GOODNAME”消息，并并显示当当前方案案（SCOTT）中的索索引列表表教师演示示讲解6.5.1课堂案例例4使用OEM管理索引引案例完成成步骤添加标题题文字2使用OEM修改索引引3使用OEM查看和删删除索引引教师演示示讲解6.5.2课堂案例例5使用PL/SQL管理

28、索引引掌握Oracle中应用PL/SQL创建索引引、修改改索引、查看索索引和删删除索引引的方法法和基本本步骤。案例学习习目标CREATEINDEX创建索引引、索引引的选择择、ALTERINDEX修改索引引、通过过数据字字典查看看索引和和DROP INDEX删除索引引。案例知识识要点6.5.2课堂案例例5使用PL/SQL管理索引引案例完成成步骤添加标题题文字1使用PL/SQL创建索引引【例4-1】创建关于于员工表表的员工工名称列列的唯一一索引。CREATEUNIQUE INDEX SCOTT.ix_EmpNameONSCOTT.Employees(e_Name);【例4-2】建立关于于商品表表的

29、商品品类别编编号列的的索引。CREATEINDEXSCOTT.ix_TypeIDONSCOTT.Goods(t_ID);【例4-3】建立关于于订单明明细表的的订单编编号列和和产品编编号列的的复合索索引。CREATEINDEXSCOTT.ix_OID_GIDONSCOTT.ORDERDETAILS(O_ID,G_ID);教师演示示讲解6.5.2课堂案例例5使用PL/SQL管理索引引案例完成成步骤添加标题题文字2使用PL/SQL修改索引引【例4-4】修改索引引ix_EmpName，重新调调整INITRANS和MAXTRANS参数的值值。ALTERINDEXSCOTT.ix_EmpNameINITR

30、ANS5MAXTRANS10;3使用PL/SQL删除索引引利用DROP INDEX命令可以以删除一一个或多多个当前前数据库库中的索索引。其其语句格格式如下下：DROP INDEX 用户方案案.,.n【例4-5】删除员工工表中为为员工名名称创建建的唯一一索引IX_EMPNAME。DROP INDEX SCOTT.EMPLOYEES.IX_EMPNAME;教师演示示讲解6.5.2课堂案例例5使用PL/SQL管理索引引案例完成成步骤添加标题题文字4查看索索引教师演示示讲解课堂案例例6聚集操作作掌握Oracle中应用聚聚集的基基本概念念、创建建聚集、修改聚聚集和删删除聚集集的方法法和基本本步骤。案例学

31、习习目标创建聚集集本身、创建聚聚集中的的表、创创建聚集集键上的的索引、修改聚聚集和删删除聚集集。案例知识识要点课堂案例例6聚集操作作聚集概述述添加标题题文字1索引聚聚集索引聚集集是保存存数据表表的一种种可选方方案。索索引聚集集在同一一个数据据块中将将多个不不同表的的相关行行存储在在一起，从而改改善相关关操作的的存取时时间。共共享公共共列的表表可以聚聚集在该该列的周周围，从从而加速速对这些些行的存存取。索索引聚集集有利于于聚集数数据上的的连接，因为所所有的数数据在一一个I/O操作中被被检索。2哈希聚聚集哈希聚集集类似于于索引聚聚集，但但它使用用哈希函函数而非非索引来来引用聚聚集键。哈希聚聚集在同

32、同一数据据块中将将相关的的行存储储在一起起，依据据是这些些行的哈哈希函数数结果。在创建建哈希聚聚集时，Oracle为聚集的的数据段段分配初初始数量量的存储储空间。教师演示示讲解课堂案例例6聚集操作作案例完成成步骤-创建聚集集添加标题题文字1创建聚聚集本身身【例5-1】创建一个个包含column0l的聚集testclu。CREATECLUSTERSCOTT.testclu(column0l VARCHAR(20);2创建聚聚集中的的表【例5-2】创建聚集集为testclu的两个测测试表testa和testb。CREATETABLESCOTT.testa(a01VARCHAR(20),a02VAR

33、CHAR(20)CLUSTER SCOTT.testclu(a01);CREATETABLESCOTT.testb(b01VARCHAR(20),b02VARCHAR(20)CLUSTER SCOTT.testclu(b01);教师演示示讲解课堂案例例6聚集操作作案例完成成步骤添加标题题文字3创建聚聚集键上上的索引引【例5-3】为聚集testclu创建聚集集键。CREATEINDEXSCOTT.ix_testcluONCLUSTER SCOTT.testclu;创建聚集集索引后后，就可可以使用用聚集了了。【例5-4】输入测试试数据。-testa表添加两两条记录录INSERTINTOSCOTT.testaVALUES(01,01);INSERTINTOSCOTT.testaVALUE