存储过程(课件).ppt
第8章 存储过程,1,本章知识点,存储过程概述存储过程分类 存储过程执行过程 创建存储过程 修改及删除存储过程调用存储过程,存储过程概述,场 景在学生成绩管理系统中，系统用户经常查询学生考试信息，包括：学生名字、所属班级、授课老师、考试科目、考试分数等信息。由于该查询在程序中很多地方都要用到，而且使用频率非常高，因此，开发人员想用一种可以重用而又高性能的方式来实现。,存储过程概述,问题分析所查询的信息分布在student、score、course、teacher四张表中，需要用连接查询,查询代码如下：,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 BY HAVINGORDER BY,存储过程概述,问题分析存在的问题该查询功能在程序中的很多地方使用，因此同样一段代码要重复写多遍，如果查询信息有所改变，则要改变很多地方，给程序员带来一定的麻烦。系统使用人员每次提交查询，数据库服务器会对查询语句进行编译、解析和执行，而且是反复做同样的事情，浪费服务器资源。,存储过程概述,解决问题解决方案常规SQL语句能实现查询功能，但存在一些问题，如：代码复用率低、可维护性差和性能低，因此SQL SERVER给出了一种可重用、易维护和高效的解决方案 存储过程（Stored Procedure ）,存储过程概述,定 义将多次重复执行的实现特定功能的代码段编写成一个“过程”，将其保存在数据库中，并由SQL Server服务器通过其过程名来进行调用，这样的“过程”就叫做存储过程。特 征存储过程是一个SQL语句组合。在创建时进行预编译，首次被调用时进行解析，以后再被调用，则可直接执行存储过程实现了模块化编程。被创建后可以被多个用户共享和重用，有效的减少网络流量，提高SQL语句的执行效率，提高数据库的安全性能,存储过程概述,优 点存储过程允许标准组件式编程。存储过程能够实现较快的执行速度。存储过程能够减少网络流量。存储过程可被作为一种安全机制来充分利用。,创建存储过程,基本语法CREATE PROC | PROCEDURE procedure_name (parameter data_type VARYING = default OUT | OUTPUT ,. WITH ENCRYPTION ASBEGIN ,. END,创建存储过程,基本语法：参数说明procedure_name ： 过程名称过程的参数 data_type ： parameter ： 参数的数据类型 VARYING ：指定作为输出参数支持的结果集，仅适用于cursor参数 DEFAULT ： 参数的默认值 OUTPUT ： 指示参数是输出参数 ENCRYPTION ： 将CREATE PROCEDURE语句的原始文本加密 AS ： 指明该存储过程将要执行的动作 ： 要包含在过程中的一个或多个T-SQL语句,创建存储过程,执行代码,CREATE PROCEDURE prcListClassesWITH ENCRYPTIONASBEGINSELECT c.classCourseName, d.depName, c.classTeacherName, c.classStartTime, cr.crBuildingName, cr.crRoomNo, c.classLimitNum, c.classExistNum, c.classCredit FROM Classes c JOIN Department d ON c.classdepNo=d.depNo JOIN ClassRoom cr ON c.classRoomNo=cr.crNo WHERE classCourseName=计算机网络 END,执行存储过程,语 法EXECUTE | EXEC proc_name ,Sp_help sp_helptext注 意在执行存储过程时，如果语句是一个批处理中的第一个语句，则不一定要指定 EXECUTE 或EXEC关键字。如果存储过程带有参数，则在执行过程时，需带上相应的参数。,实例分析二：存储过程用输出参数返回值,课堂练习一：音乐信息管理系统中创建存储过程，磁存储过程能够查询出每一种歌曲的平均价格。课堂练习二：音乐信息管理系统中创建存储过程，磁存储过程能够查询出每一类歌曲中最贵的那一首。课堂练习三：在学生成绩管理系统中，需要建立一个存储过程，此存储过程能够查询出每一名同学的平均分数。课堂练习四：在学生成绩管理系统中，需要建立一个存储过程，此存储过程能够查询出每一位老师所带的学生的个数。,执行存储过程,执行存储过程的步骤编译阶段在创建时，系统对其语句进行语法检查。若有语法错误则创建失败，否则创建成功解析阶段在首次执行时，从系统中读取存储过程，并检查引用的数据库对象是否存在。若存在找不到的数据库对象则产生错误，否则进入执行阶段执行阶段依次执行存储过程中的SQL语句,存储过程概述,存储过程分类用户定义的存储过程T-SQL存储过程CLR存储过程系统存储过程临时存储过程远程存储过程,存储过程概述,查看存储过程查看数据库的的存储过程也可以通过两种方法：利用代码命令利用Manager Studio利用代码命令查看存储过程sp_help proc_name命令查看名为proc_name的存储过程。sp_helptext proc_name命令查看名为proc_name的存储过程的详细代码。,存储过程概述,删除存储过程两种方式在Management Studio中选中要删除的存储过程，右键单击“删除”命令即可。如下图。使用DROP命令DROP PROC PROC_ClassInfo,修改存储过程,使用ALTER例 子：ALTER PROCEDURE prcListClasses()ASBEGINselect c.classNo, c.classCourseName, c.classStartTime, c.classTeacherName, cr.crBuildingName, cr.crRoomNo from Classes c join ClassRoom cr on c.classRoomNo=cr.crNoEND,存储过程的输入和输出,参数是存储过程与外界进行交互的一种途径存储过程通过输入参数和输出参数与外界进行交互.存储过程的数据传递方式：输入参数调用者向存储过程输入的数据值输出参数 OUTPUT存储过程向调用者返回的数据值RETURN语句向外返回int型数据,实例分析一：存储过程中使用输入参数,提出问题学生成绩管理系统,用户想更灵活的查询指定学生的相关信息，利用简单的存储过程缺乏灵活性，难以满足用户需求，系统应该能够让用户查询指定课程名称课程相关信息。,实例分析一,分析问题步骤1：确定存储过程所需输入变量步骤2：创建带参数的存储过程步骤3：执行存储过程，验证其是否能输入参数,实例分析一,问题求解步骤1：确定存储过程所需输入变量确定参数名： classCourseName确定参数的数据类型： varchar(20)注 意在确定输入参数数据类型时，最好和数据库定义的相关字段匹配,实例分析一,问题求解步骤2：创建带参数的存储过程,CREATE PROCEDURE getstuByname( stuName varchar(20)ASBEGINSELECT *FROM studentWHERE sname= stunameEND,实例分析一,问题求解步骤3：执行存储过程，验证其是否能输入参数EXEC stuByName 王芳,实例分析二：存储过程用输出参数返回值,课堂练习一：音乐信息管理系统中创建存储过程，磁存储过程能够查询出某类歌曲的平均价格。课堂练习二：音乐信息管理系统中创建存储过程，磁存储过程能够查询在某个价格范围内的歌曲的信息。课堂练习三：在学生成绩管理系统中，需要建立一个存储过程，此存储过程能够将指定学生的指定课程分数提高指定的分数。注意：在存储过程总传入参数的时候，只能是变量或者常量。不允许使用函数动态计算课堂练习四： 在学生成绩管理系统中，需要建立一个存储过程，此存储过程能够将指定的学生信息插入到student表中。,实例分析二：存储过程用输出参数返回值,提出问题在学生成绩管理系统中，需要建立一个存储过程，此存储过程能够查询出指定课程的平均分、最高分和最低分并将这些查询出的值返回。,实例分析二,分析问题步骤1：确定存储过程所需输入变量步骤2：创建带参数的存储过程步骤3：执行存储过程，验证其是否能输入和输出参数,实例分析二,问题求解步骤1：确定存储过程所需输入变量确定参数 - 输入参数：cname varchar(20) -指定课程名称 - 输出参数：avg int OUTPUT max int OUTPUT min float OUTPUT -返回指定课程的信息注 意：输出参数必须要用OUTPUT标识,实例分析二,问题求解步骤2：创建带参数的存储过程,CREATE PROCEDURE prcClass_Student (cname varchar(20)=,avg float OUTPUT, max int OUTPUT,min int OUTPUT)ASBEGINSELECT avg = avg(degree), max = max(degree),min=min(degree) FROM ScoreGroup by cnameHaving cname=cnameEND,实例分析二,问题求解步骤3：验证存储过程是否能输入和输出参数，查询“王芳同学的选课率。,DECLARE avg float, max int, min int EXEC prcClass_Student 操作系统,avg OUTPUT,max OUTPUT,min OUTPUT,实例分析二,结果分析代码通过调用存储过程prcClass_Student，传入参数“操作系统”，返回该课程的选课信息。输出参数和用来接收返回数据的变量的数据类型和位置必须匹配，并且用来接收返回值的参数也必须标识OUTPUT。 实例中存储过程用输入参数和输出参数与外部进行数据交互，利用输入和输出参数能给程序带来更大的灵活性。,实例分析二：存储过程用输出参数返回值,课堂练习一：音乐信息管理系统中创建存储过程，磁存储过程能够返回某位歌手所唱的歌曲名字，价格和类别。课堂练习二：音乐信息管理系统中创建存储过程，磁存储过程能够所有歌曲中的最贵的，最便宜的和均价。 课堂练习三：在学生成绩管理系统中，需要建立一个存储过程，此存储过程能够返回出指定张旭老师教的科目中最低分的学生姓名和分数。课堂练习四：在学生成绩管理系统中，需要建立一个存储过程，此存储过程能够返回指定学生的指定科目的的分数。,存储过程用默认值参数,通过存储过程的输出参数，可以对外部调用对象返回一个或多个值，同样在存储过程中还有另一种方式可以返回值使用RETURN关键字。使用RETURN只能返回单个值，并且是整型值。它通常返回下列值：操作过程中受影响的行数错误编码插入到 IDENTITY 列中的值,存储过程用RETURN返回值,通过存储过程的输出参数，可以对外部调用对象返回一个或多个值，同样在存储过程中还有另一种方式可以返回值使用RETURN关键字。使用RETURN只能返回单个值，并且是整型值。它通常返回下列值：操作过程中受影响的行数错误编码插入到 IDENTITY 列中的值,实例分析三,提出问题创建存储过程以查看指定老师所授选修课的信息，并向调用对象返回一个消息，表明查询结果。,实例分析三,分析问题步骤1：确定使用RETURN返回单个值，作为输出消息步骤2：创建存储过程步骤3：执行存储过程，验证其功能,实例分析三,问题求解RETURN语句的语法为：RETURN integer_expression步骤1：确定使用RETURN返回单个值，作为输出消息RETURN 1表示查询成功，找到所需记录RETURN 0表示查询失败，没有发现有效记录,实例分析三,问题求解步骤2：创建存储过程,CREATE PROCEDURE prcListClassesByTeacherName(classTeacherName varchar(16)ASBEGINIF exists(SELECT * FROM COURSEWHERE classTeacherName=classTeacherName)BEGINRETURN 1 ENDELSEBEGINRETURN 0ENDEND,实例分析三,问题求解步骤3：执行存储过程，验证其功能DECLARE result intEXEC result = prcListClassesByTeacherName 朱紫英 IF(result=1) PRINT 存在相关信息 ELSE PRINT 没有相关信息注 意：RETURN 不能返回NULL。若试图返回NULL，将生成警告信息并返回 0,存储过程的高级应用,存储过程的高级应用包括：存储过程间的调用在存储过程中使用游标,实例分析四：存储过程间的调用,提出问题现在需要建立一个存储过程，此存储过程在场景一的基础上能够查询查看多门课程的相关信息。,实例分析四,分析问题步骤1：创建调用现有存储过程的存储过程步骤2：执行存储过程，验证其功能,实例分析四,问题求解步骤1：创建调用现有存储过程的存储过程注 意：从一个存储过程调用另一个存储过程必须使用EXEC关键字,CREATE PROC prcListMultiClassesASBEGINEXEC prcListClassesByCourseName 大学英语EXEC prcListClassesByCourseName 软件工程END,实例分析五：在存储过程中使用游标,提出问题在BigCollege选课系统中，要求一个学期结束后，教务人员能够根据授课老师给学生的分数，给及格的学生的总学分加上该门课程的学分。,实例分析五,分析问题步骤1：确定一种遍历结果集方法步骤2：创建存储过程步骤3：执行存储过程，验证其功能,实例分析五,问题求解步骤1：确定一种遍历结果集方法为了判断学生成绩是否及格，继而修改学生的总学分，需要在存储过程中遍历和处理结果集中的数据，可以使用游标来完成此任务,实例分析五,问题求解步骤2：创建存储过程,本章小结,存储过程的概念存储过程的不同类型：用户自定义系统临时远程扩展存储过程的优点存储过程的执行过程和原理用CREATE PROCEDURE语句来创建简单和复杂存储过程,本章小结,存储过程通过以下方式接受和返回数据：输入参数输出参数返回语句使用EXECUTE语句可执行存储过程用ALTER PROCEDURE语句来修改存储过程使用sp_help 和sp_helptext查看存储过程存储过程间调用及结合游标实现更为复杂的功能,
