第3章
SQL
的高级应用
3.1 SQL 中的完整性约束
SQL把各种完整性约束作为数据库模式定义的一部分。既有效防止了对数据库的意外破坏，提高了完整性检测的效率，又可以减轻编程人员的负担。
SQL对三种不同完整性约束的设置及检测，采取了不同的方式加以实现。下面分别介绍。
3.1.1 实体完整性和主码
前面1.2.4节中已讲过，实体完整性规定，主码的任何属性都不能为空，因为，概念模型中实体和联系都是可区分的，而且它们以码为唯一性标识。如果，主码的属性值可以为空，则意味着在概念模型中存在着不以码为唯一性标识的实体。这显然是前后矛盾的。
那么怎样保证实体完整性呢？SQL中实体完整性是通过主码来实现的。一旦某个属性或属性组被定义为主码，该主码的每个属性就不能为空值，并且在关系中不能出现主码值完全相同的两个元组。
主码的定义是在Create Table 语句中使用 Primary Key关键字来实现的。方法有两种：
a) 在属性定义后加上关键字 Primary Key；
b) 在属性表定义后加上额外的定义主码的子句：Primary Key（<主码属性名表>）
（例如P42例3.1）
说明：
² 如果主码仅由一个属性组成，上述两种方法都可定义，若由两个或以上的属性组成，则只能用上述第二种方法定义了。
² 对于候选码的说明方法，可以用Unique说明该属性的值不能重复出现。Unique的使用与Primary Key相似。
² 一个表中只能有一个主码定义，但可以有多个Unique说明。
² SQL中，并没有强制为每个关系指定主码，但为每个关系指定主码通常会更好一些。（因为主码的指定可以确保关系的实体完整性）
3.1.2 参照完整性约束与外部码
参照完整性是对关系间引用数据的一种限制。即：若属性组A是基本关系R1的外码，它与基本关系R2的主码K相对应，则R1中每个元组在A上的值必须：要么取空值，要么等于R2中某元组的主码值。
一、外部码约束的说明：
SQL中就是利用外部码的说明来实现参照完整性约束，限制表中某些属性的取值的。外部码的说明也有两种方法：
1、在该属性的说明后直接加上关键字”REFERENCES <表名>（<属性名>）”，其中表名称为参照关系名，属性名称为参照关系的主码。
2、在Create Table 语句的属性清单后，加上外部码说明子句，格式为：
FOREIGN KEY <属性名表1> REFERENCES <表名>(<属性名表2>)
上式中的属性名表1和属性名表2中属性可以多于一个，但必须前后对应。
（见P43下部 例3.3）
二、参照完整性约束的实现策略
前面讲了，外部码的取值只有两种情况：要么取空，要么取参照关系中的主码值。可是当用户操作违反了这个规则时，如何保持此约束呢？
SQL提供了三种可选方案：
1、RESTRICT(限制策略)：
当用户对表进行违反了上述完整性约束、条件的插入、删除或修改操作时，将会被系统拒绝。
2、CASCADE（级联策略）：
当对参照关系进行删除和修改时，SQL所提供的一种方案。在这种策略下，当删除或修改参照关系中某元组的主码值时，被参照关系中，那些外部码具有该值的元组也将被删除或修改，以保证参照完整性。
3、SET NULL（置空策略）：
置空策略也是针对参照关系的删除或修改操作的。在这种策略下，当删除参照关系中的某一元组或修改某一元组的主码值时，被参照关系中外码值等于该主码值的元组在该外码上的值将被置空
说明：
当用户不指定参照完整性的实现策略时，一般被默认为RESTRICT(限制策略)。实现策略的说明通常被加在外部码的说明后面，格式为：ON DELETE SET NULL ON UPDATE CASCADE。
3.1.3 用户自定义完整性约束
对于用户自定义完整性约束，SQL提供了非空约束、对属性的CHECK约束、对元组的CHECK约束、触发器等来实现用户的各种完整性要求。
1、非空约束：
在CRETE TABLE 中的属性定义后面加上NOT NULL关键字即定义了该属性不能取空值。
2、基于属性的CHECK约束
使用CHECK（检查）子句可保证属性值满足某些前提条件。其一般格式为：
CHECK（<条件>）
它既可跟在属性定义的后面，也可在定义语句中另增一子句加以说明。
如：CHECK（age>=18 AND age<=65）;
CHECK（sex IN (“男”,”女”)）;
CHECK（dno IN(select dno from department)）;
从上例中可以看出，CHECK子句的条件中还可以带子查询。
3、基于元组的CHECK约束
如P46 例3.6工资表salary 的定义中的CHECK（Insure+Fund<Basepay）;
基于元组的CHECK约束往往要涉及到表中的多个域。所以它是元组约束。在对整个元组完成插入或对某一元组的修改完成之后，系统将自动检查是否符合CHECK条件表达式。若不符合条件，系统将拒绝该插入或修改操作。
基于元组CHECK约束的说明方法是在CREATE TABLE语句中的属性表、主码、外部码的说明之后加上CHECK子句。
3.1.4 约束的更新
约束与数据库中的表和视图一样，可以进行增、删、改的更新操作。为了改和删约束，需要在定义约束时对其进行命名，在各种约束的说明前加上关键字CONSTRAINT 和该约束的名称即可。
例如：在employee表的create table语句中：
eno char(4) CONSTRAINT PK_employee PRIMARY KEY,
dno char(4)CONSTRAINT FK_employee FOREIGN KEY REFERENCES department(dno);
当对各种约束进行命名后，就可以用ALTER TABLE语句来更新与属性或表有关的各种约束。如：
ALTER TABLE employee DROP CONSTRAINT FK_employee;
ALER TABLE Salary ADD CONSTRAINT RightSalary CHECK(Insure+Fund<Rest);
上述的增加约束，实际上也是通过ALTER TABLE语句定义约束的一种形式。
SQL不能直接修改约束，修改某一个约束实际上是用ALTER TABLE 语句先删除该约束，然后再增加一个与该约束同名的新约束。
3.2 SQL中的触发器
触发器（Trigger）不仅能实现完整性规则，而且能保证一些较复杂业务规则的实施。
它是一类由事件驱动的特殊过程，一旦由某个用户定义，任何用户对该触发器指定的数据进行增、删或改操作时，系统都将自动激活之，并在核心层对其进行集中的完整性控制。
3.2.1 触发器的组成和类型
触发器的定义包括两个方面：
² 指明触发器的触发事件：
²
指明触发器执行的动作。
1、触发事件包括：表中行的插入、删除和修改，即执行INSERT、DELETE、UPDATE语句，在UPDATE中还可以指定，特定的属性或属性组的修改为触发条件。
事件的触发，还有两个相关的时间：Before 和 After。顾名思义，before触发器是在事件发生之前触发，After触发器是在事件发生之后触发。
2、触发动作：是一系列SQL语句，可以有两种方式：
（1）对被事件影响的每一行（FOR EACH ROW）——每一元组执行触发过程。称为行级触发器。
（2）对整个事件只执行一次触发过程（FOR EACH STATEMENT），称为语句级触发器。此为触发器的默认方式。
3.2.2 创建触发器
1、建触发器的语句格式：
CREATE TRIGGER<触发器名>［｛BEFORE｜AFTER｝］
｛［DELETE｜INSERT｜UPDATE OF［列名清单］］｝ON <表名>
[REFERENCING <临时视图名>]［WHEN<触发条件>］
<触发动作>［FOR EACH｛ROW｜STATEMENT｝］
2、说明
（1）BEFORE／AFTER： 指示DBMS在执行触发语句之前／后激发触发器
（2）DELETE／INSERT：指明是DELETE／INSERT触发器，每当一个DELETE／INSERT语句从／向表中删除／插入一行时激发触发器。
（3）UPDATE：指明是UPDATE触发器，每当UPDATE语句修改由OF子句指定的列值时，激发触发器。（如果忽略OF子句，则意味着修改表中任何列值时，DBMS都将激发触发器）。
（4）RFFERENCING<临时视图名>：指定临时视图的别名。
在触发器运行过程中，系统会生成两个临时视图，分别存放被更新的值（旧值）和更新后的值（新值）。对于行级触发器，默认临时视图分别为OLD和NEW；对于语句级触发器，默认为OLD_TABLE和NEW_TABLE。一旦触发器运行结束，临时视图将不复存在。
（5）WHEN<触发条件>：指定触发器的触发条件。当满足触发条件时，DBMS才激发触发器。触发条件中必须包含临时视图名，不包含查询。
参考P48 下部例3.9。
3.3 嵌入式SQL的使用
SQL语言是非过程化的、面向集合的数据操纵语言，它大部分语句的使用的是独立的，与上下文条件无关。而在事务处理中，常常需要流程控制，而SQL语句却不具备这种控制。另一方面，普通编程语言（如C、BASIC、PASCAL、FORTRAN等）虽然具有很强的流程控制能力，而在涉及数据库的操作时，却不能高效率地进行数据的存取。所以将SQL语句嵌入到普通编程语言中使用，很好地结合了编程语言的过程性和SQL语言的数据操纵能力。从而提高了数据库应用程序的效率。
把这种嵌入到普通编程语言中的SQL语言称为嵌入式SQL语言（Embedded SQL）。
在这里把嵌入SQL语句的普通编程语言称为宿主语言或主语言。
DBMS有两种方法处理嵌入式SQL语言：预编译和扩充编译程序法。
² 预编译方法：是站在DBMS一方来处理嵌入式的SQL语句。DBMS的预编译器首先把它们从程序代码中识别出来，并转换成纯宿主语言源代码，最后由宿主语言编译器将整个代码译成目标码。
²
扩充编译程序法：是站在宿主语言一方来处理嵌入式SQL语句的。它是通过修改和扩充宿主语言的编译程序。使其能够直接处理SQL语句。
目前使用较多的上述第一种方法，即预编译方法。
使用嵌入式SQL，必须解决下列三个问题：
（1）如何让预编译器识别程序代码中的SQL语句，即程序中必须要有区分SQL语句与宿主语句的标记；
（2）DBMS和宿主语言程序如何进行信息传递，即进行数据交换；
（3）一条SQL语句原则上可产生或处理一组记录，而宿主语句一次只能处理一个记录，如何协调好这两种处理方式。
下面就如何解决上术三个问题来对嵌入式SQL展开介绍。
3.3.1嵌入式SQL的一般形式
两个基本相同：
² 在交互式和嵌入式两种不同的使用方式下,SQL语言的语法结构基本相同;
²
各个DBMS在实现嵌入式SQL时，对不同的宿主语言，所用的方法基本相同。
DBMS在宿主语言中怎样识别SQL语句？也就是嵌入式SQL得告诉DBMS，它在宿主语言的什么地方开始，什么地方结束？即它的开始标志和结束标志是什么？
² 开始标志：EXEC SQL
²
结束标志：视不同的宿主语言而不同，如C语言中以“；”作为结束标志。
所以嵌入式SQL语句的一般形式为：开始标志+SQL语句+结束标志
例如在C语言中的嵌入式SQL为以下标准形式：EXEC SQL <SQL语句>；
嵌入式SQL语句按功能的不同分为：可执行语句和说明性语句；
可执行语句又可分为：数据定义、数据控制和数据操纵三种。
3.3.2嵌入式SQL与宿主语言之间的信息传递
一句话：是通过宿主语言程序变量（简称主变量Host variable）来实现的。
1、主变量的作用
SQL语句引用主变量时，变量名前加“：”，以区别于数据库对象名（如列名、表名、视图名等），因此主变量可以与数据库变量同名。反过来，在宿主语言中引用主变量时就无需再加“：”。
2、主变量的说明
在嵌入式程序中，所有主变量，除系统定义的外，都必须由SQL语句加以说明，说明放在两个嵌入式SQL之间：
EXEC SQL BEGIN DECLARE SECTION；
〈主变量说明段〉
EXEC SQL END DECLARE SECTION；
记住两点：（1）说明段中变量说明的格式必须符合宿主语言的要求；（2）变量的数据类型必须是宿主语言和SQL都能处理的类型。
3、一个系统定义的主变量――SQLCA
SQLCA（SQL Communication Area）称为SQL通信区，它是一个全局变量，供应用程序和DBMS通信之用。其说明格式与对主变量的说明格式不同：
EXEC SQL INCLUDE
SQLCA；
SQLCA.SQLCODE是SQLCA的一个分量，属于整数类型，供DBMS向应用程序报告SQL语句的执行情况。每执行一条SQL语句，返回一个SQLCODE代码。
² SQLCODE＝0，表示SQL语句执行成功，无异常情况出现；
² SQLCODE为负整数，表示SQL语句执行失败，具体负值表示错误的类型；
² SQLCODE为正整数，表示SQL语句已执行，但出现了意外的情况。如当SQLCODE＝100时表示语句已执行，但无数据可取，如DB中无满足条件的记录。
在不同的系统中，上述代码值的含义可能有所不同。
参看P61例3.10。
4、指示变量
宿主语言的主变量不能取空值，而DB中的字段值可以为空（NULL），那么当SQL给主变量赋值时或者从还未赋值的主变量处取值时，遇到空值的情况如何处理。
嵌入式程序常用的方法是：在主变量后紧跟一个短整型变量，指示它的空值情况，这个短整型变量就称为指示变量。
² 一个主变量可以附带一个指示变量（Indicator Variable）；
² 指示变量也是一种主变量，紧跟在主变量之后，指示主变量的取值情况；
² 指示变量为一个短整数，其值为0时表示相应字段值不为空，并可以将该值赋给主变量；当指示变量为负值时，说明对应字段值为空，不能将空值赋给相应的主变量，主变量将保持原值不变；
² 指示变量的说明和其它使用与主变量相同；
² 主变量按其作用可分为输入主变量和输出主变量。当主变量用来向SQL输入参数时被称为输入主变量，当被用来输出SQL的执行结果时被称为输出主变量。
参看P52下半页例子
3.3.3 游标
² 游标被用来协调SQL语言和宿主语言的不同数据处理方式。
² 游标是系统为用户开设的一个数据缓冲区，用来存放SQL语句的执行结果。
SQL语句执行的结果可能是若干条数据表的记录，而宿主语言每次只能处理一条数据记录，所以SQL不能一下子就把所有的执行结果都交给宿主语言，需要暂时把它们存放在一个数据缓冲区中，然后被宿主语言一条一条地处理。这个缓冲区就是系统提供给用户的所谓游标。
游标的操作包括四个步骤：
1、
说明游标
用DECLARE 语句为一条SELECT语句定义游标，格式为:
EXEC SQL DECLARE <游标名> CURSOR FOR <SQL语句>;
其中:
² SQL语句既可以是简单查询，也可以复杂的嵌套查询或连接查询；
² SQL语句的查询结果是一个新的关系；
²
说明游标只是一条说明性语句，此时DBMS并不执行SELECT指定的查询操作。
2、
打开游标
使用OPEN语句打开游标，格式为： EXEC SQL OPEN <游标名>；
² 打开游标，将执行相应的SELECT语句；
² 满足查询条件的所有记录被从表中取到缓冲区中；
²
游标被激活，指针指向结果集的第一条记录。
3、
推进游标指针并取当前记录
使用FETCH语句，格式为：
EXEC SQL FETCH <游标名> INTO <主变量名列表>；
其作用是将缓冲区的当前记录取出送至主变量，供宿主语言作进一步处理，同时将游标指针向前推进一条记录。其中：
² 主变量列表中的变量间用“，”号分开，并与SELECT语句中的目标列一一对应；
² FETCH语句通常用在循环语句中；
²
如果结果集中记录已被取完，则SQLCA.SQLCODE返回100。
4、
关闭游标
用CLOSE语句关闭游标，释放缓冲区和其它资源。其格式为：
EXEC SQL CLOSE
<游标名>
用以上格式关闭的游标，可以用OPEN语句重新打开，重新打开后的查询结果与上次打开的查询结果不一定相同。取决于打开时的数据库状况。
参看P53例3.11
嵌入式SQL语句，有些可直接嵌入到宿主语言，而不需要使用游标，这些语句有：
² 说明性语句；
² 数据定义语句；
² 数据控制语句；
² INSERT语句（见例3.10）；
² 查询结果为单记录的SELECT语句（见例3.10）；
²
对满足条件的当前记录（或记录集），由系统根据用户要求，自动进行修改或删除的UPDATE和DELETE语句。
需要使用游标的语句有：
² 查询结果为多记录的SELECT语句（见例3.11）;
² 对满足条件的结果集中记录分别进行修改或删除的UPDATE和DELETE语句。
3.3.4 通过游标的更新和删除
² 对满足条件的记录，进行一次性处理，不需要使用游标。如果只想修改或删除其中的某条记录，则需要使用带游标的SELECT语句。
² 用UPDATE或DELETE语句修改或删除游标指向的记录时，要用
WHERE CURRENT OF <游标名>
子句来指明游标指针指向的记录。
² 在定义游标时，如果是为使用游标的UPDATE语句说明游标，在SELECT语句中要用
FOR UPDATE OF <列名>
子句指明检索出的数据在指定列上是可修改的。
参见P55例3.14和P56例3.15
3.3.5 动态SQL介绍
前面所讲的嵌入式SQL语句都以下有两个共同的特点：
² 语句中主变量的个数和数据类型在预编译时都是确定的；
²
主变量的值是在程序运行过程中动态输入的。
把具有这两个共同特点的嵌入式SQL语句称为静态SQL语句。
静态SQL语句必须在编译前全部确定，并交由嵌入式SQL预处理器进行编译。
但有些嵌入式SQL允许用户在程序运行时动态构造、提交SQL语句，以使编程更为灵活。我们把这种嵌入式SQL语句称为动态SQL语句。
解读P57下半页例子
² 例中动态SQL程序包含一个“?”称为占位变量，其所在位置的值在SQL程序执行时由主变量提供。
² 在动态SQL语句中也可以包含多个占位变量，在执行时，将按其在句中出现的先后，依次用USING后的宿主变量取代。
² 一般在预编译时下列信息不能确定，需要使用动态SQL技术：
Ø SQL语句正文；
Ø 主变量个数；
Ø 主变量的数据类型；
Ø SQL中引用的数据库对象（如：列、基本表、视图等）。
3.4 SQL中的存储过程
定义：存储过程（Stored Procedure）同触发器一样，也是由一组SQL语句组成，预先编译后存储在数据库中，供应用程序多次调用，类似于高级语言中的函数。
创建格式：
CREATE PROCEDURE <存储过程名>［<参数列表>］AS <过程体>；
说明：
Ø 参数列表：可以没有，也可以由一个或多个参数说明组成，参数之间用“，”号隔开，每个参数说明格为：
@参数名 数据类型 ＝内定值］［OUTPUT］
Ø 过程体：由一组SQL语句和必要的流程控制语句组成。
Ø 为了使存储过程的设计更方面，功能更强大，SQL中增加了些流程控制语句，主要有如下几种：
（1）赋值语句；
（2）分支语句，如:IF…THEN…ELSE…ENDIF语句，CASE语句等；
（3）循环语句，如：FOR、WHILE、REPEAT等语句；
（4）调用存储过程语句CALL或EXECUTE和从存储过程返回语句RETURN。
参看P58例3.16和例3.17（要做更正）。
3.5 SQL中的事务
3.5.1 事务的概念
先看一例，银行有一笔转账业务要执行，将一笔款从帐户一转给帐户二，采取两个步骤：第一步：从帐户一上减掉这笔款项；第二步：给帐户二加上这笔款项。
设想一下，如果执行完第一步后，系统出现了故障，此时帐户一减去了这笔钱，而帐户二还没来得急加上，这说造成了数据库的不一致性。所以，为了保证银行业务的正常开展，上述两个步骤要么都执行，要么都不执行。
为此，SQL提出了事务的概念。
所谓事务：就是用户定义的一个数据库操作序列，是一个不可分割的工作单元。
SQL的一个事务，可以是一个SQL语句、一组SQL语句或整个程序。SQL语言中处理事务的语句有三条：
BEGIN TRANSACTION ／ COMMIT ／ ROLLBACK
（1）BEGIN TRANSACTION：定义事务开始。
（2）COMMIT：提交，使事务成功地结束。
（3）ROLLBACK：回滚，因故障引起事务不能正常进行，要求系统回滚到事务开始前的状态。
事务可以嵌套使用，一个事务可以包含多个子事务。
在嵌套事务中，必须先完成所有子事务后，才能提交顶级事务。
如果最外层事务提交失败，则不管子事务是否已提交，所有的子事务都将回滚。
参看P60下部例3.18
3.5.2 事务的特性
为了保证数据完整性，要求数据库系统维护以下事务的性质：
1、原子性（Atomicity）
事务在逻辑上是数据库的最基本工作单位，一个事务中包含的操作，要么全部执行并正常结束，要么什么都不做。
2、一致性（Consistency）
事务的执行结果必须使数据库从一个一致性状态变到另一个一致性状态。因此，事务的原子性是事务一致性的保证。
3、隔离性（Isolation）
数据库系统中多个事务可以被并发执行，但必须保证一个事务的执行不能被其它事务干扰。
4、持久性（Durability）
一个事务一旦提交，它对数据库中数据的改变是持久的。即数据库因故障而受到破坏，DBMS也应能正确地恢复之。
事务的以上四个特性也称为ACID特性。保证事务的ACID特性是DBMS的重要任务之一。
3.6 小 结
这一讲，我们主要学习了SQL语言的以下内容：
Ø 数据库的完整性约束，保证了合法用户对数据库的更新不会导致数据一致性的破坏。SQL中使用主码说明实现实体完整性；使用外码说明实现参照完整性约束，保证了数据表之间的数据一致性及完整性。基于属性或元组的约束检查，可用来保证用户自定义完整性的实现。当更新数据库中数据时，系统将自动检查以上各种约束。
Ø 触发器是当预定事件发生时，系统要执行的过程。它可用来实现较复杂的完整性约束规则；
Ø 嵌入式SQL语言的使用，可以用编程语言的过程结构，弥补SQL语言实现复杂应用方面的不足；
Ø 存储过程，类似于高级语言中的函数，它是由一组SQL语句组成，经编译后存储在数据库中，供应用程序多次调用。使用存储过程可提高数据库应用程序效率，减少网络流量；
Ø
把SQL语句组成事务。通过事务管理，可保证无论是否在故障发生，数据库都处于一致性状态。