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

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 语句集，经编译后存储在数据库中。 用户或应用程序通过指定存储过程的名字并给出参数来执行它，而且允许用户声明变量、有条件执行以及其它强大的编程功能。 在SQL Server中存储过程分为两类：即系统提供的存储过程和用户自定义的存储过程。,3,浙江财经学院,12.1.2 存储过程的优点,（1）存储过程允许标准组件式编程 存储过程在被创建以后可以在程序中被多次调用，而不必重新编写该存储过程的SQL 语句。而且数据库专业人员可随时对存储过程进行修改，但对应用程序源代码毫无影响（因为应用程序源代码只包含存储过程的调用语句），从而极大地提高了程序的可移植性。 （2）存储过程能够实现较快的执行速度 如果某一操作包含大量的Transaction-SQL 代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。,4,浙江财经学院,12.1.2 存储过程的优点,（3）存储过程能够减少网络流量 对于同一个针对数据数据库对象的操作（如查询、修改），当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大增加了网络流量，降低网络负载。 （4）存储过程可被作为一种安全机制来充分利用系统管理员通过对执行某一存储过程的权限进行限制，从而能够实现对相应的数据访问权限的限制，避免非授权用户对数据的访问，保证数据的安全。 （5）自动完成需要预先执行的任务.存储过程可以在系统启动时自动执行,而不必在系统启动后再进行手工操作,大大方便了用户的使用,可以自动完成一些需要预先执行的任务。,5,浙江财经学院,12.2 系统存储过程,12.2.1 系统存储过程分类 系统存储过程就是系统创建的存储过程，目的在于能够方便地从系统表中查询信息或完成与更新数据库表相关的管理任务或其它的系统管理任务。 系统过程以“sp_“开头,在Master数据库中创建并保存在该数据库中，为数据库管理者所有。一些系统过程只能由系统管理员使用，而有些系统过程通过授权可以被其它用户所使用。,6,浙江财经学院,12.2.2 一些常用的系统存储过程,7,浙江财经学院,8,浙江财经学院,12.2 系统存储过程,例如，sp_helpdb系统存储过程，其功能是：报告有关指定数据库或所有数据库的信息。 语法： sp_helpdb dbname= 数据库名 【例】返回有关所有数据库的信息 exec sp_helpdb,9,浙江财经学院,12.3.1 创建用户存储过程,用户自定义存储过程是由用户创建并能完成某一特定功能（如查询用户所需数据信息）的存储过程。在SQL Server中，可以使用两种方法创建存储过程： 利用SQL Server对象资源管理器创建存储过程。 使用Transact-SQL语句中的CREATE PROCEDURE命令创建存储过程。 创建存储过程时，需要确定存储过程的三个组成部分： 所有的输入参数以及传给调用者的输出参数。 被执行的针对数据库的操作语句，包括调用其它存储过程的语句。 返回给调用者的状态值，以指明调用是成功还是失败。,10,浙江财经学院,1使用SQL Server对象资源管理器创建存储过程 在SQL Server对象资源管理器中，选择指定的服务器和数据库，在可编程性里选择存储过程，用右键单击，在弹出的快捷菜单中选择“新建存储过程”选项，如图12-1所示。,图12-1 选择向导,11,浙江财经学院,2使用Transact-SQL语句命令创建存储过程 创建存储过程前，应该考虑下列几个事项： 在一个批处理中，Create Procedure 语句不能与其它SQL语句合并在一起； 创建存储过程的权限默认属于数据库所有者，该所有者可将此权限授予其他用户。 存储过程是数据库对象，其名称必须遵守标识符规则。 只能在当前数据库中创建当前数据库的存储过程。 一个存储过程的最大尺寸为128M。,12,浙江财经学院,语法格式： CREATE PROCEDURE 存储过程名 ( 参数名 数据类型 VARYING =default OUTPUT ，) WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION FOR REPLICATION AS SQL语句,13,浙江财经学院,生疏参数说明： VARYING：指定由OUTPUT参数支持的结果集，仅应用于游标型参数。 OUTPUT：用来声明存储过程的返回参数，其值可以返回给调用它的EXECUTE语句。 WITH RECOMPILE：要求SQL Server不要在缓存中保存存储过程的执行计划，而在每次执行时都重新对它进行编译。 WITH ENCRYPTION：要求对存储在syscomments系统表中的存储过程定义文本进行加密。 FOR REPLICATION：说明该存储过程只能在复制过程中执行，不能与WITH RECOMPILE同时使用。,14,浙江财经学院,【例】创建一个简单的存储过程，返回所有学生的基本信息，包括学生的学号、姓名、所学专业、所在二级学院、来自的城市。 USE 教学管理 IF EXISTS(SELECT * FROM sysobjects WHERE name=P_学生部分信息 AND type=p) BEGIN DROP PROCEDURE P_学生部分信息 END GO CREATE PROCEDURE P_学生部分信息 AS SELECT 学号,姓名,专业,所在院系,籍贯 FROM 学生表 GO,15,浙江财经学院,USE 教学管理 IF EXISTS(SELECT* FROM sysobjects WHERE name=P_学生选课信息 AND type=p) BEGIN DROP PROCEDURE P_学生选课信息 END GO CREATE PROCEDURE P_学生选课信息(专业 CHAR(20), 课名 CHAR(20) AS SELECT S.学号,姓名,专业,所在院系,O.课号,课名,成绩 FROM 学生表 S, 选课表 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_学生部分信息,【例】带输入参数的存储过程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=数据结构 按参数名传递值可以改变参数的顺序： Exec P_学生选课信息 CNAME=数据结构, SMAJOR=计算机,19,浙江财经学院,（3）也可以两种方法混合使用，一旦使用了 name = value 形式之后，所有后续的参数就必须以 name = value 的形式传递。 比如： Exec P_学生选课信息 计算机, CNAME=数据结构 但是： 如果按如下命令执行P_学生选课信息存储过程，系统将提示出错信息。 Exec P_学生选课信息 CNAME =计算机, 数据结构 服务器: 消息 119，级别 15，状态 1，行 1,20,浙江财经学院,【例】使用OUTPUT参数的存储过程及其执行,USE 教学管理 GO IF EXISTS(SELECT* FROM sysobjects WHERE name=P_成绩检索和平均 AND type=p) BEGIN DROP PROCEDURE P_成绩检索和平均 END GO CREATE PROCEDURE P_成绩检索和平均 (学号 CHAR(7), 平均成绩 FLOAT OUTPUT) AS SELECT S.学号,姓名,课号,成绩 FROM 学生表 S, 开课表 O, 选课表 E WHERE S.学号=学号 AND E.学号=S.学号 AND E.开课号=O.开课号,21,浙江财经学院,SELECT 平均成绩 =AVG(成绩) FROM 学生表 S, 开课表 O, 选课表 E WHERE S.学号=学号 AND E.学号=S.学号 AND E.开课号=O.开课号 RETURN Go -然后在查询分析器中调用 P_成绩检索和平均 存储过程 DECLARE 学号 CHAR(7),平均成绩 FLOAT SET 学号=S060102 EXEC 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 平均成绩 60 SELECT 该学生的成绩=不及格,平均成绩=rtrim(cast(平均成绩 as VARCHAR(20),23,浙江财经学院,运行结果为： 学号 姓名 课号 成绩 1 S060102 张小芬 C01001 93.0 2 S060102 张小芬 C01003 NULL 3 S060102 张小芬 C02001 NULL 该学生成绩 平均成绩 优秀 93,24,浙江财经学院,12.4 带状态参数的存储过程及实例分析,12.4.1 存储过程执行状态值的返回 （1）系统自动返回 执行存储过程，总要返回一个结果码，用以指示存储过程的执行状态。如果执行成功，返回的结果码是0；如果执行失败，返回的结果码目前是一个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 必须指定教师姓名 END ELSE BEGIN SELECT T.工号,姓名,所在院系,职称,O.开课号,O.课号,课名 FROM 教师表 T,开课表 O, 课程表 C WHERE T.工号=O.工号 AND O.课号=C.课号 AND T.姓名=姓名 END,26,浙江财经学院,调用上述存储过程,1、给出调用参数曲宏伟 DECLARE status_value smallint EXECUTE status_value=P_查询教师开课 曲宏伟 SELECT 状态值=status_value 2、不给出调用参数 DECLARE status_value smallint EXECUTE status_value=P_查询教师开课 SELECT 状态值=status_value 结果： 1、有查询结果，成功，状态值=0 2、没有查询结果，但在程序中设定了没有输入参数的处理，也成功， 状态值也=0,27,浙江财经学院,【例】用RETURN语句返回自己定义的状态代码 以下存储过程检查指定学生的平均成绩。如果执行存储过程时没有输入学生学号，则返回状态码1；如果所查询的学生不存在，则返回状态码2。 CREATE PROCEDURE P_计算平均成绩 (学号 CHAR(7),平均成绩 TINYINT OUTPUT) AS IF 学号 IS NULL RETURN 1 IF NOT EXISTS (SELECT * FROM 选课表 WHERE 学号=学号) RETURN 2 SELECT 平均成绩=avg(成绩) FROM 选课表 WHERE 学号=学号,28,浙江财经学院,调用上述存储过程 第一个显示的是没有输入学生学号 DECLARE return_status INT,平均成绩 FLOAT EXEC return_status = P_计算平均成绩 NULL, 平均成绩 OUT SELECT 返回状态 = return_status 第二个显示的是输入学生学号的查询结果 DECLARE return_status INT,平均成绩 FLOAT,学号 CHAR(7) SET 学号=S060103 EXEC return_status = P_计算平均成绩 学号, 平均成绩 OUT SELECT 返回状态 = return_status 第三个输入无效学号 DECLARE return_status INT,平均成绩 FLOAT,学号 CHAR(7) SET 学号=S060205 EXEC return_status = P_计算平均成绩 学号, 平均成绩 OUT SELECT 返回状态 = return_status 结果：一、状态=1，二、状态=0，三、状态=2,29,浙江财经学院,12.5 修改和删除存储过程,12.5.1 修改存储过程 修改以前用CREATE PROCEDURE命令创建的存储过程，并且不改变权限的授予情况以及不影响任何其它的独立的存储过程或触发器，常使用ALTER 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 -修改增加的，原过程没有。 AS IF 学号 IS NULL RETURN 1 IF NOT EXISTS (SELECT * FROM 选课表 WHERE 学号=学号) RETURN 2 SELECT 平均成绩=avg(成绩) FROM 选课表 WHERE 学号=学号 GO,31,浙江财经学院,12.5.2 删除存储过程,删除存储过程可以使用DROP命令，DROP命令可以将一个或者多个存储过程或者存储过程组从当前数据库中删除。 语法格式： drop procedure 存储过程名 ,n 当然，利用企业管理器也可以很方便地删除存储过程。 例如： drop procedure P_学生部分信息 go,32,浙江财经学院,12.6 存储过程设计实例分析,回顾第11章提出的应用实例，在学生选课管理中，为了维护数据的一致性，必须保证SGPA的数量应该等于学生所选的所有成绩已经及格的课程的学分总数，需逐个检查并修改信息学院每个学生的学分获取情况。考虑到每门课程的学分获得条件以及数据库数据存放特点，学生学分获取情况的修改与检查是一件复杂的工作，不能由简单的查询完成，我们通过游标来逐个检查所有学生学分获取情况及定位修改。但是游标在定义它的批处理结束便离开作用域，故我们将设计一个使用游标的存储过程，完成该项工作。该存储过程，在需要时可以进行多次调用执行。,33,浙江财经学院,【例】完成对选课表的元组插入工作 要求检查所插入数据是否满足实体完整性和参照完整性，而且由于每个学生不能重复选同一门课，但是在选课表中存放的是开课计划号，并且同一门课程可能有多个开课计划，所以还必须对所选课程是否重复进行检查。 解决思路：我们通过定义存储过程来实现，该过程中定义两个个输入变量（学号和课程计划号），用来传递元组的属性值，在完成插入操作前，进行检查是否满足实体完整性和参照完整性，确保不重复选修某门课程，并返回状态值。,34,浙江财经学院,USE 教学管理 GO IF EXISTS(SELECT name FROM sysobjects WHERE name = P_选课信息插入 AND type = P) DROP PROCEDURE P_选课信息插入 GO -创建存储过程 CREATE PROCEDURE P_选课信息插入(学号 CHAR(7),开课号 CHAR(6) AS BEGIN DECLARE 课号 CHAR(6) IF EXISTS(SELECT * FROM 选课表 WHERE 学号=学号 AND 开课号=开课号) RETURN 1,35,浙江财经学院