9．4 练习与实训
图9．9 应用CASE搜索型结构查询成绩，显示成绩等级
4）WHILE语句
WHILE语句用于构成循环结构，即按条件重复执行某SQL语句或语句块。其语法格式如下：
WHILE＜布尔表达式＞
｛sql语句或语句块｝
［BREAK］
｛sql语句或语句块｝
［CONTINUE］
｛sql语句或语句块｝
说明：
WHILE后面通常是一个语句块，称为循环体。
＜布尔表达式＞返回TRUE或FALSE，若为TRUE则执行WHILE下面的循环体，否则结束循环。
BREAK：立即结束循环。
CONTINUE：跳出本次循环，忽略该语句后面的语句，开始下一次循环。
5）RETURN语句
RETURN语句通常用于存储过程或用户自定函数之中，用来终止当前代码段的执行，从存储过程或函数中退出。RETURN还可以返回一个整数值给调用者。其语法格式如下：
RETURN［整型表达式］
6）WAITFOR语句
用于暂停或延迟执行本语句后续的代码，其常用格式如下：
WAITFOR
｛DELAY ＜延迟时长＞｜TIME＜时间点＞｝
说明：
DELAY：表示推迟一段时间后再继续执行后续代码，＜延迟时长＞用于指定推迟时间的量。
TIME：表示暂停后续代码，直到指定的＜时间点＞达到后再继续执行WAITFOR语句后续的代码。
例如，WAITFOR delay′00：00：20′表示暂停20 s，而WAITFOR time′18：30：00′表示暂停执行，直到18点30分再继续执行。
7）GOTO语句
用于使程序无条件跳转到指定的目标位置，执行该位置开始的语句。目标位置用标签指示。例如：
…
Lab：print ′跳转至此′
…
Goto Lab
（8）批处理
一个批处理是以GO语句结束的SQL语句集合。某些SQL语句必须处于一个批处理的第一句位置。
（9）事务
事务是一种机制，它将对数据库进行的一系列更改归入一个逻辑操作，即作为一个单元来提交或取消。事务可确保遵循原子性（atomicity）、一致性（consistency）、隔离性（isolation）和持续性（durability）这4种属性通常称为ACID特性，以使数据能够正确地提交到数据库中。
9．2．3 存储过程
（1）存储过程的基本概念
存储过程（Stored Procedure）是使用SQL语言编写的一段具有某种功能的程序，它存储在数据库中，可以被其他程序调用。设计存储过程是为了处理频繁的查询、业务规则或公共例程。应用程序通过指定存储过程的名称并提供其所需的参数而执行存储过程，从而完成业务处理或获得返回值。
（2）存储过程的类型
SQL Server 2005支持的存储过程分5种类型：系统存储过程、本地存储过程、临时存储过程、远程存储过程和扩展存储过程。
1）系统存储过程
系统存储过程是在安装SQLServer时由SQLServer自动建立的，它以sp＿开头命名并被存储在master数据库中。系统存储过程主要为用户提供方便，数目很多，此前使用过的sp＿help，sp＿helpdb，sp＿helptext等都是系统存储过程。
2）本地存储过程
本地存储过程是由用户创建、存储在用户数据库中的存储过程，用来完成某一特定的功能。一般情形下，人们所说的存储过程几乎都是指的本地存储过程。
3）临时存储过程
临时存储过程是临时存储在tempdb数据库中的，建立后它只在与服务器继续连接的期间生存。临时存储过程分为本地临时存储过程和全局临时存储过程。
本地临时存储过程的名称是以“＃”开头的。只有创建它的用户才能执行它，这是“本地”的含义。一旦该用户断开了与SQL Server的连接，它就会被自动地从数据库中删除，这就是“临时”的含义。
全局临时存储过程的名称以“＃＃”开头。一旦全局临时存储过程被创建，此后连接到此服务器的用户都可以调用它，无需特定的权限。这就是“全局”的含义。当全局存储过程的创建者断开连接后，系统检查有无其他用户正在调用这个全局存储过程，若无，则系统将立即删除该存储过程；若有，则会让该存储过程继续被执行，但是不允许新的调用，待所有执行都完成后，SQL Server就会自动删除这个全局存储过程。
4）远程存储过程
远程存储过程是存储在远程服务器上的存储过程。
5）扩展存储过程
扩展存储过程是用户使用SQL Server之外其他程序语言所设计的存储过程。扩展存储过程可以弥补SQL Server的不足，以扩展存储过程的功能。
（3）存储过程的规划和设计
在一个数据库系统中需要哪些存储过程应该分析系统实际需求并结合系统的安全和性能进行规划、设计。一般而言，最常用到的数据查询、数据更新、数据备份，比较复杂的查询和统计，等等，可规划设计成存储过程。
（4）创建存储过程
在SQL Server中，可以使用SQL Server Management Studio的图形界面和使用T-SQL语句创建存储过程。这里只介绍用T-SQL语句创建、执行存储过程的方法，图形化方法留给读者自行学习。
在T-SQL语言中，使用CREATE PROCEDURE语句创建存储过程，其基本语法格式如下：
CREATE PROC［EDURE］ ＜存储过程名＞
［｛＠参数名 数据类型｝［＝默认值］［OUTPUT］］［，爥n］
［WITH ENCRYPTION］
AS
＜sql语句＞
说明：
◆CREATE PROCEDUE：在一个批处理中，CREATE PROCEDUE语句不能与其他SQL语句合并在一起。保留字PROCEDURE可以简写前面4个字母。
◆＜存储过程名＞：为新建的这个存储过程指定名称，避免使用sp＿开头。
◆＠参数名 数据类型：定义该存储过程需要的参数及其数据类型，可以无参数，也允许使用多个参数。参数名以一个＠开头。
◆OUTPUT：指明该参数是输出参数，使调用方可以获得相应的返回值。
◆WITH ENCRYPTION：加密此存储过程的定义文本。
◆AS：指明以下定义存储过程要执行的动作。
◆＜sql语句＞：SQL语句或语句块，是存储过程要执行的动作。
执行已创建的存储过程可使用EXECUTE命令，其语法格式为：
［EXECUTE］＜存储过程名＞［＠参数名＝］｛值｜＠变量［OUTPUT］［，爥n］｝
其中，＠参数名是输入参数。若以＠参数名＝｛值｜变量｝的形式，则参数的次序可以与定义时的次序不同，如果省略了＠参数名，则实参的排列次序必须与定义时形参的次序完全一致。除定义时指定了默认值的输入参数在调用时可不提供实参之外，其余所有输入参数都必须提供实参。
1）创建和执行不带参数的存储过程
例9．4 创建一个无参数的存储过程p01，用它统计各门课程的选修人数、平均分数、最高分数、最低分数。
创建该存储过程的语句如下：
create procedure p01
as
select cno，count（sno），avg（score），max（score），min（score）
from sc
group by cno
go
执行结果如图9．10所示。
执行该存储过程为
Excute p01
2）创建和执行带输入参数的存储过程
例9．5 创建和执行带输入参数的存储过程p02。p02按指定的学号查询该学生选修课程的情况。缺省学号时就查询所有学生。
创建存储过程p02的SQL语句如下：
create proc p02
图9．10 无参数的存储过程
＠sno char（7）＝′%′
as
select student．sno，sn，sc．cno，cn，score
from student join sc on student．sno＝sc．sno join course on sc．cno＝course．cno
where student．sno like rtrim（＠sno）
go
执行p02查询学号为′1101001′的学生选课情况。以下3种方式均可执行该任务：
方式1：exec p02′1101001′
方式2：exec p02＠sno＝′1101001′
方式3：declare＠xh char（7）
set＠xh＝′1101001′
exec p02＠xh
其中，方式1是直接按位置次序传递参数值给存储过程。方式2是以参数名传递参数值。方式3是利用变量给参数传值（提示：变量名可以与参数名不同）。
如果不给参数执行p02，将查询出所有学生的选课记录，如图9．11所示。
图9．11 如果定义了默认值，可以缺省实参执行存储过程
3）创建和执行带输入参数和输出参数的存储过程
例9．6 创建一个存储过程p03用来调整指定课程的成绩，调整的规则是若成绩在56分至59分则将其调整为60分。如果有成绩被调整过则显示被调整的记录条数，否则显示“此区间没有需调整的记录。”执行以下代码，创建存储过程p03：
create proc p03
＠course＿no char（5），／倡课程号倡／
＠updated＿rec＿number int output／倡用于返回被修改的记录数倡／
AS
update sc set score＝60
where（cno＝＠course＿no）and（score between 56 and 59）
set＠updated＿rec＿number＝＠＠ROWCOUNT
go
代码中的＠＠ROWCOUNT是系统变量，它是返回其上一行语句执行后受影响的记录行数。这里将它赋予输出参数以获得被调整了成绩的记录数。
现在执行存储过程p03调整课程C05，执行代码及结果如图9．12所示。
图9．12 执行带输入、输出参数的存储过程
（5）查看存储过程
要查看存储过程的信息和源代码，可以在SQL Server Management Studio的对象资源管理器中，依次展开数据库→可编程性，鼠标右键单击某个存储过程名，然后选择“属性”即可打开其属性窗口。
使用系统存储过程sp＿helptext可以查看存储过程的源代码，例如：
Exec sp＿helptext′p01′
注意：已加密的存储过程是看不到其源代码的。
（6）修改存储过程
在SQL Server Management Studio的对象资源管理器中，鼠标右键单击存储过程名，选择快捷菜单中的“修改”选项即可在查询编辑窗口打开其源代码。
使用SQL的Alter Procedure语句也可修改存储过程。其语法格式与Create Procedure相比，除第一个关键字Alter之外，其余一致。请读者参见前述Create Procedur语法格式或查阅联机丛书。
（7）删除存储过程
使用T-SQL语句可以删除一个或多个存储过程，其格式如下：
DROPProcedure｛＜存储过程名＞｝［，爥n］
（8）重命名存储过程
使用快捷菜单可以很方便地更改存储过程的名称。使用sp＿rename也可以重命名存储过程。其格式如下：
EXECUTE sp＿rename＜现在名称＞，＜新的名称＞
9．2．4 触发器
（1）触发器的基本概念
触发器是用户根据需要创建在数据库中的由事件启动的程序。本质上，触发器也是存储过程，也是SQL语句和流程控制语句的集合并提前编译、存储在数据库中的。它们的区别是启动机制完全不同，触发器是因事件发生从而触发它启动，而存储过程是人为调用它而被执行的。
（2）DDL触发器和DML触发器
SQL Server 2005可创建两类触发器：DDL触发器和DML触发器。
DDL触发器响应DDL语言，即它是由DDL事件（CREATE，ALTER或DROP）触发启动。一般用于3个方面：一是防止对数据库架构进行某些修改；二是执行一些动作响应数据库架构的更改；三是记录架构中的更改或事件。
DML触发器响应DML语言，是由INSERT，UPDATE和DELETE事件触发启动，因此它是建立在数据表上的。用户可以针对某张表对这些操作分别设置触发器，也可以针对两种或3种操作设置触发器。
通常，DML触发器与某些数据表相关联，当这些数据表发生插入、更新、删除等操作事件时，相关联的触发器就会启动。触发器的功能被设计成检查这些操作是否符合数据的有效性和完整性或其他需要执行的关联性操作。
①可通过数据库中的相关表实现级联更改。
②DML触发器可以防止恶意或错误的INSERT，UPDATE以及DELETE操作，并强制执行比CHECK约束定义的限制更为复杂的其他限制。与CHECK约束不同，DML触发器可引用其他表中的列。例如，触发器可使用另一个表中的SELECT比较插入或更新的数据，以及执行其他操作，如修改数据或显示用户定义错误信息。而CHECK约束设置在数据表的内部，只能完成诸如字段有效性检查。
③DML触发器可以评估数据修改前后表的状态，并根据该差异采取措施。
一个触发器所定义的操作以及触发它的语句被作为单个事务，如果在执行触发器的过程中发生了错误（如磁盘空间不足），则整个事务将会被自动回滚———撤销前面的操作、还原到初始状态。
（3）DML触发器的类型
DML触发器又分为两种：AFTER型触发器和INSTEAD OF型触发器。
1）AFTER型触发器
AFTER型触发器又称事后型触发器，因为它是在DML操作已经完成之后才触发的。AF-TER触发器主要用来记录数据变动或对变动进行检查和处理。如果发现变动是不被允许的，可以执行事件回滚语句（ROLLBACK TRANSACTION）撤销对数据的变动操作。
2）INSTEAD OF型触发器
这是一种替换型触发器，它用来取代对数据表记录插入、删除或更新操作，是在事前被触发的。
每个DML触发器工作时，SQL Server就为它定义两个特殊表：一个是插入表INSERTED，另一个是删除表DELETED。这两个表建立在数据库服务器的内存之中，结构与触发器所在的数据表完全一致，它们由系统管理，用户只能读取、没有修改的权限。当触发器工作完成后，这两张表就会被从内存中删除。INSERTED表和DELETED表中的数据见表9．3。
表9．3 INSERTED表和DELETED表中的数据
（4）创建DML触发器
创建DML触发器可使用SQL Server Management Studio，也可使用T-SQL语言。实际上前一种方法也需要创建者在模板文档中输入、修改需要的SQL语句，因此这里直接介绍使用T-SQL语言创建触发器的方法。创建触发器的T-SQL语句格式如下：
CREATE TRIGGER ＜触发器名＞
ON｛＜数据表＞｜＜视图＞｝
｛FOR｜AFTER｜INSTEAD OF｝
｛［INSERT］，［UPDATE］，［DELETE］｝
［WITH ENCEYPTION］
AS
｛sql语句［；］［爥n］｝
说明：
◆＜数据表＞｜＜视图＞：执行DML事件的表或视图，视图只能与INSTEAD OF触发器配合。如果一个可更新的视图在定义时使用了“WITH CHECK OPTION”选项，则在这个视图上也不允许创建INSTEAD OF触发器。
◆FOR｜AFTER：创建事后型触发器，在对表的DML操作正常完成后触发器才被触发动作。保留字FOR与AFTER等价。如果表上定义了约束，则约束的作用将优先于触发器，即这种情况下触发器不会被触发。
◆INSTEAD OF：创建替代性触发器。对表的DML操作会被触发器中指定的操作替代。
◆｛［INSERT］，［UPDATE］，［DELETE］｝：指定在被指定的数据表或视图上执行哪些数据操作时触发这个触发器。3项操作中至少要指定一种操作。若是两项及以上，两项之间要用逗号分隔。
◆［WITH ENCEYPTION］：对本语句的文本加密。
◆｛sql语句［；］［爥n］｝：触发器被触发启动后要执行的操作。
例9．7 创建AFTER型触发器tri01，当在课程表course中插入一行数据后，即显示“已插入一条课程记录！”。
创建这个触发器的T-SQL语句如下：
USE JXGL
GO
CREATE TRIGGER tri01 ON course
AFTER INSERT
AS
PRINT′已插入一条课程记录！′
GO
语句成功执行后，在对象资源管理器course表的“触发器”节点中查看到tri01，如图9．13所示。
图9．13 创建AFTER型触发器tri01
下面向course表插入一条课程记录，然后查询该表。其执行结果如图9．14所示。
图9．14 测试事后型触发器tri01
例9．8 创建替代型触发器tri02，用于阻止删除教师授课表中的记录，并显示提示信息。
创建该触发器的T-SQL语句如下：
USE JXGL
GO
CREATE TRIGGER tri02
ON tc
INSTEAD OF DELETE
AS
rollback transaction
print′不允许删除教师授课的记录数据！′
GO
触发器创建成功后，再使用T-SQL语句删除tc表中的记录以此测试这个触发器的工作，结果表明此触发器功能正常，如图9．15所示。
图9．15 测试替代型触发器tri02
例9．9 创建DDL型触发器tr03，当修改或删除数据库jxgl中的数据表时它就撤销该操作，并显示提示信息。
本例中使用回滚事务的ROLLBACK TRANSACTION语句达到撤销修改或删除表的操作。创建语句及实际测试结果如图9．16所示。
图9．16 创建和测试DDL触发
提示：如要想在对象资源管理器中看到此DDL触发器，请依次打开数据库jxgl→“可编程性”→“数据库触发器”文件夹。
（5）查看触发器信息
已经创建成功的触发器可以通过以下方式查看其信息。
1）使用系统存储过程查看触发器信息
◆sp＿help：可查看触发器的名称、所有者、创建时间等一般信息。
◆sp＿helptext：可查看触发器的定义代码。
◆sp＿depends：可查看指定表上所建的触发器，包括名称、所有者、类型等基本信息。
2）通过SQL Server Management Studio查看触发器的依赖关系。
在SQL Server Management Studio对象资源管理器中，鼠标右键单击触发器名称，选择快捷菜单中“查看依赖关系”选项即可打开查看到依赖于此触发器的对象和此触发器依赖的对象。
（6）修改触发器
①使用系统存储过程sp＿rename修改触发器的名称。
②使用SQL Server Management Studio修改触发器代码。在对象资源管理器中，鼠标右键单击触发器名称，选择快捷菜单中的“修改”命令，即可打开代码，改写后单击工具栏“分析”按钮（√）或按“Ctrl＋F5”键检查语法是否有误，无误后保存。
③使用Alter Trigger语句修改触发器代码。此语句结构与创建触发器的语句格式基本类似，在此不予赘述，请读者自行查阅联机丛书或其他相关材料。
（7）禁止和启用触发器
触发器一旦创建就处于启用状态，即随时可被触发的状态。实际应用中，有时可能必须关闭某些触发器使之其不能被触发启动。当然被禁止的触发器也可重新启用，使其处于待触发状态。相应的T-SQL命令如下：
ALTER TABLE＜表名＞｛ENABLE｜DISABLE｝TRIGGER｛ALL｜＜触发器名称＞［，爥n］｝
说明：
◆ALL｜＜触发器名称＞：要禁止的触发器，ALL表示该表上定义的所有触发器，否则要指定其名称，如果有两个以上则触发器名称之间要用逗号分隔。
◆＜表名＞：定义上述触发器的表。
◆｛ENABLE｜DISABLE｝：分别指示启用或禁止触发器。禁止并不是删除它，它还在原来的数据库中。禁止仅仅是使它不能被事件触发。
（8）删除触发器
在SQL Server Management Studio对象资源管理器中，使用右键快捷菜单可很方便地删除触发器。如果使用T-SQL语句，可按照以下格式：
DROP TRIGGER＜触发器名＞
9．3 任务小结
本次任务为教学管理数据库系统设计和创建了系统所需的存储过程和触发器。有了存储过程，使那些经常被执行的操作代码以数据库对象形式长期存储在数据库中，简化了操作、减少了网络流量。有了触发器实现了比约束更严格的数据库完整性控制。
在相关知识部分，介绍了T-SQL基本流程控制语句、批处理、事务等基本知识，重点介绍了存储过程、触发器的基本概念、创建和管理方法。
9．4 练习与实训
（1）填空题
①SQL是_____的缩写，它已成为数据库的_____。
②SQL语言的功能有4个，它们是_____、_____、_____和_____。
③SQL server中的变量分为两种，全局变量和局部变量。其中，全局变量的名称以_____字符开始。
④全局变量是由_____定义和维护。局部变量以_____字符开始，由 _____定义和赋值。
⑤BEGIN爥END 语句用于_____。
⑥SQL中IF．．．ELSE．．．是用于构成_____的控制语句，构成循环结构的语句是_____。CASE 语句的两种类型是_____和_____。
⑦以sp＿开头的存储过程是_____，用户自己创建的存储过程是_____。
⑧CREATE PROCEDURE 是用于_____的T-SQL语句，CREATE TRIGGER 是用于_____的T-SQL语句。
⑨运行时，不需提供数据的存储过程是_____，需要提供数据的_____，输出参 数的作用是_____。
⑩执行存储过程时，给输入参数提供值的方式有_____、_____和_____。⑪按事件语言的类型，触发器分为_____和_____两种。
⑫建立在数据表上的触发器，按其启动时间分为_____和_____。它们可以响 应的事件有_____ 、_____和_____。
⑬事件回滚的语句是_____。
⑭执行触发器时，SQL Server 自动建立的特殊表是_____和_____。
⑮禁止触发器的T-SQL命令是_____，启用触发器的命令是_____。
（2）问答题
①什么是批处理？
②事件的含义是什么？
③什么是存储过程？
④简述触发器与存储过程的异同。
⑤触发器主要用途是什么？它与约束的不同点是什么？
（3）操作题
①分析华龙社区图书室需要哪些存储过程，设计并创建这些存储过程，然后用实际任务测试其功能是否正常。
②分析华龙社区图书室需要哪些触发器，设计并创建这些触发器，然后进行实际测试。