第7章 存储过程、触发器和用户定义函数

第7章存储过程、触发器和用户定义函数2022/12/3011:0117.1变量及流程控制语句7.2存储过程7.3触发器7.4用户定义函数7.5游标7.1

变量2022/12/3011:012定义：变量用于存储临时存放数据，变量中的数据随着程序的运行而变化，变量定义时，必须有名字及数据类型两个属性。变量名：用于标识该变量；变量类型：确定了该变量存放值的格式，变量的取值范围及允许的运算。7.1

变量2022/12/3011:0131.变量的种类在SQLServer中变量分为全局变量和局部变量两种：（1）全局变量是以“@@”开始的变量。全局变量是由系统提供且预先声明的变量，用户一般只能查看不能修改全局变量的值。T-SQL全局变量作为函数引用。例如，@@ERROR返回执行的上一个T-SQL语句的错误号，@@CONNECTIONS返回自上次启动SQLSever以来连接或试图连接的次数。（2）局部变量，局部变量是以“@”开始的变量。局部变量是用户声明的用以保存特定类型的单个数据值的对象，它局部于一个语句批。例如，保存运算的中间结果，作为循环变量等。7.1

变量2022/12/3011:0142.变量声明与赋值（1）变量的声明：在SQLSever中，局部变量必须先声明，然后才能使用。声明局部变量的语句格式为：DECLARE @局部变量名[AS]数据类型[1,…,n]（2）变量的赋值：使用DECLARE声明完局部变量后，该变量的值将被初始化为NULL。变量的赋值语句格式为：SET @局部变量名=值|表达式7.1

变量2022/12/3011:015[例]本示例声明了三个整型变量：@x,@y,@z,并给@x,@y变量分别赋予个初始值，然后将两个变量的和赋值给@z，并显示变量@z的结果。DECLARE@xint,@yint,@zintSET@x=100SET@y=200SET@z=@x+@yPRINT@z；7.1

变量2022/12/3011:016[[例]创建局部变量，并赋值，然后输出变量的值。DECLARE@v1char(8),@v2char(50)SET@v1='中国’SET@v2=@v1+'是一个伟大的国家’SELECT@v1,@v2;7.1

变量2022/12/3011:017[例]创建一个局部变量，并在SELECT语句中使用该局部变量查找表S中所有男同学的学号、姓名。DECLARE@xbchar(2)SET@xb='男'SELECTSNO,SNAMEFROMSWHERESEX=@xb;7.1

变量2022/12/3011:018

[例]将查询结果赋给变量。DECLARE@xschar(10)SET@xs=(SELECTSNAMEFROMSWHERESNO='20180201')SELECT@xs;7.2

流程控制语句2022/12/3011:019T-SQL语言提供了一些可以用于改变语句执行顺序的命令，称为流程控制语句。流程控制语句允许用户更好地组织存储过程中的语句，可以方便地实现程序的功能。

8.1

流程控制语句2022/12/3011:0110和常见的程序设计语言类似，流程控制语句包括：1.BEGIN…END语句2.IF…ELSE语句3.CASE语句4.循环语句5.RETURN语句BEGIN…AND语句2022/12/3011:0111能够将多个T-SQL语句组合成一个语句块，并将它们视为一个单元处理。其语句格式如下：BEGIN<T-SQL语句>[,…n][<BEGIN…END>[,…n]]END在BEGIN…END语句中可以嵌套另外的BEGIN…END语句来定义另一语句块。IF…ELSE语句2022/12/3011:0112在程序中如果要对给定的条件进行判定，当条件为真或假时分别执行不同的T-SQL语句，可用IF…ELSE语句来实现。其语句格式如下：IF<条件表达式><命令行或语句块>[ELSE[条件表达式]<命令行或语句块>]注：条件表达式可以是各种表达式的组合

IF…ELSE语句可以嵌套使用IF…ELSE语句2022/12/3011:0113[例]在教学管理数据库中，如果0117号课程的平均成绩高于80分，则显示“0117号课程的平均成绩还不错”，否则显示“0117号课程的平均成绩一般”。IF(SelectAVG(Grade)FromSCWhereCNo='0117‘)>80PRINT'0117号课程的平均成绩还不错'ELSEPRINT'0117号课程的平均成绩一般'CASE语句2022/12/3011:0114CASE语句可以进行多个分支的选择，从而避免多重IF…ELSE语句的嵌套。简单CASE语句搜索CASE语句简单CASE语句2022/12/3011:0115

CASE<输入条件表达式>

WHEN<条件表达式值1>THEN<返回表达式1>

WHEN<条件表达式值2>THEN<返回表达式2>

…[ELSE<返回表达式n>]

END3.CASE语句2022/12/3011:0116[例]在教学管理数据库中，查询S表中学生所在系的中文名称。如“姜芸”的系部是“信息系”。SelectSNameAS'姓名',CASESdeptWHEN'CS'THEN'计算机科学系'WHEN'IS'THEN'信息系'WHEN'MC'

THEN'机械系'WHEN'MA'THEN'数学系'ENDAS'系部'FromSWhereSName='姜芸'搜索CASE语句2022/12/3011:0117CASE

WHEN<条件表达式值1>THEN<返回表达式1>

WHEN<条件表达式值2>THEN<返回表达式2>

…[ELSE<返回表达式n>]END搜索CASE语句2022/12/3011:0118[例]在教学管理数据库中，显示0017号课程的成绩等级

SelectSNameAS'姓名',

CASE

WHENGRADE>=90THEN'优秀'WHENGRADE>=80THEN'良好'WHENGRADE>=70THEN'中等'WHENGRADE>=60THEN'及格'WHENGRADE<60THEN'不及格'

ENDAS'成绩等级'FromSjoinSConS.SNO=SC.SNOWhereCNO=‘0017'循环语句2022/12/3011:0119使用WHILE语句可以根据指定的条件重复执行一个T-SQL语句或语句块，只要条件成立，WHILE语句会重复执行下去。WHILE<条件表达式>BEGIN<命令行或语句块>[BREAK]----断路

[CONTINUE]----短路

<命令行或语句块>END循环语句2022/12/3011:0120[例]在教学管理数据库中，利用循环的PRINT语句输出

S表中女同学的信息。DECLARE@infoVARCHAR(200)DECLARE@cursCURSORSET@curs=CURSORSCROLLDYNAMICFORSELECT'学号是：'+SNO+';姓名是：'+SNAME+';性别是：'+SEX+';年龄是：'+convert(varchar(3),AGE)+';系部是：'+SDEPTFROMSWHERESEX='F'循环语句2022/12/3011:0121[例]在教学管理数据库中，利用循环的PRINT语句输出

S表中女同学的信息。OPEN@cursFETCHNEXTFROM@cursINTO@infoWHILE(@@fetch_status=0)BEGINPRINT@infoFETCHNEXTFROM@cursINTO@infoEND游标读取下一条数据成功RETURN语句2022/12/3011:0122使用RETURN语句可以从查询或过程中无条件地退出，而不去执行位于RETURN之后的语句。格式如下：

RETURN[<整形表达式>]其中，<整形表达式>为一个整型数值，是RETURN语句要返回的值。该语句的含义是：向执行调用的过程或应用程序返回一个整数值。7.2

存储过程2022/12/3011:0123一种数据库对象，独立存储在数据库内，它可以接受参数、输出参数、返回单个或多个结果集以及返回值。

存储过程概述

创建存储过程

调用存储过程

管理存储过程存储过程概述2022/12/3011:0124是T-SQL语句和流程控制语句的预编译集合，以一个名称存储并作为一个单元处理。存储过程存储在数据库内，可由应用程序通过一个调用执行，而且允许用户声明变量、有条件的执行以及其他强大的编程功能。存储过程概述2022/12/3011:0125程序流逻辑对数据库的查询存储过程可包含：存储过程的分类2022/12/3011:0126SQLServer支持的存储过程可分为3类：系统存储过程扩展存储过程用户自定义的存储过程系统存储过程2022/12/3011:0127是由系统提供的存储过程，可以作为命令执行各种操作。系统存储过程定义在系统数据库master中，其前缀是sp_，它们为检索系统表的信息提供了方便快捷的方法。系统存储过程

存储过程

功能sp_addlogin创建一个新的login帐户。sp_addrole在当前数据库中增加一个角色。sp_cursorclose关闭和释放游标。sp_dbremove删除数据库和该数据库相关的文件。sp_droplogin删除一个登录帐户。sp_helpindex返回有关表的索引信息。sp_helprolemember返回当前数据库中角色成员的信息。sp_helptrigger显示触发器类型。sp_lock返回有关锁的信息。sp_primarykeys返回主键列的信息。sp_statistics返回表中的所有索引列表。2022/12/3011:0128常用的系统存储过程P185系统存储过程2022/12/3011:0129在教学管理数据库中，显示表S的相关性信息。EXECsp_depends@objname='S'用户自定义的存储过程2022/12/3011:0130用户自定义的存储过程是用户创建的一组T-SQL语句集合，可以接受和返回用户提供的参数，完成某些特定功能。本节只介绍用户自定义的存储过程及其使用。创建存储过程2022/12/3011:0131SSMS图形化方式T-SQL语句方式SSMS创建存储过程2022/12/3011:0132T-SQL创建存储过程2022/12/3011:0133CREATEPROCEDURE|PROC<存储过程名>[;n][<@形参名><数据类型1>[,…n][<@变参名><数据类型2>[OUTPUT][,…n][FORREPLICATION]AS<T-SQL语句>|<语句块>过程中的参数作为输出参数支持的结果集T-SQL创建存储过程2022/12/3011:0134[例]利用教学管理数据库的三个基本表，创建一个存储过程PS_GRADE，输出指定学生的姓名及课程名称、成绩信息。CREATEPROCEDUREPS_GRADE@S_NAMECHAR(8)ASSelectSName,CName,GradeFromSjoinSConS.SNo=SC.SNojoinConSC.CNo=C.CNo

Where

SName=@S_NAME

输入参数调用存储过程2022/12/3011:0135[EXEC|EXECUTE]{[<@整型变量>=]<存储过程名>[,n][[<@过程参数>]=<参数值>|<@变参名>[OUTPUT]|[DEFAULT]][,…n][WITHRECOMPILE]}调用存储过程2022/12/3011:0136[例]调用前例定义的存储过程PS_GRADE。

DECLARE@NAMECHAR(9)SET@NAME='马长友'EXECPS_GRADE@NAME

EXECPS_GRADE‘马长友'T-SQL创建存储过程2022/12/3011:0137[例]含复杂SELECT语句的存储过程：查询学生（“王珊”）的考试情况，列出学生的姓名、课程名和考试成绩。CREATEPROCEDUREPS_GRADE@S_NAMECHAR(10)ASSELECTSNAME,CNAME,GRADEFROMSJOINSCONS.SNO=SC.SNOJOINCONSC.CNo=C.CNoWHERESNAME=@S_NAME;调用存储过程2022/12/3011:0138调用前例定义的存储过程PS_GRADE。EXECPS_GRADE‘王珊’;调用存储过程2022/12/3011:0139[例]含多个输入参数并有返回值的存储过程；利用教学管理数据库的三个基本表，创建一个存储过程PV_GRADE，查询某个学生某门课的考试成绩，并返回该学生该门课程的成绩。CREATEPROCEDUREPV_GRADE@S_NAMECHAR(10),@C_NAMEVARCHAR(20)=’软件工程’,@S_GRADEREALOUTPUTASSELECT@S_GRADE=GRADEFROMSJOINSCONS.SNO=SC.SNOJOINCONSC.CNo=C.CNoWHERESNAME=@S_NAMEANDCNAME=@C_NAMEDECLARE@S_GRADEREALEXECPV_GRADE'王珊','数据库原理与应用',@S_GRADEOUTPUTPRINT'王珊的数据库成绩为：'+STR(@S_GRADE)调用存储过程2022/12/3011:0140[例]写一个存储过程，输入用户名和密码，若正确则给出该学生所选所有的课程号、课程名和成绩，若密码不正确则输出提示“用户名或密码输入不正确”。调用存储过程2022/12/3011:0141create

procgetCourseList@useridvarchar(10),@pwdvarchar(10)asif@pwd=(selectpwd

fromuserinfo

whereuserid=@userid)

o,cname,grade

o=o

wheresno=(selectsno

fromuserinfo

whereuserid=@userid)else

print

'用户名或密码输入错误'管理存储过程2022/12/3011:0142查看存储过程修改存储过程删除存储过程查看存储过程2022/12/3011:0143

sp_helptext<存储过程名称>

sp_help<存储过程名称>查看存储过程2022/12/3011:0144[例]查看存储过程PV_GRADE的相关信息。

EXECsp_helptextPV_GRADE查看存储过程2022/12/3011:0145[例]查看存储过程PV_GRADE的名称、参数等相关信息。

EXECsp_helpPV_GRADE修改存储过程2022/12/3011:0146ALTERPROCEDURE|PROC<存储过程名>[;n]

[<@形参名><数据类型1>[,…n]

[<@变参名><数据类型2>

[OUTPUT]

[,…n]

[FORREPLICATION]AS

<T-SQL语句>|<语句块>删除存储过程2022/12/3011:0147DROPPROCEDURE<存储过程名>[,…n]存储过程的优势2022/12/3011:0148提高了处理复杂任务的能力；增强了代码的复用率和共享性；减少网络中的数据流量；可作为安全机制使用。存储过程的不足2022/12/3011:0149更改输入参数烦琐；可移植性差；很多不支持面向对象的设计；代码可读性差，比较难维护。7.3

触发器2022/12/3011:0150一种特殊类型的存储过程，在用户使用一种或多种数据更新操作来更新指定表中的数据时被触发并自动执行，通常用于实现复杂的业务规则，更有效地实施数据完整性。

触发器概述

创建触发器

管理触发器触发器概述2022/12/3011:0151是一种对表进行插入、更新、删除的时候会自动执行的存储过程。触发器一般用在比CHECK约束更加复杂的约束上面。触发器常用的功能2022/12/3011:0152（1）完成比check更复杂的数据约束；（2）检查所做的操作T-SQL是否允许；（3）修改其他数据表中的数据；（4）调用更多的存储过程；（5）发送邮件；（6）返回自定义的错误信息；（7）更改原来操作的T-SQL语句；（8）防止数据表结构被更改或数据表被删除。触发器的分类2022/12/3011:0153DML触发器DDL触发器DML触发器2022/12/3011:0154当数据库服务器中发生数据操作语言事件时执行的存储过程。DML触发器又分为两类：AFTER触发器

INSTEADOF触发器DDL触发器2022/12/3011:0155是在响应数据定义语言事件时执行的存储过程。一般用于执行数据库中的管理任务。创建触发器2022/12/3011:0156SSMS图形化方式T-SQL语句方式SSMS创建触发器2022/12/3011:0157T-SQL创建触发器2022/12/3011:0158CREATETRIGGER<触发器名>ON<表名>|<视图名>FOR|AFTER|INSTEADOF[INSERT][,UPDATE][,DELETE]AS<T-SQL语句>|<语句块>FOR|AFTER：指定在相应操作（INSERT、UPDATE、DELETE）成功执行后才触发。指定触发器触发的时机INSTEADOF：指定执行DML触发器用于“代替”引发触发器执行的INSERT、UPDATE或DELETE语句。T-SQL创建触发器2022/12/3011:0159[例]在教学管理数据库中，为学生表S创建一个简单的

DML触发器S_I_U，在插入和修改数据时，都会自动显示提示信息。CREATETRIGGER

S_I_UONSFORINSERT,UPDATEASPRINT‘对S表进行了数据的插入或修改'T-SQL创建触发器2022/12/3011:0160[例]在教学管理数据库中，用T-SQL语句为S表创建一个

DELETE类型的触发器DEL_COUNT，删除数据时，

显示删除学生的个数。CREATETRIGGER

DEL_COUNTONSFORDELETEASDeclare@COUNTVARCHAR(50)Select@COUNT=STR(@@ROWCOUNT)+'个学生被删除'Select@COUNTRETURN

T-SQL创建触发器2022/12/3011:0161[例]在教学管理数据库中，创建DDL触发器JXGL_LIMITED，防止数据库JXGL中任一表被删除或修改。CREATETRIGGER

JXGL_LIMITEDONDATABASEFORDROP_TABLE,ALTER_TABLEASPRINT'不允许对数据库JXGL的表进行修改或删除’ROLLBACK管理触发器2022/12/3011:0162查看触发器信息修改触发器删除触发器禁止和启用触发器查看触发器信息2022/12/3011:0163

sp_helptext<触发器名称>

sp_help<触发器名称>

sp_helptrigger<表名>查看触发器信息2022/12/3011:0164[例]在教学管理数据库中，利用sp_helptext查看前例创建的触发器DEL_COUNT的内容

EXECsp_helptextDEL_COUNT查看触发器信息2022/12/3011:0165[例]在例5.9教学管理数据库中，查看S表上存在的触发器的相关信息。

EXECsp_helptriggerS修改触发器2022/12/3011:0166ALTERTRIGGER<触发器名>ON<表名>|<视图名>FOR|AFTER|INSTEADOF[INSERT][,UPDATE][,DELETE]AS<T-SQL语句>|<语句块>删除触发器2022/12/3011:0167DROPTRIGGER<触发器名>[,…n]禁止和启用触发器2022/12/3011:0168ALTERTABLE

<表名>[ENABLE|DISABLE]TRIGGER[ALL|<触发器名>[,…n]]禁止和启用触发器2022/12/3011:0169[例]在教学管理数据库中，禁用S表上创建的所有触发器ALTERTABLESDISABLETRIGGERALL7.4

用户定义函数2022/12/3011:0170用户定义函数像系统内置函数一样，可以接受参数，执行复杂的操作并将操作结果以值的形式返回，也可以将结果用表格变量返回。

用户定义函数概述

创建用户定义函数

管理用户定义函数用户定义函数概述2022/12/3011:0171是SQLServer的数据库对象，它不能用于执行一系列改变数据库状态的操作，但可以像系统函数一样在查询或存储过程等程序中使用，也可以像存储过程一样通过EXECUTE命令来执行。用户定义函数概述2022/12/3011:0172用户定义函数优点2022/12/3011:0173（1）模块化程序设计（2）执行速度快（3）减少网络流量用户定义函数分类2022/12/3011:0174标量型函数内联表值型函数多语句表值型函数创建用户定义函数2022/12/3011:0175创建标量型函数创建内联表值型函数创建多语句表值型函数创建标量型函数2022/12/3011:0176CREATEFUNCTION<函数名>([@<形参名><数据类型>[,…n]])RETURNS<返回值数据类型>[AS]BEGIN<T-SQL语句>|<语句块>

RETURN<返回表达式>END返回值是返回子句（RETURNS子句）中定义类型的单个数据值，不能返回多个值。创建标量型函数2022/12/3011:0177[例]在教学管理数据库中定义一个函数S_AVG，当给定一个学生的姓名时，返回该学生的平均成绩。CreateFunctionS_AVG(@S_NAMEvarchar(6))ReturnsrealASBeginDeclare@S_AVERAGErealSelect@S_AVERAGE=AVG(Grade)FromSjoinSCONS.SNo=SC.SNoandSName=@S_NAME

Return@S_AVERAGEEnd创建标量型函数2022/12/3011:0178[例]调用前例中定义的函数S_AVG，求学生王丽萍的平均成绩PRINTdbo.S_AVG('王丽萍')注：调用用户自定函数与调用系统内置函数的方法一样，

但需要在用户定义函数名前加‘dbo.’前缀，以表示该函数的拥有者。创建内联表值型函数2022/12/3011:0179以表的形式返回一个返回值。CREATEFUNCTION<函数名>([<@形参名><数据类型>[,…n]])RETURNSTABLE[AS]

RETURN(SELECT<查询语句>)创建内联表值型函数2022/12/3011:0180[例]在教学管理数据库中，定义函数S_CNO，当给定一个学生的学号，返回该学生所学的所有课程名。CreateFunctionS_CNO(@S_NOvarchar(5))ReturnstableASReturn(SelectCNameFromCjoinSConC.CNo=SC.CNoandSNo=@S_NO)创建内联表值型函数2022/12/3011:0181[例]调用内联表值型函数S_CNAME，求得学号为“S6”的学生选修课的课程名。Select*fromS_CNO('S6')创建多语句表值型函数2022/12/3011:0182可以看作标量型和内联表值型函数的结合体。CREATEFUNCTION<函数名>([<@形参名><数据类型>[,…n]])RETURNS<@返回变量>TABLE(表结构定义)[AS]BEGIN<T-SQL语句>|<语句块>RETURNEND创建多语句表值型函数2022/12/3011:01Mail:yrmeixue@83[例]在教学管理数据库中，定义一个函数S_TABLE，输入一个学生的姓名，返回该姓名的学生成绩表。CreateFunctionS_TABLE(@S_NAMEchar(8))Returns@TBtable(TB_SNOchar(9),TB_NAMEchar(8),TB_CNOchar(4),TB_GRADEreal)ASBeginInsertinto@TBSelectS.SNo,SName,CNo,GradeFromSjoinSConS.SNo=SC.SNoandSName=@S_NAME

ReturnEnd创建多语句表值型函数2022/12/3011:0184[例]调用前例中定义的多语句表值型函数S_TABLE，求得学生“李小刚”的成绩表。Select*fromS_TABLE('李小刚')管理用户定义函数2022/12/3011:0185查看用户定义函数修改用户定义函数删除用户定义函数查看用户定义函数2022/12/3011:0186

sp_helptext<触发器名称>

sp_help<触发器名称>修改用户定义函数2022/12/3011:0187ALTERFUNCTION<用户定义函数名>([<@形参名><数据类型>[,…n]])RETURNS<@返回变量>TABLE(表结构定义)[AS]BEGIN<T-SQL语句>|<语句块>RETURNEND删除用户定义函数2022/12/3011:0188DROPFUNCTION<用户定义函数名>7.5游标的概念2022/12/3011:0189

游标是一种能从包括多个元组的集合中每次读取一个元组的机制。SQLServer支持三种类型的游标：（1）T-SQL游标（2）API游标（3）客户游标主要用在T-SQL脚本、存储过程和触发器中。主要用在服务器上。主要在客户机上缓存结果集时才使用。后台游标前台游标7.5.2游标的管理2022/12/3011:0190

利用T-SQL语句定义的游标是在服务器端实现的，操作游标有5个主要步骤。1.声明游标DECLARE<游标名>[INSENSITIVE][SCROLL]CURSORFOR<SELECT语句>[FORREADONLY|UPDATE[OF<列名>[,…n]]]定义的游标所选出来的元组存放在一个临时表中（建立在tempdb数据库中），对该游标的读取操作都有临时表来应答。游标使用的读取选项，默认时为NEXT。1.声明游标2022/12/3011:0191DECLARE<游标名>[INSENSITIVE][SCROLL]CURSORFOR<SELECT语句>[FORREADONLY|UPDATE[OF<列名>[,…n]]]SCROLL选项

含义FIRST读取游标中的第一行数据。LAST读取游标中的最后一行数据。PRIOR读取游标当前位置的上一行数据。NEXT读取游标当前位置的下一行数据。RELATIVEn读取游标当前位置之前或之后的第n行数据（n为正向前，为负向后）。ABSULUTEn读取游标中的第n行数据。SCROLL的取值1.声明游标2022/12/3011:0192DECLARE<游标名>[INSENSITIVE][SCROLL]CURSORFOR<SELECT语句>[FORREADONLY|UPDATE[OF<列名>[,…n]]]只读游标指定游标内可更新的列1.声明游标2022/12/3011:0193[例]声明一个名为S_Cursor的游标，用以读取软件系的所有学生的信息。DECLARES_CursorCURSORFORSELECT*FROMSWHERESdept='软件'2.打开游标2022/12/3011:0194OPEN[GLOBAL]<游标名>|<游标变量名>指定游标为全局游标变量名，该名称可以引用一个游标

利用OPEN语句打开游标后，游标位于查询结果集的第一行，并且可以使用全局变量@@cursor_rows获得最后打开的游标中符合条件的行数。2.打开游标2022/12/3011:0195[例]打开打开例所声明的游标OPENS_CursorSELECT*FROMSWHERESdept=‘软件'3.读取游标2022/12/3011:0196FETCH[[NEXT|PRIOR|FIRST|LAST