存储过程、触发器和游标.ppt

数据库原理与应用教程SQLServer,第11章存储过程、触发器和游标,引言,在SQLServer2005应用操作中，存储过程、触发器和游标都扮演着相当重要的角色。存储过程是SQL语句和可选流程控制语句的预编译集合，它以一个名称存储并作为一个单元处理，能够提高系统的应用效率和执行速度。触发器是一种特殊类型的存储过程。当有操作影响到触发器保护的数据时，触发器就会自动触发执行。它可以扩展SQLServer完整性约束默认值对象和规则的完整性检查逻辑，实施更为复杂的数据完整性约束。游标提供了一种对从表中检索出的数据进行操作的灵活手段，就本质而言，游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。,内容,11.1存储过程11.2触发器11.3游标,11.1存储过程,1.概述2.存储过程的类别3.创建和执行存储过程4.查看、修改和删除存储过程,客户/服务器模型,客户A需要查询部门1的销售额SELECTSUM(总金额)FROM订单信息AINNERJOIN销售人员BONA.销售工号=B.工号WHERE部门号=1,有大量数据在服务器和客户端之间流动！,1.概述,11.1存储过程,1.概述-什么是存储过程,存储过程的概念一组编译在单个执行计划中的T-SQL语句，它将一些固定的操作集中起来交给SQLServer数据库服务器完成，以完成某个任务。（管理任务或应用复杂的业务规则）。是存储在服务器上的T-SQL语句的命名集合是封装重复性任务的方法支持用户声明变量、条件执行以及其他强有力的编程特性,存储过程（procedure）类似于C语言中的函数用来执行管理任务或应用复杂的业务规则存储过程可以带参数，也可以返回结果,intsum(inta,intb)ints;s=a+b;returns;,存储过程相当于C语言中的函数,1.概述-什么是存储过程,SQLServer中的存储过程可以包含执行数据库操作（包括调用其他过程）的编程语句接受输入参数向调用过程或批处理返回状态值，以表明成功或失败（以及失败原因）以输出参数的形式将多个值返回至调用过程或批处理,1.概述-什么是存储过程,存储过程-,单个SELECT语句,SELECT语句块,SELECT语句与逻辑控制语句,可以包含,存储过程可以包含数据操纵语句、变量、逻辑控制语句等,1.概述-什么是存储过程,安全机制：只给用户访问存储过程的权限，而不授予用户访问表和视图的权限。改良了执行性能：只在第一次执行时进行编译，以后执行无需重新编译，而一般SQL语句每执行一次就编译一次。减少网络流量：存储过程存在于服务器上，调用时，只需传递执行存储过程的执行命令和返回结果。模块化的程序设计：增强了代码的可重用性，提高了开发效率。,1.概述-存储过程优点,2.存储过程的类型,用户定义的存储过程：用户定义的存储过程是用户根据需要，为完成某一特定功能，在自己的普通数据库中创建的存储过程。系统存储过程：系统存储过程以sp_为前缀，主要用来从系统表中获取信息，为系统管理员管理SQLServer提供帮助，为用户查看数据库对象提供方便。比如用来查看数据库对象信息的系统存储过程sp_help。从物理意义上讲，系统存储过程存储在资源数据库中。从逻辑意义上讲，系统存储过程出现在每个系统定义数据库和用户定义数据库的sys构架中。,扩展存储过程：指SQLServer的实例动态加载和运行的DLL，这些DLL通常是用编程语言（例如：C）创建的。扩展存储过程以xp_为前缀。临时存储过程：以“#”和“#”为前缀的过程，“#”表示本地临时存储过程，“#”表示全局临时存储过程，它们存储在tempdb数据库中。远程存储过程：是在远程服务器的数据库中创建和存储的过程。这些存储过程可被各种服务器访问，向具有相应许可权限的用户提供服务。,2.存储过程的类型-常用的系统存储过程,EXECsp_databasesEXECsp_renamedbNorthwind,Northwind1USEstuDBGOEXECsp_tablesEXECsp_columnsstuInfoEXECsp_helpstuInfoEXECsp_helpconstraintstuInfoEXECsp_helpindexstuMarksEXECsp_helptextview_stuInfo_stuMarksEXECsp_stored_procedures,常用的系统存储过程,修改数据库的名称(单用户访问),列出当前系统中的数据库,当前数据库中查询的对象的列表,返回某个表列的信息,查看表stuInfo的信息,查看表stuInfo的约束,查看表stuMarks的索引,查看视图的语句文本,查看当前数据库中的存储过程,3.创建、执行存储过程,使用SSMS管理存储过程创建执行查看修改删除,3.创建、执行存储过程,利用T-SQL语句创建存储过程,CREATEPROCEDURE存储过程名;num参数1数据类型=默认值OUTPUT,其他参数WITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONASBEGINSQL语句系列END,5.2.2存储过程创建和执行,;num：可选的整数。用来对同名若干过程编号，如proc_s;1,proc_s;2,proc_s;3等。采用相同名字加编号区别若干过程的优点是删除时方便，如，DROPPROCEDUREproc_s一条命令把3个过程都删了。default：参数的默认值。如果定义了默认值，不必指定该参数的值就可调用。默认值必须是常量或NULL。如果过程将对该参数使用LIKE关键字，那么默认值中可以包含通配符(%、_、)。OUTPUT：表示该参数是返回参数。参数可将信息返回给调用过程。Text、ntext、Image参数可用作OUTPUT参数。RECOMPILE：表明该过程在运行时重新编译。ENCRYPTION：表示系统加密syscomments表中包含CREATEPROCEDURE语句文本的条目。加密后即使是过程创建者本人也无法查看过程定义文本。,说明:,3.创建、执行存储过程,定义存储过程的语法CREATEPROCEDURE存储过程名参数1数据类型=默认值OUTPUT,参数n数据类型=默认值OUTPUTASSQL语句GO和C语言的函数一样，参数可选参数分为输入参数、输出参数输入参数允许有默认值,创建不带参数的存储过程,问题：请创建存储过程，查看本次考试平均分以及未通过考试的学员名单,创建不带参数的存储过程,CREATEPROCEDUREproc_stuASDECLAREwrittenAvgfloat,labAvgfloatSELECTwrittenAvg=AVG(writtenExam),labAvg=AVG(labExam)FROMstuMarksprint笔试平均分：+convert(varchar(5),writtenAvg)print机试平均分：+convert(varchar(5),labAvg)IF(writtenAvg70ANDlabAvg70)print本班考试成绩：优秀ELSEprint本班考试成绩：较差print-print参加本次考试没有通过的学员：SELECTstuName,stuInfo.stuNo,writtenExam,labExamFROMstuInfoINNERJOINstuMarksONstuInfo.stuNo=stuMarks.stuNoWHEREwrittenExam60ORlabExam60GO,proc_stu为存储过程的名称,笔试平均分和机试平均分变量,显示考试成绩的等级,显示未通过的学员,调用存储过程,EXECUTE（执行）语句用来调用存储过程调用的语法EXEC过程名参数,EXECproc_stu,存储过程的参数分两种：输入参数输出参数输入参数：用于向存储过程传入值，类似C语言的按值传递;输出参数：用于在调用存储过程后，返回结果，类似C语言的按引用传递;,调用存储过程,intsum(inta,intb)ints;s=a+b;returns;,c=sum（5,8）,传入参数值,返回结果,传递参数的方式,使用参数位置形式：不显式指出参数名顺序要求：严格按照定义的顺序,使用参数名形式：参数名参数值顺序要求：可以不按顺序指定参数值,带输入参数的存储过程,问题：修改上例：由于每次考试的难易程度不一样，每次笔试和机试的及格线可能随时变化（不再是60分），这导致考试的评判结果也相应变化。,分析：在述存储过程添加2个输入参数：writtenPass笔试及格线labPass机试及格线,带输入参数的存储过程,CREATEPROCEDUREproc_stuwrittenPassint,labPassintASprint-print参加本次考试没有通过的学员：SELECTstuName,stuInfo.stuNo,writtenExam,labExamFROMstuInfoINNERJOINstuMarksONstuInfo.stuNo=stuMarks.stuNoWHEREwrittenExamwrittenPassORlabExamlabPassGO,输入参数：笔试及格线,输入参数：机试及格线,查询没有通过考试的学员,带输入参数的存储过程,EXECproc_stu60,55,调用带参数的存储过程假定本次考试机试偏难，机试的及格线定为55分，笔试及格线定为60分,-或这样调用：EXECproc_stulabPass=55,writtenPass=60,机试及格线降分后，李斯文（59分）成为“漏网之鱼”了,输入参数的默认值,带参数的存储过程确实比较方便，调用者可根据试卷的难易度，随时修改每次考试的及格线,问题：如果试卷的难易程度合适，则调用者还是必须如此调用：EXECproc_stu60,60，比较麻烦这样调用就比较合理：EXECproc_stu55EXECproc_stu,笔试及格线55分，机试及格线默认为60分,笔试和机试及格线都默认为标准的60分,CREATEPROCEDUREproc_stuwrittenPassint=60,labPassint=60ASprint-print参加本次考试没有通过的学员：SELECTstuName,stuInfo.stuNo,writtenExam,labExamFROMstuInfoINNERJOINstuMarksONstuInfo.stuNo=stuMarks.stuNoWHEREwrittenExamwrittenPassORlabExamlabPassGO,笔试及格线：默认为60分,机试及格线：默认为60分,查询没有通过考试的学员,输入参数的默认值,输入参数的默认值,EXECproc_stu-都采用默认值EXECproc_stu64-机试采用默认值EXECproc_stu60,55-都不采用默认值,调用带参数默认值的存储过程,-错误的调用方式：希望笔试采用默认值，机试及格线55分EXECproc_stu,55,-正确的调用方式：EXECproc_stulabPass=55,带输出参数的存储过程,如果希望调用存储过程后，返回一个或多个值，这时就需要使用输出（OUTPUT）参数了,问题：修改上例，返回未通过考试的学员人数。,CREATEPROCEDUREproc_stunotpassSumintOUTPUT,writtenPassint=60,labPassint=60ASSELECTstuName,stuInfo.stuNo,writtenExam,labExamFROMstuInfoINNERJOINstuMarksONstuInfo.stuNo=stuMarks.stuNoWHEREwrittenExamwrittenPassORlabExamlabPassSELECTnotpassSum=COUNT(stuNo)FROMstuMarksWHEREwrittenExamwrittenPassORlabExam=3print未通过人数：+convert(varchar(5),sum)+人,超过60%,及格分数线还应下调ELSEprint未通过人数：+convert(varchar(5),sum)+人,已控制在60%以下，及格分数线适中GO,调用带输出参数的存储过程,带输出参数的存储过程,调用时必须带OUTPUT关键字，返回结果将存放在变量sum中,后续语句引用返回结果,5.2.4存储过程的查看、修改和删除,查看存储过程方法1：可以通过系统存储过程来查看用户定义的存储过程。,Sp_helptext存储过程名,5查看、修改和删除存储过程,方法2：对象资源管理器中，找到所要查看的存储过程，单击鼠标右键，打开快捷菜单，选择“修改”菜单命令。,方法3：如果想查看存储过程的一般信息，可使用：Sp_help存储过程名,5.2.4存储过程的查看、修改和删除,修改存储过程（建议可视化操作完成）,在查询分析器中使用下列命令：ALTERPROCSCHEMA.存储过程名,可视化操作：选定存储过程，在其上打开快捷菜单，选择“修改”菜单命令，打开编辑窗口。修改完成后，再重新执行一次即可。,或,5.查看、修改和删除存储过程,5.查看、修改和删除存储过程,删除存储过程DROPPROCEDURE存储过程名,