第7章 表--《Oracle数据库基础与应用教程》书籍教案

1、人民邮电出版社出版人民邮电出版社出版 於岳编写於岳编写作者联系方式：作者联系方式：本书全面讲述了Oracle数据库的日常管理工作内容。全共14章，包含Oracle简介和安装、简介和安装、Oracle客户端、管理客户端、管理Oracle环境、环境、Oracle体系结构、管理体系结构、管理Oracle存储结构、存储结构、SQL语言、表、约束、视图、同义词和序列、索引、语言、表、约束、视图、同义词和序列、索引、实现数据库安全、实现数据库安全、Data Pump数据导出和导入、数据数据导出和导入、数据库备份和恢复。库备份和恢复。每章结尾提供适量的选择题、简答题和操作题，通过练习和操作实践，帮助读者巩固

2、所学内容。本书适合Oracle爱好者、Oracle工程师、数据库管理员、培训机构以及大专院校计算机专业教师和学生使用。 表简介表简介7.1OracleOracle内置数据类型内置数据类型7.2创建表创建表7.3修改表修改表7.47.1.1 什么是表什么是表 表是数据库中一个非常重要的方案表是数据库中一个非常重要的方案对象，被用来存储数据，是对象，被用来存储数据，是Oracle数据数据库中数据组织的基本单位。库中数据组织的基本单位。 一个表定义包括一个表名和列的集一个表定义包括一个表名和列的集合。列标识表中描述的实体的属性，比合。列标识表中描述的实体的属性，比如员工表中的如员工表中的employ

3、ee_id列是指员工实列是指员工实体的员工体的员工ID属性。属性。 当创建表的时候，一般会为每一个当创建表的时候，一般会为每一个列指定列名、数据类型、大小、小数位列指定列名、数据类型、大小、小数位数、默认值等。数、默认值等。 如如employee_id列指定列指定NUMBER(6)数据类型，表明该列只能包含高达数据类型，表明该列只能包含高达6位数位数大小的数字数据。大小可以由数据类型大小的数字数据。大小可以由数据类型预先确定。预先确定。 一个表只能包含一个虚拟列，虚拟一个表只能包含一个虚拟列，虚拟列不占用磁盘空间。列不占用磁盘空间。 数据库通过计算一组用户指定的表数据库通过计算一组用户指定的表

4、达式或函数生成派生需求上的虚拟列的达式或函数生成派生需求上的虚拟列的值。值。 表创建好之后，可以使用表创建好之后，可以使用SQL语句语句进行插入、查询、删除和更新行操作。进行插入、查询、删除和更新行操作。 行是列信息的集合，对应于一个表行是列信息的集合，对应于一个表中的记录，比如员工表描述指定员工的中的记录，比如员工表描述指定员工的所有属性。所有属性。 在在Oracle数据库中，表可以分为以数据库中，表可以分为以下几种类型。下几种类型。1普通表普通表2索引组织表索引组织表3临时表临时表4外部表外部表5分区表分区表6簇表簇表 创建表的时间需要为列指定数据类型，创建表的时间需要为列指定数据类型，不

5、同的数据类型存储不同类型的数据。不同的数据类型存储不同类型的数据。 Oracle内置数据类型有字符数据类型、内置数据类型有字符数据类型、数字数据类型、日期和时间数据类型、二进数字数据类型、日期和时间数据类型、二进制数据类型和大对象数据类型。制数据类型和大对象数据类型。 在在Oracle数据库中，字符数据类型存数据库中，字符数据类型存储数字和字母等组成的字符，常用的字符数储数字和字母等组成的字符，常用的字符数据类型有据类型有CHAR、NCHAR、VARCHAR2、NVARCHAR2和和VARCHAR。1CHAR(size BYTE | CHAR)2NCHAR(size)3VARCHAR2(siz

6、e BYTE | CHAR)4NVARCHAR2(size)5VARCHAR 在在Oracle数据库中，数字数据类型数据库中，数字数据类型所有的数值数据，如整数、分数、双精所有的数值数据，如整数、分数、双精度数和浮点数等，常用的数字数据类型度数和浮点数等，常用的数字数据类型有有NUMBER和和FLOAT。 以十进制格式进行储存的数字数据类型，以十进制格式进行储存的数字数据类型，p是是指精度（指数字的总位数），指精度（指数字的总位数），s是指刻度范围（小是指刻度范围（小数点右边的位数）。数点右边的位数）。p的范围可以是的范围可以是138，s的范围的范围可以是可以是-84127。 s的默认值是的默

7、认值是0，如，如NUMBER(5)、NUMBER。 在指定精度和刻度范围的时候需要遵循以在指定精度和刻度范围的时候需要遵循以下规则。下规则。当当s0时，时，s表示小数点右边的数字的个数。表示小数点右边的数字的个数。当一个数字的整数部分的长度当一个数字的整数部分的长度 p-s 时，时，Oracle就会报错。就会报错。当一个数字的小数部分的长度当一个数字的小数部分的长度 s 时，时，Oracle就就会舍入。会舍入。当当sp时，时，p表示小数点后第表示小数点后第s位向左最多位向左最多可以有多少位数字，如果大于可以有多少位数字，如果大于p则则Oracle报错，小数点后报错，小数点后s位向右的数字被舍入

8、。位向右的数字被舍入。 具有精度具有精度p的的NUMBER数据类型的子类型。数据类型的子类型。 一个一个FLOAT值，在内部表示为值，在内部表示为NUMBER。精度精度p的范围可以是的范围可以是1126的二进制数字。的二进制数字。 一个一个FLOAT值是值是122字节。字节。 简称简称INT，相当于刻度范围为，相当于刻度范围为0的的NUMBER数据类型。数据类型。 在在Oracle数据库中，日期和时间数据类型数据库中，日期和时间数据类型存储日期和时间值，常用的日期和时间数据类存储日期和时间值，常用的日期和时间数据类型有型有DATE、TIMESTAMP和和TIMESTAMP WITH TIME

9、ZONE。 在在Oracle数据库中，二进制数据类数据库中，二进制数据类型存储非结构化数据，常用的二进制数型存储非结构化数据，常用的二进制数据类型有据类型有RAW和和LONGRAW。 非结构化数据的可变二进制数据，非结构化数据的可变二进制数据，必须为必须为RAW指定指定size，最大尺寸为，最大尺寸为2000字节。字节。 非结构化数据的可变二进制数据，非结构化数据的可变二进制数据，尺寸可达尺寸可达2 GB。 不建议使用不建议使用LONG RAW数据类型，数据类型，可以使用可以使用BLOB数据类型来代替。数据类型来代替。 在在Oracle数据库中，表中每一行数数据库中，表中每一行数据在数据库中的

10、存储位置可以由行的物据在数据库中的存储位置可以由行的物理地址和逻辑地址表示，这些地址存储理地址和逻辑地址表示，这些地址存储在表的在表的ROWID伪列中，常用的行数据类伪列中，常用的行数据类型有型有ROWID和和UROWID。 以以64位为基数的字符串，表示一条位为基数的字符串，表示一条记录在表中相对唯一的地址值。记录在表中相对唯一的地址值。 该数据类型主要是该数据类型主要是ROWID伪列返回伪列返回的值。的值。 以以64位为基数的字符串，表示索引位为基数的字符串，表示索引组织表的一行的逻辑地址。组织表的一行的逻辑地址。 可选的大小是可选的大小是UROWID类型的列的类型的列的大小。大小。 最大

11、尺寸和默认尺寸为最大尺寸和默认尺寸为4000字节。字节。 在在Oracle数据库中，大对象数据类型存储数据库中，大对象数据类型存储大对象（大对象（Large OBject，LOB），），LOB是为图是为图像、视频、音频、文本、空间数据设计的，大像、视频、音频、文本、空间数据设计的，大对象最大尺寸是（对象最大尺寸是（4GB-1）*（数据库块大小），（数据库块大小），一般数据库块大小为一般数据库块大小为8K，常用的大对象数据类，常用的大对象数据类型有型有CLOB、NCLOB、BLOB、BFILE和和LONG。1CLOB2NCLOB3BLOB4BFILE5LONG CREATE TABLE语句用于创

12、建表。语句用于创建表。 要在自己的方案中创建表，必须要要在自己的方案中创建表，必须要拥有拥有CREATE TABLE系统权限。系统权限。 要在另一个用户的方案中创建表，要在另一个用户的方案中创建表，必须要拥有必须要拥有CREATE ANY TABLE系统系统权限。权限。 使用使用ALTER TABLE语句来改变表的定语句来改变表的定义。义。 要修改表，该表必须在自己的方案中，要修改表，该表必须在自己的方案中，或者必须在表上拥有或者必须在表上拥有ALTER对象权限，或对象权限，或者必须要拥有者必须要拥有ALTER ANY TABLE系统权限系统权限。 指定指定READ ONLY把表设置为只读模把

13、表设置为只读模式。式。 当表处于只读模式时，不能执行任何当表处于只读模式时，不能执行任何DML语句或语句或SELECT . FOR UPDATE语语句来影响表。只要不修改任何表中的数据句来影响表。只要不修改任何表中的数据就可以执行就可以执行DDL语句。指定语句。指定READ WRITE把表设置为读把表设置为读/写模式。写模式。 使用使用PARALLEL在表上为查询和在表上为查询和DML操作更改默认并行度。操作更改默认并行度。 使用使用ENABLE ALL TRIGGERS或或DISABLE ALL TRIGGERS来启用或禁来启用或禁用与表相关联的所有触发器。用与表相关联的所有触发器。 要启用

14、或禁用触发器，触发器必须在要启用或禁用触发器，触发器必须在自己的方案中，或者必须要拥有自己的方案中，或者必须要拥有ALTER ANY TRIGGER系统权限。系统权限。 如果在操作过程中表被锁定，如果在操作过程中表被锁定，Oracle数据库只允许在表上进行数据库只允许在表上进行DDL操作。操作。 在在DML操作上不需要这样的表锁定。操作上不需要这样的表锁定。 Oracle数据库启用表锁定之前，所有数据库启用表锁定之前，所有目前正在执行的事物必须提交或回滚；指目前正在执行的事物必须提交或回滚；指定定DISABLE TABLE LOCK禁用表锁定，禁用表锁定，从而防止在表上进行从而防止在表上进行D

15、DL操作。操作。 当目标表的表锁定被禁用时，不会执当目标表的表锁定被禁用时，不会执行并行行并行DML操作。操作。 使用使用DEALLOCATE UNUSED子句子句明确地释放表的末尾未被使用的空间，明确地释放表的末尾未被使用的空间，以供在表空间中其他段的空间使用。以供在表空间中其他段的空间使用。 禁用表锁定以后，不能解除分配未禁用表锁定以后，不能解除分配未使用的空间。使用的空间。 使用使用SET UNUSED子句以标记表中的列为子句以标记表中的列为未使用。未使用。 无法将属于无法将属于SYS的表中的列标记为未使用。的表中的列标记为未使用。 对于内部的堆组织表，指定对于内部的堆组织表，指定SET

16、 UNUSED子句实际上并没有从表中的每一行中删除目标子句实际上并没有从表中的每一行中删除目标列。列。 可以稍后通过发出可以稍后通过发出ALTER TABLE . DROP UNUSED COLUMNS语句删除语句删除标记为未使用的列。标记为未使用的列。USER_UNUSED_COL_TABS、ALL_UNUSED_COL_TABS或或DBA_UNUSED_COL_ TABS数据字典可以用来列出包含未使数据字典可以用来列出包含未使用列的所有表。用列的所有表。 这些数据字典中的这些数据字典中的COUNT列显示在表列显示在表中未使用的列的数量。中未使用的列的数量。 当修改表的时候，可以在表中添加、

17、当修改表的时候，可以在表中添加、修改和删除列。修改和删除列。1添加列添加列2修改列修改列3删除列删除列 COMMENT ON语句用于为表添加语句用于为表添加描述性的注释信息，这样就可以充分地描述性的注释信息，这样就可以充分地描述表的内容或用法。描述表的内容或用法。 注释内容可以长达注释内容可以长达4000个字节，必个字节，必须存储在单引号内。须存储在单引号内。 使用使用MOVE子句可以将表移动到其子句可以将表移动到其他的表空间中。他的表空间中。 使用使用LOGGING和和NOLOGGING可可以更改表的日志记录属性，生成重做日以更改表的日志记录属性，生成重做日志记录。志记录。 使用使用COMP

18、RESS来指示来指示Oracle数据数据库是否压缩数据段来减少磁盘和内存的库是否压缩数据段来减少磁盘和内存的使用。使用。 随着数据库的增长规模，可以考虑随着数据库的增长规模，可以考虑压缩表。压缩表。 压缩表将节省磁盘空间，减少数据压缩表将节省磁盘空间，减少数据库缓冲区高速缓存的使用，并在读取时库缓冲区高速缓存的使用，并在读取时可以显著加快查询执行。可以显著加快查询执行。 使用使用SHRINK SPACE子句可以对表手动子句可以对表手动收缩空间，只适用于使用自动段管理的表空收缩空间，只适用于使用自动段管理的表空间中的段。间中的段。 在默认情况下，在默认情况下，Oracle数据库会压缩段，数据库会

19、压缩段，调整高水位，并立即释放空间。压缩段需要调整高水位，并立即释放空间。压缩段需要先进行行移动（先进行行移动（Row Movement），因此必须），因此必须在收缩表之前启用行移动。在收缩表之前启用行移动。 收缩表具有以下限制。收缩表具有以下限制。不支持对带有基于函数的索引的表进行段不支持对带有基于函数的索引的表进行段收缩。收缩。不能为压缩表指定不能为压缩表指定SHRINK SPACE子句。子句。 使用使用RENAME COLUMN子句重命子句重命名表中的列。名表中的列。 新的列名必须与表中其他列的名称新的列名必须与表中其他列的名称不能相同。不能相同。 要对表进行重命名，可以通过要对表进行重

20、命名，可以通过RENAME和和ALTER TABLE . RENAME TO语句来实现。语句来实现。1RENAME语句语句2ALTER TABLE . RENAME TO语句语句 TRUNCATE TABLE语句用于从表语句用于从表中删除所有数据。中删除所有数据。 使用这种方法删除数据，可以比删使用这种方法删除数据，可以比删除并重新创建表更有效。除并重新创建表更有效。 TRUNCATE TABLE语句执行以后语句执行以后不能回滚。不能回滚。 DROP TABLE语句用于删除表，将表移动语句用于删除表，将表移动到回收站或从数据库中完全删除表和它的所有到回收站或从数据库中完全删除表和它的所有数据。

21、数据。 对于外部表，此语句只删除数据库中表的对于外部表，此语句只删除数据库中表的元数据。元数据。 它的实际数据并没有影响，因为它驻留在它的实际数据并没有影响，因为它驻留在数据库之外。数据库之外。 要删除表，该表必须在自己的方案中，或要删除表，该表必须在自己的方案中，或者必须要拥有者必须要拥有DROP ANY TABLE系统权限。系统权限。 在删除表的时候，将会执行以下操在删除表的时候，将会执行以下操作。作。删除表中所有的数据。删除表中所有的数据。删除与该表有关的索引、触发器和对象删除与该表有关的索引、触发器和对象权限。权限。从数据字典中删除表的定义。从数据字典中删除表的定义。回收分配给表的存储

22、空间。回收分配给表的存储空间。7.7.1 使用使用OEM创建表创建表 使用使用Oracle Enterprise Manager按以按以下步骤创建表。下步骤创建表。 （1）在）在Oracle Enterprise Manager页面中，依页面中，依次单击【方案】次单击【方案】【数据库对象】【数据库对象】【表】，【表】，如图如图7-1所示，单击【创建】按钮。所示，单击【创建】按钮。图图7-1 7-1 表表（2）在图）在图7-2所示页面中，指定表组织，在所示页面中，指定表组织，在此选择【标准（按堆组织）】单选框，然此选择【标准（按堆组织）】单选框，然后单击【继续】按钮。后单击【继续】按钮。图图7-

23、2 7-2 创建表：表组织创建表：表组织 （3）在图）在图7-3所示【一般信息】页面中，所示【一般信息】页面中，按以下要求输入内容。按以下要求输入内容。图图7-3 7-3 【一般信息一般信息】页面页面（4）在图）在图7-4所示【存储】页面中，指定所示【存储】页面中，指定存储信息，如表空间、区数、空间使用存储信息，如表空间、区数、空间使用情况、空闲列表、事物处理数、缓冲池情况、空闲列表、事物处理数、缓冲池和压缩选项等内容。和压缩选项等内容。图图7-4 7-4 【存储存储】页面页面（5）在图）在图7-5所示【选项】页面中，指定所示【选项】页面中，指定启用行移动、并行和高速缓存选项，然启用行移动、并

24、行和高速缓存选项，然后单击【确定】按钮。后单击【确定】按钮。图图7-5 7-5 【选项选项】页面页面 对表进行收缩段操作可以压缩空间碎对表进行收缩段操作可以压缩空间碎片，然后将恢复的空间释放到表空间，以片，然后将恢复的空间释放到表空间，以此节约空间使用。此节约空间使用。 使用使用Oracle Enterprise Manager按以下步按以下步骤收缩段。骤收缩段。 （1）在图）在图7-6所示页面中，搜索方案所示页面中，搜索方案SYS中的表中的表TABLE_1。选择表。选择表TABLE_1，在【操作】下，在【操作】下拉框中选择【收缩段】，然后单击【开始】按拉框中选择【收缩段】，然后单击【开始】按

25、钮。钮。图图7-6 搜索表搜索表（2）在图）在图7-7所示页面中，指定收缩选项，在所示页面中，指定收缩选项，在此选择【压缩段并释放空间】单选框，然后此选择【压缩段并释放空间】单选框，然后单击【继续】按钮。单击【继续】按钮。图图7-7 7-7 收缩段收缩段 （3）在图）在图7-8所示页面中，指定作业名称所示页面中，指定作业名称和调度选项，然后单击【提交】按钮。和调度选项，然后单击【提交】按钮。图图7-8 7-8 调度调度 （4）在图）在图7-9所示页面中，显示已经成功所示页面中，显示已经成功创建作业，可以看到调度程序作业正在创建作业，可以看到调度程序作业正在运行。运行。图图7-9 7-9 调度程序作业调度程序作业 使用使用Oracle Enterprise Manager按以按以下步骤删除表。下步骤删除表。 （1）在图）在图7-10所示页面中，搜索方案所示页面中，搜索方案SYS中的表中的表TABLE_1。选择表。选择表TABLE_1，然，然后单击【删除】按钮。后单击【删除】按钮。图图7-10 7-10 搜索表搜索表（2）在图）在图7-1