数据库系统概论——存储过程和触发器

1、SQL Server 存储过程和触发器主要内容主要内容lTransact-SQL编程基础l事务管理l游标 l存储过程l触发器 1 Transact-SQL编程基础编程基础 l标识符、运算符、变量l函数l流程控制语句lGOTO语句lRETURN语句l打印输出语句l注释语句标识符标识符l标识符是由用户定义的有意义的可识别的字符序列，通常用来标识服务器、数据库、数据库对象、常量、变量等 l命名标识符时必须遵循以下规则 l第一个字符必须是下列字符之一： l字母、下划线(_)、或者# l后续字符可以是：l字母、数字、_、#、$、等符号 l不能使用SQL中的关键字和运算符，不允许嵌入空格或其它特殊字符 运

2、算符运算符l算术运算符l+ ，- ，*， / ，%l赋值运算符l=l比较运算符l= ， ，= ，= ，l逻辑运算符land ，or ，notlBetweenand，In，Like，Existsl连接运算符l+ 用于字符串数据的连接运算符的优先顺序运算符的优先顺序 l*、/、%l+、-l=、=、=、lnotlandlbetween、in、like、orl= 算术运算符算术运算符比较运算符比较运算符逻辑运算符逻辑运算符赋值运算符赋值运算符变量变量 l局部变量l由用户定义和使用，可赋值并参与运算l作用域 批处理、触发器、存储过程l声明 declare 变量名 数据类型,l赋值 select 变量名=

3、值 或 SET 局部变量名=表达式【例【例1】DECLARE x float, var char(8) 该定义语句定义了变量该定义语句定义了变量x是浮点实型，变量是浮点实型，变量var是长度为是长度为8的定长字符数据类型。的定长字符数据类型。 【例2】定义变量c_code和score，并分别用SET和SELECT语句为它们赋值，然后查询所有课程号为c_code,且成绩小于score的学生成绩信息。DECLARE DECLARE c_codec_code char(6), score char(6), score Numeric(3,1)Numeric(3,1)SET SET c_codec_c

4、ode=130001=130001SELECT score=80SELECT score=80 SELECT SELECT * * FROM FROM T_GradeT_Grade WHERE WHERE CourseCodeCourseCode=c_codec_code AND AND GradescoreGrade=3 BEGIN SET text=你选了你选了+CAST(cn AS char(2) /* CAST函数将函数将cn的值转换为长度为的值转换为长度为2的字符数据的字符数据*/ SET text=text+门课。很好，你完成了任务！门课。很好，你完成了任务！ ENDELSE BE

5、GIN SET text=你选了你选了+CAST(cn AS char(2) SET text=text+门课。选课太少，加油！门课。选课太少，加油！ ENDSELECT text AS 选课提示选课提示多分支的选择语句多分支的选择语句-CASEl简单简单 CASE 函数形式：将某个表达式与一组函数形式：将某个表达式与一组简单表达式进行比较以确定结果。简单表达式进行比较以确定结果。lCASE 搜索函数形式：计算一组条件表达式搜索函数形式：计算一组条件表达式以确定结果以确定结果简单CASE函数l语法格式：语法格式： CASE 输入表达式输入表达式 WHEN 情况表达式情况表达式 THEN 结果表

6、达式结果表达式 . ELSE 结果表达式结果表达式 ENDl说明：说明：1)当表达式的值与某个情况表达式的值相等时，返回相当表达式的值与某个情况表达式的值相等时，返回相应结果表达式的值应结果表达式的值 。 2)如果全部都不相等，则返回如果全部都不相等，则返回 ELSE中的结果表达式，中的结果表达式，若没有若没有ELSE子句，则返回子句，则返回NULL值。值。 【例例5 5】查询查询0707级女同学的住校情况。级女同学的住校情况。SELECT StudentName AS 姓名姓名, CASE LiveInDorm WHEN 0 THEN 未住校未住校 WHEN 1 THEN 住校住校 END

7、AS 是否住校是否住校 FROM T_StudentWHERE Sex=女女 AND LEFT(StudentCode,2)=07字段：是否住校的表达CASE 搜索函数搜索函数语法格式：语法格式： CASE WHEN 条件表达式条件表达式 THEN 结果表达式结果表达式 . ELSE结果表达式结果表达式 END【例例6 6】统计每个学生平均成绩并划分等级统计每个学生平均成绩并划分等级。SELECT SELECT StudentCodeStudentCode AS AS 学号学号, STR(AVG(Grade),5,2) AS , STR(AVG(Grade),5,2) AS 平均成绩平均成绩,

8、 CASE CASE WHEN WHEN AVG(GradeAVG(Grade)=90 THEN A)=90 THEN A WHEN WHEN AVG(GradeAVG(Grade)=80 THEN B)=80 THEN B WHEN WHEN AVG(GradeAVG(Grade)=70 THEN C)=70 THEN C WHEN WHEN AVG(GradeAVG(Grade)=60 THEN D)=60 THEN D WHEN WHEN AVG(GradeAVG(Grade)60 THEN E)60 THEN E END AS END AS 等级等级 FROM FROM T_Grade

9、T_Grade GROUP BY GROUP BY StudentCodeSELECTStudentCodeSELECT流程控制语句流程控制语句2BEGINEND语句l定义语句块：l多条T-SQL语句使用BEGINEND组合起来形成一个语句块。l可以嵌套使用l语法形式为 BEGIN SQL语句1 SQL语句2 END 流程控制语句流程控制语句3WHILECONTINUEBREAK语句l条件循环语句lContinue可以使程序跳过其后面的语句，回到while循环的第一行命令;lBreak使程序完全跳出循环，结束while语句。l语法形式为 WHILE BREAK或CONTINUE 结束结束整个整

10、个循环循环结束结束本次本次循环循环while breakwhile continue【例例8 8】计算计算1010！。 DECLARE p int, i smallint, text varchar(100)SET p=1SET i=1WHILE i=10 BEGIN SET p= p*i SET i=i+1 ENDSET text=12310=+CAST(p AS char(10)SELECT text AS 计算结果GOTOGOTO语句语句 GOTO语句的语法形式为 l跳转语句，跳转到标有标识符指定位置 GOTO label label:【例9】利用利用GOTOGOTO语句求出从语句求出从

11、1 1加到加到5 5的总和的总和 RETURNRETURN语句语句 l终止语句，用于无条件终止一个查询、存储过程或者批处理。lRETURN语句的语法形式为 RETURN 整数 打印输出语句打印输出语句 可以显示变量的值PRINT语句的语法如下 PRINT | | 例例10 利用利用PRINTPRINT语句判断变量值的示例语句判断变量值的示例 注释语句注释语句 （1）整块注释/* 注释块 */（2）从行的后部分注释 语句 注释事务管理事务管理B Beginegin Transaction Transaction 事务开始事务开始 SQLSQL语句组语句组 C Commit Transaction

12、 ommit Transaction 提交事务提交事务说明：说明：l用户定义的事务也称为显式事务用户定义的事务也称为显式事务l事务只用于数据修改语句事务只用于数据修改语句l可以在可以在commit transactioncommit transaction之前使用之前使用roolbackroolback transaction transaction取取消事务并撤销对数据所作的任何改变，命令为：消事务并撤销对数据所作的任何改变，命令为：roolbackroolback transaction transaction savepoint_namesavepoint_name lSavepoint

13、_nameSavepoint_name为保存点，是用户放在事务中的一个标记，为保存点，是用户放在事务中的一个标记，指明回滚点指明回滚点l事务管理通过确保要么完成整个事务，要么回滚事务，来确事务管理通过确保要么完成整个事务，要么回滚事务，来确保数据库的一致性和可恢复性保数据库的一致性和可恢复性例例1111 建立一个事务，建立一个事务，用来将用来将mag_deptmag_dept表中表中depiddepid为为1 1的记录的的记录的depmanagerdepmanager字段的值更改为字段的值更改为“王涓涓王涓涓” 例例1212 建立一个事务，建立一个事务，用来更改用来更改mag_deptmag_

14、dept表中表中depiddepid为为1 1以及以及depiddepid为为2 2的记录的的记录的depmanagerdepmanager字段字段的值，并回滚事务到保存点的值，并回滚事务到保存点存储过程存储过程 l存储过程：可以将一些固定的操作集中起来由SQL Server数据库服务器完成，以实现某个任务；存储过程允许声明变量、输出参数、返回单个或者多个结果集以及返回值；l存储过程存在于数据库内，可由应用程序调用执行。l存储过程分为两类：l系统存储过程（存储在master数据库中）l用户自定义的存储过程。l使用存储过程有以下优点：1)可以在一个存储过程中执行多条SQL语句；2)可多次调用；3

15、)创建时就在服务器端进行了编译，节省SQL语句的运行时间；4)提供了安全机制，它限制了用户访问SQL语句的权利，只为特定用户开放存储过程。创建存储过程创建存储过程 常用存储过程的语法格式：常用存储过程的语法格式： CREATE PROCDURE 存储过程名存储过程名 形式参数形式参数 数据类型数据类型VARYING=默认值默认值OUTPUT AS SQL语句语句1 SQL语句语句n 说明：说明： l“形式参数形式参数”名称必须符合标识符规则；名称必须符合标识符规则；lOUTPUT表示该参数是可以返回的，可将信息返回调用者；表示该参数是可以返回的，可将信息返回调用者；l如果有多个参数，可以依次按

16、以上参数定义规则列出，用逗号如果有多个参数，可以依次按以上参数定义规则列出，用逗号“,”隔开。隔开。例例1313 建立一个名为建立一个名为“全部雇员全部雇员”的存储过程，的存储过程，用用来查询来查询mag_empmag_emp表的所有记录表的所有记录 例例14 建立一个名为建立一个名为“雇员查询雇员查询”的存储过程，的存储过程，查询某种职务雇员的所有情况查询某种职务雇员的所有情况 【例15】在School中创建一个的存储过程proc_SearchStudent,查询指定学生的选课情况。CREATE PROC CREATE PROC proc_SearchStudentproc_SearchSt

17、udent stcodestcode char(8) char(8)ASASSELECT SELECT T_Student.StudentName,T_Course.CourseNameT_Student.StudentName,T_Course.CourseNameFROM T_Student JOIN T_Grade JOIN T_CourseFROM T_Student JOIN T_Grade JOIN T_Course ON T_Grade.CourseCode=T_Course.CourseCode ON T_Grade.CourseCode=T_Course.CourseCode

18、 ON T_Student.StudentCode=T_Grade.StudentCode ON T_Student.StudentCode=T_Grade.StudentCodeWHERE WHERE T_Student.StudentCodeT_Student.StudentCode=scodescode 修改存储过程修改存储过程 修改存储过程的语法为 ALTER PROCEDURE ； =OUTPUT AS 例例16 修改存储过程修改存储过程“雇员查询雇员查询”，用来，用来统计某一职务雇员的总人数统计某一职务雇员的总人数 运行存储过程运行存储过程 运行存储过程的语法为 EXECUTE 存

19、储过程名存储过程名形参形参=实参值实参值|变量变量OUTPUT|DEFAULT说明：说明：1)“形参形参”是创建存储过程时定义的形参名；是创建存储过程时定义的形参名；2)“实参值实参值”是输入参数的值；是输入参数的值；3)“变量变量”表示用来保存参数或者返回参数的变量；表示用来保存参数或者返回参数的变量；OUTPUT表示表示指定参数为返回参数；指定参数为返回参数；4)DEFAULT表示使用该参数的默认值作为实参。表示使用该参数的默认值作为实参。5)如有多个参数，可依次按以上参数定义规则列出，用逗号如有多个参数，可依次按以上参数定义规则列出，用逗号“，”隔隔开。开。调用存储过程调用存储过程pro

20、c_Courseproc_Course的语句为：的语句为：EXEC EXEC proc_Courseproc_Course调用存储过程调用存储过程proc_SearchStudentproc_SearchStudent，查询查询0510110105101101学生的选课情况。学生的选课情况。EXEC EXEC proc_SearchStudentproc_SearchStudent 05101101 05101101或或EXEC proc_ EXEC proc_ SearchStudentSearchStudent scodescode=05101101=05101101采用采用“参数参数=值

21、值”的形的形式，各个参数的顺序式，各个参数的顺序可以任意排列可以任意排列。实参顺序和定义时实参顺序和定义时的参数顺序一致。的参数顺序一致。【例【例1717】在在SchoolSchool中创建一个的存储过程中创建一个的存储过程proc_SearchStudentAvgGradeproc_SearchStudentAvgGrade，查询某个学生的选课数目查询某个学生的选课数目和平均成绩和平均成绩。CREATE PROC CREATE PROC proc_SearchStudentAvgGradeproc_SearchStudentAvgGrade stcodestcode char(8), cha

22、r(8), stcountstcount intint OUTPUT,stavgOUTPUT,stavg intint OUTPUT OUTPUTASASSELECT SELECT stcountstcount= =COUNT(T_Grade.StudentCode),stavgCOUNT(T_Grade.StudentCode),stavg= =AVG(T_Grade.GradeAVG(T_Grade.Grade) )FROM FROM T_GradeT_GradeWHERE WHERE T_Grade.StudentCodeT_Grade.StudentCode=stcodestcodeG

23、ROUP BY GROUP BY T_Grade.StudentCodeT_Grade.StudentCode调用存储过程调用存储过程proc_SearchStudentAvgGradeproc_SearchStudentAvgGrade，查询查询0510110105101101学生的选课数目和平均成绩学生的选课数目和平均成绩。DECLARE DECLARE scountscount intint , ,savgsavg intint EXECEXEC proc_SearchStudentAvgGradeproc_SearchStudentAvgGrade 05101101,scount 05

24、101101,scount OUTPUT,savgOUTPUT,savg OUTPUT OUTPUT PRINT PRINT 学生学生+ 05101101+ 05101101+的选课数目为的选课数目为+CAST(scountCAST(scount AS AS char(2)+char(2)+门门 PRINT PRINT 学生学生+ 05101101+ 05101101+的平均成绩为的平均成绩为+STR(savg,5,2)+STR(savg,5,2)+分分 删除存储过程删除存储过程 删除存储过程的语法为 Drop Procedure 游标游标l问题要对查询结果进行一行行的操作，如何做到？ 游游标

25、标引引例例以前：使用SELECT语句对表格进行查询，返回的结果集包括所有满足条件的行。思考：如果要求每次只显示表格（例如上面的Course表）中的一行，该如何处理？这在将T-SQL嵌入到其他高级语言（如C、VC、Delphi等）的编程中经常用到。 游标概述游标概述l可以将游标看作一种特殊的指针，它可以指向与它相关联的结果集中的任意一行，以便对当前位置的行进行处理。l游标提供了对一个结果集进行逐行处理的能力：1.在结果集中定位特定行2.从结果集的当前位置检索行3.支持对结果集中当前位置的行进行数据处理（修改/删除）使用游标的步骤使用游标的步骤l声明游标l打开游标l处理数据（读取/修改/删除）可以

26、和其他T-SQL语句配合灵活使用l关闭游标（与打开游标配对）l删除游标（与声明游标配对，此时释放分配给游标的所有资源）【问题】游标基本使用的例子相关语法格式相关语法格式l声明游标DECLARE cursor_name CURSORDECLARE cursor_name CURSORFORWORD_ONLY|SCROLLFORWORD_ONLY|SCROLLFOR select_statementsFOR select_statementsl打开游标OPEN cursor_nameOPEN cursor_namel使用游标FETCH NEXT|PRIOR|FIRST|LASTFETCH NEXT

27、|PRIOR|FIRST|LASTFROM cursor Into variable_nameFROM cursor Into variable_namel关闭游标CLOSE cursor_nameCLOSE cursor_namel删除游标DEALLOCATION cursor_nameDEALLOCATION cursor_name如果遍历整个结果集？如果遍历整个结果集？l使用fetch_status:l该全局变量/配置函数返回被最后FETCH语句执行的游标的状态，返回类型为int：1.0：FETCH语句成功2.1:FETCH语句失败或此行不在结果集中3.2：被提取的行不存在USE mas

28、terGOCREATE PROCEDURE sp_BuildIndexesASDECLARE TableName sysname, msg varchar(100), cmd varchar(100)DECLARE table_cur CURSOR FORSELECT name FROM sysobjects WHERE type=uOPEN table_curFETCH NEXT FROM table_cur INTO TableNameWHILE fetch_status = 0BEGINIF fetch_status = -2CONTINUESELECT msg = “Building

29、indexes for table”+TableName+”PRINT msgSELECT cmd = “DBCC DBREINDEX (”+TableName+”)”EXEC (cmd)PRINT “ “FETCH NEXT FROM table_cur INTO TableNameENDDEALLOCATE table_curGO触发器触发器l定义在表上的一个对象l一种特殊类型的存储过程l不需要专门语句调用，通过事件进行触发而被执行l当执行insert、delete和update语句时自动被触发执行l触发器可以在有数据操作时自动强制执行某些业务规则l触发器执行的内容可以是报警、维护数据的完

30、整性、特殊的数据处理l触发器可以用于完整性检查，但比约束、默认值、规则等功能强大、灵活lSQL Server的两种触发器： （1）AFTER触发器：数据被修改后触发，对变动的数据进行检查。（2）INSTEAD OF触发器：数据修改之前被触发，并取代修改数据的操作，转去执行触发器定义的操作。例如，执行完下面的语句后：例如，执行完下面的语句后： CREATE TRIGGER tri_StudentInsDel ON dbo.T_Student FOR INSERT, DELETE AS SELECT * FROM T_Student 当对表当对表T_Student的数据进行插入或删除操的数据进行插

31、入或删除操作时，触发器作时，触发器“tri_StudentUpdate”将会自动执行。将会自动执行。创建触发器创建触发器 创建触发器时提供：触发器名称、定义触发器的表、触发器触发事件、数据修改语句。创建触发器的语法为CREATE TRIGGER 触发器名触发器名ON 表名表名|视图名视图名FOR INSERT,UPDATE,DELETEASSQL语句段语句段一个触发器只能用于一个表两个特殊的逻辑表：inserted、deleted例例1818 利用触发器来保证利用触发器来保证期刊采编系统数据库中期刊采编系统数据库中mag_infomag_info表的参照完整性表的参照完整性 【例【例1919】在在SchoolSchool数据库的数据库的T_StudentT_Student表上创建一个触发器表上创建一个触发器tri_StudentCodeUpdatetri_StudentCodeUpdate，当对学号列进行修改时，给出提示信息并当对学号列进行修改时，给出提示信息并取消修改操作。取消修改操作。 CREATE TRIGGER CREATE TRIGGER tri_StudentCodeUpdatetri_StudentCodeUpdateON ON T_StudentT_StudentFOR UPDATEFOR UPDATEAS