第四章Oracle数据库对象

第04章 数据库对象

本章课时要求：6课时 本章内容提纲：

表(2课时)

表的概念、基本数据类型

表的种类：基本表，索引表（IOT），聚簇表等 创建表、修改表、删除表、表信息 簇

索引簇：概念、创建索引簇、创建聚簇表、建立簇索引、维护索引簇 散列簇：概念、创建散列簇、维护散列簇 索引(2课时)

索引概念、索引分类、建立索引 修改索引、删除索引、索引信息 视图

视图概念、建立视图、维护视图、视图信息 序列(2课时)

序列概念、创建序列、使用序列、修改序列、删除序列、序列信息 数据库链接

概念、建立、删除、信息 同义词

同义词概念、作用、分类、建立、删除、信息 快照

概念、实例

- 1 -

第04章 数据库对象

4 第04章 数据库对象

表、索引、簇、视图、序列、同义词、链接、快照是Oracle重要的数据库对象。本章具体介绍各种数据库对象的建立、维护等操作。

每个数据库对象都为某个用户所有。用户拥有的全部对象称为模式(schema)，模式对象用模式对象名”用户名.对象名”表示。同一用户的对象名必须唯一，不同用户可有相同的对象名。

例如：scott用户和system用户都创建了temp表，它们完整的模式对象名是scott.temp和system.temp；如果scott访问system的temp表，须使用完整的模式对象名system.temp；如果使用自己的对象，既可以使用完整名scott.temp，也可以使用对象名temp。

4.1 表

4.1.1 表的概念

表(TABLE)是数据库的基本数据存储单元，用于存储用户数据。表是由行(row)、列(column)构成，其中行称为记录(record)，列称为字段(field)；每个列包括列名、数据类型(有的需要指定数据宽度，如果是数值类型，还有精度和刻度)、约束、默认值等。所有列组成表结构(或表定义)。表的形式如下：

sno 2050101 表

2050103 2050107 2050114 sname 柴皓 戈彬 李红梅 欧阳芳 ssex 男 男 女 女 sage 20 21 20 21 saddress 湖南 江西 广东 湖北 行 表结构

4.1.2 基本数据类型

在定义表的每一列时，要给定列名(column)，在定义列名时，一般采用英文或汉语拼音的缩写，避免直接使用汉字。在一个系统中，列名的命名要遵守一致的规范，以便相互理解列的含义和作用。

在定义列时，同时要定义列的数据类型(datetype)，如果有固定宽度的数据类型，直接指定数据类型，不必定义数据宽度。如日期性date，固定为7字节，不必定义宽度。

- 2 -

第04章 数据库对象

Oracle的基本数据类型及宽度范围列表如下：

数据类型 char(n) varchar2(n) number(p,s) 宽度范围 n=1～2000 n=1～4000 p=1～38 s=-84～127 定长字符串。 变长字符串：根据实际的长度存储。 数值型： m表示精度(precision有效位，不含小数点和符号) s表示刻度(小数位数，-或0表示整数)。 date (固定7B) 日期和时间，从公元前4712年1月1日～公元4712年12月31的合法日期，包括时间：时分秒。 blob clob nclob bfile long (最大4GB) (最大4GB) (最大4GB) (最大4GB) (最大2GB) 内部保存的二进制大对象，可读写查。 内部保存的单字符大对象，可读写查。 内部保存的多字符大对象，可读写查。 外部保存的文件对象，可读查，不能写。 可变长字符列，不能最为查询等搜索的列。是一种较老的数据类型。 raw(n) n=1～2000 可变长二进制数据，保存较小的图形图像、音频视频文件或带格式的文档。是一种较老的数据类型。 long raw (最大2GB) 可变长二进制数据，保存较大的图形图像、音频视频文件或带格式的文档。同一张表中不能同时有long和long raw类型。是一种较老的数据类型。 说明 说明：

(1)char和varchar2的区别：

char是固定长度的字符串类型，不足定义长度，补空格；而varchar2(不要忘记后面2)是变长的字符串类型，以实际长度存储。如使用字段os_name存储’windows’字符串，定义char(10)存储的实际字符串为’windows ’(后有3个空格)，其长度为10。而定义varchar2(10)存储的实际字符串是’windows’，其长度是7。在比较字符串时，char类型的字符串比较时要去掉其后的空格(ltrim()函数)，如ltrim(os_name)=’windows’；或补足长度os_name=’windows ’。char类型有较快的存储速度，而varchar2可以节省存储空间，并方便字符串比较。

(2)存储大对象的数据类型：

- 3 -

第04章 数据库对象

大对象数据(Large OBject)是指大长度的字符数据或二进制数据。一般大对象列不能见索引或作为查询的条件。其中raw(n),long,long raw是较老的数据类型，在新系统中尽量不要采用。尽量使用clob、nclob、blob、bfile，使用时不设置其宽度，但其最大长度均为4GB。bfile的实际数据存储在操作系统的文件中，所以不能修改其数据。clob、nclob、blob(合称lob)的数据存放在数据库中，可以读写查询。如果lob实际数据长度小于4000字节，则存放在与表相同的段中；如果大于4000字节，则单独建立lob段，并将它们存放在lob段中。clob(character)存放单字节大字符，nclob存放多字节大字符，blob(binary)存放二进制的图形图像、音频视频文件或带格式的文本文件(如Word文件)等。

(3)行的物理位置rowid

Oracle中每一行(记录)，都有一个唯一的物理存储位置标识，称为rowid，用于快速存取行数据。对于没有主键(不满足实体完整性)的表，如果有相同的行，可用rowid区别。

rowid占用10B，显示为18个字符。格式如下：

数据对象号 文件号 数据块号 行号 数据对象号：建立表对象时，Oracle为改表分配的唯一数据对象号。

相对文件号：数据文件在表空间中的唯一标识。注意相对文件号与绝对文件号的区别，绝对文件号是数据文件在数据库中的唯一标识。

数据块号：数据文件中，行所在数据块的编号。 行号：行在数据块的序号。

rowid是每行都自动具有的列，而采用SELECT * FROM table_name时不查询该列的值，所以称为伪列。如果用rowid指明要查询该伪列，可显示每列的rowid。如下：

SQL>SELECT ROWID,sno,sname FROM students;

rowid是不好理解的18位符号串，可用dbms_rowid包中的函数解析具体的含义，如下： SQL>SELECT dbms_rowid.rowid_relative_fno(rowid) relative_fno,

dbms_rowid.rowid_block_number(rowid) block_number, dbms_rowid.rowid_row_number(rowid) row_number FROM students;

- 4 -

第04章 数据库对象

4.1.3 表的分类

表的种类较多，根据不同的应用环境，建立满足性能和需求的表。表的种类主要有： (1)基本表：一般形式的数据表，一个数据表对应一个数据段(Segment)。 (2)索引组织表：(Index Organization Table：IOT)数据表创建在索引中。 (3)临时表：存储在临时表空间的表，按会话或事务自动删除

(4)分区表：将一个大表按一定规则分成不同表空间的多个段，称为分区。 (5)聚簇表：将具有相同列的几个表存储在一个段中，以便加快联合查询的速度。 (6)LOB表：有大数据对象的表。

(7)对象表：行由对象(Object)组成的表。

(8)嵌套表：表的列中有许多行，表中有表(Nested)。

本节只讲述基本表、临时表、分区表的建立，索引组织表在讲述索引时介绍，在”簇”一节中单独讲述聚簇表，其它种类的表，可以参考Oracle9i相关书籍。

4.1.4 创建表

建立表的方式有SQL*Plus SQL 语句和Enterprise Manager Console(EMC)的GUI图形方式。 1.建立基本表

CREATE TABLE [user.]tablename

({column datatype [DEFAULT expn] [column_constraint]|[table_constraint]},...) [TABLESPACE tablespace_name]

[PCTFREE n] [PCTUSED n] [INITRANS n][MAXTRANS n] [STORAGE storage_clause] [RECOVERABLE|UNRECOVERABLE] [CACHE|NO CACHE] [ENABLE|DISABLE] [LOGGING|NOLOGGING] [MONITORING|NOMONITORING] [AS query]

- 5 -

第04章 数据库对象

格式约定：

大写：关键字，必须照样输入的字符。

小写：参数项，必须根据实际情况填写参数、对象名、操作序列等。 [] ：可选项，不写[]。

{} ：必选项，一般用在 | 的多项之间。不写{}。 | ：多选一，多项在{}或[]之间。不写|。

... ：前面项{}可以重复多项。前面有符号，是项分隔符；后面有符号，是项结束符。 语法说明：

(1)user：用户名，也叫模式，缺省为当前用户。

(2)tablename：表名，以字符打头，不超过30字符，不区分大小写，除非用引号。 (3)column：列名

(4)datatype：数据类型及宽度定义(参考“数据类型”) (5)DEFAULT expn：缺省值，插入时自动填写的数据

(6)TABLESPACE tablespace_name：表空间，缺省为用户的默认表空间 (7)[RECOVERABLE|UNRECOVERABLE]：是否可恢复

(8)[CACHE|NO CACHE]：是将数据放入SGA的保持缓冲区。 (9)[ENABLE|DISABLE]：约束是否有效。

(10)[LOGGING|NOLOGGING]：创建操作是否写入日志文件。 (11)[MONITORING|NOMONITORING]：是否收集并修改统计量。 (12)[AS query]：用查询语句查询的数据插入定义的表中。 (13)数据块使用率控制参数：

PCTFREE n：数据块中空闲百分比，修改数据时增大时，避免行迁移，默认为10％。 PCTUSED n：数据块中使用百分比，保证空间使用率，避免浪费，默认为40％。 (14)数据块事务共享控制参数：

INITRANS n：初始时可并行访问数据块的事务数(1－255)，默认为1。 MAXTRANS n：可并行访问数据块的最大事务数(1－255)，默认为255。

- 6 -

第04章 数据库对象

注意：数据块控制参数，在一般情况下，不重新设置，使用默认值。 (15)STORAGE：存储参数：

存储参数又叫区的控制参数，决定了段在扩大时区增加的方法。

参数 INITIAL NEXT MINEXTENTS MAXEXTENS PCTINCREASE OPTIMAL 描述 默认值 最小值 2 1 第一个区的大小，小于最小值取最小，字节 5 第二个区的大小，小于最小值取最小，字节 5 最小区数 最大区数（505，249） 1回退2 1回退2 121 1回退2 0 从第三个区开始，NEXT×（1＋?）回退不用 50 回滚段的最有参数（收回保留空间） 注意：由于Oracle 8i及以后版本提供了本地管理方式，逐步淘汰字典管理方式，不再在段一级设置存储参数，只需在建立表空间时指明本地管理方式即可：

SQL>CREATE TABLESPACE ... ENTENT MANAGEMENT LOCAL {AUTOLOCATE | UNIFORM n {K|M}} (16)column_constraint：列限制，在列的后面，仅对前面一列进行限制，列限制有： ①NULL|NOT NULL:限制是否为空。NULL允许为空(缺省)，NOT NULL不允许为空。 ②PRIMARY KEY:设置列为主键，该列不允许重复和不允许为空(NOT NULL)。 ③UNIQUE: 该列值不能重复，但可为NULL。

④FOREIGN KEY REFERENCES tablename(column):该列为外键，指明被参照表及其主键。 ⑤CHECK(cond_exp):限制某列的值，必须满足设置的条件及使条件表达式为TRUE的值。 (17)table_constraint：表限制，针对多个列或整个表的限制，不能作为列限制，只能作为表限制，表限制在列定义之后，收括号之前定义。另外，设置主键或唯一键后，自动建立索引，为了说明索引的名字，并将索引存放在指定表空间，主键和唯一键的限制也作为列索引。

①主键约束：

CONSTRAINT constraint_name PRIMARY KEY (column[,column...])

USING INDEX TABLESPACE tablespace_index

②唯一键约束：

CONSTRAINT constraint_name UNIQUE (column[,column...])

- 7 -

第04章 数据库对象

USING INDEX TABLESPACE tablespace_index

③外键约束：

CONSTRAINT foreign_key_name FOREIGN KEY (column[,column...])

REFERENCES [schema.]table(column[,column...]) [ON DELETE {DESCADE|SET NULL}]

REFERENCES 指明参照表和参照列。

ON DELETE 指明被参照表记录删除时，参照表中相应记录级联删除或对应列设置为NULL。 ④CHECK约束：只有使条件表达式的值为true时的记录才能插入。

CONSTRAINT check_name CHECK (cond_exp) 建立基本表举例：

例1：基本项：说明列定义、NULL|NOT NULL、缺省值、指定数据表空间 CREATE TABLE teacher(

teacher_id NUMBER(20) name sex

VARCHAR2(10) CHAR(2) DATE

NOT NULL, NOT NULL, DEFAULT ‘男’, NULL

birthday

)TABLESPACE USERS; 说明：

(1)表名为teacher。如果没有指明前面的用户，如scott.teacher，则为当前用户的表。 (2)NULL可以省略(缺省)，可为空，表示增加记录时，如果没有指明该列值，则填写NULL。 (3)NOT NULL 表示在增加记录时，必须指明该列值，即不能为空。

(4)DEFUALT 缺省值，表示增加记录时，如果没有指明该列值，则用缺省值代替。 (5)TABLESPACE数据表的表空间，如果省略，则存储在用户的默认表空间。

(6)SQL大小写不敏感，但书写时尽量按语法约定；列名、类型、约束尽量对齐，以便阅读。 (7)建立表、数据库等DDL语句，尽量先用文本编辑保存，以便调试修改，并备份。 例2：定义列约束

- 8 -

第04章 数据库对象

CREATE TABLE teacher(

teacher_id NUMBER(20) name dno sex

VARCHAR2(10) VARCHAR2(10) CHAR(2) DATE

NOT NULL PRIMARY KEY, UNIQUE,

FOREIGN KEY REFERENCES dept(dno), CHECK(sex IN (‘男’,’女’)),

birthday ); 说明：

(1)列约束与前面的列之间用空格分隔。

(2)一个表只能有一个主键，但可有多个唯一键。系统自动为主键和唯一键建立索引，为了明确索引的名称和存放的表空间，从而约束语句较长，一般使用表约束定义主键和唯一键。

(3)列定义的基本形式：

column_name datatype [DEFAULT defualt_value] [[NOT] NULL] [column_constraint] 例3：使用表约束 CREATE TABLE teacher(

teacher_id NUMBER(20) name dno sex

VARCHAR2(10), VARCHAR2(10), CHAR(2), DATE

NOT NULL,

birthday

CONSTRAINT teacher_id_pk PRIMARY KEY(teacher_id)

USING INDEX TABLESPACE indx, CONSTRAINT teacher_name_uq UNIQUE(name)

USING INDEX TABLESPACE indx,

CONSTRAINT teacher_sex_ck CHECK(sex IN (‘男’,’女’)), CONSTRAINT teacher_dno_fk FOREIN KEY(dept_id)

- 9 -

第04章 数据库对象

REFERENCES dept(dno) ); 说明：

(1)表限制可以任意位置，但一般放在后面；

(2)对于主键和唯一键的限制名与索引名称同名，使用USING INDEX TABLESPACE子句，指定索引所在表空间，将索引和表分离，以便提高I/O性能。

(3)限制名要能标识表名、列名。命令习惯如下：

主键限制名：

表名_字段名_pk；

唯一键限制名： 表名_字段名_uq； 外键限制名：

表名_字段名_fk；

CHECK限制名： 表名_字段名_ck；

(4)定义外键之前，要建立被参照表，并设置参照列为主键。 例4：使用复制建表

CREATE TABLE teacher PARALLEL 2 NOLOGGING AS

SELECT * FROM scott.teacher;

(1)AS使用SELECT查询数据建表，表结构与查询结构相同。 (2)PARALLEL n 并行度，使用n个并行服务进程进行数据加载操作。

(3)NOLOGGING 说明建表操作不记录在日志中，加快了建表操作，但不能恢复。 2.建立临时表

临时表(temporary table)是一种使用完后自动删除的表。建立临时表的语法； CREATE GLOBAL TEMPORARY TABLE tablename

({column datatype [DEFAULT expn] [column_constraint]|[table_constraint]},...) [AS query]

ON COMMIT {DELETE|PRESERVE} ROWS 说明：

(1)ON COMMIT DELETE ROWS：创建临时表后，插入数据的事务结束后自动删除插入的数据(事

- 10 -

第04章 数据库对象

务级删除)。

(2)ON COMMIT PRESERVE ROWS：创建临时表后，插入数据的事务结束后保留数据，该会话结束时自动删除数据(会话级删除)。

(3)临时表的定义(结构)对于所有会话都是可见的，但是数据只有插入数据的会话才是可见的，因为数据在会话或事务结束后删除了。

(4)临时表有很多限制：不能被分区、索引组织、分簇；不能指定外键进行引用完整性约束；不能指定表空间、存储参数等。

例5：建立临时表

CREATE GLOBAL TEMPORARY TABLE student_temp

AS SELECT * FROM student ON COMMIT PRESERVE ROWS; 3.建立索引组织表

索引组织表(IOT:Index Organization Table)。一般情况下，表和索引是分别使用表段和索引段分开存储的(详见索引一节)。但索引组织表是将表的行和索引数据存储在一起的，将表的数据行作为B树索引叶节点。

普通表的索引查询分2步：(1)在索引中根据索引值查询相应数据行的rowid；(2)根据rowid在数据表中读取相应的行。而索引组织表在索引中，根据主码找到相应的数据行，减去了根据rowid映射数据行的过程，加快了查询速度，提高了性能。

如果某个表的大部分查询是根据主码进行的，建议建立索引组织表。索引组织表的建立要使用ORGANIZATION INDEX关键字，并指定表的主键。索引组织表一般存放在用户表空间，而不是存放在索引表空间。

例6：建立索引组织表 CREATE TABLE teacher(

teacher_id NUMBER(20) PROMARY KEY, name dno

VARCHAR2(10), VARCHAR2(10)

- 11 -

第04章 数据库对象

)

ORGANIZATION INDEX TABLESPACE users;

索引组织表对于主键的查询会显著提高查询速度，但是如果是对非主键的查询，反而没有普通表的查询速度，为了改善对非主键的查询速度，采用“溢出”存储功能，将非主码列存储在溢出区，而不是B树的叶节点上。对于大型的索引组织表，采用溢出存储功能，一是可以减少索引组织表占用的存储空间；二是加速了非主键列的查询速度。

如果采用溢出存储功能，要使用OVERFLOW子句，同时指定溢出的条件和溢出的字段。其中PCTTHRESHOLD指定索引叶节点中应该保留的空间百分比，当存储主键和部分非主键后，如果剩余空间低于PCTTHRESHOLD，则将用INCLUDING指定的列及其以后的列保存在溢出区。

例7：建立使用溢出存储的索引组织表 CREATE TABLE teacher(

teacher_id NUMBER(20) PROMARY KEY, name dno )

ORGANIZATION INDEX

OVERFLOW PCTTHRESHOLD 20 INCLUDING name TABLESPACE users; 4.建立分区表

对于数据量到达上百吉字节(GB)甚至太字节(TB)的巨型表，如人口普查表、移动用户表、话费原始记录表等等。这种巨型表有以下弊端：

(1)安全性：如果某个数据块产生错误，整个表将不可用，这对于巨型表来说是不安全的。 (2)查询速度慢：巨型表的查询需要执行大量的I/O操作，对系统性能有很大的影响。特别是一块硬盘不足以存放一个巨型表时，则一个数据段跨越多个磁盘的多个数据文件，会显著降低系统性能。

- 12 -

VARCHAR2(10), VARCHAR2(10)

第04章 数据库对象

对于这种巨型表，Oracle提供了分区技术。将一个巨型表进行划分，分别存放在较小的分区(partition)中，当进行查询时，可以只访问某个分区，而不必访问整个巨型表，从而加快了查询速度，提高了系统性能。分区与分表不同，如求平均、求和等操作需要访问整个表时，直接使用巨型表表名，不需要对各个分区进行操作。

Oracle提供了4中分区的方法：范围分区、列表分区、散列分区、组合分区。这里介绍常用的范围分区和列表分区。

(1)范围分区

范围分区使用PARTITION BY RANGE(column)关键字建立分区，日期性字段是常用分区字段。如销售表按季度分成4个区。使用关键字PARTITION 指明分区名。

例8：范围分区表 CREATE TABLE sales(

order_id

NUMBER(10), NUMBER(10), NUMBER(10), NUMBER(10), DATE,

customer_id goods_id

goods_amount sale_date

sale_province VARCHAR2(20) )

PARTITION BY RANGE(sale_date)(

PARTITION p1 VALUES LESS THAN(’2007-03-01’), PARTITION p2 VALUES LESS THAN(’2007-06-01’), PARTITION p3 VALUES LESS THAN(’2007-09-01’), PARTITION p4 VALUES LESS THAN(’2007-12-01’) );

说明：

①p1、p2、p3、p4分别为分区名，使用”表名.分区名”来标识分区，不同的分区可以使用

- 13 -

第04章 数据库对象

TABLESPACE，以便将个分区存放在不同的表空间。

②对于INSERT操作，根据插入数据，分别存放在不同的分区中，对于SELECT、UPDATE、DELETE操作，如果在WHERE条件中引用了分区列，Oracle将会在相应的分区上进行操作。

③指定分区列时，要能尽量将记录较均匀地分布在各个分区中，如果全部挤在一个分区，就失去了分区的意义。

(2)列表分区

如果分区列的值不能进行范围划分(不是数值或日期)，而且分区字段值在一个较小的集合内，可以按列表分区。如区进行分区的销售表：

例8：范围分区表 CREATE TABLE sales(

order_id

NUMBER(10), NUMBER(10), NUMBER(10), NUMBER(10), DATE,

customer_id goods_id

goods_amount sale_date

sale_province VARCHAR2(20) )

PARTITION BY LIST(sale_province)(

PARTITION p1 VALUES(’辽宁’,’吉林’,’黑龙江’),

PARTITION p2 VALUES(’北京’,’天津’,’河北’,’山西’,’内蒙’),

PARTITION p3 VALUES(’上海’,’江苏’,’浙江’,’安徽’,’福建’,’江西’,’山东’), PARTITION p4 VALUES(’河南’,’湖北’,’湖南’,’广东’,’广西’,’海南’) PARTITION p5 VALUES(’重庆’,’四川’,’贵州’,’云南’,’西藏’) PARTITION p6 VALUES(’陕西’,’甘肃’,’青海’,’宁夏’,’新疆’) PARTITION p7 VALUES(’河南’,’湖北’,’湖南’,’广东’,’广西’,’海南’,) PARTITION p8 VALUES(’香港’,’澳门’,’台湾’)

- 14 -

第04章 数据库对象

);

说明：

①列表分区用LIST关键字。使用VALUES直接列出分区的值，不是范围的比较。 ②所有分区中的列表值，要包括全部的值；在输入时，要与列表值一致。

4.1.5 修改表

表建立后，要对表进行一些修改，以便适应需求。维护主要有： 增加列或限制：ALTER TABLE tablename ADD 删除列或限制：ALTER TABLE tablename DROP 修改列或限制：ALTER TABLE tablename MODIFY (1)增加列：

SQL>ALTER TABLE teacher ADD address VARCHAR2(50); 说明：对现有记录，新增列值为NULL或设置缺省值。 (2)增加限制：

SQL>ALTER TABLE teacher ADD

CONSTRAINT teacher_sex_ck CHECK(Sex in (‘男’,’女’));

(3)删除限制：

SQL>ALTER TABLE teacher

DROP CONSTRAINT teacher_id_pk CASCADE;

说明：CASCADE删除主键和唯一键的同时删除外键。 (4)删除列：

SQL>ALTER TABLE teacher DROP COLUMN sex;

//删除单列用COLUMN

SQL>ALTER TABLE teacher DROP (sex,address); //删除多列 SQL>ALTER TABLE teacher SET UNUSED (sex); //设置列无效 SQL>ALTER TABLE teacher DROP UNUSED COLUMNS;//删除无效列 (5)修改表名:

SQL>ALTER TABLE teacher RENAME TO teacher_info;

- 15 -

第04章 数据库对象

(6)修改列：

SQL>ALTER TABLE teacher MODIFY address VARCHAR2(60); (7)修改限制：

SQL>ALTER TABLE teacher MODIFY

CONSTRAINT teacher_sex_ck(sex IN (‘m’,’f’));

说明：如果有记录存在，修改可能引起NOT NULL冲突；数据类型的不兼容，违反主键或外键约束等。当设置主键有冲突时，可以检查是否有相同的记录，并删除。

SQL>SELECT no FROM telephone

GROUP BY no HAVING COUNT(*)>1; SQL>DELETE FROM telephone t1

WHERE EXISTS(

SELECT no FROM telephone t2 WHERE t2.no=t1.no GROUP BY no HAVING COUNT(*)>1) AND ROWID NOT IN (

SELECT MIN(ROWID) FROM telephone t3 WHERE t3.no=t1.no)

或

SQL>DELETE FROM tt t1

WHERE no IN(

SELECT no FROM tt GROUP BY no HAVING COUNT(*)>1)

- 16 -

第04章 数据库对象

AND ROWID NOT IN (

SELECT MIN(ROWID)

FROM tt t2 WHERE t2.no=t1.no)

4.1.6 删除表

删除表的语法为：

DROP TABLE [schema.]table [CASCADE CONSTRAINTS] 删除表，如果有表定义了外键，将限制一起删除。 当删除一个表时，随之删除的内容有： (1)表的索引 (2)指向本表的外部键 (3)表的触发器 (4)表的分区 (5)表的快照

(6)角色和用户的实体权限 (7)本表的所有限制

4.1.7 分析表

ANALYZE TABLE对表进行分析。 (1)验证表的存储结构

SQL>ANNLYZE TABLE teacher VALIDATE STRUCTURE;

可以发现表中是否有坏块，结果记录在invalid_rows中，如果有坏块，要重建表 (2)收集表的统计信息

统计信息包括表的记录数、块使用情况、记录长度等，结果记录在dba|all|user_tables。 SQL>ANNLYZE TABLE teacher COMPUTE STATISTICS; 精确统计表的记录数、块使用情况、记录长度等。 SQL>ANNLYZE TABLE teacher ESTIMATE STATISTICS;

- 17 -

第04章 数据库对象

通过对200条记录的统计，粗略估计统计信息。 (3)查询表的行链接和行迁移

SQL>ANNLYZE TABLE teacher LIST CHAIN_ROWS;

检查结果记录在chained_rows中。可以使用重建表方法消除行链接和行迁移。

4.1.8 表信息

常用表的信息：表名、拥有者、表空间、表的限制、列名、数据类型及宽度、列的限制。 (1)查询表结构 DESC[RIBE] tablename; (2)查询表的信息：

dba|user_tables：表名、存储参数等信息 dba|user_segments：表所在表空间和表的大小 dba_extents：查询表段的区信息 dba_constrants：表的限制信息 dba_tab_columns：表的列信息 dba_cons_columns：限制列的信息

- 18 -

第04章 数据库对象

4.2 索引

索引是一种与表相关的可选对象。在一个表上建立索引，不会对表的操作方法产生任何影响，但是能显著提高对表的查询速度。

4.2.1 索引概念

全表扫描：没有索引的表查询，从头到尾的查询全表所有行，称为全表扫描。全表扫描需要对所有行进行I/O操作，对于记录较多的大表，全表扫描的查询速度较慢，查询效率不高。

索引查找：索引类似于”目录”，目录中记录了大纲和页码；类似地，索引中记录了查询内容和对应行的rowid，有索引查询过程是：

①在索引中查找到符合条件所有行的rowid； ②根据rowid读取相应的行数据。

在目录中查询比在全书中查询快得多；类似地，在索引中查询比全表扫面块得多，所以索引能加快查询速度。

索引副作用：

①索引需要占用空间，是典型的用空间换时间。一般情况下，索引与表分别存放在不同的表空间，以便提高索引和表I/O并行度，减少I/O冲突。

②由于索引的结构依附于表，当表数据发生变化时(增加、删除、修改),系统要同步维护索引，占用系统资源。

索引不是越多越好，要根据具体实际使用表的情况建立有效的索引，删除不必要的索引。 索引的特点：

①数据小：索引只存储索引字段和行ROWID，相对于数据表而言，数据量较小。

②查询快：索引查询速度快，但数据量增大时，查询速度基本不变。但全表扫描随数据的增加，查询速度明显减慢。

③自维护：当数据表的数据改变时，系统自动根据表的数据变化维护索引。

④独立性：索引虽依附于表，但索引存在与否，不影响对表的操作方法，只是提高查询速度。 ⑤多索引：主键只能有一个，但可以根据不同的字段建立多个索引。

- 19 -

第04章 数据库对象

相关问题：

①何时需要索引：索引能提高查询速度，是不是只要有索引，都能提高查询速度呢？ ②需要哪种索引：索引的种类有哪些？哪种索引最有效？

③如何建立索引：不同种类的索引如何建立？如何维护？如何查询索引信息？ 使用索引：

假设建立了teacher表： CREATE TABLE teacher(

id name dno sex

NUMBER(20) VARCHAR2(10), VARCHAR2(10), CHAR(2) DATE

CHECK(sex IN (‘男’,’女’)), NOT NULL,

birthday

CONSTRAINT teacher_id_pk PRIMARY KEY(id)

USING INDEX TABLESPACE indx, CONSTRAINT teacher_name_uq UNIQUE(name)

USING INDEX TABLESPACE indx,

CONSTRAINT teacher_dno_fk FOREIN KEY(dept_id)

REFERENCES dept(dno) );

(1)定义主键后，自动建立了索引，索引字段是id，索引名字为teacher_id_pk。 (2)定义唯一键后，自动建立了索引，索引字段为name，索引名字为teacher_name_uq。 (3)在查询时，只有查询条件字段有索引时，系统才自动使用索引。如： SQL>SELECT * FROM teacher WHERE id=1234;

//使用索引teacher_id_pk

SQL>SELECT * FROM teacher WHERE name=’张大谦’; //使用索引teacher_name_uq SQL>SELECT * FROM teacher WHERE dno=’02’; SQL>SELECT * FROM teacher WHERE sex=’男’;

- 20 -

//不使用索引 //不使用索引

第04章 数据库对象

所以只有经常作为查询条件的字段才建立索引。

4.2.2 索引分类

索引的类型有：按索引建立的对象有表索引、簇索引(在簇中介绍)； 按索引的结构有B树和位图索引。按索引内容有函数索引和反转索引。 1.B树索引

B树及改进的B+树和B*树，Oracle是采用的B*树，也是Oracle默认的索引类型。 B树索引结构示意如下：

B树索引的组成：

(1)根块：索引顶级块，包含下级节点(分支块)的入口信息，即索引字段值的分段和对应下级节点入口地址；

(2)分支块：中间节点块，包含下级节点(分支块或叶块)的入口信息，即索引字段值子分段和对应下级节点入口地址；

(3)叶块：末节点块，包含表行的入口信息，即索引列值和对应的rowid。 B树索引的查找：

B树的查询是二分查询，根据查询条件值，判断其所在左右子树，迅速查找其叶块，取得满足条件行的rowid。影响查询速度是B树的深度和宽度。

B树索引的特点：

(1)适用于大量异值列的大表，列的基数较高，即大部分值都不相同的字段； (2)可以维持平衡性(深度和宽度)；当表的数据增长时，有一定的稳定性； (3)可以采用多列索引(组合索引、连接索引)，可以升序(ASC)或降序(DESC)；

- 21 -

A Q P Z A G F P A 1 P 2 G 3 P 4 Q 5 S 6 Q T S Z T 7 Z 8 第04章 数据库对象

(4)可以生成反转索引(REVERSE)和唯一索引(UNIQUE)。 B树索引的缺点：

(1)不适合列的基数较低的索引；少于200个记录没有必要建立B树索引。 (2)不支持函数查询。如SUBSTR()等函数条件查询。 2.位图索引

当列值基数小，都在一个较小的集合内，如性别只有’男’、’女’，婚否只有’yes’、’no’，不适合建立B树索引，可以使用位图(BITMAP)索引。

位图索引的结构：位图索引是一个二维数组，行对应表的记录行，列对应索引字段的取值位图和ROWID。如员工表staff中如下：其中是否退休retire(Y、N)，性别sex(M[ale]、F[emale])

ROWID 1 2 3 4 STAFF_NO 202 203 302 302 SEX M F M F RETIRE Y Y N N 按SEX和RETIRE建立的位图索引结构示意如下：

ROWID 1 2 3 4 M 1 0 1 0 F Y N 0 1 0 1 1 0 0 0 1 1 0 1 位图索引的查找：

SQL>SELECT * FROM staff WHERE sex=’M’;

当查询男性M时，只需查询M=0的rowid。如果是多个位图字段的查询，如： SQL>SELECT * FROM staff WHERE sex=’M’ and retire=’Y’; 则需要对(M=1) and (Y=1)的逻辑运算。 位图索引的特点：

(1)占用空间较少，查询的速度快；如果几个列建立了位图索引，有专门的合并算法； (2)适合于基数少的列建立索引；

- 22 -

第04章 数据库对象

(3)适合于静态数据，如数据仓库的数据。 3.函数索引

如果建立索引的字段，在查询条件子句中使用了函数，则不会使用索引。如果经常使用字段的函数值作为查询条件，可以建立函数索引。函数索引的结构是B树索引。

函数索引的限制是：

(1)不能使用聚合函数，如SUM、AVG等；

(2)函数的返回值是可重复的，不能使用类似SYSDATE、USER等返回固定值的函数， 4.反转索引

索引字段值分布在一个比较密集的范围内，并且是顺序递增或递减，如果建立B树，则会使树的左右子树不均衡，导致树的层次急剧增加。可以使用反转索引。

反转索引是将索引字段值反向后建立的索引。反转索引结构还是B树结构，而位图索引不能使用反转索引。

4.2.3 建立索引

建立索引的要素有：

(1)索引所依附的表？小表不需要建立索引，除了定义主键自动建立的索引外。 (2)索引字段？只有经常用作查询条件的字段才需要建立索引。 (3)索引的名字？标识表和字段。 (4)建立什么类型的索引？ (5)索引存放的表空间？

除了定义主键和唯一键时，自动建立索引外，可以手工建立索引，其语法如下： CREATE [UNIQUE|BITMAP] INDEX [schema.]indexname ON [user.]tablename

({column[ASC|DESC]|column_expression},...) [CLUSTER [user.]cluster] [TABLESPACE tablespace]

[PCTFREE n] [INITRANS n] [MAXTRANS n] [STORAGE storage]

- 23 -

第04章 数据库对象

[REVERSE] [NOSORT]

//翻转索引

//已经排序，无需重新排序 //日志不记录 //在线建立 //产生统计信息

[LOGGING|NOLOGGING] [ONLINE]

[COMPUTE STATISTICS] 说明：

(1)UNIQUE是唯一索引，是B树索引，要求索引字段的值唯一。一般不单独建立唯一索引，而是在定义字段的唯一性约束自动建立。

(2)BITMAP是位图索引，位图索引不能和UNIQUE同时选。默认的为B树索引。 (3)indexname是建立索引的名字 (4)tablename是索引依附的表名

(5)column是索引字段，索引字段可以是多个字段，称为组合索引。位图索引最多30列，B树索引最多32列。

(6)ASC按升序建立索引(默认)，DESC按降序建立索引。 (7)column_expression是建立函数索引的表达式。 (8)cluster指明索引簇，关于簇索引，在”簇”一节中介绍。

(9)tablespace是索引存放的表空间，一般索引与表保存在不同的表空间。 (10)块控制参数中，不能指定PCTUSED参数。 (11)STORAGE指定存储参数。

(12)REVERSE建立反转索引，反转索引只能是B树索引，不能是位图索引。

(13)NOSORT数据行已经排好序，在建立索引时不必排序，可以节约索引建立时间，但不能和反转索引联用。

(14)LOGGING|NOLOGGING创建索引操作是否记录日志中。LOGGING是缺省方式。 (15)ONLINE在线索引，在建立索引时，允许进行对表进行DML操作。

(16)COMPUTE STATISTICS收集统计信息，以便优化程序选择执行计划。BITMAP、REVERSE、COMPUTE STATISTICS不能在线建立。

- 24 -

第04章 数据库对象

建立索引举例： 针对下表：

CREATE TABLE teacher(

id name sex age dno

NUMBER(20) VARCHAR2(10) CHAR(2) number(3), NUMBER(10), char(1)

CHECK(state IN (‘I’,’O’)),

NOT NULL, NOT NULL, DEFAULT ‘男’,

state

CONSTRAINT teacher_id_pk PRIMARY KEY(id) USING INDEX TABLESPACE indx )TABLESPACE USERS;

例1：建立B树索引：单列索引，指明表空间

CREATE INDEX teacher_name_index ON teacher(name)

TABLESPACE indx; 说明：

(1)索引名为：表名_字段名_index； (2)索引表空间一般与表所在表空间分离。 例2：建立B树索引的多索引字段组合索引

CREATE INDEX teacher_name_dept_index ON teacher(dept_id,name)

TABLESPACE indx; 例3：建立反转索引

CREATE INDEX teacher_age_index ON teacher(age)

TABLESPACE indx REVERSE; 例4：建立位图索引

CREATE BITMAP INDEX teacher_state_index ON teacher(state)

- 25 -

第04章 数据库对象

TABLESPACE indx; 例5：建立函数索引

CREATE INDEX teacher_fname_index ON teacher(UPPER(name))

TABLESPACE indx;

4.2.4 修改索引

ALTER INDEX [user.]index

说明：可以修改索引的很多参数，一般用于改名、重建、合并等操作。 例1：改名

SQL>ALTER INDEX old_index RENAME TO new_index; 例2：重建，可同时修改表空间或其它参数 SQL>ALTER INDEX teacher_name_index REBUILD;

例3：合并，相邻叶节点都有剩余空间时，可将它们存放同一个叶节点，提高空间利用率。 SQL>ALTER INDEX teacher_name_index COALESCE;

4.2.5 删除索引

DROP INDEX [user.]indexname; SQL>DROP INDEX teacher_name_index; 当一个表删除时，其上的索引自动全部删除。

4.2.6 索引信息

索引常用的信息：索引名、拥有者、表空间、依附的表名、索引的字段等。 dba|user_indexes：索引名、索引类型。

dba|user_ind_columns：索引依附的表、索引字段。 dba|user_segments：索引段的信息。

- 26 -

第04章 数据库对象

4.3 簇

簇(cluster)是表存储的可选方式，是将几个表存储一起的容器，簇分为索引簇和散列簇。

4.3.1 索引簇

有这样的设计：部门表DEPT和员工表EMP，分别单独存放，各占单独的数据段。 DEPT部门表：

DEPTNO 10 20 DNAME SALES ADMIN EMP员工表：

EMPNO 101 102 103 104 105 EMPNAME 张三 李四 王五 赵六 孙七 DEPTNO 10 20 10 10 20 经常使用如下的连接查询(花名册)：

SQL>SELECT dept.dname,emp.empno,emp.empname FROM emp

JOIN dept ON (dept.deptno=emp.deptno)

SQL>SELECT dept.dname,emp.empno,emp.empname FROM emp,dept

WHERE dept.deptno=emp.deptno

连接查询是分别读取2个数据表。能不能将被连接的几个表放在一起，一次读入呢？ 概念：所谓索引簇，简单地说就是把几个表放在一起，按一定公共属性混合存放。通常是把经常逻辑上在一起连接查询(连接JOIN)的表聚簇存放，使其物理存储尽量邻接。索引簇是由共享相同数据块的一组表组成，具有相同公共列的值存储在一个数据块中，当连接查询时，多个表的数据按公共值一次读入，减少了I/O，提高了性能。其中公共列称为聚簇键或聚簇码。

如果将2个表存储在一个簇中的形式为：deptno=10及其员工存储一个数据块。

- 27 -

第04章 数据库对象 DEPTNO 10 DNAME SALES EMPNAME 李四 王五 赵六 20 ADMIN EMPNAME 李四 孙七 EMPNO 102 105 EMPNO 101 103 104 优点：

(1)当连接查询时，只需要读连接的数据块，减少了磁盘/IO.

(2)相同的聚簇键无论其在各个表中对应的行有多少，只存储一次，节省了存储空间。 使用聚簇的条件：

(1)聚簇中的多个聚簇表必须有公共的列，并且经常按公共列连接查询，而不是单独查询。 (2)静态表，表中的数据基本不变，表主要用于查询，而不是用来增删改操作； 索引簇的使用步骤： 第一步：创建索引簇 第二步：将表插入簇中 第三步：建立簇索引

在使用表之前，必须创建簇索引，所以称这种簇为索引簇(index cluster)。

4.3.2 创建索引簇

创建索引簇的要素： (1)簇的类型(INDEX) (2)索引簇的名字

(3)聚簇键及数据类型和宽度 (4)存储簇的数据块大小 (5)索引簇所在的表空间

- 28 -

第04章 数据库对象

创建索引簇的语法：

CREATE CLUSTER cluster_name(column datatype[,column datatype,…]) [SIZE n{K|M}]

[TABLESPACE tablespace] [INDEX]; 其中

cluster_name为索引簇的名称

(column datatype[,column datatype,…])聚簇键

[SIZE n{K|M}]一个聚簇键值数据的平均大小，缺省为数据块大小，即一个数据块存放一个键值数据。

[TABLESPACE tablespace]簇所在表空间，缺省为用户默认表空间 [INDEX]索引簇(默认)

SQL>CREATE CLUSTER dept_cluster(dno number(10))

SIZE 1M TABLESPACE users INDEX;

4.3.3 创建聚簇表

聚簇表的建立与普通表相似，只是用关键字CLUSTER指明其存放的索引簇代替表空间。指明其聚簇键，聚簇表中的聚簇键可以与索引簇中的聚簇键不同名，但结构相同。

CREATE TABLE [user.]tablename

({column1 datatype [DEFAULT expn] [column_constraint]|[table_constraint],...) CLUSTER cluster_name(column);

如：创建DEPT部门表并存放在dept_cluster索引簇中 SQL>CREATE TABLE dept(

dno

NUMBER(10),

dname VARCHAR2(20)

)CLUSTER dept_cluster(deptno);

创建EMP员工表并存放在dept_cluster索引簇中

- 29 -

第04章 数据库对象

SQL>CREATE TABLE emp(

empno NUMBER(10), ename VARCHAR2(20), dno

NUMBER(10)

)CLUSTER dept_cluster(dno);

4.3.4 建立簇索引

建立簇索引与建立表索引相似，表索引ON TABLE改为簇索引为ON CLUSTER，自动按定义索引簇声明的聚簇键建立索引，在建立索引时不必指明索引字段。指明索引的名称和表空间。

CREATE INDEX indexname ON CLUSTER [user.]cluster

[TABLESPACE tablespace] 如：

SQL>CREATE INDEX dept_cluster_index ON CLUSTER dept_cluster

TABLESPACE indx;

4.3.5 维护索引簇

修改索引簇

ALTER CULSTER clustername 常用于修改其物理存储参数。 修改聚簇表

ALTER TABLE tablename

注意不能修改其物理存储参数，受制于聚簇。 修改簇索引

ALTER INDEX indexname

修改簇索引同修改其它索引一样。 删除索引簇 删除空的索引簇

DROP CLUSTER cluster_name

- 30 -

第04章 数据库对象

删除有聚簇表的索引簇

DROP CLUSTER cluster_name INCLUDE TABLES 删除有聚簇表和有约束的索引簇

DROP CLUSTER cluster_name INCLUDE TABLES CASCADE CONSTAINTS 删除聚簇表

DROP TABLE tablename

可以象删除普通表一样删除聚簇表。一般2个表的聚簇，如果删除其中一个，聚簇没有必要存在了，所以一般删除聚簇时一起删除表(INCLUDE TABLES)。

删除簇索引

DROP INDEX indexname

簇索引可以象一般索引一样删除，然后重建。但注意，没有索引，索引簇不能使用。 索引簇信息

索引簇的信息存放于视图：dba|user_clusters和dba|user_clu_columns。

4.3.6 散列簇

概念：如果有查询：SELECT * FROM students WHERE sno=1234 通常的方法是：如果没有索引，则进行全表扫描；如果建立了索引，先查询索引，然后根据索引中记录的rowid读取相应的记录。能不能根据1234直接计算rowid，然后按rowid读取相应的记录？

散列（Hash）实质上是一种将表中一个列值或几个列的组合值计算为地址的方法(KAT：Key to Address Transformation)。计算地址的列称为散列键，散列键常常是主键或唯一键。

散列簇是将具有同一散列码值的记录存储同一数据块中，每一记录的散列值是散列函数返回的值，作为地址值，同一地址值的数据存放在同一数据块中。建立一个散列聚簇时，可指定一散列函数或使用Oracle内部的散列函数。

优点：根据散列键计算记录位置，不用查询索引获得记录位置，减少了磁盘I/O。 条件：

(1)查询是基于散列键的等值查询，而不是范围查询，形如

SELECT...WHERE cluster_key=?;

- 31 -

第04章 数据库对象

(2)静态数据表，主要用于查询，而不是增删改的表

(3)散列键经过散列函数运算的结果，具有比较均匀的数值分布。 步骤：

第一步：建立散列簇 第二步：建立散列表

注意：索引簇必须建立索引，而散列簇不能建立索引。

4.3.7 创建散列簇

创建散列簇的要素： (1)簇的类型及名称

(2)散列键及数据类型的确定,要求为NUMBER类型 (3)存放同一散列值的数据块大小(记录多少) (4)表空间

(5)存放单表还是多表 (6)不同散列值的个数 (7)散列函数：内部还是自定义 创建散列簇的语法：

CREATE CLUSTER cluster_name(column datatype[,column datatype,…])

[SIZE n{K|M}]

[TABLESPACE tablespace] [SINGLE TABLE ] HASHKEYS integer [HASH IS expression] 说明：

[SIZE n{K|M}]：同一散列值的对应数据大小，如果散列值与行一一对应，则为一行大小。 [TABLESPACE tablespace]：散列簇所在表空间 [SINGLE TABLE ]： 单表散列(散列簇中只存放一个表)

- 32 -

第04章 数据库对象

HASHKEYS integer：不同散列值的个数，如果散列值与行一一对应，则为行数。 [HASH IS expression]：HASH函数，缺省为Oracle内部函数，组合键只能使用内部函数 举例：

例1：创建散列簇

CREATE CLUSTER student_cluster(sno number(10))

SIZE 200

//每条记录的大小为200字节 //表空间 //单表散列

//散列值数，即学生记录数

//散列值为sno，散列值与行一一对应，不使用内部散列函数

TABLESPACE users SINGLE TABLE

HASHKEYS 10000 HASH IS sno 例2：创建散列表 表1：学生基本情况表 CREATE TABLE student(

sno

NUMBER(10),

sname VARCHAR2(20), ??

)CLUSTER student_cluster(sno);

4.3.8 维护散列簇

修改散列簇

ALTER CLUSTER cluster_name

但是SIZE、HASHKEYS、HASH IS参数不能修改。 删除散列簇

DROP CLUSTER cluster_name [INCLUDE TABLES][CASCADE CONSTAINTS] 同时删除散列表和约束。

- 33 -

第04章 数据库对象

4.4 视图

对于一个公司，有如下的员工表(EMP)。 dno 1 1 2 2 3 4 eno 1 2 3 4 5 6 ename A B C D E F esalary 4000 3500 1000 1200 1500 800 对于内部操作员，不能查询员工的工资；

(SELECT dno,eno,ename FROM emp;)=A

对于外部操作员，不能查询部门编号为1(领导)信息，其它员工的工资也不能查询；

(SELECT dno,eno,ename FROM emp WHERE dno<>1;)=B

如果分别建立上述要求的数据表，一是重复，产生冗余；二是当修改数据时，同时修改多个表，容易产生不一致；如果增加新的要求，又建立新表？视图(VIEW)是一种很好的解决办法。

概念：视图View是一种数据库对象，是一种命名的SQL查询语句。该SQL查询语句从一个或多个表(称为基表)查询，查询的记录形成一个“虚表”。为什么是虚表呢？Oracle只在数据字典中保存SQL语句定义，并不实际保存查询的记录，只是在使用视图的时候，按名字找到相应的SQL查询语句，然后重新执行SQL查询语句，查询的记录会随基表的更新而变化。如上面定义的A，B为视图名。可以象使用表一样使用视图。

视图和基表的关系：

区别：①基表是数据存放的地方，有实际的数据，需要存储空间。②视图中没有数据，只有一条SQL查询语句，并保存在数据字典中。

联系：①视图是一个查询语句，其结果仍是关系结构，与表的结构相同，故称为”虚表”。②一个视图可以派生于一个或多个基表，也可以从其它视图中派生，基表的数据发生变化时，视图

- 34 -

第04章 数据库对象

是从最新的数据中查询，查询结果会自动更新。③如果视图允许更新，实际更新的是基表的数据。

视图的作用：

①安全：可以只呈现用户权限范围内的数据，对基表的数据进行选择或投影后呈现给用户，保护了敏感数据，增加了安全性。视图有视图权限，对视图的操作要有相关权限。

②方便：可以封装复杂的SQL查询，简化用户的操作，也消除了不同的人所用的语句差异所造成的不一致性。

4.4.1 建立视图

CREATE [or REPLACE] [FORCE | NO FORCE] VIEW [user.]viewname

[column_name,...] AS query

[WITH CHECK OPTION] [CONSTRAINT constraint] [WITH OBJECT OID | DEFAULT] [WITH READ ONLY] 说明：

[or REPLACE]：如果同名视图存在，则替换(先删除后建立)。

[FORCE | NO FORCE]：如果基表不存在或无权限访问基表，建立一个不可使用的视图。缺省为NO FORCE。

[column_name,...]：视图的列，缺省为SQL查询的列，一般用于重新命名列。 AS query：视图对应的SQL查询语句。

[WITH CHECK OPTION]：Update和Insert更新操作必须满足视图定义。 [CONSTRAINT constraint]：对视图更新操作进行限制。

[WITH OBJECT OID|DEFAULT]：对象视图的对象类型属性，DEFAULT(缺省)主关键字标识。 [WITH READ ONLY]：只读视图。 建立视图举例：

假设有如下的基表，员工表emp

- 35 -

第04章 数据库对象 eno 部门表dept

dno ename esalary dno dname 例1：如果一般职工不能查看工资，可以建立如下视图 SQL>CREATE VIEW v_emp_in AS

SELECT eno,ename,dno FROM emp;

例2：非职工不能查询员工工资外，还不能查询领导人员(部门1)的情况，视图如下： SQL>CREATE VIEW v_emp_out AS

SELECT eno,ename,dno FROM emp WHERE dno<>1; 例3：建立公司花名册视图

SQL>CREATE VIEW v_emp_dept(dept,no,name) AS

SELECT dept.dname,emp.eno,emp.ename FROM emp,dept

WHERE emp.dno=dept.dno;

4.4.2 使用视图

视图建立后，就象使用表一样可以查询。 1.查询视图：

SQL>SELECT * FROM v_emp_in WHERE dno=2;

视图的查询，最终转换为对基表的查询，这一转换过程，称为视图消解。上述查询消解为： SQL>SELECT eno,ename,dno FROM emp WHERE dno=2; 2.更新视图：

对视图的更新，最终是对基表的更新。如：

SQL>UPDATE v_emp_in SET ename=’刘晨’ WHERE eno=1234;

如果在定义视图时有WITH CHECK OPTION选项时，对视图的更新要满足视图定义的条件，如： SQL>INSERT INTO v_emp_out VALUES(2233,’蒋凤英’,1);

因为v_emp_out视图中的部门编号不能为1，所以查询数据不在视图定义中，插入出错。

- 36 -

第04章 数据库对象

如果定义视图时有选项 WITH READ ONLY选项，则不能更新。

即使没有WITH CHECK OPTION和WITH READ ONLY限制，有很多视图是不能更新的： (1)有DISTINCT关键值的视图；

(2)有GROUP BY、ORDER BY、CONNECT BY、START WITH子句的视图； (3)有AVG、SUM、MAX等分组函数的视图；

(4)有INTERSECT、UNION、MINUS等集合操作的视图。

4.4.3 维护视图

修改视图：

ALTER VIEW view AS query

重建视图：一般很少修改，直接建立或重建。

CREATE OR REPLACE VIEW viewname AS query; 重新编译：检查视图是否正确

ALTER VIEW viewname COMPILE; 删除视图：

DROP VIEW viewname;

4.4.4 视图信息

dba|user_views：保存了视图的名称、SQL语句及长度和拥有者等信息。 dba|all|user_updatable_columns：保存了视图中可以更新的字段信息。

4.4.5 系统视图

Oracle自动在数据字典中建立了很多系统视图，用于保存系统信息，这些视图不能修改，只能查询。有一种面向数据库的视图，这种视图有三个不同的前缀：

user_：当前用户相关的信息，当前用户可以访问； all_ ：当前用户可以访问的全部信息，当前用户可以访问； dba_ ：当前系统的所有信息，sys用户才可以访问。 如：user_tables，all_tables，dba_tables。 常用的有：

- 37 -

第04章 数据库对象

users、roles、sys_privs、tab_privs、role_privs用户和权限视图；

tables、indexs、views、synonyms、sequences、triggers、source数据库对象视图； ind_columns、tab_columns、updatable_columns列信息视图； tablespaces、sgements、extents逻辑结构视图。

另外，Oracle还有一种以v$打头的面向实例的视图，称为动态视图，是sys拥有的视图。如：v$parameter、v$sysstat、vssesstat、v$buffer_cache、v$rowchache、v$librarycache、v$latch、v$sga、v$pga、v$press、v$bgprocess、v$thread、v$log、v$logfile、v$archived_log、v$log__history、v$datafile、v$filestat、v$database、v$instance、v$controlfile、v$session、v$tablespace、v$tempfile、v$undostat、v$rollstat,v$transaction、v$rollname等等。

- 38 -

第04章 数据库对象

4.5 序列

4.5.1 序列概念

实体完整性要求表设置主键。当创建一个表时，如果其主键不是很明显，如流水数据，需要人工设置一个主键；如果主键是由多个字段组合而成，即主键由几个列才能确定，为了减少主键复杂性，不如单独设置一个人工主键。主键要求唯一而不重复。如何人工设置主键呢？序列可作为人工主键。

序列(sequence)是一个Oracle的对象，是一个NUMBER(38)的数值，以递增和递减的方式确保每次请求的数值唯一而不重复。序列的定义存储在数据字典中。数据库的关闭和开启不影响数值的唯一性和连续性。

Oracle序列独立于表，这与SQL SERVER不同，SQL SERVER是在一个表内定义，针对数值列使用IDENTITY(1,1)定义序列，并自动填写，在插入之前是不知道。而在Oracle中，序列是各个表可以共用，在插入之前是可知的。

4.5.2 创建序列

序列的要素有： 序列名称、初值、步长(正递增，负递减)、最大值(递增)或最小值(递减)、到达最大或最小值后的行为。创建序列的语法：

CREATE SEQUENCE [user.]sequencename

[START WITH n] [INCREMENT BY n] [MAXVALUE n|NOMAXVALUE] [MINVALUE n|NOMINVALUE] [CYCLE|NOCYCLE] [CACHE n|NOCACHE] [ORDER|NOORDER] 参数说明：

sequencename：序列名称。

- 39 -

第04章 数据库对象

[START WITH n]：初始值，缺省为1。

[INCREMENT BY n]：步长，缺省为1，为负数时是递减，正数为递增。

[MAXVALUE n|NOMAXVALUE]：最大值或没有最大限制（10的26次方），缺省为NOMAXVALUE。 [MINVALUE n|NOMINVALUE]：最小值或没有最小限制（-10d的26次方），缺省为NOMINVALUE。 [CYCLE|NOCYCLE]：到达最大值后循环(CYCLE)或停止(NOCYCLE)，缺省为NOCYCLE。 [CACHE n|NOCACHE]：是否将最近的N个值放入缓冲区中，缺省为NOCACHE。 [ORDER|NOORDER]：并行运行时是否顺序产生，缺省为NOORDER 例1：使用缺省项创建

SQL>CREATE SEQUENCE techer_ID_seq; 例2：指定参数创建

SQL>CREATE SEQUENCE student_ID_seq;

START WITH 1021 INCREMENT BY 1 MAXVALUE 9,999,999,999 NOCYCLE CACHE 20;

4.5.3 使用序列

序列有可以访问的值：CURRVAL和NEXTVAL。分别返回当前值和下一个值。访问方式为：sequencename.CURRVAL, sequencename.NEXTVAL。一般使用NEXTVAL。

查询：

SQL>SELECT teacher_id_seq.nextval FROM dual; 1

SQL>SELECT teacher_id_seq.nextval FROM dual; 2

SQL>SELECT teacher_id_seq.nextval FROM dual; 3

- 40 -

本文来源：https://www.bwwdw.com/article/45cg.html