略
第8章 存储过程、函数和触发器
一、填空题
1．服务器；客户
2．系统存储过程；用户自定义存储过程；临时存储过程；远程存储过程；扩展存储过程
3．局部临时存储过程；全局临时存储过程 4．#；##
5．输入参数；输出参数 6．RETURN
7．ALTER PROCEDURE
8．函数名；参数；编程语句；返回值 9．事件；命令
10．INSTEAD OF触发器；AFTER触发器；CLR触发器 11．原子性；一致性；隔离性；持久性 12．显式事务；隐式事务
13．ROLLBACK TRANSACTION
二、选择题
1．B 2．B 3．D 4．C 5．B 6．B 7．C
三、判断题
1．A 2．B 3．A 4．B
四、问答题
1．答
使用SQL Server中的存储过程而不使用存储在客户计算机本地的Transact-SQL程序的优势有：
（1）允许模块化程序设计
只需创建过程一次并将其存储在数据库中，以后即可在程序中调用该过程任意次。存储过程可由在数据库编程方面有专长的人员创建，并可独立于程序源代码而单独修改。
（2）允许更快执行
如果某操作需要大量Transact-SQL代码或需重复执行，存储过程将比Transact-SQL批代码的执行要快。将在创建存储过程时对其进行分析和优化，并可在首次执行该过程后使用该过程的内存中版本。每次运行Transact-SQL语句时，都要从客户端重复发送，并且在SQL Server每次执行这些语句时，都要对其进行编译和优化。
（3）减少网络流量
一个需要数百行Transact-SQL代码的操作由一条执行过程代码的单独语句就可实现，而不需要在网络中发送数百行代码。
（4）可作为安全机制使用
16
即使对于没有直接执行存储过程中语句的权限的用户，也可授予他们执行该存储过程的权限。
2．答
触发器是一种特殊类型的存储过程，它在指定表中的数据发生变化时自动执行。触发器与普通存储过程的不同之处在于：触发器的执行是由事件触发的，而普通存储过程是由命令调用执行的。
3．答
? 触发器是自动执行的，不需要管理员手动维护数据库的数据完整性。
? 触发器可以对数据库中的相关表进行级联更改。例如，可以在表Departments中定
义触发器，当用户删除表Departments中的记录时，触发器将删除表Employees中对应部门的记录。
? 触发器可以限制向表中插入无效的数据，这一点与CHECK约束的功能相似。但在
CHECK约束中不能使用到其他表中的字段，而在触发器中则没有此限制。例如，可以在表Employees中定义触发器，限制插入的记录其Dep_id字段值必须在表Departments中存在对应的记录。
五、上机练习题
（一）存储过程
1．答：
USE HrSystem
GO
IF EXISTS (SELECT name FROM sysobjects WHERE name ='avg_wage' AND type = 'P') DROP PROCEDURE avg_wage GO
CREATE PROC avg_wage @avgwage FLOAT OUTPUT AS
SELECT @avgwage=AVG(wage) FROM Employees
2．答：
DECLARE @avgwage FLOAT EXEC avg_wage @avgwage OUTPUT PRINT @avgwage
3．答：
USE HrSystem
GO
IF EXISTS (SELECT name FROM sysobjects WHERE name ='max_wage' AND type = 'P') DROP PROCEDURE max_wage GO
CREATE PROC max_wage
17
@depname char(50), @maxwage FLOAT OUTPUT AS
SELECT @maxwage=MAX(e.wage) FROM Employees e INNER JOIN Departments d ON e.Dep_id=d.Dep_id WHERE d.Dep_name=@depname
4．答：
DECLARE @maxwage FLOAT,@depname CHAR(50) EXEC max_wage '财务部', @maxwage OUTPUT PRINT @maxwage
5．答：
DROP PROCEDURE avg_price,max_wage
（二）触发器
1．答：
（1）创建触发器语句
USE 学生信息
IF EXISTS(SELECT name FROM sysobjects WHERE name = 'TRG1' AND type = 'TR') DROP TRIGGER TRG1 GO
CREATE TRIGGER TRG1 ON 专业 FOR INSERT AS
SELECT * FROM inserted
（2）测试语句
INSERT INTO 专业 VALUES('111','新专业名称','001')
（3）测试结果
在“网格”选项卡上显示：
在“消息”选项卡上显示：
（所影响的行数为 1 行）
2．答：
（1）创建触发器语句
USE 学生信息
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'TRG2' AND type = 'TR') DROP TRIGGER TRG2 GO
CREATE TRIGGER TRG2 ON 专业 FOR DELETE AS
RAISERROR ('不允许删除专业表中的记录',16,1) SELECT * FROM deleted
ROLLBACK TRANSACTION --回滚事务
（2）测试语句
18
DELETE FROM 专业 WHERE 专业编号='111'
（3）测试结果
在“消息”选项卡上显示：
服务器: 消息 50000，级别 16，状态 1，过程 TRG2，行 4 不允许删除专业表中的记录
（所影响的行数为 1 行）
在“网格”选项卡上显示：
3．答：
（1）创建触发器语句
USE 学生信息
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'TRG3' AND type = 'TR') DROP TRIGGER TRG3 GO
CREATE TRIGGER TRG3 ON 专业 FOR UPDATE AS
IF UPDATE(专业名称) BEGIN
RAISERROR('不能修改专业名称',15,1) ROLLBACK TRANSACTION END
（2）测试语句
UPDATE 专业 SET 专业名称='新专业名称B' WHERE 专业编号='111'
（3）测试结果
在“消息”选项卡上显示：
服务器: 消息 50000，级别 15，状态 1，过程 TRG3，行 6 不能修改专业名称
4．答：
（1）创建触发器语句
USE 学生信息
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'TRG4' AND type = 'TR') DROP TRIGGER TRG4 GO
CREATE TRIGGER TRG4 ON 学生基本信息 FOR UPDATE AS
IF UPDATE(学号) OR UPDATE (姓名) BEGIN
RAISERROR('注意，不允许修改学号或姓名',15,1)
19
ROLLBACK TRANSACTION END
（2）测试语句
① UPDATE 学生基本信息 SET 学号='990020211' WHERE 学号='990020210' ② UPDATE 学生基本信息 SET 姓名='张三' WHERE 学号='990020210'
（3）测试结果
服务器: 消息 50000，级别 15，状态 1，过程 TRG4，行 7 注意，不允许修改学号或姓名
5．答：
DROP TRIGGER TRG1,TRG2,TRG3,TRG4,TRG5
第9章 游标管理
8.3 练习题
一、填空题
1．Transact-SQL 游标；应用编程接口（API）服务器游标；客户端游标 2．静态游标；动态游标；只进游标；键集驱动游标 3．OPEN 4．SCROLL 5．FETCH
6．@@FETCH_STATUS 7．CLOSE
8．DEALLOCATE
二、选择题
1．A 2．C 3．C 4．D 5．D 6．B
三、判断题
1．B 2．A 3．B 4．B 5．A
四、问答题
1．答
用数据库语言来描述，游标是映射结果集并在结果集内的单个行上建立一个位置的实体。有了游标，用户就可以访问结果集中的任意一行数据了。在将游标放置到某行之后，可以在该行或从该位置开始的行块上执行操作。最常见的操作是提取（检索）当前行或行块。
游标有以下主要的功能：
? 允许定位在结果集的特定行。
? 从结果集的当前位置检索一行或多行。
? 支持对结果集中当前位置的行进行数据修改。
? 如果其他用户需要对显示在结果集中的数据库数据进行修改，游标可以提供不同级
20