存储过程、触发器和游标课件

1、存储过程、触发器和游标数据库原理与应用教程SQL Server第第1111章存储过程、触发器和游标章存储过程、触发器和游标存储过程、触发器和游标引言引言 在在SQL Server 2005应用操作中，存储过程、触发器和游应用操作中，存储过程、触发器和游标都扮演着相当重要的角色。标都扮演着相当重要的角色。 存储过程是存储过程是SQL语句和可选流程控制语句的预编译集合，语句和可选流程控制语句的预编译集合，它以一个名称存储并作为一个单元处理，能够提高系统的它以一个名称存储并作为一个单元处理，能够提高系统的应用效率和执行速度。应用效率和执行速度。 触发器是一种特殊类型的存储过程。当有操作影响到触发触发

2、器是一种特殊类型的存储过程。当有操作影响到触发器保护的数据时，触发器就会自动触发执行。它可以扩展器保护的数据时，触发器就会自动触发执行。它可以扩展SQL Server完整性约束默认值对象和规则的完整性检查逻完整性约束默认值对象和规则的完整性检查逻辑，实施更为复杂的数据完整性约束。辑，实施更为复杂的数据完整性约束。 游标提供了一种对从表中检索出的数据进行操作的灵活手游标提供了一种对从表中检索出的数据进行操作的灵活手段，就本质而言，游标实际上是一种能从包括多条数据记段，就本质而言，游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。录的结果集中每次提取一条记录的机制。存储过程、触

3、发器和游标内容内容11.1 存储过程存储过程11.2 触发器触发器11.3 游标游标存储过程、触发器和游标11.1 存储过程存储过程1. 概述概述2. 存储过程的类别存储过程的类别3. 创建和执行存储过程创建和执行存储过程4. 查看、修改和删除存储过程查看、修改和删除存储过程存储过程、触发器和游标客户客户/服务器模型服务器模型服务器服务器客户客户A客户客户B 客户客户A需要查询部门需要查询部门1的销售额的销售额SELECT SUM(总金额总金额)FROM 订单信息订单信息 A INNER JOIN 销售人员销售人员 BON A.销售工号销售工号= B. 工号工号WHERE 部门号部门号=1有大

4、量数据在服务器和客户端之间流动！有大量数据在服务器和客户端之间流动！1. 概述概述11.1存储过程存储过程存储过程、触发器和游标1.概述概述-什么是存储过程什么是存储过程 存储过程的概念存储过程的概念一组编译在单个执行计划中的一组编译在单个执行计划中的T-SQL语句，它将一语句，它将一些固定的操作集中起来交给些固定的操作集中起来交给SQL Server数据库服数据库服务器完成，以完成某个任务。（务器完成，以完成某个任务。（管理任务或应用复管理任务或应用复杂的业务规则）杂的业务规则）。 是存储在服务器上的是存储在服务器上的 T-SQL 语句的命名集合语句的命名集合 是封装重复性任务的方法是封装重

5、复性任务的方法 支持用户声明变量、条件执行以及其他强有力的支持用户声明变量、条件执行以及其他强有力的编程特性编程特性存储过程、触发器和游标 存储过程（存储过程（procedure）类似于）类似于C语言中的函数语言中的函数 用来执行管理任务或应用复杂的业务规则用来执行管理任务或应用复杂的业务规则 存储过程可以带参数，也可以返回结果存储过程可以带参数，也可以返回结果int sum(int a,int b) int s; s =a+b; return s ;存储过程相存储过程相当于当于C语言语言中的函数中的函数1.概述概述-什么是存储过程什么是存储过程存储过程、触发器和游标 SQL Server 中

6、的存储过程可以中的存储过程可以包含执行数据库操作（包括调用其他过程）的包含执行数据库操作（包括调用其他过程）的编程语句编程语句接受输入参数接受输入参数向调用过程或批处理返回状态值，以表明成功向调用过程或批处理返回状态值，以表明成功或失败（以及失败原因）或失败（以及失败原因）以输出参数的形式将多个值返回至调用过程或以输出参数的形式将多个值返回至调用过程或批处理批处理1.概述概述-什么是存储过程什么是存储过程存储过程、触发器和游标-单个单个 SELECT 语句语句SELECT 语句块语句块SELECT语句与逻辑语句与逻辑控制语句控制语句可以包含可以包含q 存储过程可以包含数据操纵语句、变量、逻辑存

7、储过程可以包含数据操纵语句、变量、逻辑 控制语句控制语句等等1.概述概述-什么是存储过程什么是存储过程存储过程、触发器和游标安全机制安全机制：只给用户访问存储过程的权限，而不授予用户：只给用户访问存储过程的权限，而不授予用户访问表和视图的权限。访问表和视图的权限。改良了执行性能改良了执行性能：只在第一次执行时进行编译，以后执行：只在第一次执行时进行编译，以后执行无需重新编译，而一般无需重新编译，而一般SQLSQL语句每执行一次就编译一次。语句每执行一次就编译一次。减少网络流量减少网络流量：存储过程存在于服务器上，调用时，只需：存储过程存在于服务器上，调用时，只需传递执行存储过程的执行命令和返回

8、结果。传递执行存储过程的执行命令和返回结果。模块化的程序设计模块化的程序设计：增强了代码的可重用性，提高了开发：增强了代码的可重用性，提高了开发效率。效率。1. 概述概述-存储过程优点存储过程优点存储过程、触发器和游标2.存储过程的类型存储过程的类型 用户定义的存储过程用户定义的存储过程：用户定义的存储过程是：用户定义的存储过程是用户根据需要，为完成某一特定功能，在自己用户根据需要，为完成某一特定功能，在自己的普通数据库中创建的存储过程。的普通数据库中创建的存储过程。 系统存储过程系统存储过程：系统存储过程以：系统存储过程以sp_为前缀，为前缀，主要用来从系统表中获取信息，为系统管理员主要用来

9、从系统表中获取信息，为系统管理员管理管理SQL Server提供帮助，为用户查看数据库提供帮助，为用户查看数据库对象提供方便。比如用来查看数据库对象信息对象提供方便。比如用来查看数据库对象信息的系统存储过程的系统存储过程sp_help。从物理意义上讲，。从物理意义上讲，系统存储过程存储在资源数据库中。从逻辑意系统存储过程存储在资源数据库中。从逻辑意义上讲，系统存储过程出现在每个系统定义数义上讲，系统存储过程出现在每个系统定义数据库和用户定义数据库的据库和用户定义数据库的sys构架中。构架中。存储过程、触发器和游标n扩展存储过程扩展存储过程：指：指SQL Server的实例动态加载和运的实例动态

10、加载和运行的行的DLL，这些，这些DLL通常是用编程语言（例如：通常是用编程语言（例如：C）创建的。扩展存储过程以创建的。扩展存储过程以xp_为前缀。为前缀。n临时存储过程临时存储过程：以：以“#”和和“#”为前缀的过程，为前缀的过程，“#”表示本地临时存储过程，表示本地临时存储过程，“#”表示全局临时存储表示全局临时存储过程，它们存储在过程，它们存储在tempdb数据库中。数据库中。n远程存储过程远程存储过程：是在远程服务器的数据库中创建和：是在远程服务器的数据库中创建和存储的过程。这些存储过程可被各种服务器访问，存储的过程。这些存储过程可被各种服务器访问，向具有相应许可权限的用户提供服务。

11、向具有相应许可权限的用户提供服务。存储过程、触发器和游标2.存储过程的类型存储过程的类型-常用的系统存储过程常用的系统存储过程系统存储过程系统存储过程说明说明sp_databases列出服务器上的所有数据库。列出服务器上的所有数据库。sp_helpdb报告有关指定数据库或所有数据库的信息报告有关指定数据库或所有数据库的信息sp_renamedb更改数据库的名称更改数据库的名称sp_tables返回当前环境下可查询的对象的列表返回当前环境下可查询的对象的列表sp_columns回某个表列的信息回某个表列的信息sp_help查看某个表的所有信息查看某个表的所有信息sp_helpconstraint

12、查看某个表的约束查看某个表的约束sp_helpindex查看某个表的索引查看某个表的索引sp_stored_procedures列出当前环境中的所有存储过程。列出当前环境中的所有存储过程。sp_password添加或修改登录帐户的密码。添加或修改登录帐户的密码。sp_helptext显示默认值、未加密的存储过程、用户定义的存显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本。储过程、触发器或视图的实际文本。存储过程、触发器和游标EXEC sp_databasesEXEC sp_renamedb Northwind,Northwind1USE stuDBGOEXEC sp_

13、tablesEXEC sp_columns stuInfo EXEC sp_help stuInfoEXEC sp_helpconstraint stuInfoEXEC sp_helpindex stuMarksEXEC sp_helptext view_stuInfo_stuMarks EXEC sp_stored_procedures 常用的系统存储过程常用的系统存储过程修改数据库的名称修改数据库的名称(单用户访问单用户访问)列出当前系统中的数据库列出当前系统中的数据库当前数据库中查询的对象的列表当前数据库中查询的对象的列表返回某个表列的信息返回某个表列的信息查看表查看表stuInfo的信

14、息的信息查看表查看表stuInfo的约束的约束查看表查看表stuMarks的索引的索引查看视图的语句文本查看视图的语句文本查看当前数据库中的存储过程查看当前数据库中的存储过程存储过程、触发器和游标3.创建、执行存储过程创建、执行存储过程使用使用SSMS管理存储过程管理存储过程创建创建执行执行 查看查看修改修改删除删除存储过程、触发器和游标3.创建、执行存储过程创建、执行存储过程 利用利用T-SQLT-SQL语句创建存储过程语句创建存储过程CREATE PROCEDURE 存储过程名存储过程名 ;num 参数参数1 数据类型数据类型 =默认值默认值 OUTPUT , 其他参数其他参数 WITH

15、RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION ASBEGIN SQL语句系列语句系列END存储过程、触发器和游标5.2.2 存储过程创建和执行存储过程创建和执行;num;num：可选的整数。用来对同名若干过程编号，如：可选的整数。用来对同名若干过程编号，如 proc_s;1, proc_s;1, proc_s;2, proc_s;3proc_s;2, proc_s;3等。等。采用相同名字加编号区别若干过程的采用相同名字加编号区别若干过程的优点优点是删除时方便，如，是删除时方便，如，DROP PROCEDURE proc_sDROP PROCED

16、URE proc_s一条命令把一条命令把3 3个过程都删了。个过程都删了。defaultdefault：参数的默认值。如果定义了默认值，不必指定该参参数的默认值。如果定义了默认值，不必指定该参数的值就可调用。默认值必须是常量或数的值就可调用。默认值必须是常量或NULLNULL。如果过程将对该参。如果过程将对该参数使用数使用LIKELIKE关键字，那么默认值中可以包含通配符关键字，那么默认值中可以包含通配符(%(%、_ _、)。OUTPUTOUTPUT：表示该参数是返回参数。参数可将信息返回给调用过表示该参数是返回参数。参数可将信息返回给调用过程。程。TextText、ntextntext、Im

17、ageImage参数可用作参数可用作OUTPUTOUTPUT参数。参数。RECOMPILERECOMPILE：表明该过程在运行时重新编译。表明该过程在运行时重新编译。 ENCRYPTION ENCRYPTION ：表示系统加密表示系统加密syscommentssyscomments表中包含表中包含CREATE CREATE PROCEDUREPROCEDURE语句文本的条目。语句文本的条目。 加密后即使是过程创建者加密后即使是过程创建者 本人也无法查看过程定义文本。本人也无法查看过程定义文本。说明说明:存储过程、触发器和游标3.创建、执行存储过程创建、执行存储过程 定义存储过程的语法定义存储过

18、程的语法 CREATE PROCEDURE 存储过程名存储过程名 参数参数1 数据类型数据类型 = 默认值默认值 OUTPUT, , 参数参数n 数据类型数据类型 = 默认值默认值 OUTPUT AS SQL语句语句 GO和和C语言的函数一样，参数可选语言的函数一样，参数可选参数分为输入参数、输出参数参数分为输入参数、输出参数 输入参数允许有默认值输入参数允许有默认值存储过程、触发器和游标 创建不带参数的存储过程创建不带参数的存储过程问题：问题：请创建存储过程，查看本次考试平均分以及未通过考试的学员请创建存储过程，查看本次考试平均分以及未通过考试的学员名单名单存储过程、触发器和游标创建不带参数

19、的存储过程创建不带参数的存储过程CREATE PROCEDURE proc_stu AS DECLARE writtenAvg float,labAvg float SELECT writtenAvg=AVG(writtenExam), labAvg=AVG(labExam) FROM stuMarks print 笔试平均分：笔试平均分：+convert(varchar(5),writtenAvg) print 机试平均分：机试平均分：+convert(varchar(5),labAvg) IF (writtenAvg70 AND labAvg70) print 本班考试成绩：优秀本班考试成

20、绩：优秀 ELSE print 本班考试成绩：较差本班考试成绩：较差 print - print 参加本次考试没有通过的学员：参加本次考试没有通过的学员： SELECT stuName,stuInfo.stuNo,writtenExam,labExam FROM stuInfo INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo WHERE writtenExam60 OR labExam60 GOproc_stu为存储过程的名称为存储过程的名称笔试平均分和机笔试平均分和机试平均分变量试平均分变量 显示考试成绩的等级显示考试成绩的等级 显示未

21、通过的学员显示未通过的学员 存储过程、触发器和游标 调用存储过程调用存储过程 EXECUTE（执行）（执行）语句用来调用存储过程语句用来调用存储过程 调用的语法调用的语法EXEC 过程名过程名 参数参数EXEC proc_stu存储过程、触发器和游标 存储过程的参数分两种：存储过程的参数分两种：输入参数输入参数输出参数输出参数 输入参数：输入参数：用于向存储过程用于向存储过程传入传入值，类似值，类似C语言的按值传递语言的按值传递; 输出参数：输出参数：用于在调用存储过程后，用于在调用存储过程后，返回返回结果，类似结果，类似C语言的语言的按引用传递按引用传递; 调用存储过程调用存储过程int s

22、um (int a, int b) int s; s=a+b; return s;c=sum（5, 8）传入参传入参数值数值返回结果返回结果存储过程、触发器和游标 传递参数的方式传递参数的方式使用参数位置使用参数位置形式：不显式指出参数名形式：不显式指出参数名顺序要求：严格按照定义的顺序顺序要求：严格按照定义的顺序使用参数名使用参数名形式：形式：参数名参数值参数名参数值顺序要求：可以不按顺序指定参数值顺序要求：可以不按顺序指定参数值存储过程、触发器和游标 带输入参数的存储过程带输入参数的存储过程问题：问题：修改上例：由于每次考试的难易程度不一样，每次修改上例：由于每次考试的难易程度不一样，每次

23、 笔试和笔试和机试的及格线机试的及格线可能随时变化（不再是可能随时变化（不再是6060分），这导致考试的分），这导致考试的评判结果也相应变化。评判结果也相应变化。分析：分析：在述存储过程添加在述存储过程添加2个输入参数：个输入参数：writtenPass 笔试及格线笔试及格线 labPass 机试及格线机试及格线 存储过程、触发器和游标 带输入参数的存储过程带输入参数的存储过程CREATE PROCEDURE proc_stu writtenPass int, labPass int AS print - print 参加本次考试没有通过的学员：参加本次考试没有通过的学员： SELECT st

24、uName,stuInfo.stuNo,writtenExam, labExam FROM stuInfo INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo WHERE writtenExamwrittenPass OR labExamlabPass GO输入参数：笔试及格线输入参数：笔试及格线输入参数：机试及格线输入参数：机试及格线查询没有通过查询没有通过考试的学员考试的学员存储过程、触发器和游标 带输入参数的存储过程带输入参数的存储过程EXEC proc_stu 60,55 调用带参数的存储过程调用带参数的存储过程 假定本次考试机试偏难

25、，机试的及格线定为假定本次考试机试偏难，机试的及格线定为55分，笔试及格线定为分，笔试及格线定为60分分-或这样调用：或这样调用：EXEC proc_stu labPass=55,writtenPass=60机试及格线降分后，机试及格线降分后，李斯文李斯文（59分）成为分）成为“漏网之鱼漏网之鱼”了了存储过程、触发器和游标 输入参数的默认值输入参数的默认值 带参数的存储过程确实比较方便，调用者可根据带参数的存储过程确实比较方便，调用者可根据试卷的难易度，随时修改每次考试的及格线试卷的难易度，随时修改每次考试的及格线问题：问题：如果试卷的难易程度合适，则调用者还是必须如果试卷的难易程度合适，则调

26、用者还是必须如此调用：如此调用： EXEC proc_stu 60,60，比较麻烦，比较麻烦这样调用就比较合理：这样调用就比较合理：EXEC proc_stu 55EXEC proc_stu 笔试及格线笔试及格线55分，机试及格线默认分，机试及格线默认为为60分分 笔试和机试及格线都默认为标准的笔试和机试及格线都默认为标准的60分分存储过程、触发器和游标CREATE PROCEDURE proc_stu writtenPass int=60, labPass int=60 AS print - print 参加本次考试没有通过的学员：参加本次考试没有通过的学员： SELECT stuName,

27、stuInfo.stuNo,writtenExam, labExam FROM stuInfo INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo WHERE writtenExamwrittenPass OR labExamlabPass GO笔试及格线：默认为笔试及格线：默认为60分分机试及格线：默认为机试及格线：默认为60分分查询没有通过考试的学员查询没有通过考试的学员 输入参数的默认值输入参数的默认值存储过程、触发器和游标输入参数的默认值输入参数的默认值EXEC proc_stu -都采用默认值都采用默认值 EXEC proc_stu

28、 64 -机试采用默认值机试采用默认值 EXEC proc_stu 60,55 -都不采用默认值都不采用默认值 调用带参数默认值的存储过程调用带参数默认值的存储过程-错误的调用方式：希望笔试采用默认值，机试及格线错误的调用方式：希望笔试采用默认值，机试及格线55分分EXEC proc_stu ,55 -正确的调用方式：正确的调用方式：EXEC proc_stu labPass=55 存储过程、触发器和游标 带输出参数的存储过程带输出参数的存储过程 如果希望调用存储过程后，返回一个或多个值，这时就需如果希望调用存储过程后，返回一个或多个值，这时就需要使用输出（要使用输出（OUTPUT）参数了）参

29、数了 问题：问题：修改上例，返回未通过考试的学员人数。修改上例，返回未通过考试的学员人数。存储过程、触发器和游标CREATE PROCEDURE proc_stu notpassSum int OUTPUT, writtenPass int=60, labPass int=60 AS SELECT stuName,stuInfo.stuNo,writtenExam, labExam FROM stuInfo INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo WHERE writtenExamwrittenPass OR labExamlabPass SELECT notpassSum=COUNT(stuNo) FROM stuMarks WHERE writtenEx