《SQL教学课程存储过程》由会员分享，可在线阅读，更多相关《SQL教学课程存储过程（32页珍藏版）》请在金锄头文库上搜索。
1、SQLSQL教学课程教学课程- -存储过程存储过程回顾回顾事务具有ACID四个属性，它们是？管理控制事务的常用T-SQL语句有哪些？索引分为哪几类？根据您的理解，什么是视图？它有什么好处？您在stuInfo表中创建了主键列stuNo， SQL Server将自动创建哪些索引？本章目标本章目标了解存储过程的优点掌握常用的系统存储过程掌握如何创建存储过程掌握如何调用存储过程q存储过程（procedure）类似于C语言中的函数q用来执行管理任务或应用复杂的业务规则q存储过程可以带参数，也可以返回结果int sum(int a,int b) int s; s =a+b; return s ;存储过程相
2、当于C语言中的函数什么是存储过程什么是存储过程 2-1存储过程-单个 SELECT 语句SELECT 语句块SELECT语句与逻辑控制语句可以包含可以包含什么是存储过程什么是存储过程 2-2q存储过程可以包含数据操纵语句、变量、逻辑 控制语句等q系统存储过程q由系统定义，存放在master数据库中q类似C语言中的系统函数q系统存储过程的名称都以“sp_”开头或”xp_”开头q返回0(成功),1(失败)q用户自定义存储过程q由用户在自己的数据库中创建的存储过程q类似C语言中的用户自定义函数存储过程的分类存储过程的分类 常用的系统存储过程常用的系统存储过程 4-1EXEC sp_databases
3、EXEC 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修改数据库的名称(单用户访问)列出当前系统中的数据库当前数据库中查询的对象的列表返回某个表列的信息查看
4、表stuInfo的信息查看表stuInfo的约束查看表stuMarks的索引查看视图的语句文本查看当前数据库中的存储过程演示：常用的存储过程q常用的扩展存储过程：xp_cmdshell q可以执行DOS命令下的一些的操作 q以文本行方式返回任何输出 q调用语法：q EXEC xp_cmdshell DOS命令 NO_OUTPUT常用的系统存储过程常用的系统存储过程 4-3常用的系统存储过程常用的系统存储过程 4-4USE masterGOEXEC xp_cmdshell mkdir d:bank, NO_OUTPUTIF EXISTS(SELECT * FROM sysdatabases WH
5、ERE name=bankDB) DROP DATABASE bankDBGOCREATE DATABASE bankDB ( )GOEXEC xp_cmdshell dir D:bank -查看文件q创建数据库bankDB，要求保存在D:bank创建文件夹D:bank查看文件夹D:bankq定义存储过程的语法 CREATE PROCEDURE 存储过程名存储过程名 参数参数1 数据类型数据类型 = 默认值默认值 OUTPUT, , 参数参数n 数据类型数据类型 = 默认值默认值 OUTPUT AS SQL语句语句 GOq和C语言的函数一样，参数可选q参数分为输入参数、输出参数 q输入参数允许
6、有默认值如何创建存储过程如何创建存储过程创建不带参数的存储过程创建不带参数的存储过程 2-1q问题问题：请创建存储过程，查看本次考试平均分以及未通过考试的学员名单创建不带参数的存储过程创建不带参数的存储过程 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 机试平均分：
7、+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为存储过程的名称笔试平均分和机试平均
8、分变量 显示考试成绩的等级 显示未通过的学员 qEXECUTE（执行）语句用来调用存储过程q调用的语法EXEC 过程名 参数 调用存储过程调用存储过程EXEC proc_stu创建带参数的存储过程创建带参数的存储过程q存储过程的参数分两种：q输入参数q输出参数int sum (int a, int b) int s; s=a+b; return s;c=sum（5, 8）传入参数值q输入参数： 用于向存储过程传入值，类似C语言的按值传递;q 输出参数： 用于在调用存储过程后， 返回结果，类似C语言的 按引用传递; 返回结果带输入参数的存储过程带输入参数的存储过程3-1问题：问题：修改上例：由于
9、每次考试的难易程度不一样，每次 笔试和机试的及格线可能随时变化（不再是60分），这导致考试的评判结果也相应变化。分析：分析：在述存储过程添加2个输入参数：writtenPass 笔试及格线 labPass 机试及格线 带输入参数的存储过程带输入参数的存储过程3-2CREATE PROCEDURE proc_stu writtenPass int, labPass int AS print - print 参加本次考试没有通过的学员： SELECT stuName,stuInfo.stuNo,writtenExam, labExam FROM stuInfo INNER JOIN stuMark
10、s ON stuInfo.stuNo=stuMarks.stuNo WHERE writtenExamwrittenPass OR labExamlabPass GO输入参数：笔试及格线输入参数：机试及格线查询没有通过考试的学员带输入参数的存储过程带输入参数的存储过程3-3EXEC proc_stu 60,55 q调用带参数的存储过程 假定本次考试机试偏难，机试的及格线定为55分，笔试及格线定为60分-或这样调用：EXEC proc_stu labPass=55,writtenPass=60机试及格线降分后，李斯文（59分）成为“漏网之鱼”了输入参数的默认值输入参数的默认值3-1带参数的存储过
11、程确实比较方便，调用者可根据试卷的难易度，随时修改每次考试的及格线问题：问题：如果试卷的难易程度合适，则调用者还是必须如此调用： EXEC proc_stu 60,60，比较麻烦这样调用就比较合理：EXEC proc_stu 55EXEC proc_stu 笔试及格线55分，机试及格线默认为60分 笔试和机试及格线都默认为标准的60分CREATE PROCEDURE proc_stu writtenPass int=60, labPass int=60 AS print - print 参加本次考试没有通过的学员： SELECT stuName,stuInfo.stuNo,writtenExa
12、m, labExam FROM stuInfo INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo WHERE writtenExamwrittenPass OR labExamlabPass GO笔试及格线：默认为60分机试及格线：默认为60分查询没有通过考试的学员输入参数的默认值输入参数的默认值3-2输入参数的默认值输入参数的默认值3-3EXEC proc_stu -都采用默认值 EXEC proc_stu 64 -机试采用默认值 EXEC proc_stu 60,55 -都不采用默认值 q调用带参数默认值的存储过程-错误的调用方式：希望
13、笔试采用默认值，机试及格线55分EXEC proc_stu ,55 -正确的调用方式：EXEC proc_stu labPass=55 带输出参数的存储过程带输出参数的存储过程 3-1q如果希望调用存储过程后，返回一个或多个值，这时就需要使用输出（OUTPUT）参数了 问题：问题：修改上例，返回未通过考试的学员人数。CREATE PROCEDURE proc_stu notpassSum int OUTPUT, writtenPass int=60, labPass int=60 AS SELECT stuName,stuInfo.stuNo,writtenExam, labExam FROM
14、 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)+ 人, 已控制
15、在60%以下，及格分数线适中GO q调用带输出参数的存储过程带输出参数的存储过程带输出参数的存储过程 3-3调用时必须带OUTPUT关键字 ，返回结果将存放在变量sum中 后续语句引用返回结果q可以使用PRINT语句显示错误信息，但这 些信息是临时的，只能显示给用户 qRAISERROR 显示用户定义的错误信息时q可指定严重级别，q设置系统变量ERRORq记录所发生的错误等处理存储过程中的错误处理存储过程中的错误 使用使用RAISERROR 语句语句4-1RAISERROR (msg_id | msg_str,severity, state WITH option,.n) RAISERROR语
16、句的用法如下： msg_id：在sysmessages系统表中指定用户定义错误信息msg_str：用户定义的特定信息，最长255个字符severity：定义严重性级别。用户可使用的级别为018级state：表示错误的状态，1至127之间的值option：指示是否将错误记录到服务器错误日志中 问题：问题：完善上例，当用户调用存储过程时，传入的及格线参数不在0100之间时，将弹出错误警告，终止存储过程的执行。 使用使用RAISERROR 语句语句4-2CREATE PROCEDURE proc_stu notpassSum int OUTPUT, -输出参数 writtenPass int=60,
17、 -默认参数放后 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使用使用RAISERROR 语句语句4-4/*-调用存储过程，测试R
18、AISERROR语句-*/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，表示出现了错误q执行速度更快q允许模块化程序设计 q提高系统安全性q减少网络流通量存储过程的优点存储过程的优点总结总结 存储过程是一组预编译的SQL语句，它可以包含数据操纵语句、变量、逻辑控制语句等 存储过程允许带参数，参数分为：输入参数输出参数 其中，输入参数可以有默认值。输入参数：可以在调用时向存储过程传递参数，此类参数可用来向存储过程中传入值 输出参数从存储过程中返回（输出）值，后面跟随OUTPUT关键字RAISERROR语句用来向用户报告错误结束结束
