【教学课件】第七章存储过程.ppt

《【教学课件】第七章存储过程.ppt》由会员分享，可在线阅读，更多相关《【教学课件】第七章存储过程.ppt（30页珍藏版）》请在三一办公上搜索。

1、第七章存储过程,本章目标,了解存储过程的优点掌握常用的系统存储过程掌握如何创建存储过程掌握如何调用存储过程,存储过程（procedure）类似于C语言中的函数用来执行管理任务或应用复杂的业务规则存储过程可以带参数，也可以返回结果,int sum(int a,int b)int s;s=a+b;return s;,存储过程相当于C语言中的函数,什么是存储过程 2-1,存储过程-,单个 SELECT 语句,SELECT 语句块,SELECT语句与逻辑控制语句,可以包含,什么是存储过程 2-2,存储过程可以包含数据操纵语句、变量、逻辑 控制语句等,执行速度更快允许模块化程序设计 提高系统安全性减少网

2、络流通量,存储过程的优点,系统存储过程由系统定义，存放在master数据库中类似C语言中的系统函数系统存储过程的名称都以“sp_”开头或”xp_”开头用户自定义存储过程由用户在自己的数据库中创建的存储过程类似C语言中的用户自定义函数,存储过程的分类,常用的系统存储过程 4-1,EXEC sp_databasesEXEC sp_renamedb Northwind,Northwind1USE stuDBGOEXEC sp_tablesEXEC sp_columns stuInfo EXEC sp_help stuInfoEXEC sp_helpconstraint stuInfoEXEC sp_

3、helpindex stuMarksEXEC sp_helptext view_stuInfo_stuMarks EXEC sp_stored_procedures,常用的系统存储过程 4-2,修改数据库的名称(单用户访问),列出当前系统中的数据库,当前数据库中查询的对象的列表,返回某个表列的信息,查看表stuInfo的信息,查看表stuInfo的约束,查看表stuMarks的索引,查看视图的语句文本,查看当前数据库中的存储过程,演示：常用的存储过程,常用的扩展存储过程：xp_cmdshell 可以执行DOS命令下的一些的操作 以文本行方式返回任何输出 调用语法：EXEC xp_cmdshel

4、l DOS命令 NO_OUTPUT,常用的系统存储过程 4-3,常用的系统存储过程 4-4,USE masterGOEXEC xp_cmdshell mkdir d:bank,NO_OUTPUTIF EXISTS(SELECT*FROM sysdatabases WHERE name=bankDB)DROP DATABASE bankDBGOCREATE DATABASE bankDB()GOEXEC xp_cmdshell dir D:bank-查看文件,创建数据库bankDB，要求保存在D:bank,创建文件夹D:bank,查看文件夹D:bank,定义存储过程的语法 CREATE PROC

5、EDURE 存储过程名 参数1 数据类型=默认值 OUTPUT,参数n 数据类型=默认值 OUTPUT AS SQL语句 GO和C语言的函数一样，参数可选参数分为输入参数、输出参数 输入参数允许有默认值,如何创建存储过程,创建不带参数的存储过程 2-1,问题：请创建存储过程，查看本次考试平均分以及未通过考试的学员名单,创建不带参数的存储过程 2-2,CREATE PROCEDURE proc_stu AS DECLARE writtenAvg float,labAvg float SELECT writtenAvg=AVG(writtenExam),labAvg=AVG(labExam)FRO

6、M stuMarks print 笔试平均分：+convert(varchar(5),writtenAvg)print 机试平均分：+convert(varchar(5),labAvg)IF(writtenAvg70 AND labAvg70)print 本班考试成绩：优秀 ELSE print 本班考试成绩：较差 print-print 参加本次考试没有通过的学员：SELECT stuName,stuInfo.stuNo,writtenExam,labExam FROM stuInfo INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo W

7、HERE writtenExam60 OR labExam60 GO,proc_stu为存储过程的名称,笔试平均分和机试平均分变量,显示考试成绩的等级,显示未通过的学员,EXECUTE（执行）语句用来调用存储过程调用的语法EXEC 过程名 参数,调用存储过程,EXEC proc_stu,创建带参数的存储过程,存储过程的参数分两种：输入参数输出参数,int sum(int a,int b)int s;s=a+b;return s;,c=sum（5,8）,传入参数值,输入参数：用于向存储过程传入值，类似C语言的按值传递;,输出参数：用于在调用存储过程后，返回结果，类似C语言的 按引用传递;,返回结

8、果,带输入参数的存储过程3-1,问题：修改上例：由于每次考试的难易程度不一样，每次 笔试和机试的及格线可能随时变化（不再是60分），这导致考试的评判结果也相应变化。,分析：在述存储过程添加2个输入参数：writtenPass 笔试及格线 labPass 机试及格线,带输入参数的存储过程3-2,CREATE PROCEDURE proc_stu writtenPass int,labPass int AS print-print 参加本次考试没有通过的学员：SELECT stuName,stuInfo.stuNo,writtenExam,labExam FROM stuInfo INNER JO

9、IN stuMarks ON stuInfo.stuNo=stuMarks.stuNo WHERE writtenExamwrittenPass OR labExamlabPass GO,输入参数：笔试及格线,输入参数：机试及格线,查询没有通过考试的学员,带输入参数的存储过程3-3,EXEC proc_stu 60,55,调用带参数的存储过程 假定本次考试机试偏难，机试的及格线定为55分，笔试及格线定为60分,-或这样调用：EXEC proc_stu labPass=55,writtenPass=60,机试及格线降分后，李斯文（59分）成为“漏网之鱼”了,输入参数的默认值3-1,带参数的存储过

10、程确实比较方便，调用者可根据试卷的难易度，随时修改每次考试的及格线,问题：如果试卷的难易程度合适，则调用者还是必须如此调用：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,stuInfo.stuNo,writtenExam,labE

11、xam FROM stuInfo INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo WHERE writtenExamwrittenPass OR labExamlabPass GO,笔试及格线：默认为60分,机试及格线：默认为60分,查询没有通过考试的学员,输入参数的默认值3-2,输入参数的默认值3-3,EXEC proc_stu-都采用默认值 EXEC proc_stu 64-机试采用默认值 EXEC proc_stu 60,55-都不采用默认值,调用带参数默认值的存储过程,-错误的调用方式：希望笔试采用默认值，机试及格线55分EXEC

12、 proc_stu,55,-正确的调用方式：EXEC proc_stu labPass=55,带输出参数的存储过程 3-1,如果希望调用存储过程后，返回一个或多个值，这时就需要使用输出（OUTPUT）参数了,问题：修改上例，返回未通过考试的学员人数。,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 stuI

13、nfo.stuNo=stuMarks.stuNo WHERE writtenExamwrittenPass OR labExamlabPass SELECT notpassSum=COUNT(stuNo)FROM stuMarks WHERE writtenExamwrittenPass OR labExamlabPass GO,输出（返回）参数：表示没有通过的人数,推荐将默认参数放后,带输出参数的存储过程 3-2,统计并返回没有通过考试的学员人数,/*-调用存储过程-*/DECLARE sum int EXEC proc_stu sum OUTPUT,64 print-IF sum=3 pr

14、int 未通过人数：+convert(varchar(5),sum)+人,超过60%,及格分数线还应下调ELSE print 未通过人数：+convert(varchar(5),sum)+人,已控制在60%以下，及格分数线适中GO,调用带输出参数的存储过程,带输出参数的存储过程 3-3,调用时必须带OUTPUT关键字，返回结果将存放在变量sum中,后续语句引用返回结果,可以使用PRINT语句显示错误信息，但这 些信息是临时的，只能显示给用户 RAISERROR 显示用户定义的错误信息时可指定严重级别，设置系统变量ERROR记录所发生的错误等,处理存储过程中的错误,使用RAISERROR 语句4

15、-1,RAISERROR(msg_id|msg_str,severity,state WITH option,.n),RAISERROR语句的用法如下：,msg_id：在sysmessages系统表中指定用户定义错误信息msg_str：用户定义的特定信息，最长255个字符severity：定义严重性级别。用户可使用的级别为018级state：表示错误的状态，1至127之间的值option：指示是否将错误记录到服务器错误日志中,问题：完善上例，当用户调用存储过程时，传入的及格线参数不在0100之间时，将弹出错误警告，终止存储过程的执行。,使用RAISERROR 语句4-2,CREATE PROC

16、EDURE proc_stu notpassSum int OUTPUT,-输出参数 writtenPass int=60,-默认参数放后 labPass int=60-默认参数放后 AS IF(NOT writtenPass BETWEEN 0 AND 100)OR(NOT labPass BETWEEN 0 AND 100)BEGIN RAISERROR(及格线错误，请指定0100之间的分 数，统计中断退出,16,1)RETURN-立即返回，退出存储过程 END.其他语句同上例，略GO,错误处理,引发系统错误，指定错误的严重级别16，调用状态为1（默认）,并影响ERROR系统变量的值,使用

17、RAISERROR 语句4-3,使用RAISERROR 语句4-4,/*-调用存储过程，测试RAISERROR语句-*/DECLARE sum int,t intEXEC proc_stu sum OUTPUT,604 SET t=ERROR print 错误号：+convert(varchar(5),t)IF t0 RETURN-退出批处理，后续语句不再执行print-IF sum=3 print 未通过人数：+convert(varchar(5),sum)+人,超过60%,及格分数线还应下调ELSE print 未通过人数：+convert(varchar(5),sum)+人,已控制在60%以下，及格分数线适中GO,笔试及格线误输入604分,如果执行了RAISERROR语句，系统全局ERROR将不等于0，表示出现了错误,总结,存储过程是一组预编译的SQL语句，它可以包含数据操纵语句、变量、逻辑控制语句等 存储过程允许带参数，参数分为：输入参数输出参数 其中，输入参数可以有默认值。输入参数：可以在调用时向存储过程传递参数，此类参数可用来向存储过程中传入值 输出参数从存储过程中返回（输出）值，后面跟随OUTPUT关键字RAISERROR语句用来向用户报告错误,