第3-3章 存储过程

Click to edit Title Slide,Click to edit Master text styles,Second level,Third level,Fourth level,Fifth level,第三章,-3,存储过程,回顾,事务具有,ACID,四个属性，它们是,？,管理控制事务的常用,T-SQL,语句有哪些？,索引分为哪几类？,根据您的理解，什么是视图？它有什么好处？,您在,stuInfo,表中创建了主键列,stuNo,，,SQL Server,将自动创建哪些索引？,本章目标,了解存储过程的优点,掌握常用的系统存储过程,掌握如何创建存储过程,掌握如何调用存储过程,存储过程（,procedure,）类似于,C,语言中的函数,用来执行管理任务或应用复杂的业务规则,存储过程可以带参数，也可以返回结果,int sum(int a,int b),int s;,s=a+b;,return s;,存储过程相当于,C,语言中的函数,什么是存储过程,存储过程,-,-,-,单个,SELECT,语句,SELECT,语句块,SELECT,语句与逻辑控制语句,可以包含,存储过程可以包含数据操纵语句、变量、逻辑 控制语句等,什么是存储过程,存储过程的优点,执行速度更快,允许模块化程序设计,提高系统安全性,减少网络流通量,存储过程的分类,系统存储过程,由系统定义，存放在,master,数据库中,类似,C,语言中的系统函数,系统存储过程的名称都以“,sp_”,开头或”,xp_”,开头,用户自定义存储过程,由用户在自己的数据库中创建的存储过程,类似,C,语言中的用户自定义函数,常用的系统存储过程,系统存储过程,说明,sp_databases,列出服务器上的所有数据库。,sp_helpdb,报告有关指定数据库或所有数据库的信息,sp_renamedb,更改数据库的名称,sp_tables,返回当前环境下可查询的对象的列表,sp_columns,回某个表列的信息,sp_help,查看某个表的所有信息,sp_helpconstraint,查看某个表的约束,sp_helpindex,查看某个表的索引,sp_stored_procedures,列出当前环境中的所有存储过程。,sp_password,添加或修改登录帐户的密码。,sp_helptext,显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本。,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,修改数据库的名称,(,单用户访问,),列出当前系统中的数据库,当前数据库中查询的对象的列表,返回某个表列的信息,查看表,stuInfo,的信息,查看表,stuInfo,的约束,查看表,stuMarks,的索引,查看视图的语句文本,查看当前数据库中的存储过程,演示：常用的存储过程,常用的系统存储过程,常用的系统存储过程,常用的扩展存储过程：,xp_cmdshell,可以执行,DOS,命令下的一些的操作,以文本行方式返回任何输出,调用语法：,EXEC xp_cmdshell DOS,命令,NO_OUTPUT,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,常用的系统存储过程,如何创建存储过程,定义存储过程的语法,CREATE PROCEDURE,存储过程名,参数,1,数据类型,=,默认值,OUTPUT,参数,n,数据类型,=,默认值,OUTPUT,AS,SQL,语句,GO,和,C,语言的函数一样，参数可选,参数分为输入参数、输出参数,输入参数允许有默认值,创建不带参数的存储过程,问题：,请创建存储过程，查看本次考试平均分以及未通过考试的学员名单,创建不带参数的存储过程,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,为存储过程的名称,笔试平均分和机试平均分变量,显示考试成绩的等级,显示未通过的学员,调用存储过程,EXECUTE,（执行）,语句用来调用存储过程,调用的语法,EXEC,过程名,参数,EXEC proc_stu,存储过程的参数分两种：,输入参数,输出参数,输入参数：,用于向存储过程,传入,值，类似,C,语言的按值传递,;,输出参数：,用于在调用存储过程后，,返回,结果，类似,C,语言的,按引用传递,;,int sum(int a,int b),int s;,s=a+b;,return s;,c=sum,（,5,8,）,传入参数值,返回结果,调用存储过程,带输入参数的存储过程,问题：,修改上例：由于每次考试的难易程度不一样，每次,笔试和机试的及格线,可能随时变化（不再是,60,分），这导致考试的评判结果也相应变化。,分析：,在述存储过程添加,2,个输入参数：,writtenPass,笔试及格线,labPass,机试及格线,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 writtenExam,writtenPass,OR labExam,labPass,GO,输入参数：笔试及格线,输入参数：机试及格线,查询没有通过考试的学员,带输入参数的存储过程,EXEC proc_stu 60,55,调用带参数的存储过程,假定本次考试机试偏难，机试的及格线定为,55,分，笔试及格线定为,60,分,-,或这样调用：,EXEC proc_stu labPass=55,writtenPass=60,机试及格线降分后，,李斯文,（,59,分）成为“漏网之鱼”了,带输入参数的存储过程,输入参数的默认值,带参数的存储过程确实比较方便，调用者可根据试卷的难易度，随时修改每次考试的及格线,问题：,如果试卷的难易程度合适，则调用者还是必须,如此调用：,EXEC proc_stu 60,60,，比较麻烦,这样调用就比较合理：,EXEC proc_stu 55,EXEC proc_stu,笔试及格线,55,分，机试及格线默认为,60,分,笔试和机试及格线都默认为标准的,60,分,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 writtenExam,writtenPass,OR labExam,labPass,GO,笔试及格线：默认为,60,分,机试及格线：默认为,60,分,查询没有通过考试的学员,输入参数的默认值,EXEC proc_stu -,都采用默认值,EXEC proc_stu 64 -,机试采用默认值,EXEC proc_stu 60,55 -,都不采用默认值,调用带参数默认值的存储过程,-,错误的调用方式：希望笔试采用默认值，机试及格线,55,分,EXEC proc_stu ,55,-,正确的调用方式：,EXEC proc_stu labPass=55,输入参数的默认值,如果希望调用存储过程后，返回一个或多个值，这时就需要使用输出（,OUTPUT,）参数了,问题：,修改上例，返回未通过考试的学员人数。,带,输出参数的存储过程,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,调用带输出参数的存储过程,调用时必须带,OUTPUT,关键字，返回结果将存放在变量,sum,中,后续语句引用返回结果,带,输出参数的存储过程,处理存储过程中的错误,可以使用,PRINT,语句显示错误信息，但这 些信息是临时的，只能显示给用户,RAISERROR,显示用户定义的错误信息时,可指定严重级别，,设置系统变量,ERROR,记录所发生的错误等,使用,RAISERROR,语句,RAISERROR,语句的用法如下,：,RAISERROR(msg_id|msg_str,severity,state WITH option,.n),msg_id,：在,sysmessages,系统表中指定用户定义错误信息,msg_str,：用户定义的特定信息，最长,255,个字符,severity,：定义严重性级别。用户可使用的级别为,018,级,state,：表示错误的状态，,1,至,127,之间的值,option,：指示是否将错误记录到服务器错误日志中,问题：,完善上例，当用户调用