存储过程(课件).ppt

1、第8章 存储过程1page2本章知识点w 存储过程概述w 存储过程分类 w 存储过程执行过程 w 创建存储过程 w 修改及删除存储过程w 调用存储过程page3存储过程概述w 场 景F在学生成绩管理系统中，系统用户经常在学生成绩管理系统中，系统用户经常查询学生考试查询学生考试信息，信息，包括：学生名字、所属班级、授课老师、考试科目、考试分包括：学生名字、所属班级、授课老师、考试科目、考试分数等信息。由于该查询在程序中很多地方都要用到，而且使数等信息。由于该查询在程序中很多地方都要用到，而且使用频率非常高，因此，开发人员想用一种可以用频率非常高，因此，开发人员想用一种可以重用重用而又而又高性高性

2、能能的方式来实现。的方式来实现。page4存储过程概述w 问题分析F所查询的信息分布在所查询的信息分布在studentstudent、scorescore、coursecourse、teacherteacher四四张表中，需要用连接查询张表中，需要用连接查询,查询代码如下：查询代码如下：SELECT*FROM STUDENT INNER JOIN SCORE ON STUDENT.SNO=SCORE.SNOINNER JOIN COURSE ON SCORE.CNO=COURSE.CNOINNER JOIN TEACHER ON COURSE.TNO=TEACHER.TNOWHEREGROP

3、BY HAVINGORDER BYpage5存储过程概述w 问题分析F存在的问题存在的问题 该查询功能在程序中的很多地方使用，因此同样一段代码要重复写多遍，如果查询信息有所改变，则要改变很多地方，给程序员带来一定的麻烦。系统使用人员每次提交查询，数据库服务器会对查询语句进行编译、解析和执行，而且是反复做同样的事情，浪费服务器资源。page6存储过程概述w 解决问题F解决方案解决方案 常规SQL语句能实现查询功能，但存在一些问题，如：代码复用率低、可维护性差和性能低，因此SQL SERVER给出了一种可重用、易维护和高效的解决方案 存储过程（Stored ProcedureStored Proc

4、edure）page7存储过程概述w 定 义F将多次重复执行的实现特定功能的代码段编写成一个将多次重复执行的实现特定功能的代码段编写成一个“过程过程”，将其保存在数据库中，并由将其保存在数据库中，并由SQL ServerSQL Server服务器通过其过程名来进服务器通过其过程名来进行调用，这样的行调用，这样的“过程过程”就叫做就叫做存储过程。存储过程。w 特 征 存储过程是一个SQL语句组合。在创建时进行预编译，首次被调用时进行解析，以后再被调用，则可直接执行 存储过程实现了模块化编程。被创建后可以被多个用户共享和重用，有效的减少网络流量，提高SQL语句的执行效率，提高数据库的安全性能pag

5、e8存储过程概述w 优 点F存储过程允许标准组件式编程。存储过程允许标准组件式编程。F存储过程能够实现较快的执行速度。存储过程能够实现较快的执行速度。F存储过程能够减少网络流量。存储过程能够减少网络流量。F存储过程可被作为一种安全机制来充分利用。存储过程可被作为一种安全机制来充分利用。page9创建存储过程w 基本语法CREATE PROC|PROCEDURE procedure_name(parameter data_type VARYING =default OUT|OUTPUT ,.WITH ENCRYPTION ASBEGIN ,.ENDpage10创建存储过程w基本语法：参数说明参数

6、说明 procedure_name procedure_name：过程名称过程名称 过程的参数过程的参数 data_type data_type：parameter parameter：参数的数据类型参数的数据类型 VARYING VARYING：指定作为输出参数支持的结果集，仅适用于：指定作为输出参数支持的结果集，仅适用于cursorcursor参参数数 DEFAULT DEFAULT：参数的默认值参数的默认值 OUTPUT OUTPUT：指示参数是输出参数指示参数是输出参数 ENCRYPTION ENCRYPTION：将将CREATE PROCEDURECREATE PROCEDURE语句

7、的原始文本加密语句的原始文本加密 AS AS：指明该存储过程将要执行的动指明该存储过程将要执行的动作作 ：要包含在过程中的一个或多个要包含在过程中的一个或多个T-SQLT-SQL语句语句page11创建存储过程w 执行代码CREATE PROCEDURE prcListClassesWITH ENCRYPTIONASBEGINSELECT c.classCourseName,d.depName,c.classTeacherName,c.classStartTime,cr.crBuildingName,cr.crRoomNo,c.classLimitNum,c.classExistNum,c.c

8、lassCredit FROM Classes c JOIN Department d ON c.classdepNo=d.depNo JOIN ClassRoom cr ON c.classRoomNo=cr.crNo WHERE classCourseName=计算机网络 ENDpage12执行存储过程w 语 法 EXECUTEEXECUTE|EXECEXEC proc_name ,proc_name ,Sp_help sp_helptextSp_help sp_helptextF注注 意意 在执行存储过程时，如果语句是一个批处理中的第一个语句，则不一定要指定 EXECUTE 或EXEC关

9、键字。如果存储过程带有参数，则在执行过程时，需带上相应的参数。page13实例分析二：存储过程用输出参数返回值w 课堂练习一：F音乐信息管理系统中创建存储过程，磁存储过程能够查询出每一音乐信息管理系统中创建存储过程，磁存储过程能够查询出每一种歌曲的平均价格。种歌曲的平均价格。w 课堂练习二：F音乐信息管理系统中创建存储过程，磁存储过程能够查询出每一音乐信息管理系统中创建存储过程，磁存储过程能够查询出每一类歌曲中最贵的那一首。类歌曲中最贵的那一首。w 课堂练习三：F在学生成绩管理系统中，需要建立一个存储过程，此存储过程能在学生成绩管理系统中，需要建立一个存储过程，此存储过程能够查询出每一名同学的

10、平均分数。够查询出每一名同学的平均分数。w 课堂练习四：F在学生成绩管理系统中，需要建立一个存储过程，此存储过程能在学生成绩管理系统中，需要建立一个存储过程，此存储过程能够查询出每一位老师所带的学生的个数。够查询出每一位老师所带的学生的个数。page14执行存储过程w 执行存储过程的步骤F编译阶段编译阶段 在创建时，系统对其语句进行语法检查。若有语法错误则创建失败，否则创建成功F解析阶段解析阶段 在首次执行时，从系统中读取存储过程，并检查引用的数据库对象是否存在。若存在找不到的数据库对象则产生错误，否则进入执行阶段F执行阶段执行阶段 依次执行存储过程中的SQL语句page15存储过程概述w 存

11、储过程分类F用户定义的存储过程用户定义的存储过程 T-SQL存储过程 CLR存储过程F系统存储过程系统存储过程F临时存储过程临时存储过程F远程存储过程远程存储过程page16存储过程概述w 查看存储过程F查看数据库的的存储过程也可以通过两种方法：查看数据库的的存储过程也可以通过两种方法：利用代码命令 利用Manager StudioF利用代码命令查看存储过程利用代码命令查看存储过程 sp_help proc_name命令查看名为proc_name的存储过程。sp_helptext proc_name命令查看名为proc_name的存储过程的详细代码。page17存储过程概述w删除存储过程两种方

12、式F 在在Management StudioManagement Studio中选中要删除的存储过程，右键单中选中要删除的存储过程，右键单击击“删除删除”命令即可。如下图。命令即可。如下图。F 使用使用DROPDROP命令命令DROP PROC PROC_ClassInfoDROP PROC PROC_ClassInfopage18修改存储过程w 使用ALTERF例例 子：子：ALTERALTER PROCEDURE PROCEDURE prcListClasses()ASASBEGINBEGINselect c.classNo,select c.classNo,c.classCourseNa

13、me,c.classStartTime,c.classCourseName,c.classStartTime,c.classTeacherName,c.classTeacherName,cr.crBuildingName,cr.crBuildingName,cr.crRoomNo cr.crRoomNo from Classes c joinfrom Classes c join ClassRoom cr on c.classRoomNo=cr.crNoClassRoom cr on c.classRoomNo=cr.crNoENDENDpage19存储过程的输入和输出w 参数是存储过程与外界

14、进行交互的一种途径F存储过程通过存储过程通过输入参数输入参数和和输出参数输出参数与外界进行交互与外界进行交互.w 存储过程的数据传递方式：F输入参数输入参数 调用者向存储过程输入的数据值F输出参数输出参数 OUTPUTOUTPUT 存储过程向调用者返回的数据值FRETURNRETURN语句语句 向外返回int型数据page20实例分析一：存储过程中使用输入参数w 提出问题F学生成绩管理系统学生成绩管理系统,用户想更灵活的查询用户想更灵活的查询指定学生指定学生的相关信的相关信息，利用简单的存储过程缺乏灵活性，难以满足用户需求，息，利用简单的存储过程缺乏灵活性，难以满足用户需求，系统应该能够让用户

15、查询指定课程名称课程相关信息。系统应该能够让用户查询指定课程名称课程相关信息。page21实例分析一w 分析问题F步骤步骤1 1：确定存储过程所需输入变量：确定存储过程所需输入变量F步骤步骤2 2：创建带参数的存储过程：创建带参数的存储过程F步骤步骤3 3：执行存储过程，验证其是否能：执行存储过程，验证其是否能输入参数输入参数page22实例分析一w 问题求解F步骤步骤1 1：确定存储过程所需输入变量：确定存储过程所需输入变量 确定参数名：classCourseName classCourseName 确定参数的数据类型：varchar(20)varchar(20)F注注 意意 在确定输入参数

16、数据类型时，最好和数据库定义的相关字段匹配page23实例分析一w 问题求解F步骤步骤2 2：创建带参数的存储过程：创建带参数的存储过程CREATE PROCEDURE getstuBynameCREATE PROCEDURE getstuByname(stuName stuName varchar(20)varchar(20)ASASBEGINBEGINSELECT SELECT *FROM studentFROM studentWHERE sname=WHERE sname=stunamestunameENDENDpage24实例分析一w 问题求解F步骤步骤3 3：执行存储过程，验证其是否

17、能：执行存储过程，验证其是否能输入参数输入参数 EXEC stuByName 王芳page25实例分析二：存储过程用输出参数返回值w 课堂练习一：F音乐信息管理系统中创建存储过程，磁存储过程能够查询出某类音乐信息管理系统中创建存储过程，磁存储过程能够查询出某类歌曲的平均价格。歌曲的平均价格。w 课堂练习二：F音乐信息管理系统中创建存储过程，磁存储过程能够查询在某个音乐信息管理系统中创建存储过程，磁存储过程能够查询在某个价格范围内的歌曲的信息。价格范围内的歌曲的信息。w 课堂练习三：F在学生成绩管理系统中，需要建立一个存储过程，此存储过程能在学生成绩管理系统中，需要建立一个存储过程，此存储过程能

18、够将指定学生的指定课程分数提高指定的分数。够将指定学生的指定课程分数提高指定的分数。注意：在存储过注意：在存储过程总传入参数的时候，只能是变量或者常量。不允许使用函数动程总传入参数的时候，只能是变量或者常量。不允许使用函数动态计算态计算F课堂练习四：在学生成绩管理系统中，需要建立一个存储过程，此存储过程能够将指在学生成绩管理系统中，需要建立一个存储过程，此存储过程能够将指定的学生信息插入到定的学生信息插入到studentstudent表中。表中。page26实例分析二：存储过程用输出参数返回值w 提出问题F在学生成绩管理系统中，需要建立一个存储过程，此存储过在学生成绩管理系统中，需要建立一个存

19、储过程，此存储过程能够查询出程能够查询出指定课程指定课程的平均分、最高分和最低分并将这些的平均分、最高分和最低分并将这些查询出的值返回。查询出的值返回。page27实例分析二w 分析问题F步骤步骤1 1：确定存储过程所需输入变量：确定存储过程所需输入变量F步骤步骤2 2：创建带参数的存储过程：创建带参数的存储过程F步骤步骤3 3：执行存储过程，验证其是否能：执行存储过程，验证其是否能输入和输出参数输入和输出参数page28page29实例分析二w 问题求解F步骤步骤1 1：确定存储过程所需输入变量：确定存储过程所需输入变量 确定参数 -输入参数：cname varchar(20)-指定课程名称

20、 -输出参数：avg int OUTPUT max int OUTPUTOUTPUT min float OUTPUT -返回指定课程的信息 注 意：输出参数必须要用OUTPUT标识page30实例分析二w 问题求解F步骤步骤2 2：创建带参数的存储过程：创建带参数的存储过程CREATE PROCEDURE prcClass_Student CREATE PROCEDURE prcClass_Student(cname varchar(20)=,cname varchar(20)=,avg float OUTPUT,avg float OUTPUT,max int OUTPUT,max int

21、 OUTPUT,min int OUTPUTmin int OUTPUT)ASASBEGINBEGINSELECT SELECT avgavg=avg(degree),=avg(degree),maxmax=max(degree=max(degree),min),min=min(degree)=min(degree)FROM ScoreFROM ScoreGroup by cnameGroup by cnameHaving cname=cnameHaving cname=cnameENDENDpage31实例分析二w 问题求解F步骤步骤3 3：验证存储过程是否能输入和输出参数，查询：验证存储过

22、程是否能输入和输出参数，查询“王芳王芳同学的选课率。同学的选课率。DECLARE avg float,max int,min int DECLARE avg float,max int,min int EXEC prcClass_Student EXEC prcClass_Student 操作系统操作系统,avg OUTPUT,avg OUTPUT,max OUTPUT,max OUTPUT,min OUTPUTmin OUTPUTpage32实例分析二w 结果分析F代码通过调用存储过程代码通过调用存储过程prcClass_StudentprcClass_Student，传入参数，传入参数“操

23、作系操作系统统”，返回该课程的选课信息。，返回该课程的选课信息。F输出参数输出参数和和用来接收返回数据的变量用来接收返回数据的变量的数据类型和位置必须匹配，的数据类型和位置必须匹配，并且用来接收返回值的参数也必须标识并且用来接收返回值的参数也必须标识OUTPUTOUTPUT。F实例中存储过程用输入参数和输出参数与外部进行数据交互，利实例中存储过程用输入参数和输出参数与外部进行数据交互，利用输入和输出参数能给程序带来更大的灵活性。用输入和输出参数能给程序带来更大的灵活性。page33实例分析二：存储过程用输出参数返回值w 课堂练习一：F音乐信息管理系统中创建存储过程，磁存储过程能够返回某位歌音乐

24、信息管理系统中创建存储过程，磁存储过程能够返回某位歌手所唱的歌曲名字，价格和类别。手所唱的歌曲名字，价格和类别。w 课堂练习二：F音乐信息管理系统中创建存储过程，磁存储过程能够所有歌曲中音乐信息管理系统中创建存储过程，磁存储过程能够所有歌曲中的最贵的，最便宜的和均价。的最贵的，最便宜的和均价。w 课堂练习三：F在学生成绩管理系统中，需要建立一个存储过程，此存储过程能在学生成绩管理系统中，需要建立一个存储过程，此存储过程能够返回出指定张旭老师教的科目中最低分的学生姓名和分数。够返回出指定张旭老师教的科目中最低分的学生姓名和分数。w 课堂练习四：F在学生成绩管理系统中，需要建立一个存储过程，此存储

25、过程能在学生成绩管理系统中，需要建立一个存储过程，此存储过程能够返回指定学生的指定科目的的分数。够返回指定学生的指定科目的的分数。page34存储过程用默认值参数 通过存储过程的输出参数，可以对外部调用对象返回一个或通过存储过程的输出参数，可以对外部调用对象返回一个或多个值，同样在存储过程中还有另一种方式可以返回值多个值，同样在存储过程中还有另一种方式可以返回值使用使用RETURNRETURN关键字。关键字。w 使用RETURN只能返回单个值，并且是整型值。它通常返回下列值：F操作过程中受影响的行数操作过程中受影响的行数F错误编码错误编码F插入到插入到 IDENTITY IDENTITY 列中

26、的值列中的值page35存储过程用RETURN返回值 通过存储过程的输出参数，可以对外部调用对象返回一个或通过存储过程的输出参数，可以对外部调用对象返回一个或多个值，同样在存储过程中还有另一种方式可以返回值多个值，同样在存储过程中还有另一种方式可以返回值使用使用RETURNRETURN关键字。关键字。w 使用RETURN只能返回单个值，并且是整型值。它通常返回下列值：F操作过程中受影响的行数操作过程中受影响的行数F错误编码错误编码F插入到插入到 IDENTITY IDENTITY 列中的值列中的值page36实例分析三w 提出问题F创建存储过程以查看创建存储过程以查看指定老师指定老师所授选修课

27、的信息，并向调用所授选修课的信息，并向调用对象对象返回一个消息返回一个消息，表明查询结果。，表明查询结果。page37实例分析三w 分析问题F步骤步骤1 1：确定使用：确定使用RETURNRETURN返回单个值，作为输出消息返回单个值，作为输出消息F步骤步骤2 2：创建存储过程：创建存储过程F步骤步骤3 3：执行存储过程，验证其功能：执行存储过程，验证其功能page38实例分析三w 问题求解FRETURNRETURN语句的语法为：语句的语法为：RETURN integer_expressionF步骤步骤1 1：确定使用：确定使用RETURNRETURN返回单个值，作为输出消息返回单个值，作为输

28、出消息 RETURN 1表示查询成功，找到所需记录 RETURN 0表示查询失败，没有发现有效记录page39实例分析三w 问题求解F步骤步骤2 2：创建存储过程：创建存储过程CREATE PROCEDURE prcListClassesByTeacherNameCREATE PROCEDURE prcListClassesByTeacherName(classTeacherName varchar(16)classTeacherName varchar(16)ASASBEGINBEGINIF IF existsexists(SELECT(SELECT*FROM COURSE FROM COU

29、RSEWHERE classTeacherName=classTeacherName)WHERE classTeacherName=classTeacherName)BEGINBEGINRETURN 1RETURN 1 ENDENDELSEELSEBEGINBEGINRETURN 0RETURN 0ENDENDENDENDpage40实例分析三w 问题求解F步骤步骤3 3：执行存储过程，验证其功能：执行存储过程，验证其功能 DECLARE result intEXEC result=prcListClassesByTeacherName 朱紫英 IF(result=1)PRINT 存在相关信息

30、 ELSE PRINT 没有相关信息F注注 意：意：RETURN 不能返回NULL。若试图返回NULL，将生成警告信息并返回 0 page41存储过程的高级应用w 存储过程的高级应用包括：F存储过程间的调用存储过程间的调用F在存储过程中使用游标在存储过程中使用游标page42实例分析四：存储过程间的调用w 提出问题F现在需要建立一个存储过程，此存储过程在场景一的基础上现在需要建立一个存储过程，此存储过程在场景一的基础上能够查询查看能够查询查看多门课程多门课程的相关信息。的相关信息。page43实例分析四w 分析问题F步骤步骤1 1：创建：创建调用现有存储过程调用现有存储过程的存储过程的存储过程

31、F步骤步骤2 2：执行存储过程，验证其功能：执行存储过程，验证其功能page44实例分析四w 问题求解F步骤步骤1 1：创建：创建调用现有存储过程调用现有存储过程的存储过程的存储过程F注注 意：意：从一个存储过程调用另一个存储过程必须使用EXEC关键字CREATE PROC prcListMultiClassesASBEGINEXEC prcListClassesByCourseName 大学英语EXEC prcListClassesByCourseName 软件工程END page45实例分析五：在存储过程中使用游标 w 提出问题F在在BigCollegeBigCollege选课系统中，要求

32、一个学期结束后，教务人员选课系统中，要求一个学期结束后，教务人员能够根据授课老师给学生的分数，给能够根据授课老师给学生的分数，给及格及格的学生的总学分加的学生的总学分加上该门课程的学分。上该门课程的学分。page46实例分析五w 分析问题F步骤步骤1 1：确定一种遍历结果集方法：确定一种遍历结果集方法F步骤步骤2 2：创建存储过程：创建存储过程F步骤步骤3 3：执行存储过程，验证其功能：执行存储过程，验证其功能page47实例分析五w 问题求解F步骤步骤1 1：确定一种遍历结果集方法：确定一种遍历结果集方法 为了判断学生成绩是否及格，继而修改学生的总学分，需要在存储过程中遍历和处理结果集中的数

33、据，可以使用游标来完成此任务page48实例分析五w 问题求解F步骤步骤2 2：创建存储过程：创建存储过程page49本章小结w 存储过程的概念w 存储过程的不同类型：F用户自定义用户自定义F系统系统F临时临时F远程远程F扩展扩展w 存储过程的优点w 存储过程的执行过程和原理w 用CREATE PROCEDURE语句来创建简单和复杂存储过程page50本章小结w 存储过程通过以下方式接受和返回数据：F输入参数输入参数F输出参数输出参数F返回语句返回语句w 使用EXECUTE语句可执行存储过程w 用ALTER PROCEDURE语句来修改存储过程w 使用sp_help 和sp_helptext查看存储过程w 存储过程间调用及结合游标实现更为复杂的功能