SqlServer高级

,*,*,*,*,存储过程,第七章,回顾,管理控制事务的常用,T-SQL,语句有哪些？,什么是视图？它有什么好处？,T-SQL,中哪个关键字可以实现用索引查询数据？,本章任务,使用存储过程完成以下功能：,查看各学期的课程信息,查询指定学期开设的课程信息,获得指定学期开设的课程数目,插入新增课程记录,本章目标,了解存储过程的优点,掌握常用的系统,/,扩展存储过程,使用存储过程封装业务逻辑,掌握如何实现错误处理,什么是存储过程,2-1,预先存储好的,SQL,程序,保存在,SQL Server,中,通过名称和参数执行,int sum(int a, int b),int s;,s = a + b;,return s;,类似于,C#,语言中的方法,5,什么是存储过程,2-2,可带参数，也可返回结果,可包含数据操纵语句、变量、逻辑控制语句等,存储过程,-,-,-,单个,SELECT,语句,SELECT,语句块,SELECT,语句与逻辑控制语句,可以包含,6,存储过程的优点,执行速度更快,允许模块化程序设计,提高系统安全性,减少网络流通量,存储过程的分类,系统存储过程,系统存储过程的名称一般以“,sp_”,开头,由,SQLServer,创建、管理和使用,存放在,Resource,数据库中,类似,C#,语言类库中的方法,扩展存储过程,扩展存储过程的名称通常以“,xp_”,开头,使用编辑语言（如,C#),创建的外部存储过程,以,DLL,形式单独存在,用户自定义存储过程,由用户在自己的数据库中创建的存储过程,类似,C#,语言中用户自定义的方法,常用的系统存储过程,系统存储过程,说 明,sp_databases,列出服务器上的所有数据库,sp_helpdb,报告有关指定数据库或所有数据库的信息,sp_renamedb,更改数据库的名称,sp_tables,返回当前环境下可查询的对象的列表,sp_columns,返回某个表列的信息,sp_help,查看某个表的所有信息,sp_helpconstraint,查看某个表的约束,sp_helpindex,查看某个表的索引,sp_stored_procedures,列出当前环境中的所有存储过程,sp_password,添加或修改登录帐户的密码,sp_helptext,显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本,调用存储过程,调用存储过程的语法,或,EXECUTE,过程名,参数,EXEC,过程名,参数,如果执行存储过程的语句是批处理中的第一个语句，则可以不指定,EXECUTE,关键字,sp_databases,EXEC,sp_renamedb,MyBank,Bank,USE,MySchool,GO,sp_tables,EXEC,sp_columns,Student,EXEC,sp_help,Student,EXEC,sp_helpconstraint,Student,EXEC,sp_helptext,view_Student_,Result_Info,EXEC,sp_stored_procedures,常用的系统存储过程,修改数据库的名称,(,单用户访问,),列出当前系统中的数据库,当前数据库中查询的对象的列表,返回某个表列的信息,查看表,Student,的信息,查看表,Student,的约束,查看视图的语句文本,查看当前数据库中的存储过程,演示案例,1,：常用的存储过程,常用的扩展存储过程,xp_cmdshell,可以执行,DOS,命令下的一些的操作,以文本行方式返回任何输出,EXEC xp_cmdshell DOS,命令,NO_OUTPUT,创建数据库,bankDB,，要求保存在,D:bank,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,-,查看文件,创建文件夹D:bank,查看,文件夹D:bank,练习,使用存储过程查看表信息,需求说明：,查看,Student,表中的列、约束信息,比较下面三个系统存储过程输出的数据库信息的特点,sp_columns,sp_helpconstraint,sp_help,提示：,观察这,3,个系统存储过程的执行结果,完成时间：,15,分钟,13,如何创建存储过程,定义存储过程的语法,存储过程的参数,和,C#,语言的方法一样，参数可选,参数分为输入参数、输出参数,输入参数允许有默认值,CREATE PROCEDURE,存储过程名,参数,1,数据类型,=,默认值,OUTPUT, ,参数,n,数据类型,=,默认值,OUTPUT,AS,SQL,语句,GO,第一步：获得“,Java Logic”,的课程编号,第二步：获得“,Java Logic”,最近一次的考试时间,第三步：查询得到平均成绩,第四步：查询这次考试成绩低于,60,分的学生,创建,、执行无,参的存储过程,创建存储过程，,查询,Java Logic,最近一次考试平均分以及未通过考试的学员名单,CREATE PROCEDURE,usp_GetAvgResult,AS, ,SELECT subjectNo=subjectNo FROM Subject,WHERE SubjectName,= ,J,ava,L,ogic,SELECT date=max(ExamDate) FROM Result INNER JOIN Subject,ON Result.SubjectNo=Subject.SubjectNo,WHERE,Result.SubjectNo,=,subjectNo,SELECT avg=AVG(StudentResult) FROM Result,WHERE ExamDate=date and SubjectNo=subjectNo, ,IF (avg70) PRINT 考试成绩：优秀,ELSE,PRINT 考试成绩：较差, ,SELECT StudentName,Student.StudentNo,StudentResult,FROM Student INNER JOIN Result,ON Student.StudentNo=Result.StudentNo,WHERE StudentResult60,AND, ,GO,EXEC,usp_GetAvgResult,获得考试时间,显示考试成绩的等级,查询未通过的学员,考试平均分,获得课程编号,执行存储过程,演示案例,2,：创建无参的存储过程,15,指导,查询获得各学期课程信息,2-1,训练要点：,使用无参存储过程完成数据查询,需求说明：,利用存储过程查询各学期开设的课程名称和每门课程的课时,讲解需求说明,16,指导,查询获得各学期课程信息,2-2,实现思路：,检测是否存在存储过程,创建存储过程，通过联接查询获得结果,编译、执行，获得结果,完成时间：,20,分钟,IF EXISTS (SELECT * FROM,sysobjects,WHERE name = usp_grade_subject ),DROP PROCEDURE usp_grade_subject,GO,/*-,创建存储过程,-*/,CREATE PROCEDURE,usp_grade_subject,AS,SELECT GradeName,SubjectName,ClassHour FROM Grade,INNER JOIN Subject,ON Grade.GradeId=Subject.GradeId,ORDER BY Subject.GradeId,SubjectNo,GO,/*-,调用执行存储过程,-*/,EXEC,usp_grade_subject,17,共性问题讲解,常见调试问题及解决办法,代码规范问题,共性问题集中讲解,存储过程的参数分两种：,输入参数,输出参数,输入参数：,向存储过程,传入,值,输出参数：,调用存储过程后，,传出,执行结果,存储过程参数,- C#,方法,int add (int a, int b,out,int c),int s=1;,c=0;,if (a0 | b0),s=0;,else,c=a+b;,return s;,int rt, sum;,rt =add(5, 8, out sum);,传入参数值,传出参数值,返回结果,上述存储过程添加,2,个输入参数,score,：,考试及格线,subName,：课程名称,带输入参数的存储过程,变更上例的需求,假定,C# OOP,课程最近一次考试的试题偏难，考试及格线定为,50,分,CREATE PROCEDURE,usp_unpass,subName varchar(50),score int,AS,DECLARE date datetime -,最近考试时间,DECLARE subjectNo int -,课程编号,SELECT subjectNo=SubjectNo FROM Subject,Where SubjectName =,subName, ,SELECT StudentName,Student.StudentNo,StudentResult,FROM Student,INNER JOIN Result ON Student.StudentNo = Result.StudentNo,WHERE StudentResult ,score,AND ExamDate = date,AND SubjectNo=subjectNo,GO,输入参数：考试及格线,查询某课程最近一次考试没有通过的学员,输入参数：课程名称,EXEC,usp_unpass,C# OOP,50,EXEC,usp_unpass,score,=50,subName=,C# OOP,或,演示案例,3,：创建、执行输入参数的存储过程,“C# OOP ”,课程最近一次考试及格线降分后，田园（,39,分）仍然没有通过,20,输入参数的默认值,2-1,如果试卷难易程度合适，则调用者仍须调用：,有简便的方法吗？,EXEC,usp_unpass,C# OOP, 60,使用参数的默认值,输入参数默认值,2-2,创建带参数默认值的存储过程,CREATE PROCEDURE,usp_unpass,subName varchar(50),score int = 60,AS, ,GO,调用带参数默认值的存储过程,EXEC,usp_unpass,C# OOP,EXEC,usp_unpass,subName =,C# OOP,或,如果有默认值的参数出现在没有默认值参数的前面，那么需要指定参数名为其赋值,考试及格线默认为标准的,60,分,有默认值的参数放在存储过程参数列表的最后,22,常见错误,2-1,CREATE PROCEDURE,usp_unpass,score int = 60,subName varchar(50),AS, ,GO,EXEC,usp_unpass,Java Logic,EXEC usp_unpass,subName=,Java Logic,此处不能用空格替代默认值,EXEC usp_unpass,default,Java Logic,使用参数名指定参数值,使用,default,代表默认值,23,常见错误,2-2,CREATE PROCEDURE,usp_unpass,score int = 60,subName varchar(50),AS, ,EXEC,usp_unpass,50,Java Logic,GO,存储过程的调用语句被放置到了创建语句中，形成了递归调用,GO,SQLServer,存储过程的,最大嵌套层数,为,32,。,如果超过最大嵌套层数，会造成执行出错,24,小结,请写出创建存储过程的语法,如何调用存储过程？,列举出调用有默认参数存储过程的几种方法,25,练习,用存储过程查指定学期课程,需求说明：,查询输出指定学期总课时和开设的课程名称、每门课程的课时,如果没有指定学期名称则查看每个学期的总课时和开设的课程名称、课时,提示：,为输入参数,课程名称指定默认值为,NULL,判断输入参数是否为,NULL,使用,UNION,关键字合并,2,个查询的记录集,学期的总课时,每门课程名称和课时,完成时间：,25,分钟,26,CREATE PROCEDURE usp_query_subject,GradeName VARCHAR(50),= NULL,AS,IF GradeName IS NULL,SELECT,GradeName,SubjectName,ClassHour FROM Grade,LEFT,JOIN,Subject ON Grade.GradeId=Subject.GradeId,UNION,SELECT,GradeName, ,SUM(ClassHour)FROM Grade,LEFT,JOIN,Subject ON Grade.GradeId=Subject.GradeId,GROUP BY GradeName,ELSE,SELECT GradeName,SubjectName,ClassHour FROM Grade,LEFT JOIN Subject ON Grade.GradeId=Subject.GradeId,WHERE GradeName=GradeName,UNION,SELECT GradeName, ,SUM(ClassHour)FROM Grade,LEFT JOIN Subject ON Grade.GradeId=Subject.GradeId,WHERE GradeName=GradeName,GROUP BY GradeName,GO,27,带输出参数的存储过程,3-1,修改上例需求，要求获得参加考试的学生人数和未通过的学生人数,调用存储过程返回二个数据值，可以使用输出参数,28,带输出参数的存储过程,3-2,创建带输出参数的存储过程,CREATE PROCEDURE usp_query_num,UnPassNum,INT,OUTPUT,TotalNum,INT,OUTPUT,SubjectName NCHAR(10),Pass INT = 60,AS,DECLARE date datetime,DECLARE subjectNo int, ,SELECT,UnPassNum,= COUNT(*) FROM Result,WHERE ExamDate=date AND subjectNo=subjectNo,AND StudentResult 0,BEGIN,IF ratio 60,PRINT,及格分数线不需下调,ELSE,PRINT,及格分数,线应下调,END,ELSE,PRINT ,恭喜！本次考试成绩优良，没有不及格的学生,调用时必须带,OUTPUT,关键字 ，返回结果将分别存放在变量,unPassNum,和,TotalNum,中,后续语句引用返回结果,演示案例,5,：执行带输出参数的存储过程,30,指导,存储过程获指定学期课程数,2-1,训练要点：,使用输入、输出参数的存储过程完成数据查询，获得相关数据,需求说明：,查询获得指定学期开设的课程数和总课时,如果学期名称为空，则显示“,学期名称不能为空,”，并返回,讲解需求说明,31,指导,存储过程获指定学期课程数,2-2,实现思路：,输入参数是学期名称,输出参数是课程数和总课时数,查询获得指定学期所开设的课程、课时,查询该学期的总课程数、总课时数,完成时间：,20,分钟,CREATE PROCEDURE usp_query_subject,CourseNum,INT,OUTPUT,HourNum,INT,OUTPUT,GradeName VARCHAR(50),AS,IF LEN(GradeName) = 0,BEGIN,PRINT ,学期名称不能为空,RETURN,END,PRINT -,学期课程信息如下,-,SELECT GradeName,SubjectName,ClassHour FROM Grade,LEFT JOIN Subject ON Grade.GradeId=Subject.GradeId,WHERE GradeName=GradeName,SELECT,CourseNum=,COUNT,(0),HourNum=,SUM,(ClassHour,),FROM Grade,INNER JOIN Subject ON Grade.GradeId=Subject.GradeId,WHERE GradeName=GradeName,GO,32,共性问题讲解,常见调试问题及解决办法,代码规范问题,共性问题集中讲解,常见错误,CREATE PROCEDURE,usp_query_subject,CourseNum INT OUTPUT,GradeName VARCHAR(50),AS, ,GO,EXEC,usp_query_subject GradeName= S1, CourseNum OUTPUT,GO,存储过程的调用语句中数据类型和参数位置要匹配,EXEC,usp_query_subject,CourseNum OUTPUT,GradeName=,S1,存储过程最后一个参数后不能有逗号,DECLARE CourseNum INT,DECLARE GradeName varchar(50),没有声明变量,34,处理存储过程中的错误,使用,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,：指示是否将错误记录到服务器错误日志中,完善上例。调用存储过程时，如果传入的及格线参数不,在,0,100,之间时，将弹出错误警告，终止存储过程的执行,使用,RAISERROR,示例,2-1,CREATE PROCEDURE usp_stu,UnPassNum OUTPUT,SubjectName NCHAR(10),Pass int = 60,AS,IF,(NOT Pass BETWEEN 0 AND 100) -,判断及格线是否不在取值范围内,BEGIN,RAISERROR,(,及格线错误，请指定之间的分数，统计中断退出,16,1),RETURN,END, ,GO,错误处理,RAISERROR(),引发系统错误，指定错误的严重级别,16,，调用状态为,1,（默认），并影响,ERROR,系统变量的值,立即返回，退出存储过程,演示案例,6,：创建有错误处理的存储过程,使用,RAISERROR,示例,2-2,/*-,调用存储过程，测试,RAISERROR,语句,-*/,DECLARE UnPassNum int,EXEC usp_stu UnPassNum OUTPUT,Java logic,109,DECLARE err int,SET err = ERROR,IF err 0,BEGIN,PRINT ,错误号：,+convert(varchar(5),err ),RETURN -,退出批处理，后续语句不再执行,END,ELSE,PRINT ,未通过人数：, + convert(varchar(5),UnPassNum),GO,考试及格线误输入,109,分,如果执行了,RAISERROR,语句，系统全局,ERROR,将不等于,0,，表示出现了错误,演示案例,7,：执行有错误处理的存储过程,练习,插入新课程记录,需求说明：,增加指定学期开设的课程记录,如果学期名称或课程名称为空，则终止存储过程执行返回,如果课程所属学期不存在，要添加学期记录,返回增加的课程编号，对应的学期编号,获得操作的结果，并根据不同的执行结果显示相应的提示信息,提示：,将要增加的课程数据作为输入参数,新增的课程编号作为输出参数,增加记录的执行结果作为返回值,先检查和增加学期记录，后增加课程记录,课程名称为空的提示信息,课程记录插入失败的提示信息,课程记录插入成功的提示信息,39,总结,存储过程是什么？,存储过程允许带参数，参数分为哪两种？,输出参数从存储过程中返回（输出）值，后面跟随什么关键字？,RAISERROR,语句作用是什么？,