第8章存储过程

1、学习目标n理解存储过程的作用，学会根据需要创建、修改、删除存储过程（带输入、输出参数），在实际应用开发时能够灵活运用存储过程以提高开发效率。要把某完成特定功能的SQL做成类似C语言的函数，供需要时调用，如何做？问题 存储过程（存储过程（procedure）类似于）类似于c语言中的函数语言中的函数 是一组被编译在一起的是一组被编译在一起的T-SQL语句的集合，它们被集合在语句的集合，它们被集合在一起以完成一个特定的任务。用来执行管理任务或应用复一起以完成一个特定的任务。用来执行管理任务或应用复杂的业务规则杂的业务规则 存储过程可以带参数，也可以返回结果存储过程可以带参数，也可以返回结果什么是存储

2、过程什么是存储过程int sum(int a,int b) int s; s =a+b; return s;存储过程相存储过程相当于当于c语言语言中的函数中的函数-单个单个 SELECT 语句语句SELECT 语句块语句块SELECT语句与逻辑语句与逻辑控制语句控制语句可以包含可以包含什么是存储过程什么是存储过程 存储过程可以包含数据操纵语句、变量、逻辑控制语句存储过程可以包含数据操纵语句、变量、逻辑控制语句等等存储过程的作用模块化编程：模块化编程： 创建一个存储过程存放在数据库中后，就可以被其他程序反复使用。快速执行快速执行 存储过程第一次被执行后，就驻留在内存中。以后执行就省去了重新分析、

3、优化、编译的过程。减少网络通信量减少网络通信量 有了存储过程后，在网络上只要一条语句就能执行一个存储过程。安全机制安全机制 通过隔离和加密的方法提高了数据库的安全性，通过授权可以让用户只能执行存储过程而不能直接访问数据库对象。存储过程的分类系统存储过程 由系统定义，存放在master数据库中 类似Java语言类库中的方法 系统存储过程的名称都以“sp_”开头或“xp_”开头用户自定义存储过程 由用户在自己的数据库中创建的存储过程 类似c语言中用户自定义的函数常用的系统存储过程系统存储过程说明sp_databases列出服务器上的所有数据库sp_helpdb报告有关指定数据库或所有数据库的信息s

4、p_renamedb更改数据库的名称sp_tables返回当前环境下可查询的对象的列表sp_columns返回某个表列的信息sp_help查看某个表的所有信息sp_helpconstraint查看某个表的约束sp_helpindex查看某个表的索引sp_stored_procedures列出当前环境中的所有存储过程sp_password添加或修改登录帐户的密码sp_helptext显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本EXEC sp_databasesEXEC sp_renamedb Northwind,Northwind1USE stuDBGOEXEC sp

5、_tablesEXEC sp_columns stuInfo EXEC sp_help stuInfoEXEC sp_helpconstraint stuInfoEXEC sp_helpindex stuMarksEXEC sp_helptext view_stuInfo_stuMarks EXEC sp_stored_procedures 常用的系统存储过程常用的系统存储过程修改数据库的名称修改数据库的名称(单用户访问单用户访问)列出当前系统中的数据库列出当前系统中的数据库当前数据库中查询的对象的列表当前数据库中查询的对象的列表返回某个表列的信息返回某个表列的信息查看表查看表stuInfo的

6、信息的信息查看表查看表stuInfo的约束的约束查看表查看表stuMarks的索引的索引查看视图的语句文本查看视图的语句文本查看当前数据库中的存储过程查看当前数据库中的存储过程演示：常用的存储过程演示：常用的存储过程常用的系统存储过程n常用的扩展存储过程：xp_cmdshell q可以执行DOS命令下的一些的操作 q以文本行方式返回任何输出 q调用语法：qEXEC xp_cmdshell DOS命令 NO_OUTPUTnSQL Server2005中启动执行 xp_cmdshell 扩展存储过程的方法 EXEC sp_configure show advanced options, 1GORE

7、CONFIGUREGOEXEC sp_configure xp_cmdshell, 1GORECONFIGUREGO 常用的系统存储过程常用的系统存储过程USE 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 -查看文件查看文件 创建数据库创建数据库bankDB，要求保存在，要求保存

8、在D:bank创建文件夹创建文件夹D:bank查看文件夹查看文件夹D:bank如何创建存储过程定义存储过程的语法定义存储过程的语法 CREATE PROCEDURE 存储过程名 参数1 数据类型 = 默认值 OUTPUT, , 参数n 数据类型 = 默认值 OUTPUT AS SQL语句 GOq和c语言的函数一样，参数可选q参数分为输入参数、输出参数 q输入参数允许有默认值调用存储过程nEXECUTE（执行）语句用来调用存储过程n调用的语法EXEC 过程名 参数EXEC proc_stun存储过程的参数分两种存储过程的参数分两种：q输入参数q输出参数n输入参数输入参数：q用于向存储过程传入值，

9、类似c语言带参函数n输出参数：输出参数：q用于在调用存储过程后，返回结果调用存储过程调用存储过程int sum (int a, int b) int s; s=a+b; return s;c=sum（5, 8）传入参传入参数值数值返回结果返回结果创建不带参数的存储过程 请创建存储过程，查看本次考试平均分以及未通过考试的学员名单请创建存储过程，查看本次考试平均分以及未通过考试的学员名单创建不带参数的存储过程CREATE PROCEDURE proc_stu AS DECLARE writtenAvg float,labAvg float SELECT writtenAvg=AVG(written

10、Exam), 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,writt

11、enExam,labExam FROM stuInfo INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo WHERE writtenExam60 OR labExam60 GOproc_stu为存储过程的名称为存储过程的名称笔试平均分和机笔试平均分和机试平均分变量试平均分变量 显示考试成绩的等级显示考试成绩的等级 显示未通过的学员显示未通过的学员 带输入参数的存储过程修改上例：由于每次考试的难易程度不一样，每次修改上例：由于每次考试的难易程度不一样，每次笔试和机试的及格线笔试和机试的及格线可能随时变化（不再是可能随时变化（不再是6060分）

12、，这导致考试的评判结果也相应变化分），这导致考试的评判结果也相应变化上述存储过程添加上述存储过程添加2个输入参数：个输入参数：writtenPass 笔试及格线笔试及格线 labPass 机试及格线机试及格线 带输入参数的存储过程带输入参数的存储过程CREATE PROCEDURE proc_stu writtenPass int, labPass int AS print - print 参加本次考试没有通过的学员：参加本次考试没有通过的学员： SELECT stuName,stuInfo.stuNo,writtenExam, labExam FROM stuInfo INNER JOIN

13、stuMarks ON stuInfo.stuNo=stuMarks.stuNo WHERE writtenExamwrittenPass OR labExamlabPass GO输入参数：笔试及格线输入参数：笔试及格线输入参数：机试及格线输入参数：机试及格线查询没有通过查询没有通过考试的学员考试的学员带输入参数的存储过程带输入参数的存储过程EXEC proc_stu 60,55 调用带参数的存储过程调用带参数的存储过程 假定本次考试机试偏难，机试的及格线定为假定本次考试机试偏难，机试的及格线定为55分，笔试及格线定为分，笔试及格线定为60分分-或这样调用：或这样调用：EXEC proc_st

14、u labPass=55,writtenPass=60机试及格线降分后，机试及格线降分后，李斯文李斯文（59分）成为分）成为“漏网之鱼漏网之鱼”了了输入参数的默认值n带参数的存储过程确实比较方便，调用者可根据试卷的难易度，随时修改每次考试的及格线如果试卷的难易程度合适，则调用者还是必须如果试卷的难易程度合适，则调用者还是必须如此调用：如此调用： EXEC proc_stu 60,60，比较麻烦，比较麻烦这样调用就比较合理：这样调用就比较合理：EXEC proc_stu 55EXEC proc_stu 笔试及格线笔试及格线55分，机试及格线默认为分，机试及格线默认为60分分 笔试和机试及格线都默

15、认为标准的笔试和机试及格线都默认为标准的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 writtenExamwrittenPass OR labExamlabPass GO笔试及格线

16、：默认为笔试及格线：默认为60分分机试及格线：默认为机试及格线：默认为60分分查询没有通过考试的学员查询没有通过考试的学员输入参数的默认值输入参数的默认值输入参数的默认值输入参数的默认值EXEC proc_stu -都采用默认值都采用默认值 EXEC proc_stu 64 -机试采用默认值机试采用默认值 EXEC proc_stu 60,55 -都不采用默认值都不采用默认值 调用带参数默认值的存储过程调用带参数默认值的存储过程-错误的调用方式：希望笔试采用默认值，机试及格线错误的调用方式：希望笔试采用默认值，机试及格线55分分EXEC proc_stu ,55 -正确的调用方式：正确的调用方

17、式：EXEC proc_stu labPass=55 创建带输入参数的存储过程n创建格式：CREATE PROCEDURE proc_namepara_name datatype =default, ASSql_statements当存储过程含有多个输入参数时，参数值可以按任意顺序指定，对于允许空值和具有默认值的输入参数可以不给出参数的传递值。EXECUTE procedure_nameparameter_name=value,n执行带输入参数的存储过程使用参数名传递参数值使用参数名传递参数值按位置传递参数按位置传递参数当存储过程含有多个输入参数时，参数值的顺序必须与存储过程中定义的输入参数顺

18、序一致。案例 创建一个名为p_StuByPara的存储过程，根据给定的班级编码和学生学号，显示相应学生的信息使用参数名传递参数值的方法执行存储过程使用参数名传递参数值的方法执行存储过程p_StudentPara，分别查找班级编号为分别查找班级编号为“2000001”，“20000002”的所有学生信息。的所有学生信息。带输出参数的存储过程带输出参数的存储过程 如果希望调用存储过程后，返回一个或多个值，这时就需如果希望调用存储过程后，返回一个或多个值，这时就需要使用输出（要使用输出（OUTPUT）参数了）参数了 修改上例，返回未通过考试的学员人数修改上例，返回未通过考试的学员人数CREATE P

19、ROCEDURE 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 writtenE

20、xamwrittenPass OR labExam=3 print 未通过人数：未通过人数：+convert(varchar(5),sum)+ 人人, 超过超过60%,及格分数线还应下调及格分数线还应下调ELSE print 未通过人数：未通过人数：+convert(varchar(5),sum)+ 人人, 已控制在已控制在60%以下，及格分数线适中以下，及格分数线适中GO 调用带输出参数的存储过程调用带输出参数的存储过程带输出参数的存储过程带输出参数的存储过程调用时必须带调用时必须带OUTPUT关键字关键字 ，返，返回结果将存放在变量回结果将存放在变量sum中中 后续语句引用返回结果后续语句引用返回结果修改存储过程n修改格式：ALTER PROCEDURE proc_namepara_name datatype =default, WITH ENCRYPTIONASSql_statements【问题】使用Transact-SQL语句，修改存储过程p_StudentPara，使其能根据用户提供的班级名称，而不是班级编号进行模糊查询，并要求加密存储过程。重命名