SQL教学课程-存储过程.ppt

第六章,存储过程,回顾,事务具有ACID四个属性，它们是？管理控制事务的常用T-SQL语句有哪些？索引分为哪几类？根据您的理解，什么是视图？它有什么好处？您在stuInfo表中创建了主键列stuNo，SQLServer将自动创建哪些索引？,本章目标,了解存储过程的优点掌握常用的系统存储过程掌握如何创建存储过程掌握如何调用存储过程,存储过程（procedure）类似于C语言中的函数用来执行管理任务或应用复杂的业务规则存储过程可以带参数，也可以返回结果,intsum(inta,intb)ints;s=a+b;returns;,存储过程相当于C语言中的函数,什么是存储过程2-1,存储过程-,单个SELECT语句,SELECT语句块,SELECT语句与逻辑控制语句,可以包含,什么是存储过程2-2,存储过程可以包含数据操纵语句、变量、逻辑控制语句等,系统存储过程由系统定义，存放在master数据库中类似C语言中的系统函数系统存储过程的名称都以“sp_”开头或”xp_”开头返回0(成功),1(失败)用户自定义存储过程由用户在自己的数据库中创建的存储过程类似C语言中的用户自定义函数,存储过程的分类,常用的系统存储过程4-1,EXECsp_databasesEXECsp_renamedbNorthwind,Northwind1USEstuDBGOEXECsp_tablesEXECsp_columnsstuInfoEXECsp_helpstuInfoEXECsp_helpconstraintstuInfoEXECsp_helpindexstuMarksEXECsp_helptextview_stuInfo_stuMarksEXECsp_stored_procedures,常用的系统存储过程4-2,修改数据库的名称(单用户访问),列出当前系统中的数据库,当前数据库中查询的对象的列表,返回某个表列的信息,查看表stuInfo的信息,查看表stuInfo的约束,查看表stuMarks的索引,查看视图的语句文本,查看当前数据库中的存储过程,演示：常用的存储过程,常用的扩展存储过程：xp_cmdshell可以执行DOS命令下的一些的操作以文本行方式返回任何输出调用语法：EXECxp_cmdshellDOS命令NO_OUTPUT,常用的系统存储过程4-3,常用的系统存储过程4-4,USEmasterGOEXECxp_cmdshellmkdird:bank,NO_OUTPUTIFEXISTS(SELECT*FROMsysdatabasesWHEREname=bankDB)DROPDATABASEbankDBGOCREATEDATABASEbankDB()GOEXECxp_cmdshelldirD:bank-查看文件,创建数据库bankDB，要求保存在D:bank,创建文件夹D:bank,查看文件夹D:bank,定义存储过程的语法CREATEPROCEDURE存储过程名参数1数据类型=默认值OUTPUT,参数n数据类型=默认值OUTPUTASSQL语句GO和C语言的函数一样，参数可选参数分为输入参数、输出参数输入参数允许有默认值,如何创建存储过程,创建不带参数的存储过程2-1,问题：请创建存储过程，查看本次考试平均分以及未通过考试的学员名单,创建不带参数的存储过程2-2,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,创建带参数的存储过程,存储过程的参数分两种：输入参数输出参数,intsum(inta,intb)ints;s=a+b;returns;,c=sum（5,8）,传入参数值,输入参数：用于向存储过程传入值，类似C语言的按值传递;,输出参数：用于在调用存储过程后，返回结果，类似C语言的按引用传递;,返回结果,带输入参数的存储过程3-1,问题：修改上例：由于每次考试的难易程度不一样，每次笔试和机试的及格线可能随时变化（不再是60分），这导致考试的评判结果也相应变化。,分析：在述存储过程添加2个输入参数：writtenPass笔试及格线labPass机试及格线,带输入参数的存储过程3-2,CREATEPROCEDUREproc_stuwrittenPassint,labPassintASprint-print参加本次考试没有通过的学员：SELECTstuName,stuInfo.stuNo,writtenExam,labExamFROMstuInfoINNERJOINstuMarksONstuInfo.stuNo=stuMarks.stuNoWHEREwrittenExamwrittenPassORlabExamlabPassGO,输入参数：笔试及格线,输入参数：机试及格线,查询没有通过考试的学员,带输入参数的存储过程3-3,EXECproc_stu60,55,调用带参数的存储过程假定本次考试机试偏难，机试的及格线定为55分，笔试及格线定为60分,-或这样调用：EXECproc_stulabPass=55,writtenPass=60,机试及格线降分后，李斯文（59分）成为“漏网之鱼”了,输入参数的默认值3-1,带参数的存储过程确实比较方便，调用者可根据试卷的难易度，随时修改每次考试的及格线,问题：如果试卷的难易程度合适，则调用者还是必须如此调用：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分,查询没有通过考试的学员,输入参数的默认值3-2,输入参数的默认值3-3,EXECproc_stu-都采用默认值EXECproc_stu64-机试采用默认值EXECproc_stu60,55-都不采用默认值,调用带参数默认值的存储过程,-错误的调用方式：希望笔试采用默认值，机试及格线55分EXECproc_stu,55,-正确的调用方式：EXECproc_stulabPass=55,带输出参数的存储过程3-1,如果希望调用存储过程后，返回一个或多个值，这时就需要使用输出（OUTPUT）参数了,问题：修改上例，返回未通过考试的学员人数。,CREATEPROCEDUREproc_stunotpassSumintOUTPUT,writtenPassint=60,labPassint=60ASSELECTstuName,stuInfo.stuNo,writtenExam,labExamFROMstuInfoINNERJOINstuMarksONstuInfo.stuNo=stuMarks.stuNoWHEREwrittenExamwrittenPassORlabExamlabPassSELECTnotpassSum=COUNT(stuNo)FROMstuMarksWHEREwrittenExam=3print未通过人数：+convert(varchar(5),sum)+人,超过60%,及格分数线还应下调ELSEprint未通过人数：+convert(varchar(5),sum)+人,已控制在60%以下，及格分数线适中GO,笔试及格线误输入604分,如果执行了RAISERROR语句，系统全局ERROR将不等于0，表示出现了错误,执行速度更快允