存储过程与触发器.docx
《存储过程与触发器.docx》由会员分享，可在线阅读，更多相关《存储过程与触发器.docx（21页珍藏版）》请在冰豆网上搜索。
存储过程与触发器
第8章存储过程与触发器
教学目标
通过本章学习，使学生掌握SQLServer存储过程的创建、运行、查看、修改和删除等基本操作方法，掌握DML触发器的基本概念及其创建、触发、查看、修改和删除等基本操作，解决实际的应用问题。
教学要求
知识要点
能力要求
关联知识
存储过程
（1）掌握存储过程的基本概念和特点
（2）掌握存储过程的建立、执行和修改等基本操作方法
CREATEPROCEDURE，EXECUTE，ALTERPROCEDURE等语句
触发器
（1）掌握触发器的基本概念和分类
（2）掌握触发器的创建、触发和修改等基本操作方法
CREATETRIGGER，
ALTERTRIGGER等语句
重点难点
存储过程的基本概念和特点
存储过程的创建、执行和修改方法
触发器的基本概念和分类
触发器的创建、触发和修改方法
8.1任务描述
本章完成项目的第8个任务：
在大学生选课管理数据库Student中，完成以下基本操作：
1．创建一个添加学生选课信息的存储过程。
2．创建一个删除学生选课信息的存储过程。
3．创建一个修改学生选课信息的存储过程。
4．创建一个统计某被选课程的平均成绩、最高和最低成绩的存储过程。
5．创建一个INSERT触发器。
6．创建一个DELETE触发器。
7．创建一个UPDATE,INSERT触发器。
8.2存储过程综述
8.2.1存储过程的概念
存储过程是数据库的一种对象，是为了实现某个特定任务，以一个存储单元的形式存储在服务器上的一组SQL语句的集合。
用户也可以把存储过程看成是以数据库对象形式存储在SQLServer中的一段程序或函数。
存储过程是由一系列的SQL语句或控制流程语句组成的。
存储过程的特点：
●接收输入参数并以输出参数的形式将多个值返回至调用过程。
●包含执行数据库操作的编程语句。
●向调用过程或批处理返回状态值，以表明成功或失败及失败原因。
使用存储过程的优点：
●增强安全机制。
SQLServer可以只给用户访问存储过程的权限，而不授予用户访问存储过程引用的对象（表或视图）的权限。
这样，可以保证用户通过存储过程操作数据库中的数据，而不能直接访问与存储过程相关的表，从而保证了数据的安全性。
●提高执行速度。
用户可以多次使用存储过程的名称调用存储过程。
存储过程在第一次执行时进行编译，然后将编译好的代码保存在高速缓存中，当用户再次执行该存储过程时，调用的是高速缓存中的编译代码，因此，其执行速度要比执行相同的SQL语句快得多。
●减少网络流量。
存储过程中包含大量的SQL语句，但是它以一个独立的单元存放在服务器上。
调用执行过程中，只需传递执行存储过程的调用命令即可将执行结果返回调用过程或批处理，从而减少了网络上数据的传输。
8.2.2存储过程的类型
在SQLServer2008中，存储过程可以分为三种类型：
用户定义的存储过程、系统存储过程和扩展存储过程。
●用户定义的存储过程：
用户定义的存储过程是用户根据需要，为完成某一特定功能，在自己的普通数据库中创建的存储过程。
●系统存储过程：
系统存储过程以sp_为前缀，主要用来从系统表中获取信息，为系统管理员管理SQLServer提供帮助，为用户查看数据库对象提供方便。
系统存储过程存储在资源数据库中。
●扩展存储过程：
扩展存储过程以xp_为前缀，它是关系数据库引擎的开放式数据服务层的一部分，其可以使用户在动态链接库（DLL）文件所包含的函数中实现逻辑，从而扩展了SQL的功能，并且可以像调用SQL过程那样从SQL语句调用这些函数。
这里主要介绍用户定义的存储过程。
8.3创建、执行、修改和删除存储过程
8.3.1创建存储过程
存储过程是数据库的一种对象，只能在当前数据库中创建存储过程，且存储过程名必须唯一。
创建存储过程的语法格式：
CREATEPROCEDURE[dbo.]存储过程名
[形式参数定义]
[WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]
AS
[BEGIN]
{过程体语句组}
[END]
其中：
●形式参数定义格式为：
{@形式参数数据类型[=默认值][OUTPUT]}[,……n]
形式参数分为输入参数和输出参数。
OUTPUT指示参数为输出参数。
●RECOMPILE：
表明该过程将在执行时重新编译。
●ENCRYPTION：
表示加密存储过程文本。
在创建存储过程时，一般是先编写实现存储过程功能的SQL语句，然后进行执行调试，待调试成功后，再按照存储过程的语法创建存储过程。
8.3.2执行存储过程
调用存储过程的语法格式：
EXECUTE存储过程名[实际参数][WITHRECOMPILE]
其中，实际参数格式为：
{输入参数对应的表达式|@输出参数对应的变量OUTPUT}[,……n]
注意：
实际参数与形式参数从类型、个数和顺序必须一一对应。
下面通过例题学习存储过程的创建和执行方法。
1．创建和执行无参存储过程
【例8-1】在数据库Student中，创建一个存储过程st_jsjbj，该存储过程可列出计算机系的所有班级名称。
USEStudent
GO
CREATEPROCEDUREst_jsjbj
AS
Selectbj班级名称FromstabWheressx=’计算机系’
GO
在查询编辑器中执行以上代码，创建存储过程st_jsjbj。
调用该存储过程：
USEStudent
GO
EXECUTEst_jsjbj
GO
【例8-2】在数据库Student中，创建一个存储过程st_xmax，该存储过程可列出选修人数最多的课程号、课程名和学生人数。
USEStudent
GO
CREATEPROCEDUREst_xmax
AS
BEGIN
CREATEVIEWctab_view1（课程号,课程名,学生人数）
ASSelectctab.kch,ctab.kcm,count（*）
Fromctab,sctab
Wherectab.kch=sctab.kch
GroupByctab.kch,ctab.kcm
Select*Fromctab_view1
Where学生人数>=ALL（Select学生人数Fromctab_view1）
DropViewctab_view1
END
GO
在查询编辑器中执行以上代码，创建存储过程st_xmax。
调用该存储过程：
USEStudent
GO
EXECUTEst_xmax
GO
2．创建和执行有参存储过程
（1）带有输入参数
【例8-3】在数据库Student中，创建一个存储过程st_xk，该存储过程可通过输入的学号来列出该学生的选课信息。
USEStudent
GO
CREATEPROCEDUREst_xk@nochar（6）
AS
IFExists（Select*FromsctabWherexh=@no）
Selectstab.xm姓名,ctab.kcm课程名,sctab.cj成绩
Fromstab,ctab,sctab
Wherestab.xh=sctab.xhandctab.kch=sctab.kchandsctab.xh=@no
ELSE
PRINT‘此学生无选修课！
’
GO
在查询编辑器中执行以上代码，创建存储过程st_xk。
调用该存储过程：
例如：
利用该存储过程查看’100006’号学生的选课情况。
USEStudent
GO
EXECUTEst_xk‘100006’
GO
（2）带有输出参数
【例8-4】在数据库Student中，创建一个存储过程st_gsp，该存储过程可以返回高等数学这门课的平均成绩。
USEStudent
GO
CREATEPROCEDUREst_gsp@pfdecimal（5,1）=0OUTPUT
AS
Select@pf=Avg（cj）Fromsctab
Wherekch=（SelectkchFromctabWherekcm=’高等数学’）
GO
在查询编辑器中执行以上代码，创建存储过程st_gsp。
调用该存储过程：
利用该存储过程输出高等数学这门课的平均成绩。
USEStudent
GO
DECLARE@pjdecimal（5,1）
EXECUTEst_gsp@pjOUTPUT
PRINT‘高等数学平均成绩为：
’+STR（@pj）
GO
（3）带有多个参数
【例8-5】在数据库Student中，创建一个存储过程st_ksp，该存储过程可根据输入的课程名称返回该课程的平均成绩。
USEStudent
GO
CREATEPROCEDUREst_ksp
@kmvarchar（20）,@pfdecimal（5,1）OUTPUT
AS
IFExists（Select*FromctabWherekcm=@km）
Select@pf=Avg（cj）Fromsctab
Wherekch=（SelectkchFromctabWherekcm=@km）
ELSE
SET@pf=0
GO
在查询编辑器中执行以上代码，创建存储过程st_ksp。
调用该存储过程：
例如，利用该存储过程输出高等数学这门课的平均成绩。
USEStudent
GO
DECLARE@pjdecimal（5,1）
EXECUTEst_ksp‘高等数学’,@pjOUTPUT
PRINT‘高等数学平均成绩为：
’+STR（@pj）
GO
8.3.3查看存储过程
查看存储过程信息语法格式：
sp_helptext存储过程名
【例8-6】查看数据库Student中存储过程st_ksp的信息。
USEStudent
GO
sp_helptextst_ksp
GO
8.3.4修改存储过程
修改存储过程语法格式：
ALTERPROCEDURE[dbo.]存储过程名
[形式参数定义]
[WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]
AS
[BEGIN]
{过程体语句组}
[END]
【例8-7】修改数据库Student中的存储过程st_gsp，使该存储过程返回程序设计这门课的最高成绩。
USEStudent
GO
ALTERPROCEDUREst_gsp@pfdecimal（5,1）=0OUTPUT
AS
Select@pf=Max（cj）Fromsctab
Wherekch=（SelectkchFromctabWherekcm=’程序设计’）
GO
8.3.5删除存储过程
删除存储过程语法格式：
DROPPROCEDURE存储过程名
【例8-8】删除数据库Student中的存储过程st_jsjbj.
USEStudent
GO
DROPPROCEDUREst_jsjbj
GO
8.4存储过程的重新编译与加密
8.4.1存储过程的重新编译
存储过程第一次执行后，其被编译的代码将驻留在高速缓存中，当用户再次执行该存储过程时，SQLServer将其从缓存中调出执行。
有时，在使用了存储过程后，可能会因为某些原因，必须向表中新增加数据列或者为表新添加索引，从而改变了数据库的逻辑结构，而SQLServer不自动进行优化，直接下一次重新启动后，再运行该存储过程。
这时，需要对它进行重新编译，使存储过程能够得到优化。
SQLServer提供三种重新编译存储过程的方法，下面分别介绍。
1．在建立存储过程时设定重新编译
在CREATEPROCEDURE命令中指定WITHRECOMPILE选项。
【例8-9】在数据库Student中，建立一个存储过程st_kq，该存储过程可根据输入的课程号列出其被选修的情况，要求SQLServer在每次执行该过程时都要重新编译。
USEStudent
GO
CREATEPROCEDUREst_kq@khchar（3）
WITHRECOMPILE
AS
IFExists（Select*FromsctabWherekch=@kh）
Selectctab.kch课程号,ctab.kcm课程名,count（*）选修人数
Fromctab,sctab
Wherectab.kch=sctab.kchandsctab.kch=@kh
GroupByctab.kch,ctab.kcm
ELSE
PRINT‘此课无人选修！
’
GO
2．在执行存储过程时设定重新编译
在EXECUTE命令中指定WITHRECOMPILE选项。
【例8-10】调用数据库Student中的存储过程st_ksp，输出高等数学这门课的平均成绩，且对该存储过程强制重新编译。
USEStudent
GO
DECLARE@pjdecimal（5,1）
EXECUTEst_ksp‘高等数学’,@pjOUTPUTWITHRECOMPILE
PRINT‘高等数学平均成绩为：
’+STR（@pj）
GO
3．使用系统存储过程对存储过程设定重新编译
语法格式：
EXECsp_recompile存储过程名
【例8-11】对数据库Student中的存储过程st_gsp设定重新编译。
USEStudent
GO
EXECsp_recompilest_gsp
GO
8.4.2存储过程的加密
在CREATEPROCEDURE命令中指定WITHENCRYPTION选项。
【例8-12】在数据库Student中，创建一个加密存储过程st_nkq，该过程可列出未选修任何课程的学生信息。
USEStudent
GO
CREATEPROCEDUREst_nkq
WITHENCRYPTION
AS
Selectxh学号,xm姓名Fromstab
WherexhNOTIN（SelectDISTINCTxhFromsctab）
GO
8.5触发器综述
触发器是一种特殊类型的存储过程。
与存储过程类似，它也是由SQL语句组成的，可以实现一定的功能。
不同的是，触发器的执行不能通过名称调用来完成，而是当用户对数据库进行操作时，如INSERT、DELETE、UPDATE数据时，将会自动触发执行与该操作相关的触发器，使其自动执行。
触发器不允许带参数，它的定义与表紧密相连，即作用于表的触发器，可以作为表的一部分，该表称为触发器表。
在SQLServer2008中，触发器分为两大类：
DML触发器和DDL触发器。
这里主要介绍DML触发器。
DML触发器是对表或视图进行了INSERT、DELETE和UPDATE操作而激活的触发器，该类触发器有助于在表或视图中修改数据时强制业务规则，扩展数据完整性。
根据引起触发器自动执行的操作，DML触发器分为三种类型：
INSERT、DELETE和UPDATE触发器。
根据触发器被激活的时机，DML触发器分为两种类型：
AFTER触发器和INSTEADOF触发器。
AFTER触发器又称后触发器，当引起触发器执行的操作成功完成之后激发该类触发器。
如果因操作错误而执行失败，触发器将不会执行。
此类触发器只能定义在表上，不能创建在视图上。
可以为每个触发操作（INSERT、DELETE或UPDATE）创建多个AFTER触发器。
INSTEADOF触发器又称为替代触发器，该类触发器代替触发操作执行，即触发器在数据发生变动之前被触发执行，取代变动数据的操作（INSERT、DELETE或UPDATE操作），执行触发器定义的操作。
该类触发器可在表和视图上定义。
只能为每个触发操作（INSERT、DELETE或UPDATE）创建一个INSTEADOF触发器。
DML触发器包含复杂的处理逻辑，能够实现复杂的数据完整性约束。
同其它约束相比，它有以下优点：
●触发器自动执行。
系统内部机制可以侦测用户在数据库中的操作，并自动激活相应的触发器执行，实现相应的功能。
●触发器能够对数据库中的相关表实现级联操作。
触发器是基于一个表创建的，但是可以针对多个表进行操作，实现数据库中相关表的级联操作。
●触发器可以实现比CHECK约束更为复杂的数据完整性约束。
在数据库中为了实现数据完整性约束，可以使用CHECK约束或触发器。
CHECK约束不允许引用其他表中的列来完成检查工作，而触发器可以引用其他表中的列。
●触发器可以评估数据修改前后表的状态，并根据其差异采取对策。
●一个表中可以同时存在三种不同操作的触发器（INSERT、DELETE或UPDATE）。
8.6触发器的创建执行、修改和删除
8.6.1触发器的创建执行
1．Inserted表和Deleted表
在创建触发器前，需要了解两个与触发器紧密相关的专用临时表：
Inserted表和Deleted表。
系统为每个触发器创建专用临时表，其表结构与触发器作用的表结构相同。
专用临时表被存放在内存中，由系统进行维护，用户可以对其查询，不能对其修改。
触发器执行完成后，与该触发器相关的临时表被删除。
当向表中插入数据时，如果该表存在INSERT触发器，触发器将被触发而自动执行。
此时，系统将自动创建一个与触发器表具有相同表结构的Inserted临时表，新的记录被添加到触发器表和Inserted表中。
Inserted表中保存了所有新插入记录的副本，方便用户查找当前的插入数据。
当从表中删除数据时，如果该表存在DELETE触发器，触发器将被触发而自动执行。
此时，系统将自动创建一个与触发器表具有相同表结构的Deleted临时表，用来保存触发器表中被删除的记录。
Deleted表中保存了所有被删除的记录，方便用户查找当前删除的数据。
当修改表中的数据时，就相当于删除一条旧的记录，添加一条新的记录，其中被删除的记录存放在Deleted表中，同时添加的新记录存放在Inserted表中。
2．创建触发器
只能在当前数据库中创建触发器，且触发器的名称必须唯一。
创建触发器是指为哪个表或视图创建触发器，当对这个表进行操作（INSERT、DELETE和UPDATE）时，其相应的触发器就会被自动触发执行。
语法格式：
CREATETRIGGER触发器名ON触发器的表名
[WITHENCRYPTION]
{FOR}{[INSERT][,][DELETE][,][UPDATE]}
AS
[IFUPDATE（列）[{AND|ORUPDATE（列）}[……n]]
[BEGIN]
{触发器语句组}
[END]
其中：
●WITHENCRYPTION：
表示对触发器文本进行加密；
●INSERT、DELETE、UPDATE：
用于指定触发器类型；
●IFUPDATE（列）……：
指定对表中字段进行增加或修改内容时起作用，不能用于删除操作。
说明：
本命令创建的都是AFTER触发器。
【例8-13】在数据库Student中，为教师信息表ttab建立一个名称为del_js的DELETE触发器，其作用是当删除某个教师的记录时，检查教师教课信息表tctab中是否存在该教师的上课信息，如果存在则提示不允许删除该教师的记录。
USEStudent
GO
CREATETRIGGERdel_jsONttab
FORDELETE
AS
DECLARE@jhchar（4）/*变量@jh用于存放删除的教师的编号*/
Select@jh=jshFromDeleted
/*从Deleted表中提取刚删除的教师记录的教师号*/
IFExists（Select*FromtctabWherejsh=@jh）
BEGIN
PRINT‘该教师能够上课，不能删除！
’
ROLLBACKTRANSACTION/*撤销以前的所有操作，回到起始状态*/
END
GO
在查询编辑器中执行以上代码创建触发器del_js。
【例8-14】删除教师信息表ttab中编号为’0002’的教师记录，观察触发器del_js的作用。
USEStudent
GO
DELETEFromttabWherejsh=’0002’
GO
注意：
如果触发器表上存在约束，则在INSTEADOF触发器执行后、在AFTER触发器执行前检查这些约束。
如果违反了约束，则回滚INSTEADOF触发器操作并且不执行AFTER触发器。
【例8-15】设数据库Teaching中有如下两个数据表：
其中“总分”指每个学生的中文、英文和数学的总分。
表8-1score（学生成绩表）
学号
姓名
中文
英文
数学
（续表）
100001
孙大亮
78
90
67
100005
良关英
76
54
91
100009
张小红
68
92
83
100012
王伟
56
87
90
100016
孙启名
67
78
98
100034
和大卫
76
80
91
表8-2total（学生成绩汇总表）
学号
姓名
总分
100001
孙大亮
235
100005
良关英
221
100009
张小红
243
100012
王伟
233
100016
孙启名
243
100034
和大卫
247
在数据库Teaching中，为表score创建一个名称为add_cj的INSERT触发器，当向该表中添加新的学生的成绩记录时，会自动计算该学生的中文、英文和数学的总分并将其相应信息添加在total表中。
USETeaching
GO
CREATETRIGGERadd_cjONscore
FORINSERT
AS
DECLARE@xhchar（6）,@xmvarchar（10）,@zfint
Select@xh=score.学号,@xm=score.姓名,
@zf=score.中文+score.英文+score.数学
Fromscore,Inserted
Wherescore.学号=Inserted.学号
INSERTINTOtotalvalues（@xh,@xm,@zf）
GO
【例8-16】在数据库Teaching中，为表score创建一个名称为del_cj的DELETE触发器，当删除该表中某个学生的成绩记录时，同时也会将total表中该学生的相应记录删掉。
USETeaching
GO
CREATETRIGGERdel_cjONscore
FORDELETE
AS
DEC
升级会员 