存储过程和用户存储过程设计.ppt

《存储过程和用户存储过程设计.ppt》由会员分享，可在线阅读，更多相关《存储过程和用户存储过程设计.ppt（38页珍藏版）》请在三一办公上搜索。

1、1,浙江财经学院,第12章 存储过程和用户存储过程设计,12.1 存储过程概述 12.2 系统存储过程12.3 创建和执行用户存储过程12.4 带状态参数的存储过程及实例分析12.5 修改和删除存储过程12.6 存储过程设计实例分析,2,浙江财经学院,12.1 存储过程概述,12.1.1 存储过程的概念和分类 SQL Server提供了一种方法，它可以将一些固定的操作集中起来由SQL Server数据库服务器来完成，以实现某个任务，这种方法就是存储过程。存储过程（Stored Procedure）是一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中。用户或应用程序通过指定存储过程的名

2、字并给出参数来执行它，而且允许用户声明变量、有条件执行以及其它强大的编程功能。在SQL Server中存储过程分为两类：即系统提供的存储过程和用户自定义的存储过程。,3,浙江财经学院,12.1.2 存储过程的优点,（1）存储过程允许标准组件式编程 存储过程在被创建以后可以在程序中被多次调用，而不必重新编写该存储过程的SQL 语句。而且数据库专业人员可随时对存储过程进行修改，但对应用程序源代码毫无影响（因为应用程序源代码只包含存储过程的调用语句），从而极大地提高了程序的可移植性。（2）存储过程能够实现较快的执行速度 如果某一操作包含大量的Transaction-SQL 代码或分别被多次执行，那么

3、存储过程要比批处理的执行速度快很多。,4,浙江财经学院,12.1.2 存储过程的优点,（3）存储过程能够减少网络流量 对于同一个针对数据数据库对象的操作（如查询、修改），当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大增加了网络流量，降低网络负载。（4）存储过程可被作为一种安全机制来充分利用系统管理员通过对执行某一存储过程的权限进行限制，从而能够实现对相应的数据访问权限的限制，避免非授权用户对数据的访问，保证数据的安全。（5）自动完成需要预先执行的任务.存储过程可以在系统启动时自动执行,而不必在系统启动后再进行手工操作,大大方便了用户的使用,可以自动完成一些需要预先执行的

4、任务。,5,浙江财经学院,12.2 系统存储过程,12.2.1 系统存储过程分类 系统存储过程就是系统创建的存储过程，目的在于能够方便地从系统表中查询信息或完成与更新数据库表相关的管理任务或其它的系统管理任务。系统过程以“sp_开头,在Master数据库中创建并保存在该数据库中，为数据库管理者所有。一些系统过程只能由系统管理员使用，而有些系统过程通过授权可以被其它用户所使用。,6,浙江财经学院,12.2.2 一些常用的系统存储过程,7,浙江财经学院,8,浙江财经学院,12.2 系统存储过程,例如，sp_helpdb系统存储过程，其功能是：报告有关指定数据库或所有数据库的信息。语法：sp_hel

5、pdb dbname=数据库名【例】返回有关所有数据库的信息 exec sp_helpdb,9,浙江财经学院,12.3.1 创建用户存储过程,用户自定义存储过程是由用户创建并能完成某一特定功能（如查询用户所需数据信息）的存储过程。在SQL Server中，可以使用两种方法创建存储过程：利用SQL Server对象资源管理器创建存储过程。使用Transact-SQL语句中的CREATE PROCEDURE命令创建存储过程。创建存储过程时，需要确定存储过程的三个组成部分：所有的输入参数以及传给调用者的输出参数。被执行的针对数据库的操作语句，包括调用其它存储过程的语句。返回给调用者的状态值，以指明调

6、用是成功还是失败。,10,浙江财经学院,1使用SQL Server对象资源管理器创建存储过程 在SQL Server对象资源管理器中，选择指定的服务器和数据库，在可编程性里选择存储过程，用右键单击，在弹出的快捷菜单中选择“新建存储过程”选项，如图12-1所示。,图12-1 选择向导,11,浙江财经学院,2使用Transact-SQL语句命令创建存储过程创建存储过程前，应该考虑下列几个事项：在一个批处理中，Create Procedure 语句不能与其它SQL语句合并在一起；创建存储过程的权限默认属于数据库所有者，该所有者可将此权限授予其他用户。存储过程是数据库对象，其名称必须遵守标识符规则。只

7、能在当前数据库中创建当前数据库的存储过程。一个存储过程的最大尺寸为128M。,12,浙江财经学院,语法格式：CREATE PROCEDURE 存储过程名(参数名 数据类型 VARYING=default OUTPUT，)WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION FOR REPLICATION AS SQL语句,13,浙江财经学院,生疏参数说明：VARYING：指定由OUTPUT参数支持的结果集，仅应用于游标型参数。OUTPUT：用来声明存储过程的返回参数，其值可以返回给调用它的EXECUTE语句。WITH RECOMPILE：要求SQL Se

8、rver不要在缓存中保存存储过程的执行计划，而在每次执行时都重新对它进行编译。WITH ENCRYPTION：要求对存储在syscomments系统表中的存储过程定义文本进行加密。FOR REPLICATION：说明该存储过程只能在复制过程中执行，不能与WITH RECOMPILE同时使用。,14,浙江财经学院,【例】创建一个简单的存储过程，返回所有学生的基本信息，包括学生的学号、姓名、所学专业、所在二级学院、来自的城市。USE 教学管理IF EXISTS(SELECT*FROM sysobjects WHERE name=P_学生部分信息 AND type=p)BEGIN DROP PROC

9、EDURE P_学生部分信息 ENDGOCREATE PROCEDURE P_学生部分信息 AS SELECT 学号,姓名,专业,所在院系,籍贯 FROM 学生表GO,15,浙江财经学院,USE 教学管理IF EXISTS(SELECT*FROM sysobjects WHERE name=P_学生选课信息 AND type=p)BEGIN DROP PROCEDURE P_学生选课信息 ENDGOCREATE PROCEDURE P_学生选课信息(专业 CHAR(20),课名 CHAR(20)AS SELECT S.学号,姓名,专业,所在院系,O.课号,课名,成绩 FROM 学生表 S,选课

10、表 E,开课表 O,课程表 C where 专业=专业 AND 课名=课名 AND S.学号=E.学号 AND E.开课号=O.开课号 AND O.课号=C.课号GO,【例】创建带参数的存储过程，实现对指定的某一专业某门课程的学生选课信息和成绩的查询。,16,浙江财经学院,执行已创建的存储过程使用EXECUTE命令语法格式：EXEC UTE return_status=;版本号|procedure_name_var 参数=value|variable OUTPUT|DEFAULT,.n WITH RECOMPILE,17,浙江财经学院,【例】对存储过程P_学生部分信息的执行。EXEC P_学生

11、部分信息,【例】带输入参数的存储过程P_学生选课信息的执行。（1）按参数位置传递值,或者：,DECLARE smajor CHAR(20),cname CHAR(20)SET smajor=计算机SET cname=数据结构EXEC P_学生选课信息 smajor,cname,或者：,DECLARE smajor CHAR(20)SET smajor=计算机EXEC P_学生选课信息 smajor,数据结构,Exec P_学生选课信息 计算机,数据结构,18,浙江财经学院,（2）按参数名传递值 Exec P_学生选课信息 SMAJOR=计算机,CNAME=数据结构 按参数名传递值可以改变参数的

12、顺序：Exec P_学生选课信息 CNAME=数据结构,SMAJOR=计算机,19,浙江财经学院,（3）也可以两种方法混合使用，一旦使用了 name=value 形式之后，所有后续的参数就必须以 name=value 的形式传递。比如：Exec P_学生选课信息 计算机,CNAME=数据结构 但是：如果按如下命令执行P_学生选课信息存储过程，系统将提示出错信息。Exec P_学生选课信息 CNAME=计算机,数据结构 服务器:消息 119，级别 15，状态 1，行 1,20,浙江财经学院,【例】使用OUTPUT参数的存储过程及其执行,USE 教学管理GOIF EXISTS(SELECT*FRO

13、M sysobjects WHERE name=P_成绩检索和平均 AND type=p)BEGIN DROP PROCEDURE P_成绩检索和平均 ENDGOCREATE PROCEDURE P_成绩检索和平均(学号 CHAR(7),平均成绩 FLOAT OUTPUT)ASSELECT S.学号,姓名,课号,成绩FROM 学生表 S,开课表 O,选课表 EWHERE S.学号=学号AND E.学号=S.学号 AND E.开课号=O.开课号,21,浙江财经学院,SELECT 平均成绩=AVG(成绩)FROM 学生表 S,开课表 O,选课表 EWHERE S.学号=学号 AND E.学号=S.

14、学号 AND E.开课号=O.开课号RETURNGo-然后在查询分析器中调用 P_成绩检索和平均 存储过程DECLARE 学号 CHAR(7),平均成绩 FLOATSET 学号=S060102EXEC P_成绩检索和平均 学号,平均成绩 OUTPUT,22,浙江财经学院,IF 平均成绩=90 SELECT 该学生的成绩=优秀,平均成绩=rtrim(cast(平均成绩 as VARCHAR(20)IF 平均成绩=80 AND 平均成绩=70 AND 平均成绩=60 AND 平均成绩 70 SELECT 该学生的成绩=及格,平均成绩=rtrim(cast(平均成绩 as VARCHAR(20)IF

15、 平均成绩 60 SELECT 该学生的成绩=不及格,平均成绩=rtrim(cast(平均成绩 as VARCHAR(20),23,浙江财经学院,运行结果为：学号 姓名 课号 成绩 1 S060102 张小芬 C01001 93.02 S060102 张小芬 C01003 NULL3 S060102 张小芬 C02001 NULL该学生成绩 平均成绩 优秀 93,24,浙江财经学院,12.4 带状态参数的存储过程及实例分析,12.4.1 存储过程执行状态值的返回（1）系统自动返回 执行存储过程，总要返回一个结果码，用以指示存储过程的执行状态。如果执行成功，返回的结果码是0；如果执行失败，返回的

16、结果码目前是一个0 到-14的负数。可按以下语法执行存储过程并用return_status接收状态值即可：EXECUTE return_status=procedure_name（2）用RETURN语句 RETURN语句的功能是：从查询或过程中无条件退出。不执行位于 RETURN 之后的语句。语法格式：RETURN integer_expression,25,浙江财经学院,【例】定义存储过程，状态值返回过程执行成功的默认值,CREATE PROCEDURE P_查询教师开课(姓名 CHAR(10)=NULL)AS IF 姓名 IS NULL BEGIN PRINT 必须指定教师姓名 ENDEL

17、SE BEGIN SELECT T.工号,姓名,所在院系,职称,O.开课号,O.课号,课名 FROM 教师表 T,开课表 O,课程表 C WHERE T.工号=O.工号 AND O.课号=C.课号 AND T.姓名=姓名 END,26,浙江财经学院,调用上述存储过程,1、给出调用参数曲宏伟DECLARE status_value smallintEXECUTE status_value=P_查询教师开课 曲宏伟 SELECT 状态值=status_value 2、不给出调用参数DECLARE status_value smallintEXECUTE status_value=P_查询教师开课

18、SELECT 状态值=status_value 结果：1、有查询结果，成功，状态值=0 2、没有查询结果，但在程序中设定了没有输入参数的处理，也成功，状态值也=0,27,浙江财经学院,【例】用RETURN语句返回自己定义的状态代码 以下存储过程检查指定学生的平均成绩。如果执行存储过程时没有输入学生学号，则返回状态码1；如果所查询的学生不存在，则返回状态码2。CREATE PROCEDURE P_计算平均成绩(学号 CHAR(7),平均成绩 TINYINT OUTPUT)ASIF 学号 IS NULL RETURN 1IF NOT EXISTS(SELECT*FROM 选课表 WHERE 学号=

19、学号)RETURN 2SELECT 平均成绩=avg(成绩)FROM 选课表WHERE 学号=学号,28,浙江财经学院,调用上述存储过程 第一个显示的是没有输入学生学号 DECLARE return_status INT,平均成绩 FLOATEXEC return_status=P_计算平均成绩 NULL,平均成绩 OUTSELECT 返回状态=return_status第二个显示的是输入学生学号的查询结果 DECLARE return_status INT,平均成绩 FLOAT,学号 CHAR(7)SET 学号=S060103EXEC return_status=P_计算平均成绩 学号,平均

20、成绩 OUTSELECT 返回状态=return_status第三个输入无效学号DECLARE return_status INT,平均成绩 FLOAT,学号 CHAR(7)SET 学号=S060205EXEC return_status=P_计算平均成绩 学号,平均成绩 OUTSELECT 返回状态=return_status 结果：一、状态=1，二、状态=0，三、状态=2,29,浙江财经学院,12.5 修改和删除存储过程,12.5.1 修改存储过程 修改以前用CREATE PROCEDURE命令创建的存储过程，并且不改变权限的授予情况以及不影响任何其它的独立的存储过程或触发器，常使用ALT

21、ER PROCEDURE 命令。语法格式：ALTER PROC EDURE 存储过程名；版本号(参数名 数据类型 VARYING=default OUTPUT，)WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION FOR REPLICATION AS SQL语句,30,浙江财经学院,修改“P_计算平均成绩”存储过程,ALTER PROCEDURE P_计算平均成绩(学号 CHAR(7),平均成绩 TINYINT OUTPUT)WITH ENCRYPTION-修改增加的，原过程没有。ASIF 学号 IS NULL RETURN 1IF NOT EXIST

22、S(SELECT*FROM 选课表 WHERE 学号=学号)RETURN 2SELECT 平均成绩=avg(成绩)FROM 选课表WHERE 学号=学号GO,31,浙江财经学院,12.5.2 删除存储过程,删除存储过程可以使用DROP命令，DROP命令可以将一个或者多个存储过程或者存储过程组从当前数据库中删除。语法格式：drop procedure 存储过程名,n 当然，利用企业管理器也可以很方便地删除存储过程。例如：drop procedure P_学生部分信息 go,32,浙江财经学院,12.6 存储过程设计实例分析,回顾第11章提出的应用实例，在学生选课管理中，为了维护数据的一致性，必须

23、保证SGPA的数量应该等于学生所选的所有成绩已经及格的课程的学分总数，需逐个检查并修改信息学院每个学生的学分获取情况。考虑到每门课程的学分获得条件以及数据库数据存放特点，学生学分获取情况的修改与检查是一件复杂的工作，不能由简单的查询完成，我们通过游标来逐个检查所有学生学分获取情况及定位修改。但是游标在定义它的批处理结束便离开作用域，故我们将设计一个使用游标的存储过程，完成该项工作。该存储过程，在需要时可以进行多次调用执行。,33,浙江财经学院,【例】完成对选课表的元组插入工作 要求检查所插入数据是否满足实体完整性和参照完整性，而且由于每个学生不能重复选同一门课，但是在选课表中存放的是开课计划号

24、，并且同一门课程可能有多个开课计划，所以还必须对所选课程是否重复进行检查。解决思路：我们通过定义存储过程来实现，该过程中定义两个个输入变量（学号和课程计划号），用来传递元组的属性值，在完成插入操作前，进行检查是否满足实体完整性和参照完整性，确保不重复选修某门课程，并返回状态值。,34,浙江财经学院,USE 教学管理GOIF EXISTS(SELECT name FROM sysobjects WHERE name=P_选课信息插入 AND type=P)DROP PROCEDURE P_选课信息插入GO-创建存储过程CREATE PROCEDURE P_选课信息插入(学号 CHAR(7),开课

25、号 CHAR(6)AS BEGINDECLARE 课号 CHAR(6)IF EXISTS(SELECT*FROM 选课表WHERE 学号=学号 AND 开课号=开课号)RETURN 1,35,浙江财经学院,IF NOT EXISTS(SELECT*FROM 学生表WHERE 学号=学号)RETURN 2IF NOT EXISTS(SELECT*FROM 开课表WHERE 开课号=开课号)RETURN 3 SELECT 课号=课号 FROM 开课表 WHERE 开课号=开课号 IF EXISTS(SELECT*FROM 选课表E,开课表OWHERE 学号=学号AND 课号=课号AND E.开课号

26、=O.开课号)RETURN 4,36,浙江财经学院,INSERT INTO 选课表(学号,开课号)VALUES(学号,开课号)END-调用存储过程，插入选课登记信息DECLARE return_status INT,学号 CHAR(7),开课号 CHAR(6)SET 学号=S060101SET 开课号=010201EXEC return_status=P_选课信息插入学号,开课号IF(return_status=1)PRINT 该选课数据重复！IF(return_status=2)PRINT 学生不存在！IF(return_status=3)PRINT 课程计划不存在！IF(return_status=4)PRINT 该课程已注册选修！IF(return_status=0)PRINT 选课登记信息插入成功！GO,37,浙江财经学院,小 结,存储过程是一组实现特定功能的Transact SQL 语句集，经编译后存储在数据库中，它与其他程序设计语言中的过程类似，也可以接受输入参数，以参数形式返回输出值，或者返回成功、失败的状态信息。每次存储过程运行时，无须重新编译（除非强制要求）。本章主要讨论了存储过程的创建、修改、执行和管理，并列举了大量的示例，最后对两个应用实例进行分析。,38,浙江财经学院,Thank you very much!,谢谢您的光临！,下一章,