第 3 章 数据库的操作 3.1 操作表数据 通过 PL/SQL 语句操作表数据比较方便、灵活。所有 SQL 命令将在 SQL*Plus中编译完成。进入 SQL*Plus ，用户名和密码分别为： ADMIN 和 manager 。 3.1.1 插入表记录 语法格式：

INSERT INTO table_name[column_list] VALUES(constant1,constant2,…) 该语句的功能是向由 table_name 指定的表中加入一行，由 VALUES 指定的各列值。 (1) 在插入时，列值表必须与列名表顺序和数据类型一致。 (2) VALUES 中描述的值可以是一个常量、变量或一个表达式。 (3) 如果列值为空，则值必须置为 NULL 。如果列值指定为该列的默认值，则用 DEFAULT 。 (4) 在对表进行插入行时，若新插入的行中所有可取空值的列值均取空值，则就可以在 INSERT 语句中通过列表指出插入的行值中所包含非空的列，而在VALUES 中只要给出这些列的值即可。

3.1.1 插入表记录【例 3.1 】向 XSCJ 数据库的表 XS 中插入如下的一行 :

061101 王林 计算机 男 19870201 50可以使用如下的 PL/SQL 语句 : INSERT INTO XS(XH,XM,ZYM,XB,CSSJ,ZXF) VALUES('061101',' 王林 ', 计算机 ',' 男 ',TO_DATE('19860210','YYYYMMDD'),50);请读者使用 INSERT INTO 语句向学生表插入以下记录： 学号 姓名 专业名 性别 出生时间 总学分 备注061102 王平 计算机 女 1986-09-02 38 NULL061103 王燕 计算机 女 1985-10-06 40 NULL061104 韦严平 计算机 男 1986-08-26 40 NULL061105 吴庆红 计算机 男 1985-12-30 36 有两门不及格061106 李方方 计算机 男 1986-11-20 40 NULL061107 李明 计算机 男 1986-05-01 40 NULL061108 林一帆 计算机 男 1985-08-05 42 已提前修完一门课061109 张强民 计算机 男 1984-08-11 40 NULL061110 张蔚 计算机 女 1987-07-22 40 NULL061111 赵琳 计算机 女 1986-03-18 40 NULL061112 罗林琳 计算机 女 1984-01-30 40 NULL

3.1.1 插入表记录用 SELECT 语句进行查询，可以发现表中已经增加的记录，如图 3.1 所示。

图 3.1 插入新数据后查询的结果

3.1.1 插入表记录 【例 3.2 】建立了表 test 。 CREATE TABLE test ( xm char(20) NOT NULL, zy varchar(30) DEFAULT(‘ 计算机’ ), nj number NOT NULL ); 用 INSERT 向 test 表中插入一条记录： INSERT INTO test(xm,nj) VALUES(‘ 王林’ ,3); 则插入到 test 表中的记录为：王林 计算机 3 语法格式： INSERT INTO table_name

derived_table derived_table 是一个由 SELECT 语句查询所得到的结果集。利用该参数，可把一个表中的部分数据插入到表 table_name 中。

3.1.1 插入表记录【例 3.3 】用如下的 CREATE 语句建立表 XS1 ： CREATE TABLE XS1 ( num char(6) NOT NULL, name char(8) NOT NULL, speiality char(10) NULL );用如下的 INSERT 语句向 XS1 表中插入数据： INSERT INTO XS1 SELECT XH,XM,ZYM FROM XS WHERE ZYM=’ 计算机’ ;

3.1.1 插入表记录 这条 INSERT 语句将 XS 表中专业名为‘计算机’的各记录的学号、姓名和专业名列的值插入到 XS1 表的各行中。用 SELECT 语句可查看插入结果： num name speciality ------- -------- ----------- 061101 王林 计算机 061102 王平 计算机 061103 王燕 计算机 061104 韦严平 计算机 061105 吴庆红 计算机 061106 李方方 计算机 061107 李明 计算机 061108 林一帆 计算机 061109 张强民 计算机 061110 张蔚 计算机 061111 赵琳 计算机 061112 罗林琳 计算机 从 Oracle 10g 开始新增了 MERGE 语句，使用这个语句可以实现对表的更新或插入。

3.1.1 插入表记录 语法格式：

MERGE INTO table_name USING table_name ON (join_condition) WHEN MATCHED THEN UPDATE SET… WHEN NOT MATCHED THEN INSERT(…) VALUES(…)

Joni_condition ：连接条件。 【例 3.4 】 检查表 XS_JSJ 中的数据是否和表 XS 的数据相匹配，如果匹配则使用INSERT 子句执行插入数据行。 首先在表 XS_JSJ 使用 INSERT 语句添加一行数据： INSERT INTO XS_JSJ(XH,XM,ZYM,XB,CSSJ,ZXF) VALUES(‘101112’,’ 霍甲’ , ‘ 计算机’ ,‘ 女’ ,TO_DATE(‘19860130’,’YYYYMMDD’),36); 使用 MERGE 语句 XS_JSJ 表中新增的数据插入表 XS 中：

MERGE INTO XS a USING XS_JSJ b ON(a.XH=b.XH)

WHEN NOT MATCHED THEN INSERT Values(b.xh,b.xm,b.zym,b.xb,b.cssj,b.zxf,b.bz); 用 SELECT 语句进行查询，可以发现表中已经增加一条姓名为霍甲的学生记录。

3.1.2 删除表记录 1. 使用 DELETE 删除数据 在 PL/SQL 语言中，删除行可以使用 DELETE 语句。 语法格式：

DELETE FROM table_name view_name∣ [WHERE condition]

该语句的功能为从 table_name 指定的表或 view_name 指定的视图中删除满足condition 查询条件的行，若省略该条件，表示删除所有的行。 【例 3.5 】将 XSCJ 数据库的 XS 表中总学分小于 39 的行删除，使用如下的 PL

/SQL语句。 DELETE FROM XS

WHERE ZXF<39;

3.1.2 删除表记录 1. 使用 DELETE 删除数据 用 SELECT 语句进行查询，可以发现表中学号为“ 061102” 和“ 061105” 两行已被删除，结果为： XH XM ZYM ------- -------- ----------- 061101 王林 计算机 061103 王燕 计算机 061104 韦严平 计算机 061106 李方方 计算机 061107 李明 计算机 061108 林一帆 计算机 061109 张强民 计算机 061110 张蔚 计算机 061111 赵琳 计算机 061112 罗林琳 计算机

3.1.2 删除表记录 2. 使用 TRUNCATE TABLE 语句删除表数据 如果确实要删除一个大表里的全部记录 , 可以用 TRUNCATE 命令 , 它可以释放占用的数据块表空间。此操作不可回退。 语法格式：

TRUNCATE TABLE table_name 其中 table_name 为要删除数据的表名。由于 TRUNCATE TABLE 语句删除表中的所有数据，且不能恢复，所以使用时要谨慎。 使用 TRUNCATE TABLE 删除了指定表中的所有行，但表的结构及其列、约束、索引等保持不变。 TRUNCATE TABLE 在功能上与不带 WHERE 子句的DELETE 语句相同，二者均删除表中的全部行。但 TRUNCATE TABLE 执行速度比 DELETE 快。 对于由外键 (FOREIGN KEY) 约束引用的表不能使用 TRUNCATE TABLE 删除数据，而应使用不带 WHERE 子句的 DELETE 语句。另外， TRUNCATE TABL

E也不能用于索引视图的表。

3.1.3 修改表记录 在 PL/SQL 语言中， UPDATE 语句可以用来修改表中的数据行。 语法格式：

UPDATE table_name view_name∣ SET column_name=expression,[…n] [WHERE condition]该语句 table_name 指定的表或 view_name 指定的视图中满足 condition 查询条件的记录中由 SET 指定的各列的列值设置为 SET 指定的新值。若不使用 WHERE子句，则更新所有记录的指定列值。 【例 3.6 】 将 XSCJ 数据库的 XS 表中学号为“ 061110” 的学生备注列值改为“三好学生”，使用如下 PL/SQL 语句：

UPDATE XS SET BZ=’ 三好学生’

WHERE XH=’061110’;

3.1.3 修改表记录 用 SELECT 语句进行查询，可以发现表中学号为“ 061110” 的行的备注字段值已被修改，如图 3.2 所示。

图 3.2 修改数据以后的表

3.1.3 修改表记录 【例 3.7 】将 XS 表中的所有学生的总学分都增加 10 。 UPDATE XS SET ZXF=ZXF+10; 用 SELECT 语句进行查询，可以发现表中所有学生的总学分都增加了 10 分，如图 3.3 所示。

图 3.3 增加学分后的数据

3.1.3 修改表记录 【例 3.8 】将姓名为“罗林琳”的同学的专业改为“通信工程”，备注改为“转专业学习”，学号改为“ 061241” 。 UPDATE XS SET ZYM= ‘ 通信工程’ , BZ=‘转专业学习’ , XH= ‘061241’ WHERE XM= ‘ 罗林琳’ ; 【例 3.9 】对 XS 表进行修改，将姓名为“李明”的学生的总学分加 4 ，备注改为“提前修完《数据结构》，并获得学分”。 UPDATE XS SET ZXF=ZXF+ 4, BZ= ‘ 提前修完《数据结构》，并获得学分’ WHERE XM=’ 李明’ ;

3.2 默认值约束 对于某些字段，可在程序中定义默认值以方便用户。定义一个字段的默认值可以在定义表或修改表时，定义默认值约束。 1. 默认值约束的定义 在使用 OEM 或 SQL 命令定义表或修改表时，可定义一个字段的默认值约束。下面通过例子介绍利用 SQL 语句定义一个字段的默认值约束的方法，有兴趣的读者可以练习一下在 OEM 中定义一个字段的默认值约束。 默认值约束定义的一般格式为：

CREATE TABLE table_name (column_name datatype NOT NULL | NULL [DEFAULT constraint_expression] /* 默认值约束的表达式 */ [,…n])

table_name 为创建的表名； column_name 为列名； datatype 为对应列的数据类型； DEFAULT关键字表示其后的 constraint_expression 表达式为缺省值约束表达式，此表达式只能是常量（如字符串）、系统函数或 NULL；参数 n 表示可定义多个数据字段。

3.2 默认值约束 【例 3.10 】在定义表时定义一个字段的默认值约束。

CREATE TABLE XS ( 学号 char(6) NOT NULL, 姓名 char(8) NOT NULL, 专业名 char(10) NULL, 性别 char(2) NOT NULL, 出生时间 date NOT NULL, 总学分 number DEFAULT 0, /* 定义默认值约束 */ 备注 varchar2(200) NULL ) 【例 3.11 】在修改表时定义一个字段的默认值约束。

ALTER TABLE XS ADD(Addcolumn number(2) DEFAULT 0);

3.3 索引 为什么在字典中进行查找时能够很快地找到要查的内容呢？主要原因是字典中已按某种顺序进行了排序。 在 Oracle 10g 中，索引是一种供服务器在表中快速查找一个行的数据库结构。在数据库中建立索引主要有以下作用： (1) 快速存取数据； (2) 既可以改善数据库性能又可以保证列值的唯一性； (3) 实现表与表之间的参照完整性； (4) 在使用 ORDER BY 、 GROUP BY 子句进行数据检索时，利用索引可以减少排序和分组的时间。

3.3 索引3.3.1 索引的分类 索引按存储方法分类，可以分为 2 类： B*树索引和位图索引。 (1) B*树索引。 B*树索引的存储结构类似书的索引结构，有分支和叶两种类型的存储数据块，分支块相当于书的大目录，叶块相当于索引到的具体的书页。 (2) 位图索引。位图索引储存主要用来节省空间，减少 ORACLE 对数据块的访问，它采用位图偏移方式来与表的行 ID 号对应，采用位图索引一般是重复值太多的表字段。 索引按功能和索引对象还有以下类型： (1) 唯一索引。唯一索引意味着不会有两行记录相同的索引键值。 (2) 非唯一索引。不对索引列的值进行唯一性限制的所以称为非唯一索引。 (3) 分区索引。所谓分区索引是指索引可以分散地存在于多个不同的表空间中，其优点是可以提高数据查询的效率。 (4) 未排序索引。未排序索引也称为正向索引。 Oracle 10g 数据库中的行是按升序排序的，因此创建索引时不必指定对其排序而使用默认的顺序。 (5) 逆序索引。逆序索引也称为反向索引。该索引同样保持索引列按顺序排列，但是颠倒已索引的每列的字节。 (6) 基于函数的索引。基于函数的索引是指索引中的一列或者多列是一个函数或者表达式，索引根据函数或者表达式计算索引列的值。

3.3.2 使用索引的原则 在正确使用索引的前提下，索引可以提高检索相应的表的速度。当用户考虑在表中使用索引时，应遵循下列一些基本的原则： (1) 在表中插入数据后创建索引 (2) 索引正确的表和列 (3) 合理安排索引列 (4) 限制表中索引的数量 (5) 指定索引数据块空间的使用 (6) 根据索引大小设置存储参数

3.3.3 创建索引 创建索引有三种方法：随数据库表创建、使用 Oracle 10g管理控制台工具单独创建和使用 SQL 命令创建索引。 1. 随数据库表一起创建索引在创建数据库表时，如果表中包含有唯一关键字或主关键字，则 Oracle 10g自动为这两种关键字所包含的列建立索引。如果不特别指定，系统将默认为该索引定义一个名字。表建立之后，实际上就是在表 XS 的列 XH 上建立了一个索引，如图 3.4 所示。这种方法创建的索引是非排序索引，既正向索引，以 B*树形式存储。

图 3.4 随数据库表创建的索引

3.3.3 创建索引 2. 在 OEM 中创建索引 【例 3.11 】为 XS 表的姓名列创建索引。 在如图 3.5 所示的界面中，在方案中选择“索引”，鼠标单击左键，打开如图

3.4所示的“索引搜索”界面。

图 3.5 Oracle 企业管理器

3.3.3 创建索引 2. 在 OEM 中创建索引 在如图 3.4 所示界面，单击“创建”按钮，进入如图 3.6 所示的界面。“创建索引”窗口包含一般信息、分区、存储、选项和统计信息 5 个选项页面。

图 3.6 创建索引— 一般信息选项界面

3.3.3 创建索引 2. 在 OEM 中创建索引 (1) “ 一般信息”选项页面。它可以设置以下信息： 名称：指定索引名。创建索引时指定一个有效的 Oracle标识符。这里指定的索引名是 XS_NAME_INDEX 。 方案：单击“手电筒”形状的按钮，打开如图 3.7 所示的“搜索和选择方案”界面。

图 3.7 搜索和选择方案界面

3.3.3 创建索引 2. 在 OEM 中创建索引 表空间：指定索引所属的表空间。 表名：指定哪个表创建索引。 索引类型：指定要创建的索引是标准索引还是位图索引。 表列：指定索引列和顺序。单击“置入列”按钮，表列电子表格列出表 XS 所有的列。电子表格包含列名、数据类型、排序次序和顺序，由“顺序”字段指定索引的列及其顺序。 (2) “存储”选项选项页面。“存储”选项页面如图 3.8 所示。在该界面指定存储参数。 事件记录：指示是否生成重做日志。 区数：初始大小指定对象的第一个区的大小。 空间空闲： 区定义保留用于更新的空闲的百分比 事务处理数量：类别定义下列参数： 初始值：对象的每个数据块内分配的事务处理条目的初始数量。范围为 1~255 。 最大值：可同时更新分配给对象的数据块的并行事务处理的最大值。范围为1~255 。 缓冲池：区定义通过“缓冲池”下拉列表选择使用的缓冲池。可以选择的缓冲池包括 KEEP 、 RECYCLE 和 DEFAULT 。

3.3.3 创建索引

图 3.8 创建索引—存储选项界面

3.3.3 创建索引 2. 在 OEM 中创建索引 (3) “选项”选项页面。“选项”选项界面如图 3.9 所示。在该选项页面，可以指定如何执行并行查询、如何存储重做日志等信息。 唯一：该复选框指定表中将索引的列或列组合的值必须唯一。 逆序：该复选框指定创建逆序（从大到小）关键字索引。 并行：该复选框可用于创建一个常规表或一个按索引组织的表，并以并行方式装载。该选项可指定并行执行某一操作。程度表示单个例程的操作并行度，并行度可以有两种设置： 默认值：查询服务器数量根据 CPU 数和存储要并行扫描的表的“设备”数计算得出。 值：用户指定的查询服务器数量。 压缩：是为了避免关键字列的值重复出现，可以大大减少存储空间。 执行选项：有 3 个复选框可以勾选设置： 联机：在创建或重建索引时，允许对表进行 DML 操作。 计算统计信息：在创建或重建索引时，以非常小的代价收集统计信息。 不排序：表明存储在数据库中的数据按升序排序，所以创建索引时不对其进行排序。

3.3.3 创建索引

图 3.9 创建索引—选项选项界面

3.3.3 创建索引 2. 在 OEM 中创建索引 (4) “ 分区”选项页面。“分区”选项界面如图 3.10 所示。

图 3.10 创建索引—分区选项界面

3.3.3 创建索引 2. 在 OEM 中创建索引 对索引进行分区有 2种方法： ① 全局－范围：在使用范围分区的全局索引时 , 每个索引分区都包含由分区边界定义的值。 ② 全局－散列：在使用散列分区的全局索引时 , 每个索引分区都包含由 Oracle 的散列函数确定的值。 (5) 单击“统计信息”选项页面，出现如图 3.11 所示的界面。该界面显示索引使用情况说明。

3.3.3 创建索引

图 3.11 统计信息选项界面

3.3.3 创建索引 3. 利用 SQL 命令建立索引 使用 SQL 命令可以灵活方便地创建索引。在使用 SQL 命令创建索引时，必须满足下列条件之一： (1) 索引的表或簇必须在自己的模式中； (2) 必须在要索引的表上具有 INDEX权限； (3) 必须具有 CREATE ANY INDEX权限。 语法格式： CREATE [UNIQUE BITMAP] INDEX ∣ /* 索引类型 */ [schema.]index_name /* 索引名称 */ ON [schema.]table_name(column_name[ASC DESC],…n,[column_expression])∣ ∣ /* 索引建于表 */ CLUSTER [schema.]cluster_name /* 索引建于簇 */ [INITRANS integer] [MAXTRANS integer] [PCTFREE integer] [PCTUSED integer] /* 建立索引的物理和存储特征值 */ [TABLESPACE tablespace_name] /* 索引所属表空间 */ [STORAGE storage_clause] /* 为索引建立存储特征 */ [NOSORT] [REVERSE]

3.3.3 创建索引 3. 利用 SQL 命令建立索引 其中： UNIQUE ：指定索引所基于的列 ( 或多列 ) 值必须唯一。默认的索引是非唯一索引。 BITMAP ：指定建成位映射索引而不是 B* 索引。 Schema ：表示包含索引的方案。 ON table_name ：建立 table_name 表索引。 column_expression ：创建基于函数的索引。 ON CLUSTER ：创建 cluster_name簇索引。 NOSORT ：数据库中的行以升序保存，在创建索引时不必对行排序。 REVERSE ：指定以反序索引块的字节，不包含行标识符。

3.3.3 创建索引 【例 3.12 】为 KC 表的课程名列创建索引。 CREATE INDEX kc_name_idx ON KC(KCM) TABLESPACE "INDX"; 【例 3.13 】为 XS 表的姓名列创建索引，指定索引的物理和存储特征值，数据库中的行以升序保存。

CREATE INDEX xs_xm_idx ON XS (XM) TABLESPACE "INDX" INITRANS 2 MAXTRANS 255 NOSORT;

3.3.4 维护索引 1. 在 OEM 中维护索引 在 OEM 中维护索引的操作，除了一些特殊的信息不能再修改，其他与创建索引的操作相同。 在如图 3.4 所示的界面中，选择要维护的索引，单击“编辑”按钮，进入“编辑索引”界面，如图 3.12 所示。在各个选项页面上重新指定相应的设置，确认无误后，单击“应用”按钮，完成修改。

图 3.12 维护索引

3.3.4 维护索引 2. 利用 ALTER INDEX 命令维护索引 语法格式：

ALTER INDEX [schema.]index_name [INITRANS integer] [MAXTRANS integer] [PCTFREE integer] /* 建立索引的物理和存储特征值 */ [STORAGE storage_clause] /* 为索引建立存储特征 */ [RENAME TO new_index_name] 【例 3.14 】修改例 3.12 中创建的索引 kc_name_idx 。

ALTER INDEX admin.xs_name_index INITRANS 2 MAXTRANS 128; 【例 3.15 】重命名索引 kc_name_idx 。

ALTER INDEX kc_name_idx RENAME TO kc_idx;

3.3.5 删除索引 索引的删除既可以通过 OEM 删除，也可以通过执行 SQL 命令删除。 1. 利用 OEM 删除索引 在如图 3.4 所示的界面中，选中要删除的索引，单击“删除”，系统弹出确认界面，单击“是”按钮后，就成功删除该索引了。 2. 利用 SQL 命令删除索引 语法格式： DROP INDEX [schema.]index_name 其中， schema 是包含索引的方案。 index_name 是要删除的索引名称。 【例 3.16 】删除 XSCJ 数据库中表 XS 的一个索引名为 XS_NAME_IDX 的索引。 DROP INDEX XS_NAME_IDX;

3.4 同义词 3.4.1 创建同义词 1. 利用 OEM 创建同义词 【例 3.17 】为本地数据库 XSCJ 的表 XS创建同义词 XS 。 如图 3.5 所示，在 OEM 中，选择方案选项中的同义词，单击鼠标左键，进入“同义词搜索”界面，如图 3.13 所示。单击“创建”按钮，进入“创建同义词”界面，如图3.14 所示。

图 3.13 同义词搜索界面 图 3.14 创建同义词界面

3.4.1 创建同义词 2. 利用 CREATE SYNONYM 命令创建同义词 语法格式：

CREATE [PUBLIC] SYNONYM [schema.]synonym_name FOR [schema.]object [@dblink]

【例 3.18 】创建同义词。 (1) 为 XSCJ 数据库的 XS_KC 表创建公用同义词 XS_KC 。

CREATE PUBLIC SYNONYM XS_KC FOR ADMIN.XS_KC;

(2) 为 XSCJ 数据库 XS 表创建远程数据库同义词。 CREATE PUBLIC SYNONYM XS

FOR ADMIN.XS@MY_LINK; (3) 为 XSCJ 数据库的 CS_XS 视图创建公用同义词 CS_XS 。

CREATE PUBLIC SYNONYM CS_XS FOR ADMIN.CS_XS;

3.4.2 使用同义词 一旦创建同义词后，数据库的用户就可以直接通过同义词名称访问该同义词所指的数据库对象，而不需要特别指出该对象的所属关系。 【例 3.19 】 SYSTEM 用户查询 XSCJ 数据库 XS 表中所有学生的情况。

SELECT * FROM XS; 如果没有为 XSCJ 数据库 XS 表创建同义词 XS ，那么 SYSTEM 用户查询 XS表则需指定 XS 表的所有者。

SELECT * FROM ADMIN.XS;

3.4.3 删除同义词 1. 利用 OEM 删除同义词 在如图 3.13 所示的“同义词搜索”界面中，在搜索栏输入搜索条件，单击“开始”按钮查找要删除的同义词后，选中要删除的同义词，单击“删除”，在出现的“确认”界面，单击“是”按钮后，就能删除该同义词。 2. 利用 DROP SYNONYM 命令删除同义词 语法格式：

DROP [PUBLIC] SYNONYM [schema.]synonym_name 说明： PUBLIC 表明删除一个公用同义词。 Schema 指定将要删除的同义词的用户方案。 synonym_name 为将要删除的同义词名称。 【例 3.20 】删除公用同义词 CS_XS 。

DROP PUBLIC SYNONYM CS_XS;

3.5 数据库链接 3.5.1 创建数据库链接 1. 利用 OEM 创建数据库链接 【例 3.21 】利用 OEM创建数据库链接 MY_LINK 。 (1) 如图 3.15 所示，在企业管理中选择单击“数据库链接”，进入如图 3.16所示的“ 数据库链接搜索”界面。

图 3.15 Oracle 企业管理器 图 3.16 数据库链接搜索界面

3.5.1 创建数据库链接 1. 利用 OEM 创建数据库链接(2)单击“创建”按钮，进入“创建数据库链接”界面，如图 3.17 所示。

图 3.17 创建数据库链接界面

3.5.1 创建数据库链接 1. 利用 OEM 创建数据库链接 (3) 单击“确定”按钮，创建成功后，系统返回到图 3.16 所示的界面，完成数据库链接操作。 2. 利用 CREATE DATABASE LINK 命令创建数据库链接 语法格式：

CREATE [PUBLIC] DATABASE LINK dblink_name [CONNECT TO user IDENTIFIED BY password] USING connect_string

【例 3.22 】为 XSCJ 数据库创建一个名为 MY_PLINK 的公用链接。 CREATE PUBLIC DATABASE LINK MY_PLINK

CONNECT TO ADMIN IDENTIFIED BY MANAGE USING ‘XSCJ’;

3.5.2 使用数据库链接 创建了数据库链接，就可以使用远程数据库的对象了。 【例 3.23 】查询远程数据库 XSCJ 表 KC 中的所有课程情况。

SELECT * FROM ADMIN.KC@MY_PLINK;上述查询将通过 MY_PLINK 数据库链接来访问 KC 表，也可以为该表创建一个同义词。

【例 3.24 】为 XSCJ远程数据库表 KC创建一个同义词。 CREATE PUBLIC SYNONYM KC

FOR ADMIN.KC@ MY_PLINK; 这时数据库对象的全限定标志已被定义，其中包括通过服务名的主机和实例、通过数据库链接的拥有者 (ADMIN) 和表名 (KC) 。

3.5.3 删除数据库链接 1. 利用 OEM 删除数据库链接 在如图 3.16 所示的窗口中，选择要删除的数据库链接，单击“删除”按钮，在出现的“确认”界面，单击“是”按钮后，就能成功删除该数据库链接。 2. 利用 DROP DATABASE LINK 删除数据库链接 语法格式：

DROP DATABASE LINK dblink_name dblink_name 为要删除的数据库链接名称。 【例 3.25 】删除公用数据库链接 MY_PLINK 。

DROP DATABASE LINK MY_PLINK; 注意：公用数据库链接可由任何有相应权限的用户删除，而私有数据库链接只能由 SYS系统用户删除。

3.6 数据完整性 Oracle 使用完整性约束防止不合法的数据进入到基表中。管理员和开发人员可以定义完整性规则，以增强商业规则，限制数据表中的数据。 使用完整性约束有以下几个好处： (1) 在数据库应用的代码中增强了商业规则。 (2) 使用存储过程，完整控制对数据的访问。 (3) 增强了触发存储数据库过程的商业规则。 3.6.1 数据完整性的分类 1. 域完整性 域完整性又称为列完整性，指定一个数据集对某一个列是否有效和确定是否允许空值。

3.6.1 数据完整性的分类 【例 3.26 】定义表 KC 的同时定义学分的约束条件。

CREATE TABLE KC ( KCH char(6) NOT NULL,

KCM char(8) NOT NULL, XF NUMBER(2) CHECK ( 总学分 >=0 AND 总学分 <=10) NULL,

/* 通过 CHECK 子句定义约束条件 */ BZ VARCHAR2 NULL ); 2. 实体完整性 实体完整性也可以称为行完整性，要求表中的每一行有一个唯一的标识符，这个标识符就是主关键字。 3. 参照完整性 参照完整性又可以称为引用完整性。参照完整性保证主表中的数据与从表（被参照表）中数据的一致性。 主键：在表中能唯一标识表的每个数据行的一个或多个表列。外键：如果一个表中的一个字段或若干个字段的组合是另一个表的主键则称该字段或字段组合为该表的外键。

3.6.1 数据完整性的分类 3. 参照完整性 例如，对于 XSCJ 数据库中 XS 表的每一个学号，在 XS_KC 表中都有相关的课程成绩记录，将 XS 作为主表，学号字段定义为主键， XS_KC 作为从表，表中的学号字段定义为外键，从而建立主表和从表之间的联系实现参照完整性。 XS 和 XS_KC 表的对应关系如表 3.1 、 3.2 所示。 如果定义了两个表之间的参照完整性，则要求： (1) 从表不能引用不存在的键值。 (2) 如果主表中的键值更改了，那么在整个数据库中，对从表中该键值的所有引用要进行一致的更改。 (3) 如果主表中没有关联的记录，则不能将记录添加到从表。 (4) 如果要删除主表中的某一记录，应先删除从表中与该记录匹配的相关记录。

3. 参照完整性

3.6.1 数据完整性的分类 3. 参照完整性 完整性约束是通过限制列数据、行数据和表之间数据来保证数据完整性的有效的方法。约束是保证数据完整性的标准方法。每一种数据完整性类型，如域完整性、实体完整性和参照完整性，都可以由不同的约束类型来保障。约束确保有效的数据输入到列中和确保维护表与表之间的关系。 表 3.3 描述了不同类型的完整性约束。

表 3.3 完整性约束描述

3.6.2 约束的状态 在 Oracle 中，完整性约束有 4种状态： (1) 禁止的非校验状态。表示该约束是不起作用的，即使该约束定义依然存储在数据字典。 (2) 禁止的校验状态。表示对约束列的任何修改都是禁止的。 (3) 允许的非校验状态或强制状态。该状态可以向表中添加数据，但是与约束有冲突的数据不能添加。 (4) 允许的校验状态。表示约束处于正常的状态。这时，表中所有的数据，无论是已有的还是新添加的，都必须满足约束条件。

3.6.3 域完整性的实现 Oracle 可以通过 CHECK 约束实现域完整性。 CHECK 约束实际上是字段输入内容的验证规则，表示一个字段的输入内容必须满足 CHECK 约束的条件；若不满足，则数据无法正常输入。 (1) 通过 Oracle企业管理创建与删除 CHECK 约束在 XSCJ 数据库的 XS_KC 表中，学生每门功课的成绩一般在 0～ 100 的范围内，如果对用户的输入数据要施加这一限制，可按如下操作进行。 如图 3.18 所示，在搜索栏输入搜索条件，单击“开始”按钮进行查找。系统根据输入的条件，查找出相应的表 XS_KC ，单击“编辑”按钮，进入“编辑表”界面，如图 3.19 所示。

3.6.3 域完整性的实现

图 3.18 表搜索界面

3.6.3 域完整性的实现

图 3.19 编辑表界面

3.6.3 域完整性的实现 在“约束条件”选项卡界面，在“添加”按钮左边的下拉框选择“ CHECK” 约束条件，单击“添加”，进入“添加 CHECK 约束条件”界面，如图 3.20 所示。 名称栏输入约束名称 CH_CJ ，检查条件是“ CJ>=0 AND CJ<=100” ，单击“确定”按钮，完成 CHECK 约束的创建，系统返回到图 3.19 所示界面，单击“应用”保存上述操作。

图 3.20 添加 CHECK 约束条件界面

3.6.3 域完整性的实现 (2) 利用 SQL 语句在创建表时创建 CHECK 约束 语法格式：

CREATE TABLE table_name /* 指定表名 */ ( column_name datatype [NOT NULL | NULL]

[DEFAULT constraint_expression] /* 默认值 */ CONSTRAINT check_name CHECK(check_expression),…n) /*CHECK 约束表达式 */ 【例 3.27 】在 XSCJ 数据库中创建表 books ，其中包含所有的约束定义。

CREATE TABLE books ( book_id number(10),

book_name varchar2(50) not null, book_desc varchar2(50) DEFAULT ‘New book’, max_lvl number(3,2) not null, trade_price number(4,1) not null, CONSTRAINT ch_cost CHECK(max_lvl<=250));

3.6.3 域完整性的实现 (3) 利用 SQL 语句在修改表时创建 CHECK 约束 语法格式：

ALTER TABLE table_name ADD( CONSTRAINT check_name CHECK(check_expression)) 【例 3.28 】通过修改 XSCJ 数据库的 books 表，增加批发价字段 trade_price的CHECK 约束。

ALTER TABLE books ADD( CONSTRAINT ch_price CHECK(trade_price<=250));

(4) 利用 SQL 语句删除 CHECK 约束 CHECK 约束的删除可在 OEM 中删除，有兴趣的读者可以自己试一试，在此介绍如何利用 SQL 命令删除。 语法格式： ALTER TABLE table_name DROP CONSTRAINT check_name 功能：在 table_name 指定的表中，删除名为 check_name 的约束。 【例 3.29 】删除 XSCJ 数据库中 books 表批发价字段的 CHECK 约束。 ALTER TABLE books DROP CONSTRAINT ch_price;

3.6.4 实体完整性的实现 如前所述，表中应有一个列或列的组合，其值能唯一地标识表中的每一行，选择这样的一列或多列作为主键可实现表的实体完整性。 一个表只能有一个 PRIMARY KEY 约束，而且 PRIMARY KEY 约束中的列不能取空值。由于 PRIMARY KEY 约束能确保数据的唯一，所以经常用来定义标识列。当为表定义 PRIMARY KEY 约束时， Oracle 10g 为主键列创建唯一索引，实现数据的唯一性，在查询中使用主键时，该索引可用来对数据进行快速访问。如果 PRIMARY KEY 约束是由多列组合定义的，则某一列的值可以重复，但 PRIMARY KEY 约束定义中所有列的组合值必须唯一。 PRIMARY KEY 约束与 UNIQUE 约束的主要区别如下： (1) 一个数据表只能创建一个 PRIMARY KEY 约束，但一个表中可根据需要对不同的列创建若干个 UNIQUE 约束； (2) PRIMARY KEY字段的值不允许为 NULL ，而 UNIQUE字段的值可取 NUL

L；PRIMARY KEY 约束与 UNIQUE 约束的相同点在于：二者均不允许表中对应字段存在重复值；在创建 PRIMARY KEY 约束与 UNIQUE 约束时会自动产生索引。

3.6.4 实体完整性的实现 对于 PRIMARY KEY 约束与 UNIQUE 约束来说，都是由索引强制实现。在实现PRIMARY KEY 约束与 UNIQUE 约束时， Oracle按照下面过程来实现： (1) 如果禁止该约束，则不创建索引。 (2) 如果约束是允许的，且约束中的列是某个索引的一部分，则该索引用来强制约束。 (3) 如果约束是允许的，且约束中的列都不是某个索引的一部分，那么按照下面的规则创建索引： ① 如果约束是可延迟的，则在这种约束的列上创建一个非唯一性索引。 ② 如果约束是非可延迟的，则创建一个唯一性索引。 1. 利用 OEM 创建和删除 PRIMAY KEY 或 UNIQUE 约束 利用 OEM创建和删除 PRIMAY KEY 或 UNIQUE 约束的操作过程同利用 OE

M创建和删除 CHECK 约束基本相同，唯一的区别是在选择约束类型时选择 PRIMAY KEY 或 UNIQUE 约束，详细操作请参照利用 OEM创建和删除 CHECK 约束。

3.6.4 实体完整性的实现2. 利用 SQL 语句创建和删除 PRIMAY KEY 及 UNIQUE 约束(1) 创建表的同时创建 PRIMAY KEY 或 UNIQUE 约束语法格式：

CREATE TABLE table_name /* 指定表名 */(column_name datatype /* 定义字段 */[CONSTRAINT constraint_name /* 定义约束名 */[NOT] NULLPRIMARY KEY | UNIQUE /* 定义约束类型 */[,…n]) /*n 表示可定义多个字段 */在语法格式中，通过关键字 PRIMARY KEY 、 UNIQUE说明所创建的约束类型。

3.6.4 实体完整性的实现 2. 利用 SQL 语句创建和删除 PRIMAY KEY 及 UNIQUE 约束 【例 3.30 】对 XSCJ 数据库中 XS 表的学号字段创建 PRIMARY KEY 约束，对身份证号码字段定义 UNIQUE 约束。 CREATE TABLE XS ( XH char(6) NOT NULL CONSTRAINT PK_XH PRIMARY KEY, XM char(8) NOT NULL,

INDENTITY char(20) CONSTRAINT UN_ID UNIQUE, ZYM char(10) NULL, XB char(2) NOT NULL, CSSJ ldate NOT NULL,

ZXF number(2) NULL, BZ varchar2(100) NULL,

RXSJ date );

3.6.4 实体完整性的实现2. 利用 SQL 语句创建和删除 PRIMAY KEY 及 UNIQUE 约束 (2) 利用修改表创建 PRIMAY KEY 或 UNIQUE 约束 语法格式： ALTER TABLE table_name ADD( CONSTRAINT constraint_name PRIMARY KEY(column_name,…n)说明：这是创建 PRIMAY KEY 约束， ADD CONSTRAINT 表示对 table_name 表增加一个约束，约束名由 constraint_name 指定，约束类型为 PRIMARY KEY 。索引字段由 column_name参数指定，可包含一列或多列。语法格式： ALTER TABLE table_name ADD(CONSTRAINT constraint_name UNIQUE(column_name,…n)这是创建 UNIQUE 约束。

3.6.4 实体完整性的实现 【例 3.31 】 先在 XSCJ 数据库中创建表 XS ，然后通过修改表，对学号字段创建PRIMARY KEY 约束，对身份证号码字段定义 UNIQUE 约束。 CREATE TABLE XS ( XH char(6) NOT NULL, XM char(8) NOT NULL,

INDENTITY char(20), ZYM char(10) NULL, XB char(2) NOT NULL, CSSJ date NOT NULL,

ZXF number(2) NULL, BZ varchar2(100) NULL, RXSJ date

); ALTER TABLE XS

ADD(CONSTRAINT PK_XS PRIMARY KEY(XH)); ALTER TABLE XS

ADD(CONSTRAINT UN_XS UNIQUE(INDENTITY));

3.6.4 实体完整性的实现2. 利用 SQL 语句创建和删除 PRIMAY KEY 及 UNIQUE 约束(3) 删除 PRIMARY KEY 或 UNIQUE 约束语法格式：

ALTER TABLE table_name DROP CONSTRAINT constraint_name[,…n];

【例 3.32 】删除创建的 UN_XS 的 UNIQUE 约束。 ALTER TABLE XS DROP CONSTRAINT UN_XS;

3.6.5 参照完整性的实现 对两个相关联的表 (主表与从表 ) 进行数据插入和删除时，通过参照完整性保证它们之间数据的一致性。利用 FOREIGN KEY 定义从表的外键， PRIMARY KEY 约束定义主表中的主键 ( 不允许为空 ) ，可实现主表与从表之间的参照完整性。 定义表间参照关系，可先定义主键，再对从表定义外键约束（根据查询的需要可先对从表的该列创建索引）。 对于 FOREIGN KEY 约束来说，在创建时应该考虑以下因素： (1) 在删除主表之前，必须删除 FOREIGN KEY 约束。 (2) 如果不删除或禁止 FOREIGN KEY 约束，则不能删除主表。 (3) 在删除包含主表的表空间之前，必须删除 FOREIGN KEY 约束。

3.6.5 参照完整性的实现 1. 利用 OEM 定义表之间的参照关系 例如，要建立 XS 表和 XS_KC 表之间的参照完整性，操作步骤如下： (1) 按照前面所介绍的方法定义主表的主键。在此，定义 XS 表中的学号字段为主键。 (2) 如图 3.21 所示，选择“ FOREIGN” 约束条件，单击“添加”按钮，进入“添加FOREIGN 约束条件”界面，如图 3.22 所示。 (3) 在名称栏输入约束名称 FK_XH 。在表列项的“可用列”列表选择“ XH”字段添加到所选列列表。在引用表列项的引用表选择所关联表；单击“开始”，引入关联表所有字段到可用列列表，选择“ XH”字段添加至所选列。单击“确定”，系统会自动完成创建工作。

图 3.21 修改表界面

图 3.22 添加 FOREIGN 约束条件界面

3.6.5 参照完整性的实现 2. 利用 OEM 删除表间的参照关系利用 OEM 删除表间的参照关系和删除 CHECK 约束的方法一样，请参照删除CHECK 约束的方法。 3. 利用 SQL 命令定义表间的参照关系 前面已介绍了创建主键 (PRMARY KEY 约束 ) 的方法，在此将介绍通过 S

QL命令创建外键的方法。 (1) 创建表时同时定义外键约束语法格式：

CREATE TABLE table_name /* 指定表名 */(column_name datatype [FOREIGN KEY]

REFERENCES ref_table(ref_column)[,…n] /*n 表示可定义多个字段 */

3.6.5 参照完整性的实现 【例 3.33 】在 XSCJ 数据库中创建主表 XS ， XS.XH 为主键，然后定义从表XS_KC ， XS_KC.XH 为外键。 首先创建主表：

CREATE TABLE XS ( XH char(6) NOT NULL

CONSTRAINT PK_XH PRIMARY KEY, XM char(8) NOT NULL, ZYM char(10) NULL, XB char(2) NOT NULL, CSSJ date NOT NULL,

ZXF number(1) NULL, BZ varchar2(100) NULL );

3.6.5 参照完整性的实现然后创建从表：

CRAETE TABLE XS_KC( XH char(6) NOT NULL

CONSTRAINT FK_XH FOREIGN KEY, REFENCES XS(XH), KCH char(3) NOT NULL, CJ number(2,1), XF number(1),

);(2) 通过修改表定义外键约束语法格式：

ALTER TABLE table_nameADD CONSTRAINT constraint_nameFOREIGN KEY( column[,…n])REFERENCES ref_table(ref_column[,…n])

3.6.5 参照完整性的实现 【例 3.34 】假设 XSCJ 数据库中 KC 表为主表， KC.KCH字段已定义为主键。XS_KC 表为从表，如下示例用于将 XS_KC.KCH字段定义为外键。

ALTER TABLE XS_KC ADD(CONSTRAINT FK_KC FOREIGN KEY(KCH)

REFERENCES KC(KCH));4. 利用 SQL 语句删除表间的参照关系删除表间的参照关系，实际上删除从表的外键约束即可。语法格式与前面其它约束删除的格式相同。 【例 3.35 】删除上面对 XS_KC.KCH字段定义的 FK_kc 外键约束。

ALTER TABLE XS_KC DROP CONSTRAINT FK_KC;