管理数据库对象范文.docx

1、管理数据库对象范文第3章管理数据库对象本章学习内容1. 同义词2. 序列3. 视图4. 索引本章学习目标1. 掌握同义词的使用2. 掌握序列的使用3. 掌握视图的使用4. 掌握索引的使用本章简介在第2章中，我们已经学习了使用DDL语句、DML语句、SELECT语句、DCL数据控制语言、TC事务控制语言、基本函数等，并能将这些语句用于Oracle的SQL查询语句中，特别是使用基本函数可以提高书写SQL语句的效率。本章将继续学习Oracle方案、同义词、序列、视图和索引等知识。方案是数据库对象的集合，为了区分各个集合，需要给集合命名，其名称即为方案名。方案中包含各种对象，如表、视图、索引、同义词、

2、序列、数据库链接等对象。同义词是方案对象的一个别名，常用于简化对象和提高对象访问的安全性。序列（SEQUENCE）是一个命名的顺序编号生成器，它能以串行的方式生成一系列顺序整数。在表中序列经常作为表的自动编号列。视图（VIEW）是由SELECT子查询语句定义的一个逻辑表，只有定义而无数据，是一个“虚表”。视图是查看和操作表数据的一种方法，使用视图有诸多优点，如提供各种数据表现形式、提供某些数据的安全性、隐藏数据的复杂性、简化查询语句、执行特殊查询、保存复杂查询等。索引是一种与表相关的可选的方案对象。通过在表中的一个或多个列上创建索引，能够为数据的检索提供快捷的存取路径，减少查询时的硬盘I/O操

3、作，加快数据的检索速度。3.1 同义词3.1.1 方案方案是数据库对象的集合，为了区分各个集合，需要给集合命名，其名称即为方案名。方案中包含各种对象，如表、视图、索引、同义词、序列、数据库链接等对象。一般情况下，一个用户对应一个方案，在创建用户的同时为该用户创建一个与用户名同名的方案，并作为该用户的缺省方案。当然，一个用户还可以使用其他用户的方案。如果访问一个表时没有指明该表属于哪一个用户方案，则系统将会自动地在访问表前加上缺省的方案名。例如，访问SCOTT用户下的emp表的SQL语句为：select * from emp。事实上，这条SQL语句的完整写法应为：select * from sc

4、ott.emp，因为在数据库中一个对象的完整名称为“方案名.对象名”，而不是“用户名.对象名”。解释3.1.2 同义词概述同义词是方案对象的一个别名，常用于简化对象和提高对象访问的安全性。同义词并不占用实际存储空间，只在数据库字典中保存同义词的定义。可以创建同义词的对象主要包括表、视图、同义词、序列、存储过程等对象。在开发数据库应用程序时，应当普遍遵守的规则是：尽量避免直接引用表、视图或其他对象的名称。否则，当DBA改变了这些对象的名称时，就必须重新更改并编译应用程序。因此，DBA应当为开发人员建立对象的同义词，这样即使基础表或其他对象发生了变动，也只需要在数据库中对同义词进行修改，而不必对应

5、用程序作出任何改动。Oracle中可以创建两种类型的同义词：公用同义词和方案同义词。3.1.3 公用同义词与方案同义词1. 公用同义词（public synonym）公用同义词由一个特殊的用户组PUBLIC所拥有，数据库中所有的用户都可以使用公用同义词。SYS用户创建的数据字典视图就是公用同义词的示例。2. 方案同义词（schema synonym）方案同义词由创建它的用户所拥用，也称为私有同义词（private synonym），用户可以控制其他用户是否有权使用属于自己的方案同义词。方案同义词常在应用开发中使用，为应用开发提供命名上的解决方案。值得注意的是，当代码引用一个未限定的表、视图、同

6、义词、序列、函数等对象时，Oracle会按以下顺序来查看这3个位置是否有被引用的对象：（1）当前用户拥有的对象。（2）由当前用户拥有的一个方案同义词。（3）公用同义词。可见，方案同义词的搜索顺序优于公用同义词。如果在这3个地方都没有找到该对象的名称，将会出现错误提示，如“ORA-00942：表或视图不存在”。3.1.4 创建同义词1. 创建方案同义词需要在自己的方案中创建方案同义词时，用户必须具有CREATE SYNONYM系统权限；需要在其他的方案中创建方案同义词时，用户必须具有CREATE ANY SYNONYM系统权限。语法：CREATE OR REPLACE SYNONYM synon

7、ym_name schema.object;其中，OR REPLACE表示如果同义词已经存在，将重新覆盖新建同义词。如图1.3.1所示，在被授予CREATE SYNONYM系统权限后，SCOTT用户在其dept表上创建方案同义词syn_dept。图1.3.1 创建方案同义词2. 创建公用同义词需要创建公用同义词时，用户必须具有CREATE PUBLIC SYNONYM系统权限。语法：CREATE OR REPLACE PUBLIC SYNONYM synonym_name schema.object;如图1.3.2所示，在被授予CREATE SYNONYM系统权限后，SCOTT用户在其dept

8、表上创建公用同义词pubsyn_dept。图1.3.2 创建公用同义词注意3.1.5 使用同义词1. 使用方案同义词使用方案同义词可分为方案用户使用和其他用户使用。（1）方案用户使用自己的方案同义词。方案用户具有对象的所有权限，所以可以像使用原对象一样使用该对象的同义词。SCOTT用户使用自己方案中syn_dept方案同义词的示例如图1.3.3所示。图1.3.3 方案用户使用自己的方案同义词（2）其他用户使用另一个用户创建的方案同义词。由于方案同义词是私有的，所以其他用户无法直接使用另一个用户所创建的方案同义词，但能够以在方案同义词前面加上方案名作为前缀的方式来使用其他用户所创建的方案同义词，

9、如图1.3.4所示。图1.3.4 其他用户使用另一个用户创建的方案同义词（2）通过自己的方案同义词访问其他方案中的对象。通过在自己的方案中创建指向其他方案中对象的方案同义词，SCOTT用户给HR用户授予dept表上的SELECT对象权限，HR用户在自己的方案中创建一个指向SCOTT用户的dept表的syn_scott_dept方案同义词，则HR用户可以通过自己的syn_scott_dept方案同义词来查询SCOTT用户的dept表，如图1.3.5所示。图1.3.5 通过自己的方案同义词访问其他方案中的对象2. 使用公用同义词与方案同义词不同，如果使用公用同义词来访问其他方案中的对象，就不需要在

10、该公用同义词前面添加方案名。但是，如果用户没有被授予访问该公用同义词所引用的对象的对象权限，仍然不能使用该公用同义词，如图1.3.6所示。图1.3.6 使用公用同义词3.1.6 删除同义词删除同义词后，同义词所引用的基础对象不会受影响。1. 删除方案同义词用户能够删除自己方案中的任何方案同义词，但若需要删除其他用户方案中的方案同义词，必须拥有DROP ANY SYNONYM系统权限。语法：DROP SYNONYM synonym_name2. 删除公用同义词需要删除公用同义词时，用户必须具有DROP PUBLIC SYNONYM系统权限。语法：DROP PUBLIC SYNONYM synon

11、ym_name3.2 序列3.2.1 序列概述序列（SEQUENCE）是一个命名的顺序编号生成器，它能以串行的方式生成一系列顺序整数。与视图、约束相似，序列也不占用实际的存储空间，而只在数据字典中保存序列的定义。引入序列的理由或序列的主要用途在于以下几点。1. 主键、外键值应用需求在某些表中，主键可能需要由几个字段组成，如果将这些字段合起来作为主键，就会使问题复杂化。因此，可以使用简单的顺序号来代替多个字段的主键标识记录，以简化子表对于父表的引用关系。如果在一个数据库应用中创建一个序列，使各个表都使用这个序列产生的整数作为主键、外键值，则会对数据库的设计、编程、各种主键和外键关系的建立都起到良

12、好的作用。2. 流水号应用需求在现实生活中，很多情况下都需要流水号。例如，商场打印销售单时需要将销售单按顺序编号，一般每张销售单对应一个编号，且编号是依次递增的。如果需要产生诸如此类的单据流水号，则可以借助序列来完成。3. 序列的生成与定义的内容序列由Oracle服务器端产生，所有的序列都出自一处。如果有多个用户同时向序列提出申请，则序列会按照串行机制依次处理各个用户的请求，绝不会生成两个相同的整数。而且序列生成下一个整数的速度很快，即使在并发用户数量很多的联机事务处理环境中，也不会产生明显的延迟。说明3.2.2 创建序列需要在自己的方案中创建序列时，用户必须具有CREATE SEQUENCE

13、系统权限；需要在其他的方案中创建序列时，用户必须具有CREATE ANY SEQUENCE系统权限。语法：CREATE SEQUENCE sequnce_name START WITH n1 INCREMENT BY n2 MAXVALUE n3 | NOMAXVALUE MINVALUE n4 | NOMINVALUE CACHE n5 | NOCACHE CYCLE | NOCYCLE ORDER;其中：（1）START WITH：指定要生成的第一个序列号。（2）INCREMENT BY：用于指定序列号之间的间隔，默认值为1。如果n为正值，则生成的序列将按升序排列；如果n为负值，则生成的序

14、列将按降序排列。（3）MAXVALUE：指定序列可以生成的最大值，必须大于或等于START WITH中的n1，并且必须大于MINVALUE中的n4。（4）NOMAXVALUE：用于指定序列没有上限，最大值可达1027。（5）MINVALUE：指定序列可以生成的最小值，必须小于或等于START WITH中的n1，并且必须小于MAXVALUE中的n3。（6）NOMINVALUE：用于指定序列没有下限，最小值可达10-26。（7）CACHE：用于指定在高速缓存中可以预分配的序列号个数，默认为20。（8）NOCACHE：用于指定在高速缓存中不预先分配序列号，即序列生成器会每次生成一个序列号，这是默认值

15、。（9）CYCLE：用于指定在达到序列的最大值或最小值后是否循环，即再次从n1开始生成序列号。默认为NOCYCLE，不循环。（10）ORDER：用于指定按顺序生成序列号。在图1.3.7中，SCOTT用户创建了一个seq_id序列，利用这个序列可以为其方案中的表生成唯一的整数。图1.3.7 创建序列该序列从300开始，每次增量为1，最大值为，每次生成10个序列号，到达最大值后不循环。3.2.3 使用序列1. NEXTVAL和CURRVAL列在引用序列时，需要使用到序列的NEXTVAL和CURRVAL两个列：NEXTVAL列返回序列生成的下一个值，CURRVAL列返回序列生成的当前值。2. 序列的

16、初始化在第一次引用CURRVAL列之前，必须引用过一次NEXTVAL列，用于初始化序列的值，否则会出现错误提示，如图1.3.8所示。图1.3.8 序列的初始化3. 在SQL语句中使用序列在SQL语句中，可以直接使用引用序列的值。图1.3.9中先创建了一张tb_test表，然后使用seq_id序列生成的整数作为该表的主键值。图1.3.9 在SQL语句中使用序列3.2.4 更改序列需要更改自己方案中的序列时，用户必须具有ALTER SEQUENCE系统权限；需要更改其他方案中的序列时，用户必须具有ALTER ANY SEQUENCE系统权限。需要注意的是，不能修改序列中的起始值，如果要修改起始值，

17、必须删除序列再重建。更改序列包括：（1）修改MAXVALUE和MINVALUE。（2）修改INCREMENT增量值。（3）修改缓存中的序列的数目。语法：ALTER SEQUENCE sequnce_nameINCREMENT BY n2MAXVALUE n3 | NOMAXVALUEMINVALUE n4 | NOMINVALUECACHE n5 | NOCACHECYCLE | NOCYCLE图1.3.10中演示了对seq_id序列进行更改的过程。图1.3.10 更改seq_id序列思考3.2.5 删除序列需要删除自己方案中的序列时，用户必须具有DROP SEQUENCE系统权限；需要删除其

18、他方案中的序列时，用户必须具有DROP ANY SEQUENCE系统权限。语法：DROP SEQUENCE sequence_name图1.3.11中的代码用于删除上述创建的序列seq_id。图1.3.11 删除序列3.3 视图3.3.1 视图概述视图（VIEW）是由SELECT子查询语句定义的一个逻辑表，只有定义而无数据，是一个“虚表”。视图是查看和操作表数据的一种方法。使用视图有诸多优点，如提供各种数据表现形式、提供某些数据的安全性、隐藏数据的复杂性、简化查询语句、执行特殊查询、保存复杂查询等。在许多方面，视图的使用和管理都与表相似，例如都可以被创建、更改和删除，都可以通过它们来操作数据库

19、中的数据。除了SELECT之外，视图在INSERT、UPDATE和DELETE方面受到了某些限制。3.3.2 创建视图需要在当前方案中创建视图时，用户必须具有CREATE VIEW系统权限；需要在其他方案中创建视图时，用户必须具有CREATE ANY VIEW系统权限。视图的拥有者必须被明确授予访问在视图定义中所参考的所有基础对象的权限。例如，如果视图的拥有者只具有在SCOTT方案的emp表上INSERT对象的权限，则该视图仅能用于在emp表中插入新行，而不能进行SELECT、UPDATE、DELETE等操作。语法：CREATE OR REPLACE FORCE VIEW view_name(

20、alias1,alias2.)AS select_statementWITH CHECK OPTION CONSTRAINT constraint WITH READ ONLY;其中：（1）OR REPLACE：如果视图已经存在，该选项将重新创建该视图。（2）FORCE：无论基表是否存在，都将创建视图。（3）view_name：指定创建视图时的名称。（4）alias：指定由视图的查询所选择的表达式或列的别名。别名的数目必须与视图所选择的表达式的数目匹配。（5）select_statement：创建视图时的SELECT语句。（6）WITH CHECK OPTION：在使用视图时，检查涉及的数据是

21、否能通过SELECT子查询的WHERE条件，否则不允许操作并返回错误提示。（7）WITH READ ONLY：创建的视图只能用于查询，而不能用于更改数据。该子句不能与ORDER BY子句同时存在。在图1.3.12中，在给SCOTT用户授予CREATE VIEW系统权限之后，SCOTT用户就能在自己的方案中创建基于emp表的视图v_scott_1。图1.3.12 创建视图在图1.3.13中，在创建视图时，可以指定WITH READ ONLY选项，使该视图只用于执行SELECT语句，而禁止执行INSERT、UPDATE和DELETE语句。图1.3.13 创建具有WITH READ ONLY选项的视

22、图在图1.3.14中，在创建视图时，还可以指定WITH CHECK OPTION选项，该选项用于在视图上定义CHECK约束，并可以使用CONSTRAINT选项指定约束的名称。之后在此类视图上执行DML操作时，就要求所操作的数据必须满足SELECT子查询中的WHERE条件。图1.3.14 创建WITH CHECK OPTION的视图3.3.3 强制创建视图正常情况下，如果基本表不存在，则创建视图会失败；但如果创建视图的语句没有语法错误，则只要使用FORCE选项即可创建该视图，这种强制创建的视图被称为带有编译错误的视图。此时，这种视图处于失效状态，不能执行该视图，但之后随着基础表的创建，该视图就可

23、以正常运行了。图1.3.15将演示强制创建视图的过程。解释图1.3.15 强制创建视图3.3.4 在连接视图上执行DML操作对于在视图上进行的所有DML操作，最终都会在基础表的数据上完成。可以像对普通表一样对视图进行SELECT操作，但如果需要对视图进行更新（包括UPDATE、DELETE、INSERT）操作，则会受到某些限制，即并非在所有的视图上都可以执行全部的DML操作。对于一个基于基础表的简单视图而言，它只是简单地去掉了基础表中的某些记录或某些列，但如果视图中包括基础表的键（主键、外键），则总可以在其上进行DML操作。与简单视图相对应，当一个视图基于多个基础表（或视图）时，即定义视图的查

24、询是一个连接查询，这类视图被称为连接视图。对于连接视图，有些可以更新，但有些不能更新。1. 可更新连接视图当创建连接视图的SELECT子句满足以下条件时，连接视图是可更新的：（1）不包含集中运算符（UNION、UNION ALL、INTERSECT、MINUS等）。（2）不包含DISTINCT关键字。（3）不包含GROUP BY、ORDER BY、CONNECT BY 或START WITH子句。（4）不包含子查询。（5）不包含分组函数。（6）需要更新的列不是由列表达式定义的。（7）表中所有的NOT NULL列均属于该视图。但是，这只是最基本的条件，即并非在可更新连接视图中就可以进行更新操作了

25、，还需要遵守更新标准，也就是只能对“键值保存表”进行更新。2. 键值保存表如果连接视图中的一个基础表的主键（主键、唯一键）在它的视图中仍然存在，则称这个基础表为键值保存表。创建关于SCOTT方案中DEPT表和EMP表的连接视图，如图1.3.16所示。图1.3.16 对连接视图的键值保存表进行更新在EMP表中，EMPNO列是该表上的主键；而在DEPT表中，DEPTNO是该表中的主键。EMPNO可以同时作为视图v_dept_emp_1视图的主键，但DEPTNO不能作为该视图的主键，所以EMP是键值保存表，而DEPT表则不是。值得注意的是，对于在连接视图上的任何INSERT、UPDATE或DELET

26、E操作，一次只能对视图中的一个键值保存表进行更新。注意3.3.5 查询视图信息1. 查询视图的可更新列可以使用USER_UPDATABLE_COLUMNS数据字典来查询当前用户方案中所有的表以及视图中所有可修改的列。图1.3.17中的代码用于查询v_dept_emp_1视图中所有可更新的列。图1.3.17 查询v_dept_emp_1视图中所有可更新的列2. 查询视图的定义信息可以使用USER_VIEWS数据字典来查询当前方案中视图的定义信息。图1.3.18中的代码用于查询v_dept_emp_1视图的定义信息。图1.3.18 查询v_dept_emp_1视图的定义3.3.6 删除视图可以删除

27、当前模式中的各种视图，需要删除其他方案中的视图时，必须拥有DROP ANY VIEW系统权限。语法：DROP VIEW view_name图1.3.19中的代码用于删除上述创建的v_dept_emp_1视图。图 1.3.19 删除视图3.4 索引3.4.1 索引概述索引是一种与表相关的可选的方案对象。通过在表中的一个或多个列上创建索引，就能为数据的检索提供快捷的存取路径，减少查询时的硬盘I/O操作，加快数据的检索速度。与其他具有独立存储结构的方案对象类似，索引需要占用实际的存储空间。一旦创建了索引，在表上执行DML操作时，Oracle就会自动地对索引进行维护，并且由Oracle决定何时使用索引

28、，用户完全不需要考虑在SQL语句中指定使用哪个索引以及如何使用索引。如果将表看成一本书，则索引的作用类似于书中的目录。需要在表中查询指定的记录时，在没有索引的情况下，必须遍历整张表中的记录；但如果存在索引，则只需要先在索引中找到符合查询条件的索引列值，然后通过保存在索引中的ROWID即可快速找到表中对应的记录。因此，为表建立索引能够减少查询操作的时间并减少I/O操作的开销。3.4.2 创建索引可以使用CREATE INDEX命令在一列或若干列的组合上创建索引。需要在自己的方案中创建索引时，必须拥有CREATE VIEW INDEX系统权限；需要在其他用户的方案中创建索引时，必须拥有CREATE

29、 ANY INDEX系统权限。语法：CREATE INDEX index_name on table_name (columm list) TABLESPACE tablespace_name其中：（1）index_name：指定所创建的索引名。（2）table_name：指定创建索引的表名。（3）column_list：指定在表上创建索引的列名列表，可以基于多列创建索引。（4）tablespace_name：为索引指定表空间。在创建索引时，会对表进行扫描，对索引列的数据进行排序，为索引分配存储空间，将索引的定义信息存储到数据字典中。创建后的索引完全由Oracle自动管理、维护和使用。图1.3

30、.20中的代码用于为SCOTT方案中的emp表创建基于sal的索引。图1.3.20 创建索引即使在表中创建了索引，Oracle也不是机械地为该表上的所有查询都使用索引，而是根据查询的具体情况决定是否使用索引。在运行查询语句之前，Oracle一般需要对其进行优化。优化的目的是找到运行该查询语句的最佳途径。Oracle会使用两种优化器：RBO优化器（Rule Based Optimizer，基于规则的优化器）、CBO优化器（Cost Based Optimizer，基于开销的优化器）。优化器会将使用全表扫描所需的资源开销与使用索引所需的资源开销进行对比，如果使用全表扫描所需的资源开销更节省，则不会

31、使用索引。使用全表扫描所需的资源与表中数据量的大小密切相关。一般而言，当表中数据量达到一定数量时，优化器才会考虑使用索引。注意以下通过一个示例来说明索引可以大大提高查询的效率，步骤如下：1. 创建用于测试的表以SYS方案中的dba_objects表为参考数据，在SCOTT方案中创建一张用于测试的tb_idx_test表，如图1.3.21所示。为了使SCOTT用户查询SYS方案中的dba_objects表，必须首先为SCOTT用户赋予SELECT对象权限。图1.3.21 创建用于测试索引的大数据表2. 通过查询查看执行计划以object_name为查询条件，查询tb_idx_test表中的记录，并显示执行计划，如图1.3.22所示。图1.3.22 查看查询执