第8章 存储过程与触发器

第8章

存储过程与触发器8.1设计和管理存储过程存储过程与函数和vfp中的过程类似，是SQL服务器上一组预编译的T-SQL语句；用于完成某项任务。1、存储过程的类型（1）系统存储过程:存在master数据库中，名称以sp开头（2）用户定义存储过程:（3）扩展存储过程:存在于DDL(动态链接库)中，名称以xp_开头2、存储过程的主要优点（1）模块化编程。（2）快速执行（3）减少网络通信量（4）提供安全机制8.1.1存储过程概述8.1.2创建存储过程1．格式CREATEPROCEDURE[架构名称.]存储过程名

[{@parameter数据类型}[=default]--设置默认值。[OUTPUT]--说明@parameter参数为一返回值。[,..n][WITHencryption|recompile]--对过程加密。[FORREPLICATION]--不能在服务器上执行为复制创建的存储过程AS

<SQL语句>]2．存储过程的各选项设置规则1）@parameter是过程中的参数。在CREATEPROCEDURE语句中可以声明一个或多个参数。2）如果定义了default值，则无需指定此参数的值即可执行过程。默认值必须是常量或NULL3）OUTPUT选项指示参数是输出参数。4）如果创建存储过程时，使用WITHencryption子句，过程定义将以不可读的形式存储。5）FORREPLICATION不能在服务器上执行为复制创建的存储过程。6）<SQL语句>指定过程要执行的操作7）可以在存储过程内引用临时表。8.1.2创建存储过程【例8.1】创建一个存储过程proc_student1用于显示学号为“J0402”的学生基本信息（包括学生学号、姓名、性别、系）。CREATEPROCEDUREproc_student1ASSELECT学号,姓名,性别,系

FROMsWHERE学号=’J0402’GO8.1.2创建存储过程1.格式

EXEC|EXECUTE[@返回状态=]存储过程名称[@形参=]{value|@变量[OUTPUT]|[DEFAULT][,...n]2.说明(1)“@返回状态”是保存过程的返回状态值。“@形参”是在定义存储过程时的参数。(2)在采用“@形参=value”格式时，参数名称和常量不必按顺序提供。但是，如果参数使用了“@形参=value”格式，则对后续的所有参数均必须使用该格式。(3)“value”是传递给存储过程的参数值。如果参数名称没有指定，参数值必须以在存储过程中定义的顺序提供。8.1.3执行存储过程【例8.2】现在我们就来执行刚才创建好的存储过程proc_student1，显示学号为“J0402”的学生基本信息（包括学生学号、姓名、性别、系）。方法一：在SQLServerManagementStudio中执行存储过程方法二：使用ＳＱＬ命令在新建的查询窗口输入命令：Executeproc_student18.1.3执行存储过程存储过程和调用程序之间通过参数来传递数据！1.参数存储过程的参数在创建时声明，SQLServer支持两种参数：输入参数和输出参数。1）输入参数输入参数允许调用程序为存储过程传送数据值。但必须事先在CREATEPROCEDURE语句中声明一个或多个形参。2）输出参数输出参数允许存储过程将形参的数据值返回给调用程序中的实参。OUTPUT关键字用来指出输出参数。8.1.4存储过程的参数和状态值1)输入参数例8.3:创建一个有输入参数的存储过程proc_student2，显示指定学号的学生基本信息（包括学生学号、姓名、性别、系）。执行该存储过程显示学号为J0404的学生信息。createprocedureproc_student2@numchar(6)asSELECT学号,姓名,性别,系FROMsWHERE学号=@numexecproc_student2@num='J0404'8.1.4存储过程的参数和状态值例8.4:创建存储过程s_info。根据学生姓名和学号查询学生的学号，姓名，性别和所在系。ifexists(selectnamefromsysobjectswherename='s_info'andtype='p')dropprocedures_infocreateprocs_info@stnamevarchar(8),@stsnovarchar(10)asSELECT学号,姓名,性别,系FROMsWHERE姓名=@stnameand学号=@stsnogoexecs_info李丽,J0401

8.1.4存储过程的参数和状态值例8.7:

创建存储过程s_like，根据姓名，查询学生的姓名和平均成绩。如果执行时，没带参数，则显示姓陈的学生平均成绩。Createprocedures_like@stnamevarchar(8)=‘陈%’Asselect姓名,平均成绩=avg(sc.成绩)fromsc,swheres.学号=sc.学号ands.姓名like@stname

groupby姓名GoExecutes_likeExecutes_like‘李丽’8.1.4存储过程的参数和状态值例8.8:创建存储过程s_count，根据课程名，检索选修某门课程的学生人数。Createprocdure

s_count@ctnamevarchar(30)=NULLAsif@ctnameisNULLprint‘请输入课程名！’

elseselect课程名,学生选修人数=count(学号)fromsc,cwherec.课程号=sc.课程号andc.课程名=@ctname

groupby课程名GoExecutes_count‘c语言’8.1.4存储过程的参数和状态值2)输出参数

作用：将过程定义中的形参的值返回到调用程序中。例8.11：建立一个过程，用于显示指定学号的各门课程平均成绩，并返回该生的平均成绩。createprocproc_student3@numchar(16),@savgsmallintoutputasselect@savg=avg(成绩)fromsjoinscons.学号=sc.学号wheres.学号=@numgodeclare@savg_valuesmallintexecproc_student3@num='J0401',@savg=@savg_valueoutputselect@savg_valueas‘平均成绩’8.1.4存储过程的参数和状态值如果没有output关键字结果会怎么样2)输出参数例8.12：创建过程sg,根据输入的学号和课程号，显示某个同学指定课程的成绩并返回该成绩。Createprocsg@snvarchar(8)=‘j0401’,@cnvarchar(3)=‘c02’,@grsmallintoutputAsSelect学号,课程号,成绩fromscwheresc.学号=@snandsc.课程号=@cnSelect@gr=成绩fromscwheresc.学号=@snandsc.课程号=@cngoDeclare@myscore

smallintset@myscore=0Executesg@sn=‘j0402’,@gr=@myscoreoutput8.1.4存储过程的参数和状态值2．返回值存储过程可以返回整型状态值，表示过程是否成功执行。默认返回代码为0，表示成功执行；若返回-1到-99之间的整数，表示执行失败。可以使用RETURN语句，用大于0或小于-99之间的整数来定义自己的返回状态值，以表示不同执行结果１）RETURN语句格式RETURN[返回整型值的表达式]２）功能RETURN语句将无条件地从过程、批处理或语句块中退出并返回整型值。8.1.4存储过程的参数和状态值2．返回值例8.14:

创建存储过程checkstate，查询指定课程的最高成绩，如果最高成绩大于90分，则返回状态代码1。否则，返回状态代码2createproccheckstate@cnovarchar(3)asif(selectmax(成绩)fromscwhere课程号=@cno)>90return1elsereturn0godeclare@mystateintExecute@mystate=checkstate'c01'select@mystate8.1.4存储过程的参数和状态值8.1.5修改存储过程1.格式ALTERPROCEDURE[架构名称.]存储过程名[@parameter数据类型][=default]--设置默认值。[OUTPUT]--说明定义的存储过程参数为一返回值。[,..n][WITHencryption|recompile]--对过程进行加密。[FORREPLICATION]AS<SQL语句>2.功能其语法和CREATEPROCEDURE很相似。【例8.18】现在我们就来修改创建好的存储过程proc_student3，用于显示指定学号的学生各门课程的最高成绩，执行该存储过程返回指定学生的最高成绩.ALTERPROCEDUREc_student3 @numchar(6),@maxsmallintoutputASBEGIN SELECT@max=max(成绩)FROMsJOINscONs.学号=sc.学号

WHEREs.学号=@numEND8.1.5修改存储过程1．格式DROPPROCEDURE{存储过程名}[,...n]2．功能从当前数据库中删除一个或多个存储过程【例8.19】现在我们就来删除刚才创建的存储过程proc_student4。方法一：在SQLServerManagementStudio中删除存储过程方法二：使用SQL命令新建一个查询窗口，在里面输入命令：DROPPROCEDUREproc_student48.1.6删除存储过程【例8.23】建立存储过程sc_look_delete，查询某个同学的所有课程成绩，如果存在不及格课程，则删除不及格成绩记录，否则显示所有课程成绩。CREATEPROCEDURE

sc_look_delete@sno

varchar(6)ASIFEXISTS(SELECT学号FROMscWHERE学号=@sno

and成绩<60)

DELETEFROMscWHERE学号=@sno

AND成绩<60ELSESELECT学号,课程号,成绩FROMscWHERE学号=@snoExecutesc_look_delete‘j0401’8.1.6删除存储过程方法一：在ManagementStudio中查看存储过程的定义方法二：使用命令1．sp_help格式：sp_help[[@objname=]name]2．sp_helptext格式：sp_helptext[[@objname=]name]3．sp_depends

格式：sp_depends[@objname=]’name’4．sp_stored_procedures格式：sp_stored_procedures8.1.7查看存储过程的定义1．格式SP_RENAME

原存储过程名，新存储过程名2．功能将存储过程名更改为新存储过程名。【例8.25】将存储过程sg

更名为student_proc方法一：使用SQL命令SP_RENAMEsg,student_proc注意:更改对象名可能破坏脚本和存储过程。方法二：在ManagementStudio重命名存储过程8.1.8重命名存储过程触发器是特殊的存储过程，它也定义了一组Transact-SQL语句，用于完成某项任务。

触发器的主要作用是能强制数据完整性，保证数据一致性，主要表现为：强化约束保证参照完整性级联运行跟踪变化创建触发器时需指定：名称、在其上定义触发器的表、触发器将何时激发、激活触发器的数据修改语句。8.2设计和管理触发器8.2.1触发器概述1．格式CREATETRIGGER[架构的名称.]触发器名

ON表名|视图[WITHencryption]--对文本进行加密。{FOR|AFTER|INSTEADOF}[delete][,insert][,update]AS[SQL语句]2．功能AFTER：指定触发器只有在触发SQL语句中指定的所有操作都已成功执行后才激发。INSTEADOF：指定执行触发器而不是执行“触发SQL语句”，从而替代“触发语句”的操作。对于表或视图，每个INSERT、UPDATE或DELETE语句最多可定义一个INSTEADOF触发器。8.2.2创建触发器【例8.26】在学生选课表sc上创建一个触发器trigger_student1，该触发器被INSERT操作触发，当用户向sc表插入一条新记录时，判断该记录的学号在学生基本信息表s中是否存在，如果存在插入成功，否则插入失败。CREATETRIGGERtrigger_student1ONscAFTERINSERTASBEGINIF(SELECTcount(*)FROMinsertedJOINsONinserted.学号=s.学号)=0BEGIN ROLLBACKTRAN–取消所做的插入操作 PRINT'插入记录无效！' ENDEND8.2.2创建触发器【例8.26】在学生信息表S上创建一个触发器trigger_stu2,该触发器被delete操作触发。当在表S中删除一条记录时，判断该生是否在选课成绩表sc中有数据，如果没有则允许删除，否则不允许删除。Createtriggertrigger_stu2onsafterdeleteAsbegin

if(exists(select*fromdeletedjoinscondeleted.学号=sc.学号))beginrollbacktranprint‘不允许删除该生信息’endend8.2.2创建触发器注意：1、在触发器执行过程中，SQLSERVER会建立和管理两个临时的虚拟表：deleted表和inserted表。2、当向表中插入数据时，insert触发器会执行，并将新记录插入到inserted表中。3、当从表中删除数据时，delete触发器会执行，并将被删除的记录存放到deleted表中。4、对于修改操作，update触发器会执行，并将更新前的记录存储在deleted表中，然后将更新后的记录储在inserted表中。8.2.2创建触发器【例8.28】在学生信息表s上创建一个触发器my_edit，该触发器被update操作触发。当用户在S表修改某个学生的学号时，同时自动更新选课成绩表SC中该生对应记录的学号Createtriggermy_edit

onSafterupdateAsbeginupdatescset学号=(select学号frominserted)where学号in(select学号fromdeleted)end8.2.2创建触发器【例8.28】创建一个触发器reminder，如果修改、删除和插入学生信息表S中的任何数据，则向用户显示信息“不能对数据表进行任何修改！”Ifexists(selectnamefromsysobjectswherename=‘reminder’andtype=‘tr’)droptriggerreminderCreatetriggerreminderonsforinsert,update,deleteAsbegin

raiserror(‘不能对数据表进行任何修改！’,16,10)end8.2.2创建触发器返回用户定义的错误提示信息，记录发生的错误。１.格式RAISERROR({msg_id|msg_str}{,严重级别,状态}２.功能（1）msg_id是存储于sysmessages