Sql Server 2008 学习讲义 课件 12 PPT 存储过程

存储过程2回顾事务具有ACID四个属性，它们是？管理控制事务的常用T-SQL语句有哪些？索引分为哪几类？您在stuInfo表中创建了主键列stuNo，SQLServer将自动创建哪些索引？3本章目标了解存储过程的优点掌握常用的系统存储过程掌握如何创建存储过程掌握如何调用存储过程4存储过程（procedure）类似于C语言中的函数用来执行管理任务或应用复杂的业务规则存储过程可以带参数，也可以返回结果intsum(inta,intb){ints;s=a+b;returns;}存储过程相当于C语言中的函数什么是存储过程5存储过程------------------------单个SELECT语句SELECT语句块SELECT语句与逻辑控制语句可以包含什么是存储过程存储过程可以包含数据操纵语句、变量、逻辑控制语句等6存储过程的优点执行速度更快允许模块化程序设计提高系统安全性减少网络流通量7存储过程的分类系统存储过程由系统定义，存放在master数据库中类似C语言中的系统函数系统存储过程的名称都以“sp_”开头或”xp_”开头用户自定义存储过程由用户在自己的数据库中创建的存储过程类似C语言中的用户自定义函数8常用的系统存储过程系统存储过程说明sp_databases列出服务器上的所有数据库。sp_helpdb报告有关指定数据库或所有数据库的信息sp_renamedb更改数据库的名称sp_tables返回当前环境下可查询的对象的列表sp_columns回某个表列的信息sp_help查看某个表的所有信息sp_helpconstraint查看某个表的约束sp_helpindex查看某个表的索引sp_stored_procedures列出当前环境中的所有存储过程。sp_password添加或修改登录帐户的密码。sp_helptext显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本。9EXECsp_databasesEXECsp_renamedb'Northwind','Northwind1'USEstuDBGOEXECsp_tablesEXECsp_columnsstuInfoEXECsp_helpstuInfoEXECsp_helpconstraintstuInfoEXECsp_helpindexstuMarksEXECsp_helptext'view_stuInfo_stuMarks'EXECsp_stored_procedures常用的系统存储过程修改数据库的名称(单用户访问)列出当前系统中的数据库当前数据库中查询的对象的列表返回某个表列的信息查看表stuInfo的信息查看表stuInfo的约束查看表stuMarks的索引查看视图的语句文本查看当前数据库中的存储过程演示：常用的存储过程10如何创建存储过程定义存储过程的语法CREATEPROC[EDURE]存储过程名@参数1数据类型=默认值OUTPUT,……,@参数n数据类型=默认值OUTPUTASSQL语句GO和C语言的函数一样，参数可选参数分为输入参数、输出参数输入参数允许有默认值11创建不带参数的存储过程问题：请创建存储过程，查看本次考试平均分以及未通过考试的学员名单12创建不带参数的存储过程CREATEPROCEDUREproc_stuASDECLARE@writtenAvgfloat,@labAvgfloatSELECT@writtenAvg=AVG(cSharpExam),@labAvg=AVG(SQLExam)FROMstuMarksprint'C#平均分：'+convert(varchar(5),@writtenAvg)print'SQL平均分：'+convert(varchar(5),@labAvg)IF(@writtenAvg70AND@labAvg70)print'本班考试成绩：优秀'ELSEprint'本班考试成绩：较差'print'--------------------------------------------------'print'参加本次考试没有通过的学员：'SELECTstuName,stuInfo.stuNo,cSharpExam,SQLExamFROMstuInfoLeftJOINstuMarksONstuInfo.stuNo=stuMarks.stuNoWHEREcSharpExam60ORSQLExam60GOproc_stu为存储过程的名称C#平均分和SQL平均分变量显示考试成绩的等级显示未通过的学员13调用存储过程EXECUTE（执行）语句用来调用存储过程调用的语法EXEC过程名[参数]EXECproc_stu14存储过程的参数分两种：输入参数输出参数输入参数：用于向存储过程传入值输出参数：用于在调用存储过程后，返回结果带参数存储过程15带输入参数的存储过程问题：修改上例：由于每次考试的难易程度不一样，每次C#和SQL的及格线可能随时变化（不再是60分），这导致考试的评判结果也相应变化。分析：在述存储过程添加2个输入参数：@SQLPassC#及格线@cSharpPassSQL及格线16带输入参数的存储过程CREATEPROCEDUREproc_stu@SQLPassint,@cSharpPassintASprint'--------------------------------------------------'print'参加本次考试没有通过的学员：'SELECTstuName,stuInfo.stuNo,cSharpExam,SQLExamFROMstuInfoLEFTJOINstuMarksONstuInfo.stuNo=stuMarks.stuNoWHEREcSharpExam@SQLPassORSQLExam@cSharpPassGO输入参数：C#及格线输入参数：SQL及格线查询没有通过考试的学员17带输入参数的存储过程EXECproc_stu60,55调用带参数的存储过程假定本次考试SQL偏难，SQL的及格线定为55分，C#及格线定为60分--或这样调用：EXECproc_stu@cSharpPass=55,@SQLPass=6018输入参数的默认值带参数的存储过程确实比较方便，调用者可根据试卷的难易度，随时修改每次考试的及格线问题：如果试卷的难易程度合适，则调用者还是必须如此调用：EXECproc_stu60,60，比较麻烦这样调用就比较合理：EXECproc_stu55EXECproc_stuC#及格线55分，SQL及格线默认为60分C#和SQL及格线都默认为标准的60分19CREATEPROCEDUREproc_stu@SQLPassint=60,@cSharpPassint=60ASprint'--------------------------------------------------'print'参加本次考试没有通过的学员：'SELECTstuName,stuInfo.stuNo,cSharpExam,SQLExamFROMstuInfoLEFTJOINstuMarksONstuInfo.stuNo=stuMarks.stuNoWHEREcSharpExam@SQLPassORSQLExam@cSharpPassGOC#及格线：默认为60分SQL及格线：默认为60分查询没有通过考试的学员输入参数的默认值20输入参数的默认值EXECproc_stu--都采用默认值EXECproc_stu64--SQL采用默认值EXECproc_stu60,55--都不采用默认值调用带参数默认值的存储过程--错误的调用方式：希望C#采用默认值，SQL及格线55分EXECproc_stu,55--正确的调用方式：EXECproc_stu@cSharpPass=5521带输出参数的存储过程如果希望调用存储过程后，返回一个或多个值，这时就需要使用输出（OUTPUT）参数了问题：修改上例，返回未通过考试的学员人数。22CREATEPROCEDUREproc_stu@notpassSumintOUTPUT,@SQLPassint=60,@cSharpPassint=60AS……SELECTstuName,stuInfo.stuNo,cSharpExam,SQLExamFROMstuInfoINNERJOINstuMarksONstuInfo.stuNo=stuMarks.stuNoWHEREcSharpExam@SQLPassORSQLExam@cSharpPassSELECT@notpassSum=COUNT(stuNo)FROMstuMarksWHEREcSharpExam@SQLPassORSQLExam@cSharpPassGO输出（返回）参数：表示没有通过的人数推荐将默认参数放后带输出参数的存储过程统计并返回没有通过考试的学员人数23/*---调用存储过程----*/DECLARE@sumintEXECproc_stu@sumOUTPUT,64print'--------------------------------------------------'IF@sum=3print'未通过人数：'+convert(varchar(5),@sum)+'人,超过60%,及格分数线还应下调'ELSEprint'未通过人数：'+convert(varchar(5),@sum)+'人,已控制在60%以下，及格分数线适中'GO调用带输出参数的存储过程带输出参数的存储过程调用时必须带OUTPUT关键字，返回结果将存放在变量@sum中后续语句引用返回结果24处理存储过程中的错误可以使用PRINT语句显示错误信息，但这些信息是临时的，只能显示给用户RAISERROR显示用户定义的错误信息时可指定严重级别，设置系统变量@@ERROR记录所发生的错误等25使用RAISERROR语句RAISERROR(msg_id|msg_str,severity,stateWITHoption[,...n]])RAISERROR语句的用法如下：msg_id：在sysmessages系统表中指定用户定义错误信息msg_str：用户定义的特定信息，最长255个字符severity：定义严重性级别。用户可使用的级别为0–18级state：表示错误的状态，1至127之间的值option：指示是否将错误记录到服务器错误日志中26问题：完善上例，当用户调用存储过程时，传入的及格线参数不在0～100之间时，将弹出错误警告，终止存储过程的执行。使用RAISERROR语句27CREATEPROCEDUREproc_stu@notpassSumintOUTPUT,--输出参数@SQLPassint=60,--默认参数放后@cSharpPassint=60--默认参数放后ASIF(NOT@SQLPassBETWEEN0AND100)OR(NOT@cSharpPassBETWEEN0AND100)BEGINRAISERROR(‘及格线错误，请指定0－100之间的分数，统计中断退出',16,1)RETURN---立即返回，退出存储过程END…..其他语句同上例，略GO错误处理引发系统错误，指定错误的严重级别16，调用状态为1（默认）,并影响@@ERROR系统变量的值使用RAISERROR语句28使用RAISERROR语句/*---调用存储过程，测试RAISERROR语句----*/DECLARE@sumint,@tintEXECproc_stu@sumOUTPUT,604SET@t=@@ERRORprint'错误号：'+convert(varchar(5),@t)IF@t0RETURN--退出批处理，后续语句不再执行print'--------------------------------------------------