数据库技术与应用：Chap05-1 存储过程

1、1 数据库技术与应用数据库技术与应用 第五章第五章 存储过程存储过程 2Tongji University 2 5.15.1 Transact-SQLTransact-SQL语言基础语言基础 5.25.2 存储过程存储过程 ( (重点重点) ) 5.35.3 事务事务 5.45.4 触发器触发器 ( (重点重点) ) 本节内容提要本节内容提要 3Tongji University 3 Transact-SQL (简为T-SQL)语言用于管理微软微软 SQL Server 数据库引擎实例，创建和管理数据库对象，以及插入、 检索、修改和删除数据等。Transact-SQL是对标准SQL语言 的扩展

2、。 u 它 在标准SQL语言的基础上添加了控制语句，是一种结 构化的程序设计语言； v 既可在查询分析器中交互运行，也可嵌入到宿主语言中 去运行。 5.1 T-SQL 参考文献：参考文献： http:/ 4Tongji University 4 SQL ServerSQL Server中变量分两种中变量分两种： 1. 1. 全局变量全局变量 () ) 开始的标识符，以保存SQL Server系统的某些参数值等。 由系统定义和维护,用户只能引用而不能修改或定义。 查看全局变量值可用SELECT语句。 2.2. 局部变量局部变量() ) 以开始的字母、数字、 _等字符串，不区分大小写字母。 用于保

3、存用临时数据或由存储过程返回的结果。 由用户定义(DECLARE语句)和赋值(SET语句或SELECT语句)， 使用范围只局限于某一个语句或过程体内； T-SQL变量变量 5Tongji University 5 1. 1.声明语法：声明语法： DECLARE variable1 DECLARE variable1 类型类型, variable2 , variable2 类型类型 , 说明：声明变量后，所有变量均初始化为 NULL。 局部变量声明与赋值局部变量声明与赋值 2.2. 赋值语法赋值语法1 1： SET variable1= SET variable1= 值值 | | 返回单值的子查

4、询返回单值的子查询| | NULLNULL 说明：不能用SET同时对多个变量进行赋值。 3.3. 赋值语法赋值语法2 2： SELECT variable1=SELECT variable1=值值1 ,variable2=1 ,variable2=值值2, 2, FROMWHERE FROMWHERE 说明：如果查询返回的结果包含多个值，将最后一个值赋给 局部变量。SELECT语句可以直接为一个或多个局部变量赋值。 6Tongji University 6 例例5.15.1 声明两个局部变量并赋值。 DECLAREDECLARE mmNNameame varcharvarchar(30), (

5、30), mLenmLen intint SETSET mmNNameame=Forrest Gump Forrest Gump - -赋值赋值 SET SET mLenmLen=142 =142 - -赋值赋值 赋值实例赋值实例1 DECLARE DECLARE mNamemName varcharvarchar(30)(30), , mLenmLen intint SELECT SELECT mNamemName=Forrest GumpForrest Gump,mLenmLen=142 =142 - -赋值赋值 7Tongji University 7 例例5.2 5.2 使用查询为局部

6、变量赋值. DECLARE DECLARE rows rows intint SETSET rows = ( rows = (SELECTSELECT COUNTCOUNT( (* *) ) FROMFROM movies) movies) 赋值实例赋值实例2 DECLAREDECLARE rows rows intint SELECT SELECT rows=rows=COUNTCOUNT( (* *) ) FROMFROM movies movies 8Tongji University 8 常用流程控制语句常用流程控制语句(一一) 语句语句语法语法功能功能 语句块语句块BEGIN SQL

7、语句语句 1; SQL语句语句2; . END 定义语句块。可嵌套 IF语句语句IF 条件表达式条件表达式 SQL语句语句1| 语句块语句块1 ELSE SQL语句语句2| 语句块语句块2 定义条件以及当指定条件为 TRUE 或 FALSE 时执行的SQL 语句。 WHILE语句语句 (可含可含BREAK、 CONTINUE) WHILE 条件表达式条件表达式 SQL语句语句1| 语句块语句块1 BREAK|CONTINUE SQL语句语句2| 语句块语句块2 当条件为 TRUE 时重复语句。 BREAK:退出最内层的 WHILE 循环。 CONTINUE:重新开始 WHILE 循环,忽略 C

8、ONTINUE 关键字后面的任何语句。 9Tongji University 9 常用流程控制语句常用流程控制语句(二二) 语句语句语法语法功能功能 简单简单CASE CASE 输入表达式输入表达式 WHEN 表达式表达式1 1 THEN 结果表达式结果表达式1 n ELSE 结果表达式结果表达式n+1 END 将输入表达式的值 与when中表达式 值比较，若相等则 计算THEN后的表 达式；若与所有 WHEN子句中的表 达式值都不等，则 计算ELSE后的表达 式。最后将值返回。 搜索型搜索型 CASE CASE WHEN 条件表达式条件表达式1 1 THEN 结果表达式结果表达式1 n EL

9、SE 结果表达式结果表达式n+1 END RETURN语句语句RETURN 整型表达式整型表达式无条件退出。 10Tongji University 10 CASE表达式应用表达式应用 例例5.3 5.3 实验实验四中的四中的1212小题小题, ,统计每门课程的选课人数及不及格人数。统计每门课程的选课人数及不及格人数。 SELECTSELECT um ASAS 课程号课程号, ,cnamecname ASAS课程课程名名, , countcount( (snumsnum) ) ASAS 选课人数选课人数, , sumsum( (CASE CASE WHEN WHEN score60score6

10、0 THENTHEN 1 1 ELSEELSE 0 0 ENDEND) ) ASAS 不不及格人数及格人数 FROM FROM sc,sections,coursesc,sections,course WHEREWHERE sc.secnumsc.secnum= =sections.secnumsections.secnum ANDAND um= =um GROUP BY GROUP BY um,um,cname 11Tongji University 11 5.2 存储过程存储过程 存储过程(Stored Procedure)是指封装了可重用代码 的、存储在服务器服务器端的数据库中的程序模块

11、(modules)。 它类似传统语言(如C)中的过程。可接收0或多个输入 参数；具有返回状态值(整型，默认为0)，表明被调用成功 与否；并能以多输出参数的格式返回多个结果。一经创建， 就可在SQL查询或宿主语言中被调用。 优点：存储过程在第一次被执行时，便在服务器上被 编译。以后的每次调用直接执行已编译好的二进制代码， 不再重新编译。执行效率高、显著降低了网络通信量。它 是数据库编程的首选模式。 分类：系统存储过程、用户存储过程、扩展存储过程。 12Tongji University 12 创建存储过程创建存储过程 CREATE CREATE PROC PROC parameter param

12、eter 类型类型 =默认值默认值 OUTPUT OUTPUT , ,n n AS AS 说明：说明： 存储过程参数存储过程参数：参数名参数名-类型类型-模式模式 parameter：参数名； 类型：除table之外的其他所有数据类型均可用作存储过程的参 数类型。 参数模式：OUTPUT(或OUT)表示该参数为输出参数；默认为输 入参数。 ：表示包含在过程中的一个或多个T-SQL语句。 存储过程参数 13Tongji University 13 例例5.4 一个无参数存储过程一个无参数存储过程 例例5.4 5.4 创建一个简单的存储过程，完成显示所有学生的平均 分数，数据源见关系SC。 CRE

13、ATE PROC CREATE PROC sp_sc_avgsp_sc_avg ASAS SELECT SELECT snumsnum, AVG(score) as , AVG(score) as _ _avgavg FROM FROM scsc GROUP BY GROUP BY snumsnum 14Tongji University 14 一个存储过程可看作一个一个存储过程可看作一个SQLSQL语句，语句，它它可在如下可在如下3 3种种 环境中使用：环境中使用： 1. 1. 在服务器端在服务器端 SSMS SSMS 查询编辑查询编辑窗口以窗口以EXECUTEEXECUTE为关为关 键字调

14、用。键字调用。 2.2. 可在另一存储过程或函数中被调用。可在另一存储过程或函数中被调用。 3.3. 在宿主语言在宿主语言( (如如VB.NET)VB.NET)中被当作嵌入式中被当作嵌入式SQLSQL被调被调 用。用。 ( (具体，见第具体，见第8 8章课件：章课件：chap08chap08数据库访问数据库访问 技术技术-2)-2) 存储过程的调用存储过程的调用 15Tongji University 15 在服务器端SSMS中调用存储过程，其语法： 说明：说明： return_status 可选的整型变量，存储过程的返回状态。 ;number 是可选整数，用于对同名的过程分组。注意：后续版本

15、的 MicrosoftSQLServer将删除该功能。 parameter 参数名，首字符必须为。如果任何参数使用了 parameter=参数值 格式，则后续的所有参数均必须使用该格式。 variable 是用来存储参数或返回参数的变量。 OUTPUT指定存储过程返回一个参数。 在服务器端调用在服务器端调用 EXEC EXEC return_statusreturn_status = = 存储过程名存储过程名 ; ;number number parameter parameter = = 参数值参数值 | |variable variable OUTPUTOUTPUT | | 默认值默认值

16、,.n ,.n 16Tongji University 16 在服务器端调用并执行例5.4中创建的存储过程。 在SSMS的新建查询窗口中输入如下语句后,点击工具栏中 的执行按钮“!”。 DECLARE return_value INT EXEC return_value=sp_sc_avg SELECT return value=return_value 服务器服务器端执行端执行 例例5.4 结果集结果集 返回值返回值 执行结果执行结果 17Tongji University 17 例例5.55.5 创建一个存储过程，实现如下功能：创建一个存储过程，实现如下功能： 从University中查询

17、选修指定课号的学生成绩， 如果该课成绩中存在90分及其以上的学生记录，则将 其姓名输出显示，并返回 成绩为优的记录数 为: 消息；否则返回 成绩为优的记录数为 零！ 消息 。 例例5.5 一个带输入参数的存储过程一个带输入参数的存储过程 18Tongji University 18 CREATE PROCCREATE PROC sp_topstudentsp_topstudent _ _cnumcnum charchar(4)(4) ASAS Declare Declare _i i intint ; ; SET SET _i i=(=(Select Select countcount( (*

18、 *) ) FROM FROM scsc joinjoin sections sections on on sc.secnumsc.secnum = =sections.secnumsections.secnum WHEREWHERE cnumcnum=_=_cnumcnum andand score=90score=90); ); IF IF _i i0 0 ELSE ELSE 例例5.5(续续) BEGIN BEGIN RAISERRORRAISERROR( (%s%s成绩为优的记录数为成绩为优的记录数为:%d:%d,1 1, 1 1,_cnum, _cnum, _i i); ); SEL

19、ECT SELECT snamesname FROM FROM student,sc,sectionsstudent,sc,sections WHEREWHERE student.snumstudent.snum= =sc.snumsc.snum andand sections.secnumsections.secnum = =sc.secnumsc.secnum and and cnumcnum=_=_cnumcnum and score= and score=9090 ENDEND PRINT PRINT _cnumcnum + + 成绩成绩为优的记录数为零为优的记录数为零! ! ; ;

20、19Tongji University 19 服务器端执行服务器端执行 例例5.5 在服务器端调用并执行例5.7中创建的存储过程。 在SSMS的新建查询窗口中输入如下语句后,点击执行按钮“!” DECLARE DECLARE c c charchar(4)(4) SETSET c= c= c120 c120 EXECEXEC sp_topstudentsp_topstudent c c EXEC EXEC sp_topstudentsp_topstudent _ _cnumcnum= = c120c120 参数名必须与存储过程定义参数名必须与存储过程定义 中的参数名完全相同中的参数名完全相同

21、20Tongji University 20 CREATE PROC CREATE PROC sp_sc_5Levelssp_sc_5Levels _secnumsecnum charchar(5)(5) ASAS SELECTSELECT snumsnum AS AS 学号学号, , 五五级级制制 = CASE CASE score/10score/10 WHEN WHEN 6 6 THENTHEN 及格及格 WHEN WHEN 7 7 THEN THEN 中中 WHEN WHEN 8 8 THENTHEN 良良 WHEN WHEN 9 9 THEN THEN 优优 WHEN WHEN 10

22、10 THEN THEN 优优 ELSE ELSE 不及格不及格 END END FROM FROM sc sc WHERE WHERE secnumsecnum=_=_secnumsecnum 例例5.6 带输入参数的存储过程二带输入参数的存储过程二 例例5.65.6 创建一个存储过程，将指定班号的学生成绩 ( (百分制百分制) )按优、良、中、按优、良、中、 及格和不及格五级制的形式及格和不及格五级制的形式输出输出，数据源为关系sc。 21Tongji University 21 服务器端执行服务器端执行 例例5.6 在服务器端调用并执行例5.6中创建的存储过程。 在SSMS的新建查询窗口

23、中输入如下语句后,点击执行按钮“!” DECLAREDECLARE _ _secnumsecnum charchar(5)(5) SETSET _ _secnumsecnum = =1160111601 printprint 班号为班号为 + _+ _secnumsecnum + + 的五分制成绩如下：的五分制成绩如下： EXECEXEC sp_sc_5Levels _ sp_sc_5Levels _secnumsecnum 22Tongji University 22 例例5.7 一个带输入输出参数的存储过程一个带输入输出参数的存储过程 例例5.7 5.7 创建带输出参数的存储过程，求指定课

24、号的成绩方差，并 保存在变量_variance中，数据源为关系sc,sections。 CREATE CREATE PROC PROC sp_cnum_variancesp_cnum_variance _cnumcnum charchar(4(4),_),_variance variance REAL REAL OUT OUT ASAS DECLARE DECLARE _score _score intint,_,_i i intint, _mean real;, _mean real; SET SET _i i=0 ;=0 ; SET SET _variance =0 ; _variance

25、 =0 ; DECLAREDECLARE score_cursorscore_cursor CURSOR FOR CURSOR FOR SELECT SELECT score score FROMFROM scsc JOINJOIN sections sections ON ON sc.secnumsc.secnum= =sections.secnumsections.secnum WHEREWHERE cnumcnum=_=_cnumcnum ; ; OPEN OPEN score_cursorscore_cursor ; ; FETCH FETCH NEXT FROM NEXT FROM

26、score_cursorscore_cursor INTOINTO _score; _score; WHILE WHILE (FETCH_STATUS (FETCH_STATUS = 0)= 0) BEGIN BEGIN SET SET _variance=_variance + _score_variance=_variance + _score* *_score;_score; SET SET _i i = _ = _i i + + 1;1; FETCH FETCH NEXT FROM NEXT FROM score_cursorscore_cursor INTO INTO _score;

27、_score; END END SELECT SELECT _mean=_mean=AVGAVG(score) (score) FROMFROM scsc JOINJOIN sections sections ONON sc.secnumsc.secnum = =sections.secnumsections.secnum wherewhere um=_=_cnumcnum; ; SETSET _variance = _variance/_i - _mean _variance = _variance/_i - _mean * * _mean _mean CLOSE CLOSE score_c

28、ursorscore_cursor DEALLOCATE DEALLOCATE score_cursorscore_cursor 23Tongji University 23 服务器端执行服务器端执行 例例5.7 在服务器端调用并执行例5.9中创建的存储过程。 在SSMS的新建查询窗口中输入如下语句后,点击执行按钮“!” DECLARE DECLARE _variance _variance REALREAL EXEC EXEC dbo.sp_cnum_variancedbo.sp_cnum_variance c120c120, _variance , _variance OUTOUT; ;

29、SELECTSELECT cnumcnum = =c120c120, ,variancevariance=_variance=_variance 24Tongji University 24 存储过程的返回状态值存储过程的返回状态值 每个存储过程的执行，都将自动返回一个整型 状态值，表明被调用存储过程的执行状态。返回状 态值若为0 0，表示存储过程执行成功；-1-99-1-99之间 的数表示存储过程执行失败。 用户(即编程者)可在被调存储过程中，自定义返回 状态值(取0或-99的整数)，以表示不同的执行状 态。然后在调用程序中，根据返回状态值作相应的 处理。 25Tongji Universi

30、ty 25 自定义自定义返回状态值返回状态值 例例5.8 5.8 创建具有返回执行状态的存储过程。检查给定学号的学生有 无不及格的记录，若无则返回0，并输出该生的成绩记录；若有则返 回5；若没有提供学号，则返回15。 CREATE PROC CREATE PROC sp_sc_score_1sp_sc_score_1 _snumsnum CHARCHAR(4)=(4)=NULLNULL ASAS IF IF _snumsnum IS NULLIS NULL RETURN RETURN 1515 ELSEELSE IF IF EXISTS EXISTS ( (SELECTSELECT * * F

31、ROMFROM sc sc WHERE WHERE snumsnum=_=_snumsnum ANDAND score60)score60) RETURN RETURN 5 5 ELSEELSE BEGINBEGIN SELECTSELECT * * FROM FROM scsc WHERE WHERE snumsnum=_=_snumsnum RETURN RETURN 0 0 END END 26Tongji University 26 CREATE CREATE PROC PROC sp_sc_score_2sp_sc_score_2 _snumsnum charchar(4)=(4)=

32、nullnull ASAS DECLARE DECLARE return_statusreturn_status INTINT EXEC EXEC return_statusreturn_status=sp_sc_score_1 =sp_sc_score_1 _snumsnum IF IF return_statusreturn_status=15=15 PRINTPRINT 缺少输入参数！缺少输入参数！ IF IF return_statusreturn_status=5=5 PRINT PRINT 该生有不及格的记录！该生有不及格的记录！ 新建一存储过程调用新建一存储过程调用sp_sc_s

33、core_1 27Tongji University 27 服务器端执行服务器端执行sp_sc_score_2 服务器端执行sp_sc_score_2,指定学号为指定学号为s001： EXECEXEC sp_sc_score2 sp_sc_score2 _snumsnum= =s001s001 服务器端执行sp_sc_score_2,不指定学号不指定学号 EXECEXEC sp_sc_scoresp_sc_score 服务器端执行sp_sc_score_2,指定指定学学号号为为s005 EXECEXEC return_statusreturn_status= =sp_sc_scoresp_sc

34、_score s005s005 28Tongji University 28 n可以通过系统存储过程可以通过系统存储过程sp_helptextsp_helptext，来查看用户自定义的，来查看用户自定义的 存储过程。存储过程。 sp_helptextsp_helptext 存储过程名存储过程名 n可以在对象资源管理器中，展开指定的数据库节点，选择可以在对象资源管理器中，展开指定的数据库节点，选择 【可编程性可编程性】【存储过程存储过程】，选择目标，选择目标存储过程，存储过程， 点点击鼠标右键，从快捷菜单中选择击鼠标右键，从快捷菜单中选择【修改修改】，便可在查询，便可在查询 设计器中查看。设计器

35、中查看。 查看存储过程查看存储过程 29Tongji University 29 ALTER PROC ALTER PROC 存储过程名存储过程名 参数列表参数列表 ASAS 修改存储过程修改存储过程 DROP PROC DROP PROC 存储过程名存储过程名 删除删除存储过程存储过程 30Tongji University 30 T-SQLT-SQL提供三种函数提供三种函数 1. 1. 行行集函数集函数 OpendatasourceOpendatasource(provider name, (provider name, init_stringinit_string) ) 例如：例如： 2

36、.2. 聚合函数聚合函数 3.3. 标量标量函数：表函数：表5.95.9中列出的函数、数据转换函数中列出的函数、数据转换函数 函数函数 31Tongji University 31 1. 1. 字串函数：字串函数：2323个个 常用标量函数常用标量函数-字符串函数字符串函数 函数语法函数语法功能描述功能描述举例举例 SUBSTRING(str 1,start,length) 返回字符串从指定的 start位置开始截取长度 为length的子串。 SUBSTRING(abced,2,3)返回bce LOWER(str1)将字符串中的所有字母 变成小写字母。 LOWER(AcedBF)返回aced

37、bf UPPER(str1)将字符串中的所有字母 变成大写字母。 UPPER(AcedBF)返回ACEDBF LEN(str1)返回字符串的字符个数， 其中不包含尾随空格。 LEN(howareyou)返回11 32Tongji University 32 函数语法函数语法功能描述功能描述举例举例 DATEPART(datepart, date) 返回指定日期的指定部 分的整数 DATEPART(day,05132010)返 回13 DAY(date)返回指定日期中天的整 数 DAY(05/13/2010)返回13 GETDATE()返回当期系统日期和时 间 GETDATE()返回 05132

38、01021:30PM MONTH(date)返回指定日期中的月份MONTH(05/13/2010)返回5 YEAR(date)返回指定日期中的年份YEAR(05/13/2010)返回2010 DATEADD(datepart, number,date) 在指定的日期上加一段 时间/日期间隔，返回新 的日期值 DATEADD(Year,1,2014-1-1) 返回2015-01-01 DATEDIFF(datepart, startdate,enddate) 计算两个日期/时间之间 的间隔，返回整数 SelectDATEDIFF(YEAR, birthday,GETDATE()as年龄 From

39、Student 2.2. 时间时间日期函数日期函数：7 7个个 常用标量函数常用标量函数-日期时间函数日期时间函数 33Tongji University 33 3.3. 数据类型转换函数数据类型转换函数 CONVERT( data_type(length),expression,style ) 其中参数说明： expression: 待转换数据表达式. data_type: 目标数据类型. Length：指定目标数据类型长度，默认值为30. style：表示将日期时间型数据转换为字符数据的样式。 如，样式为101，则返回“mm/dd/yyyy”； 样式为102，返回“yyyy.mm.dd”；

40、 样式11，返回“yy/mm/dd” 常用标量函数常用标量函数-数据类型转换函数数据类型转换函数 34Tongji University 34 CAST(expression CAST(expression AS AS data_typedata_type(length)(length) （续上）（续上） 例例 用CAST()函数将表student中birthday由日期时间类型转 换为字符串，要求只显示日期信息。 SELECT SELECT snum,sname,snum,sname,CASTCAST(birthday (birthday ASAS CHARCHAR(10) (10) asa

41、s birthday birthday fromfrom student student 35Tongji University 35 1. 1. 标量标量函数函数 用户自定义函数用户自定义函数标量函数标量函数 CREATE FUNCTION CREATE FUNCTION 函数名函数名 ( (形式参数定义表形式参数定义表) ) RETURNSRETURNS 返回值数据类型返回值数据类型 ASAS BEGIN BEGIN 函数体函数体 RETURN RETURN 返回值表达式返回值表达式 ENDEND 36Tongji University 36 已知已知SC(SC(snum,secnum,s

42、coresnum,secnum,score),Sections(),Sections(secnum,cnum,pnumsecnum,cnum,pnum) ) 创建创建 一个标量函数，返回某课程的平均分。一个标量函数，返回某课程的平均分。 例例5.9 自定义自定义标量函数标量函数 CREATE FUNCTION CREATE FUNCTION get_cnum_avgget_cnum_avg (_(_cnumcnum charchar(4)(4) RETURNSRETURNS intint AS AS BEGIN BEGIN DECLAREDECLARE temp temp intint SEL

43、ECT SELECT temp=temp=avgavg(score)(score) FROM FROM SC, SectionsSC, Sections WHERE WHERE SC.secnumSC.secnum=Sections.secnumSections.secnum andand cnumcnum=_=_cnumcnum RETURN RETURN temptemp END END 37Tongji University 37 dbodbo. .函数名函数名 例例 调用调用get_cnum_avgget_cnum_avg函数，求函数，求c116c116课程的平均分课程的平均分 DECLARE DECLARE _avgavg intint, _, _cnumcnum char(4) char(4) SET SET _cnumcnum=c116c116 SETSET _avgavg=dbo.get_cnum_avgdbo.get_cnum_avg(_(_cnumcnum) ) PRINT PRINT _cnumcnum + + 平均平均分分: : + + cast(_cast(_avgavg as char(2) as char(2) 调用自定义调用自定义标量函数标量函数 38Tongji Uni