SQL教学课程存储过程省公共课一等奖全国赛课获奖课件.pptx

第六章存放过程第1页回顾回顾事务含有ACID四个属性，它们是？管理控制事务惯用T-SQL语句有哪些？索引分为哪几类？依据您了解，什么是视图？它有什么好处？您在stuInfo表中创建了主键列stuNo，SQL Server将自动创建哪些索引？第2页本章目标本章目标了解存放过程优点掌握惯用系统存放过程掌握怎样创建存放过程掌握怎样调用存放过程第3页q存放过程（procedure）类似于C语言中函数q用来执行管理任务或应用复杂业务规则q存放过程能够带参数，也能够返回结果int sum(int a,int b)int s;s=a+b;return s;存放过程相当于C语言中函数什么是存放过程什么是存放过程 2-1第4页存放过程-单个 SELECT 语句SELECT 语句块SELECT语句与逻辑控制语句能够包含能够包含什么是存放过程什么是存放过程 2-2q存放过程能够包含数据操纵语句、变量、逻辑 控制语句等第5页q系统存放过程q由系统定义，存放在master数据库中q类似C语言中系统函数q系统存放过程名称都以“sp_”开头或”xp_”开头q返回0(成功),1(失败)q用户自定义存放过程q由用户在自己数据库中创建存放过程q类似C语言中用户自定义函数存放过程分类存放过程分类 第6页惯用系统存放过程惯用系统存放过程 4-1系统存放过程说明sp_databases列出服务器上全部数据库。sp_helpdb汇报相关指定数据库或全部数据库信息sp_renamedbsp_rename更改数据库名称在当前数据库中更改用户创建对象名称。sp_tables返回当前环境下可查询对象列表sp_columns回某个表列信息sp_help查看某个表全部信息sp_helpconstraint查看某个表约束sp_helpindex查看某个表索引sp_stored_procedures列出当前环境中全部存放过程。sp_password添加或修改登录帐户密码。sp_helptext显示默认值、未加密存放过程、用户定义存放过程、触发器或视图实际文本。第7页EXEC sp_databasesEXEC sp_renamedb Northwind,Northwind1USE stuDBGOEXEC sp_tablesEXEC sp_columns stuInfo EXEC sp_help stuInfoEXEC sp_helpconstraint stuInfoEXEC sp_helpindex stuMarksEXEC sp_helptext view_stuInfo_stuMarks EXEC sp_stored_procedures 惯用系统存放过程惯用系统存放过程 4-2修改数据库名称(单用户访问)列出当前系统中数据库当前数据库中查询对象列表返回某个表列信息查看表stuInfo信息查看表stuInfo约束查看表stuMarks索引查看视图语句文本查看当前数据库中存放过程演示：惯用存放过程第8页q惯用扩展存放过程：xp_cmdshell q能够执行DOS命令下一些操作 q以文本行方式返回任何输出 q调用语法：q EXEC xp_cmdshell DOS命令 NO_OUTPUT惯用系统存放过程惯用系统存放过程 4-3第9页惯用系统存放过程惯用系统存放过程 4-4USE 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-查看文件q创建数据库bankDB，要求保留在D:bank创建文件夹D:bank查看文件夹D:bank第10页q定义存放过程语法 CREATE PROCEDURE 存放过程名存放过程名 参数参数1 数据类型数据类型=默认值默认值 OUTPUT,参数参数n 数据类型数据类型=默认值默认值 OUTPUT AS SQL语句语句 GOq和C语言函数一样，参数可选q参数分为输入参数、输出参数 q输入参数允许有默认值怎样创建存放过程怎样创建存放过程第11页创建不带参数存放过程创建不带参数存放过程 2-1q问题问题：请创建存放过程，查看此次考试平均分以及未经过考试学员名单第12页创建不带参数存放过程创建不带参数存放过程 2-2CREATE 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 GOproc_stu为存放过程名称笔试平均分和机试平均分变量 显示考试成绩等级 显示未经过学员 第13页qEXECUTE（执行）语句用来调用存放过程q调用语法EXEC 过程名 参数 调用存放过程调用存放过程EXEC proc_stu第14页创建带参数存放过程创建带参数存放过程q存放过程参数分两种：q输入参数q输出参数int sum(int a,int b)int s;s=a+b;return s;c=sum（5,8）传入参数值q输入参数：用于向存放过程传入值，类似C语言按值传递;q 输出参数：用于在调用存放过程后，返回结果，类似C语言 按引用传递;返回结果第15页带输入参数存放过程带输入参数存放过程3-1问题：问题：修改上例：因为每次考试难易程度不一样，每次 笔试和机试及格线可能随时改变（不再是60分），这造成考试评判结果也对应改变。分析：分析：在述存放过程添加2个输入参数：writtenPass 笔试及格线 labPass 机试及格线 第16页带输入参数存放过程带输入参数存放过程3-2CREATE 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-3EXEC proc_stu 60,55 q调用带参数存放过程 假定此次考试机试偏难，机试及格线定为55分，笔试及格线定为60分-或这么调用：EXEC proc_stu labPass=55,writtenPass=60机试及格线降分后，李斯文（59分）成为“漏网之鱼”了第18页输入参数默认值输入参数默认值3-1带参数存放过程确实比较方便，调用者可依据试卷难易度，随时修改每次考试及格线问题：问题：假如试卷难易程度适当，则调用者还是必须如此调用：EXEC proc_stu 60,60，比较麻烦这么调用就比较合理：EXEC proc_stu 55EXEC 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-3EXEC proc_stu -都采取默认值 EXEC proc_stu 64 -机试采取默认值 EXEC proc_stu 60,55 -都不采取默认值 q调用带参数默认值存放过程-错误调用方式：希望笔试采取默认值，机试及格线55分EXEC proc_stu ,55-正确调用方式：EXEC proc_stu labPass=55 第21页带输出参数存放过程带输出参数存放过程 3-1q假如希望调用存放过程后，返回一个或多个值，这时就需要使用输出（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 q调用带输出参数存放过程带输出参数存放过程带输出参数存放过程 3-3调用时必须带OUTPUT关键字，返回结果将存放在变量sum中 后续语句引用返回结果第24页q能够使用PRINT语句显示错误信息，但这 些信息是暂时，只能显示给用户 qRAISERROR 显示用户定义错误信息时q可指定严重级别，q设置系统变量ERRORq统计所发生错误等处理存放过程中错误处理存放过程中错误 第25页使用使用RAISERROR 语句语句4-1RAISERROR(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 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，表示出现了错误第29页q执行速度更加快q允许模块化程序设计 q提升系统安全性q降低网络流通量存放过程优点存放过程优点第30页总结总结 存放过程是一组预编译SQL语句，它能够包含数据操纵语句、变量、逻辑控制语句等 存放过程允许带参数，参数分为：输入参数输出参数 其中，输入参数能够有默认值。输入参数：能够在调用时向存放过程传递参数，这类参数可用来向存放过程中传入值 输出参数从存放过程中返回（输出）值，后面跟随OUTPUT关键字RAISERROR语句用来向用户汇报错误第31页