《SQL存储过程学习总结.docx》由会员分享，可在线阅读，更多相关《SQL存储过程学习总结.docx（12页珍藏版）》请在金锄头文库上搜索。
1、select * from 成绩表select * from 学生信息表1 存储过程的定义 存储过程（procedure）类似于C语言中的函数 用来执行管理任务或应用复杂的业务规则 存储过程可以带参数，也可以返回结果 存储过程可以包含数据操纵语句、变量、逻辑 控制语句等 2 存储过程的优点 执行速度更快 允许模块化程序设计 提高系统安全性 减少网络流通量3、存储过程的分类3.1 系统存储过程 由系统定义，存放在master数据库中 类似C语言中的系统函数 系统存储过程的名称都以“sp_”开头或”xp_”开头3.2 用户自定义存储过程 由用户在自己的数据库中创建的存储过程 类似C语言中的用户自定
2、义函数4、常用的系统存储过程4.1 系统存储过程列表系统存储过程说明sp_databases列出服务器上的所有数据库。sp_helpdb报告有关指定数据库或所有数据库的信息sp_renamedb更改数据库的名称sp_tables返回当前环境下可查询的对象的列表sp_columns回某个表列的信息sp_help查看某个表的所有信息sp_helpconstraint查看某个表的约束sp_helpindex查看某个表的索引sp_stored_procedures列出当前环境中的所有存储过程。sp_password添加或修改登录帐户的密码。sp_helptext显示默认值、未加密的存储过程、用户定义的
3、存储过程、触发器或视图的实际文本。4.2 调用常用的系统存储过程EXEC sp_databases-列出当前系统中的数据库EXEC sp_renamedb Northwind,Northwind1 -修改数据库的名称(单用户访问)USE stuDB -当前数据库中查询的对象的列表GOEXEC sp_tables -返回某个表列的信息EXEC sp_columns stuInfo -返回某个表列的信息EXEC sp_help stuInfo -查看表stuInfo的信息EXEC sp_helpconstraint stuInfo -查看表stuInfo的约束EXEC sp_helpindex s
4、tuMarks -查看表stuMarks的索引EXEC sp_helptext view_stuInfo_stuMarks -查看视图的语句文本EXEC sp_stored_procedures -查看当前数据库中的存储过程4.3 调用常用的扩展存储过程：xp_cmdshell 可以执行DOS命令下的一些的操作,以文本行方式返回任何输出 调用语法：EXEC xp_cmdshell DOS命令NO_OUTPUT【案例分析】创建数据库bankDB，要求保存在D:bankUSE masterGOEXEC xp_cmdshell mkdir d:bank, no_output -创建文件夹D:bank
5、IF EXISTS(SELECT * FROM sysdatabases WHERE name=bankDB) DROP DATABASE bankDBGOCREATE DATABASE bankDB ( )GO -查看文件夹D:bankEXEC xp_cmdshell dir D:bank -查看文件5、创建存储过程5.1 定义存储过程的语法 create procedure 存储过程名 参数 数据类型= 默认值output, , 参数n 数据类型= 默认值output AS SQL语句 GO 和C语言的函数一样，参数可选 参数分为输入参数、输出参数 输入参数允许有默认值5.2 创建不带参数
6、的存储过程 【问题】请创建存储过程，查看本次考试平均分以及未通过考试的学员名单说明：笔试和机试都通过了60分才算通过。-创建不带参数的存储过程create procedure proc_stuASdeclare avgwri float,avglab floatselect avgwri=avg(笔试成绩),avglab=avg(上机成绩) from 成绩表 -笔试平均分和机试平均分变量print 笔试成绩分数=+convert(varchar(10),avgwri)print 上机成绩分数=+convert(varchar(10),avglab)if avgwri70 and avglab7
7、0 -显示考试成绩的等级print 本班成绩：优秀elseprint 本班成绩：较差 -显示未通过的学员print -print 参加考试不及格的学生 select a.学生姓名,a.学号,b.笔试成绩,b.上机成绩from 学生信息表as a inner join 成绩表as b on a.学号=b.学号where b.笔试成绩60 or b.上机成绩60GO5.3 调用存储过程execute（执行）语句用来调用存储过程调用的语法：EXEC 过程名 参数EXEC proc_stu5.4 带参数的存储过程 存储过程的参数分两种：输入参数、输出参数 输入参数：用于向存储过程传入值，类似C语言的按
8、值传递; 输出参数：用于在调用存储过程后，返回结果，类似C语言的按引用传递; 【问题】修改上例：由于每次考试的难易程度不一样，每次笔试和机试的及格线可能随时变化（不再是分），这导致考试的评判结果也相应变化。说明：根据试卷的难度，我们希望笔试和机试的及格线应该是随时变化的，而不是固定的60分。【分析】在述存储过程添加个输入参数：writtenPass 笔试及格线labPass 机试及格线-带输入参数的存储过程create procedure proc_stu2 writtenPass int, -输入参数：笔试及格线 labPass int -输入参数：机试及格线 ASprint =print
9、参加考试不及格的学生select a.学生姓名,a.学号,b.笔试成绩,b.上机成绩 from 学生信息表 as a -查询没有通过考试的学员 inner join 成绩表as b on a.学号=b.学号 where b.笔试成绩writtenpass or b.上机成绩labpass GO 调用带参数的存储过程-假定本次考试机试偏难，机试的及格线定为分，笔试及格线定为分-机试及格线降分后，李斯文（分）成为“漏网之鱼”了exec proc_stu2 60,55-或这样调用：EXEC proc_stu2 labPass=55,writtenPass=605.5 带输入参数的默认值问题：如果试卷
10、的难易程度合适，则调用者还是必须如此调用：EXEC proc_stu2 60,60，比较麻烦这样调用就比较合理：EXEC proc_stu2 55-笔试及格线分，机试及格线默认为分EXEC proc_stu2-笔试和机试及格线都默认为标准的分create procedure proc_stu3 writtenPass int=60, -笔试及格线：默认为分 labPass int=60 -机试及格线：默认为分 AS print = print 参加本次考试没有通过的学员：select a.学生姓名,a.学号,b.笔试成绩,b.上机成绩 from 学生信息表 as a-查询没有通过考试的学员 i
11、nner join 成绩表 as b ON a.学号=b.学号 WHERE 笔试成绩writtenPass OR 上机成绩labPassGO 调用带参数默认值的存储过程EXEC proc_stu -都采用默认值EXEC proc_stu 64 -机试采用默认值EXEC proc_stu 60,55 -都不采用默认值-错误的调用方式：希望笔试采用默认值，机试及格线分EXEC proc_stu ,55 -正确的调用方式：EXEC proc_stu labPass=55 5.6 带输出参数的存储过程如果希望调用存储过程后，返回一个或多个值，这时就需要使用输出（OUTPUT）参数了 问题：修改上例，返
12、回未通过考试的学员人数。CREATE PROCEDURE proc_stu4 notpassSum int OUTPUT, -输出（返回）参数：表示没有通过的人数 writtenPass int=60, labPass int=60 AS -推荐将默认参数放后 select a.学生姓名,a.学号,b.笔试成绩,b.上机成绩 from 学生信息表 as a -统计并返回没有通过考试的学员人数 inner join 成绩表 as b on a.学号=b.学号 where b.笔试成绩writtenpass or b.上机成绩labpass select notpassnum=count(学号) from 成绩表 where 笔试成绩writtenpass or 上机成绩=3 -后续语句引用返回结果 print 未通过人数：+convert(varchar(5),sum)+ 人,超过%,及格分数线还应下调ELSE print 未通过人数：+convert(varchar(5),sum)+ 人,已控制在%以下，及格分数线适中GO强调：1.调用时也必须跟随关键字OUTPUT，否则SQL Server将视为输入参数。5.7 处理存储过程中的错误 可以使用print语句显示错误信息，
