SQL教学课程存储过程公开课获奖课件.pptx

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,第六章,寄存过程,第1页,回忆,事务具有ACID四个属性，它们是？,管理控制事务常用T-SQL语句有哪些？,索引分为哪几类？,根据您理解，什么是视图？它有什么好处？,您在stuInfo表中创立了主键列stuNo，SQL Server将自动创立哪些索引？,第2页,本章目旳,理解寄存过程长处,掌握常用系统寄存过程,掌握怎样创立寄存过程,掌握怎样调用寄存过程,第3页,寄存过程（procedure）类似于C语言中函数,用来执行管理任务或应用复杂业务规则,寄存过程可以带参数，也可以返回成果,int sum(int a,int b),int s;,s=a+b;,return s;,寄存过程相称于C语言中函数,什么是寄存过程 2-1,第4页,寄存过程,-,-,-,单个 SELECT 语句,SELECT 语句块,SELECT语句与逻辑控制语句,可以包括,什么是寄存过程 2-2,寄存过程可以包括数据操纵语句、变量、逻辑 控制语句等,第5页,系统寄存过程,由系统定义，寄存在master数据库中,类似C语言中系统函数,系统寄存过程名称都以“sp_”开头或”xp_”开头,返回0(成功),1(失败),顾客自定义寄存过程,由顾客在自己数据库中创立寄存过程,类似C语言中顾客自定义函数,寄存过程分类,第6页,常用系统寄存过程 4-1,系统存放过程,说明,sp_databases,列出服务器上全部数据库。,sp_helpdb,汇报相关指定数据库或全部数据库信息,sp_renamedb,s,p_rename,更改数据库名称,在当前数据库中更改用户创建对象名称。,sp_tables,返回当前环境下可查询对象列表,sp_columns,回某个表列信息,sp_help,查看某个表全部信息,sp_helpconstraint,查看某个表约束,sp_helpindex,查看某个表索引,sp_stored_procedures,列出当前环境中全部存放过程。,sp_password,添加或修改登录帐户密码。,sp_helptext,显示默认值、未加密存放过程、用户定义存放过程、触发器或视图实际文本。,第7页,EXEC sp_databases,EXEC,sp_renamedb,Northwind,Northwind1,USE stuDB,GO,EXEC,sp_tables,EXEC,sp_columns,stuInfo,EXEC,sp_help,stuInfo,EXEC,sp_helpconstraint,stuInfo,EXEC,sp_helpindex,stuMarks,EXEC,sp_helptext,view_stuInfo_stuMarks,EXEC,sp_stored_procedures,常用系统寄存过程 4-2,修改数据库名称(单顾客访问),列出目前系统中数据库,目前数据库中查询对象列表,返回某个表列信息,查看表stuInfo信息,查看表stuInfo约束,查看表stuMarks索引,查看视图语句文本,查看目前数据库中寄存过程,演示：常用寄存过程,第8页,常用扩展寄存过程：xp_cmdshell,可以执行DOS命令下某些操作,以文本行方式返回任何输出,调用语法：,EXEC xp_cmdshell DOS命令 NO_OUTPUT,常用系统寄存过程 4-3,第9页,常用系统寄存过程 4-4,USE master,GO,EXEC xp_cmdshell mkdir d:bank,NO_OUTPUT,IF EXISTS(SELECT*FROM sysdatabases,WHERE name=bankDB),DROP DATABASE bankDB,GO,CREATE DATABASE bankDB,(,),GO,EXEC xp_cmdshell dir D:bank-查看文献,创立数据库bankDB，规定保留在D:bank,创立文献夹D:bank,查看文献夹D:bank,第10页,定义寄存过程语法,CREATE PROCEDURE 寄存过程名,参数1 数据类型=默认值 OUTPUT,参数n 数据类型=默认值 OUTPUT,AS,SQL语句,GO,和C语言函数同样，参数可选,参数分为输入参数、输出参数,输入参数容许有默认值,怎样创立寄存过程,第11页,创立不带参数寄存过程 2-1,问题：请创立寄存过程，查看本次考试平均分以及未通过考试学员名单,第12页,创立不带参数寄存过程 2-2,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 本班考试成绩：优秀,ELSE,print 本班考试成绩：较差,print-,print 参与本次考试没有通过学员：,SELECT stuName,stuInfo.stuNo,writtenExam,labExam,FROM stuInfo INNER JOIN stuMarks ON,stuInfo.stuNo=stuMarks.stuNo,WHERE writtenExam60 OR labExam60,GO,proc_stu为寄存过程名称,笔试平均分和机试平均分变量,显示考试成绩等级,显示未通过学员,第13页,EXECUTE（执行）语句用来调用寄存过程,调用语法,EXEC 过程名 参数,调用寄存过程,EXEC proc_stu,第14页,创立带参数寄存过程,寄存过程参数分两种：,输入参数,输出参数,int sum(int a,int b),int s;,s=a+b;,return s;,c=sum（5,8）,传入参数值,输入参数：,用于向寄存过程传入值，类似C语言按值传递;,输出参数：,用于在调用寄存过程后，,返回成果，类似C语言,按引用传递;,返回成果,第15页,带输入参数寄存过程3-1,问题：,修改上例：由于每次考试难易程度不一样样，每次 笔试和机试及格线也许随时变化（不再是60分），这导致考试评判成果也对应变化。,分析：,在述寄存过程添加2个输入参数：,writtenPass 笔试及格线,labPass 机试及格线,第16页,带输入参数寄存过程3-2,CREATE PROCEDURE proc_stu,writtenPass int,labPass int,AS,print-,print 参与本次考试没有通过学员：,SELECT stuName,stuInfo.stuNo,writtenExam,labExam FROM stuInfo,INNER JOIN stuMarks ON,stuInfo.stuNo=stuMarks.stuNo,WHERE writtenExamwrittenPass,OR labExamlabPass,GO,输入参数：笔试及格线,输入参数：机试及格线,查询没有通过考试学员,第17页,带输入参数寄存过程3-3,EXEC proc_stu 60,55,调用带参数寄存过程,假定本次考试机试偏难，机试及格线定为55分，笔试及格线定为60分,-或这样调用：,EXEC proc_stu labPass=55,writtenPass=60,机试及格线降分后，,李斯文,（59分）成为“漏网之鱼”了,第18页,输入参数默认值3-1,带参数寄存过程确实比较以便，调用者可根据试卷难易度，随时修改每次考试及格线,问题：,假如试卷难易程度合适，则调用者还是必须,如此调用：EXEC proc_stu 60,60，比较麻烦,这样调用就比较合理：,EXEC proc_stu 55,EXEC proc_stu,笔试及格线55分，机试及格线默认为60分,笔试和机试及格线都默认为原则60分,第19页,CREATE PROCEDURE proc_stu,writtenPass int=60,labPass int=60,AS,print-,print 参与本次考试没有通过学员：,SELECT stuName,stuInfo.stuNo,writtenExam,labExam FROM stuInfo,INNER JOIN stuMarks ON,stuInfo.stuNo=stuMarks.stuNo,WHERE writtenExamwrittenPass,OR labExamlabPass,GO,笔试及格线：默认为60分,机试及格线：默认为60分,查询没有通过考试学员,输入参数默认值3-2,第20页,输入参数默认值3-3,EXEC proc_stu -都采用默认值,EXEC proc_stu 64 -机试采用默认值,EXEC proc_stu 60,55 -都不采用默认值,调用带参数默认值寄存过程,-错误调用方式：但愿笔试采用默认值，机试及格线55分,EXEC proc_stu ,55,-对旳调用方式：,EXEC proc_stu labPass=55,第21页,带输出参数寄存过程 3-1,假如但愿调用寄存过程后，返回一种或多种值，这时就需要使用输出（OUTPUT）参数了,问题：,修改上例，返回未通过考试学员人数。,第22页,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 writtenExamwrittenPass,OR labExam=3,print 未通过人数：+convert(varchar(5),sum)+人,超过60%,及格分数线还应下调,ELSE,print 未通过人数：+convert(varchar(5),sum)+人,已控制在60%如下，及格分数线适中,GO,调用带输出参数寄存过程,带输出参数寄存过程 3-3,调用时必须带OUTPUT关键字，返回成果将寄存在变量sum中,后续语句引用返回成果,第24页,可以使用PRINT语句显示错误信息，但这 些信息是临时，只能显示给顾客,RAISERROR 显示顾客定义错误信息时,可指定严重级别，,设置系统变量ERROR,记录所发生错误等,处理寄存过程中错误,第25页,使用RAISERROR 语句4-1,RAISERROR(msg_id|msg_str,severity,state WITH option,.n),RAISERROR语句使用措施如下：,msg_id：在sysmessages系统表中指定顾客定义错误信息,msg_str：顾客定义特定信息，最长255个字符,severity：定义严重性级别。顾客可使用级别为018级,state：表达错误状态，1至127之间值,option：指示与否将错误记录到服务器错误日志中,第26页,问题：,完善上例，当顾客调用寄存过程时，传入及格线参数不,在0100之间时，将弹出错误警告，终止寄存过程执行。,使用RAISERROR 语句4-2,第27页,CREATE PROCEDURE 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系统变量值,使用RAISERROR 语句4-3,第28页,使用RAISERROR 语句4-4,/*-调用寄存过程，测试RAISERROR语句-*/,DECLARE sum int,t int,EXEC 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，表达出现了错误,第29页,执行速度愈加紧,容许模块化程序设计,提高系统安全性,减少网络流通量,寄存过程长处,第30页,总结,寄存过程是一组预编译SQL语句，它可以包括数据操纵语句、变量、逻辑控制语句等,寄存过程容许带参数，参数分为：,输入参数,输出参数,其中，输入参数可以有默认值。,输入参数：可以在调用时向寄存过程传递参数，此类参数可用来向寄存过程中传入值,输出参数从寄存过程中返回（输出）值，背面跟随OUTPUT关键字,RAISERROR语句用来向顾客汇报错误,第31页,