第八讲存储过程

第八讲存储过程第一页，共二十一页，编辑于2023年，星期四2本章目标了解存储过程的优点掌握常用的系统存储过程掌握如何创建存储过程掌握如何调用存储过程第二页，共二十一页，编辑于2023年，星期四3存储过程（procedure）类似于C#语言中的函数用来执行管理任务或应用复杂的业务规则存储过程可以带参数，也可以返回结果intsum(inta,intb){ints;s=a+b;returns;}存储过程相当于C#语言中的函数什么是存储过程第三页，共二十一页，编辑于2023年，星期四4存储过程------------------------单个SELECT语句SELECT语句块SELECT语句与逻辑控制语句可以包含什么是存储过程存储过程可以包含数据操纵语句、变量、逻辑控制语句等第四页，共二十一页，编辑于2023年，星期四5执行速度更快允许模块化程序设计提高系统安全性减少网络流通量存储过程的优点第五页，共二十一页，编辑于2023年，星期四6系统存储过程由系统定义，存放在master数据库中类似C#语言中的系统函数系统存储过程的名称都以“sp_”开头或”xp_”开头用户自定义存储过程由用户在自己的数据库中创建的存储过程类似C#语言中的用户自定义函数存储过程的分类第六页，共二十一页，编辑于2023年，星期四7常用的系统存储过程系统存储过程说明sp_databases列出服务器上的所有数据库。sp_helpdb报告有关指定数据库或所有数据库的信息sp_renamedb更改数据库的名称sp_tables返回当前环境下可查询的对象的列表sp_columns回某个表列的信息sp_rename更改表名/视图名/存储过程名sp_help查看某个表的所有信息第七页，共二十一页，编辑于2023年，星期四8EXECsp_databasesEXECsp_renamedb'Northwind','Northwind1'USEstuDBGOEXECsp_tablesEXECsp_columnsstuInfoEXECsp_helpstuInfoEXECsp_helpconstraintstuInfoEXECsp_helpindexstuMarksEXECsp_helptext'view_stuInfo_stuMarks'EXECsp_stored_procedures

常用的系统存储过程修改数据库的名称(单用户访问)列出当前系统中的数据库当前数据库中查询的对象的列表返回某个表列的信息查看表stuInfo的信息查看表stuInfo的约束查看表stuMarks的索引查看视图的语句文本查看当前数据库中的存储过程演示：常用的存储过程第八页，共二十一页，编辑于2023年，星期四9定义存储过程的语法CREATEPROC[EDURE]存储过程名

@参数1数据类型=默认值OUTPUT,……,@参数n数据类型=默认值OUTPUTASSQL语句GO和C#语言的函数一样，参数可选参数分为输入参数、输出参数输入参数允许有默认值创建存储过程第九页，共二十一页，编辑于2023年，星期四创建不带参数的存储过程问题：请创建存储过程，查看本次考试平均分以及未通过考试的学员名单第十页，共二十一页，编辑于2023年，星期四11EXECUTE（执行）语句用来调用存储过程调用的语法

EXEC[UTE]

存储过程名[参数]

调用执行存储过程EXECproc_stu第十一页，共二十一页，编辑于2023年，星期四12创建带参数的存储过程存储过程的参数分两种：输入参数输出参数

intsum(inta,intb){ints;s=a+b;returns;}c=sum（5,8）传入参数值输入参数：用于向存储过程传入值，类似C#语言的按值传递;输出参数：用于在调用存储过程后，

返回结果，类似C#语言的按引用传递;

返回结果第十二页，共二十一页，编辑于2023年，星期四13带输入参数的存储过程问题：第十三页，共二十一页，编辑于2023年，星期四14带输入参数的存储过程EXECproc_stu60,55调用带参数的存储过程

--或这样调用：EXECproc_stu@labPass=55,@writtenPass=60第十四页，共二十一页，编辑于2023年，星期四15输入参数的默认值带参数的存储过程确实比较方便，调用者可根据试卷的难易度，随时修改每次考试的及格线问题：如果试卷的难易程度合适，则调用者还是必须如此调用：EXECproc_stu60,60，比较麻烦这样调用就比较合理：EXECproc_stu55EXECproc_stu

笔试及格线55分，机试及格线默认为60分笔试和机试及格线都默认为标准的60分第十五页，共二十一页，编辑于2023年，星期四16CREATEPROCEDUREproc_stu

@writtenPassint=60,

@labPassint=60

ASprint'--------------------------------------------------'print'参加本次考试没有通过的学员：'SELECTstuName,stuInfo.stuNo,writtenExam,labExamFROMstuInfoINNERJOINstuMarksONstuInfo.stuNo=stuMarks.stuNoWHEREwrittenExam<@writtenPass

ORlabExam<@labPass

GO笔试及格线：默认为60分机试及格线：默认为60分查询没有通过考试的学员输入参数的默认值第十六页，共二十一页，编辑于2023年，星期四17输入参数的默认值EXECproc_stu--都采用默认值EXECproc_stu64--机试采用默认值EXECproc_stu60,55--都不采用默认值调用带参数默认值的存储过程--错误的调用方式：希望笔试采用默认值，机试及格线55分EXECproc_stu,55--正确的调用方式：EXECproc_stu@labPass=55第十七页，共二十一页，编辑于2023年，星期四18带输出参数的存储过程如果希望调用存储过程后，返回一个或多个值，这时就需要使用输出（OUTPUT）参数了问题：第十八页，共二十一页，编辑于2023年，星期四19CREATEPROCEDUREproc_stu

@notpassSumintOUTPUT,@writtenPassint=60,@labPassint=60AS……SELECTstuName,stuInfo.stuNo,writtenExam,labExamFROMstuInfoINNERJOINstuMarksONstuInfo.stuNo=stuMarks.stuNoWHEREwrittenExam<@writtenPassORlabExam<@labPassSELECT@notpassSum=COUNT(stuNo)

FROMstuMarksWHEREwrittenExam<@writtenPassORlabExam<@labPassGO输出（返回）参数：表示没有通过的人数推荐将默认参数放后带输出参数的存储过程3-2统计并返回没有通过考试的学员人数第十九页，共二十一页，编辑于2023年，星期四20/*---调用存储过程----*/DECLARE@sumintEXECproc_stu@sumOUTPUT,64print'--------------------------------------------------'IF@sum>=3print'未通过人数：'+convert(varchar(5),@sum)+'人,超过60%,及格分数线还应下调'ELSEprint'未通过人数：'+convert(varchar(5),@sum)+'人,已控制在60%以下，及格分数线适中'GO调用带输出参数的存储过程带输出参数的存储过程3-3调用时必须带OUTPUT关键字，返回结果将存放在变量@sum中后续语句引