第9章 存储过程的创建与使用课件.ppt

SQL SERVER应应 用用 与与 开开 发发计算机工程技术学院（软件学院） 任淑美二00七年十一月（九）（九）本课主要内容本课主要内容 存贮过程概述创建存储过程执行存储过程查看和修改存储过程常用的系统存储过程本课核心知识点本课核心知识点利用T-SQL创建存储过程存贮过程的执行存贮过程的重新编译 本课重点难点本课重点难点在存贮过程中使用参数重新编译存贮过程 本课教学目的本课教学目的了解存储过程的概念和优点,能够在您的实际应用中设计合适的存储过程理解存储过程的类型，掌握一定数量的系统存储过程掌握如何创建和执行存储过程。掌握管理存储过程的方法。 第第9章章 存储过程的创建和使用存储过程的创建和使用 顾名思义，存储过程就是存储在SQL SERVER服务器中的一组编译成单个执行计划的T-SQL语句。 9.1 概述概述 存储过程存储在数据库内，可由应用程序通过调用执行。使用存储过程不但可以提高T-SQL的执行效率，而且可以使对数据库的管理、以及实现应用复杂的业务更客易。存储过程也可看作是一种没有返回值的特殊函数，它与函数的区别是：函数有返回值，其返回值可以直接在表达式、计算列中使用；而存储过程只能调用执行，它不能通过名称得到返回值，也不能直接在表达式、计算列中使用。 9.1.1 理解存储过程的概念理解存储过程的概念 存储过程是SQL语句和可选控制流语句的预编译集合，以一个名称存储并作为一个单元处理，其中可包含程序流、逻辑以及对数据库的查询，存储过程可以包含一条SELECT语句，也可包含一系列使用控制流的SQL语句，如图9-1所示。它们还可接受参数、返回输出参数、返回状态值，主要用来执行管理任务或实现应用复杂的业务规则。 。9.1.2 理解存储过程的优点理解存储过程的优点 使用存储过程与本地的Transact-SQL程序相比有如下好处： 1简化用户操作存储过程将一系列复杂的T-SQL代码封装在一起，作为数据库对象存放于数据库服务器内，用户使用时不必思考复杂的T-SQL语句而只需调用存储过程即可得到所需结果，简化了用户操作。2执行效率更高存储过程已被编译，执行时不再花费编译与优化时间。另外，一个存储过程在执行一次之后，其执行规划就被自动缓存到内存中，以后使用时就可直接从内存中获取。这不同于T-SQL语句，每次运行时都要从客户端重复发送，并由SQL SERVER进行编译和优化。在需要大量T-SQL代码或需重复执行时，存储过程比T-SQL批代码的执行要快。 3减少网络流量特别对于复杂的商业业务计算，例如计算用户的每月应交电费，如采用T-SQL实现，则需要大量的T-SQL语句和中间数据在客户机与SQL SERVER服务器之间的网络通道上传送；采用存储过程实现，则只需由一条执行存储过程的单独语句就可实现，大大减少网络流量。4提高系统安全性可作为安全机制使用。通过适当的权限设置，可以使系统的安全性得到有效保障。5允许模块化程序设计存储过程可以封装企业的功能模块，这种企业的功能模块也为商业规则或者商业策赂，可以只创建一次并将其存储在数据库中，以后即可在程序中调用该过程任意次，而且可以统一修改。9.1.3 掌握存储过程的分类掌握存储过程的分类 存储过程分为两大类：系统存储过程和用户自定义存储过程。系统存储过程：由系统定义的存储过程，存放在master数据库中，类似C语言中的系统函数。系统存储过程的名称都以“sp_”开头或”xp_”开头，主要用于系统管理、登录管理、查看数据库信息、数据复制等操作。用户自定义存储过程：由用户在自己的数据库中创建的存储过程，类似C语言中的。用户自定义函数。可根据不同的分类标准予以分类：（1）临时存储过程。是以存储过程名、开头的存储过程。其生命周期与一般的局部临时变量、全局临时变量的生命周期相同。（2）本地存储过程。用户创建并存放在用户当前连接的SQL SERVER数据库中的存储过程, 是我们通常意义上的存储过程。（3）扩展存储过程。是利用高级语言（如DELPHI和C+等）编写的存储过程。实际是包含在一个WIn32.DLL中的一个函数。只有那些可以独立创建DLL和EXE文件的高级程序语言可用来创建扩展存储过程。 9.2 创建存储过程创建存储过程 在当前数据库创建的用户自定义本地存储过程，不包括扩展存储过程。创建存储过程的方法共有三种：n 使用企业管理器n 使用T-SQL语句n 使用创建存储过程向导9.2.1 利用企业管理器创建存储过程利用企业管理器创建存储过程 可使用企业管理器创建存储过程。（1）启动SQL SERVER企业管理器，依次单击控制台树上的【服务器组】【服务器】【数据库】【要创建存储过程的数据库】右击【存储过程】，在弹出的快捷菜单中单击【新建存储过程】，如图所示。（2）弹出存储过程属性对话框，如图所示。（3）在“文本”框中输入存储过程。包括修改存储过程名, 并在”AS”后输入存储过程内容。（4）单击【检查语法】按钮，检查所输入的SQL语句语法的是否正确。（5）单击【确定】按钮，创建该存储过程。9.2.2 使用使用Transact-SQL语言创建存储过程语言创建存储过程用CREATE PROCEDURE语句创建存储过程。语法格式语法格式:CREATE PROC EDURE 存储过程名 ; number 参数 数据类型 VARYING = 默认值 OUTPUT ,.n WITH RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION FOR REPLICATION AS Sql语句 .n 参数说明：参数说明：存储过程名：新存储过程的名称。;number：是可选的整数，用来对同名的过程分组，以便用一条 DROP PROCEDURE 语句即可将同组的过程一起除去。默认值：参数的默认值。如果定义了默认值，不必指定该参数的值即可执行过程。OUTPUT：表明参数是返回参数。将信息返回给调用过程。Text、ntext 和 image 参数可用作 OUTPUT 参数。N：表示最多可以指定 2.100 个参数的占位符。RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION：RECOMPILE： 表明 SQL Server 不会缓存该过程的计划，该过程将在运行时重新编译。在使用非典型值或临时值而不希望覆盖缓存在内存中的执行计划时，请使用 RECOMPILE 选项。ENCRYPTION： 表示 SQL Server 加密 syscomments 表中包含 CREATE PROCEDURE 语句文本的条目。使用 ENCRYPTION 可防止将过程作为 SQL Server 复制的一部分发布。FOR REPLICATION：指定不能在订阅服务器上执行为复制创建的存储过程。.使用 FOR REPLICATION 选项创建的存储过程可用作存储过程筛选，且只能在复制过程中执行。本选项不能和 WITH RECOMPILE 选项一起使用。1创建带有复杂 SELECT 语句的简单过程【例9-1】创建存储过程，返回所有帖子信息。这些帖子信息要求显示编号的意义。如：发帖人不使用用户编号，而使用发帖人的昵称；帖子所在版块不使用版块编号，而显示版块名。 【分析】：（1）使用CREATE PROCEDURE语句创建存储过程，该存储过程无输入参数、输出参数。 （2）帖子信息存放于TOPIC表，发帖人信息存放于USERS表，版块信息存放于SECTION表, 三张表作联接查询。代码如下：USE bbsdbGOCREATE PROCEDURE p_topic_all ASSELECT TID, TSID, Users.UName, TReplyCount, TEmotion, TTopic, TContents, TTime, TClickCount, TFlag, TlastClickT, SECTION.SNAMEFROM Topic INNER JOIN Users ON TUID = Users.UIDINNER JOIN SECTION ON TSID = SECTION.SIDGO执行上述T-SQL便可创建存储过程，创建成功的存储过程可由SQL的EXECUTE调用，也可由前端开发语言（DELPHI, C#, JAVA等）通过数据库接口(如OLD DB)的方法调用。p_topic_all存储过程可以通过以下方法执行：USE bbsdbGOEXECUTE p_topic_all- 或EXEC p_topic_allGO注意注意：T-SQL允许有选择地使用一个名称和数字区分一个存储过程(例如，p_topic_all；1)。当执行用数字创建的存储过程时，也必须包括该数字。虽然这种方法允许在一个Drop Procedure语句中删除有相同名称(和不同数字)的全部过程，但是实践汪明，这种命名不好。建议对于全部存储过程使用唯一的描述名（不包括数字）2创建带有参数的简单存储过程【例9-2】创建存储过程，除【例9-1】的要求（不显示代码,需要显示代码意义）外， 还要求只返回指定某版块编号的帖子信息。 【分析】：（1）创建存储过程，该存储过程有输入参数“版块编号”，无输出参数。 （2）帖子信息存放于TOPIC表，发帖人信息存放于USERS表，版块信息存放于SECTION表, 三张表作联接查询，增加条件过滤。 代码如下：USE bbsdbGOCREATE PROCEDURE p_topic_by_sid SID INT ASSELECT TID, TSID, Users.UName, TReplyCount, TEmotion, TTopic, TContents, TTime, TClickCount, TFlag, TlastClickT, SECTION.SNAMEFROM Topic INNER JOIN Users ON TUID = Users.UIDINNER JOIN SECTION ON TSID = SECTION.SID WHERE SECTION.SID = SIDGOp_topic_by_sid存储过程可以通过以下方法执行：USE bbsdbGOEXEC p_topic_by_sid 2- OrEXEC p_topic_by_sid 23使用 OUTPUT 参数创建带有输出参数的存储过程【例9-3】创建存储过程，除【例9-1】, 【例9-2】的要求（不显示代码,需要显示代码意义；返回指定版块编号的帖子信息）外，还要求返回指定版块的发帖总页数，以及该版块第1页的帖子信息(假设版块帖子数很多)。【分析】：（1）创建存储过程，该存储过程有输入参数“版块编号”，有输出参数“页数”。 （2）帖子信息存放于TOPIC表，发帖人信息存放于USERS表，版块信息存放于SECTION表, 三张表作联接查询，增加条件过滤。（3）页数帖子总数/每页的帖子数N（N可考虑使用输入参数指定），并取整（使用函数CEILING）。（4）该版块第1页的帖子信息, 即前面的N行，这样，需要动态构建SQL, 并执行。代码如下：USE bbsdbGOCREATE PROCEDURE p_topic_by_sid1SID INT, Pages INT output, RowsOfPage INT AS-获取该版块的页数SELECT Pages = ceiling(count(*)*1.0/RowsOfPage)FROM TopicWHERE TSID = SID-获取该版块的该版块第1页的帖子信息DECLARE sql varchar(8000)SET sql = select TOP + convert(varchar(5), RowsOfPage) + TID, TSID, Users.UName, TReplyCount, TEmotion, TTopic, + TContents, TTime, + TClickCount, TFlag, TlastClickT, SECTION.SNAME + FROM Topic INNER JOIN Users ON TUID = Users.UID + INNER JOIN SECTION ON TSID = SECTION.SID + WHERE SECTION.SID = + convert(varchar(10), SID)EXEC(sql)GOp_topic_by_sid存储过程可以通过以下方法执行：USE bbsdbGODECLARE cnt intEXEC p_topic_by_sid1 2, cnt OUTPUT, 20 -存储过程执行时，也带OUTPUTPrint cnt4输入参数的默认值上述【例9-3】确实比较方便，调用者可随时改变每页显示的帖子数。但通常情况下，系统每页显示20张帖子，调用者也需要在严格按照上述调用方法，这样就较麻烦。思考思考：能不能在不指定每页的帖子数时，系统用最常规的每页显示20来调用执行呢？ 答案是肯定的。如果当调用存储过程时没有提供参数，可以定义一个缺省的输入值。具体方法是：CREATE PROC 存储过程名 parameter data_type = 默认值, AS 如上述问题的解决, 可修改上例。【例9-4】使用默认值调用存储过程。USE bbsdbGOCREATE PROCEDURE p_topic_by_sid2SID INT,PagesINT output,RowsOfPage INT=20 AS-获取该版块的页数SELECT Pages = ceiling(count(*)*1.0/RowsOfPage)FROM TopicWHERE TSID = SID-获取该版块的该版块第1页的帖子信息declare sql varchar(8000)set sql = select TOP + convert(varchar(5), RowsOfPage) + TID, TSID, Users.UName, TReplyCount, TEmotion, TTopic, TContents, TTime, + TClickCount, TFlag, TlastClickT, SECTION.SNAME + FROM Topic INNER JOIN Users ON TUID = Users.UID + INNER JOIN SECTION ON TSID = SECTION.SID + WHERE SECTION.SID = + convert(varchar(10), SID)exec(sql)GO 修改后，p_topic_by_sid存储过程可以通过以下方法执行：Declare cnt intEXECUTE p_topic_by_sid2 2, cnt OUTPUT -默认值，每页20条帖子5使用状态返回值存储过程可返回一个整数值，以判断存储过程的执行状态。使用RETURN语句可指定返回值。与OUTPUT参数相似，执行该存储过程时，必须将返回值保存到变量中，以便进一步判断，如下例所示。【例9-5】创建存储过程，除【例9-4】的要求外，要求检查输入的版块号，以及判断执行结果等，返回代码要求如表9-1所示。代码如下：CREATE PROCEDURE p_topic_by_sid3 SID INT, Pages INT output, RowsOfPage INT=20 AS -检查SECTION表中有无输入的版块号。IF NOT exists(SELECT sid from section where sid=sid)Return(1)elseBegin -获取该版块的页数SELECT Pages = ceiling(count(*)*1.0/RowsOfPage)FROM TopicWHERE TSID = SIDIf error 0Return(2)else Begin -获取该版块的该版块第1页的帖子信息 declare sql varchar(8000) set sql = SELECT TOP + convert(varchar(5), RowsOfPage) + TID, TSID, Users.UName, TReplyCount, TEmotion, TTopic, TContents, TTime, + TClickCount, TFlag, TlastClickT, SECTION.SNAME + FROM Topic INNER JOIN Users ON TUID = Users.UID + INNER JOIN SECTION ON TSID = SECTION.SID + WHERE SECTION.SID = + convert(varchar(10), SID) exec(sql) If error 0Return(3) elseReturn(0) endendGOp_topic_by_sid3存储过程可以通过以下方法执行：USE bbsdbGODeclare cnt int, result intEXECUTE result=p_topic_by_sid3 2, cnt OUTPUTPrint cnt6处理错误语句【例9-6】创建存储过程，除【例9-5】的要求外，要求检查输入的版块号，如版块号0, 则引发错误。代码如下：CREATE PROCEDURE p_topic_by_sid SID INT,PagesINT output,RowsOfPage INT AS-检查版块号是否0。If SID0RAISERROR (对不起对不起,版块号错误版块号错误,请指定大于请指定大于0的版块号的版块号, 16,1)GO9.2.3 使用向导创建存储过程使用向导创建存储过程1、在企业管理器中打开“创建存储过程向导”对话框。方法一：启动企业管理器，单击控制台树上的【服务器组】【服务器】【数据库】【展开要创建视图的数据库】，单击【工具】菜单【向导】，如图所示。在出现的“选择向导”对话框中单击“数据库”选项左侧的加号，在下一级选项中就可以看到“创建存储过程向导”选项，如图所示。方法二：启动SQL SERVER企业管理器，单击工具栏中的图标，就可以直接打开“选择向导”对话框。（2）选择希望在其中创建存储过程的数据库， （3）为要创建存储过程选择一个或多个操作， （4）修改用缺省方式创建的存储过程， （5）在“编辑存储过程属性” 窗体中，如图所示， 单击【编辑SQLE】。（6）在“编辑存储过程 SQL”窗体中，如图所示， 编写存储过程语句，。（7）单击【分析P】进行分析，无误后，单击【确定】。 （8）单击【完成】。9.3 执行存储过程执行存储过程 当需要执行存储过程时，要使用EXECUTE 语句。如果存储过程是批处理中的第一条语句，那么不使用 EXECUTE 关键字也可以执行该存储过程。 9.3.1 执行存储过程执行存储过程 如果存储过程的输入参数已设置默认值，还可以省略那些已提供默认值的参数。要注意的是：如果第N个参数为默认值，那么，第N+1、N+2、N+3.个参数都必须为默认值。例如：p_topic_by_sid共有3个参数，其中第1个、第3个为输入参数，都有缺省值，第2个为输出参数，则不能这样调用：Declare cnt intEXECUTE p_topic_by_sid , cnt OUTPUT - 错误提示提示：因为有缺省值的参数主要是可选参数，所以可把它们放在参数列表的末尾。这更便于在调用存储过程时，省略相应的变量。当然，也可以使用另一方法调用存储过程。即在调用时，指定相应的参数名称。它的好处是：不要求变量按照创建存储过程时指定的参数顺序来指定。下面说明该种方法的使用。如上例，可以这样来调用：Declare cnt intEXECUTE p_topic_by_sid Pages=cnt OUTPUT 9.3.2 重新编译存储过程重新编译存储过程 存储过程和触发器所用的查询只在编译时进行优化。对数据库进行了索引或其它会影响数据库统计的更改后，已编译的存储过程和触发器可能会失去效率。通过对存储过程和作用于表上的触发器进行重新编译，可以重新优化查询。SQL SERVER提供三种重新编译存储过程的方法 。1使用系统存储过程使用系统存储过程sp_recompile该过程将使存储过程和触发器在下次运行时重新编译。语法：sp_recompile objname = object参数说明：objname = object：是当前数据库中的存储过程、触发器、表或视图的限定的或非限定的名称。如果 object 是存储过程或触发器的名称，那么该存储过程或触发器将在下次运行时重新编译。如果 object 是表或视图的名称，那么所有引用该表或视图的存储过程都将在下次运行时重新编译。2使用使用CREATE PROCEDURE时指定时指定WITH RECOMPILE在创建存储过程时使用 WITH RECOMPILE选项，新的执行计划将不进行高速缓存或存储在内存中，每次执行都进行重新编译，会降低执行效率。语法：CREATE PROCEDURE procedure_nameParameter_name data_typeWITH RECOMPILEAS3使用使用EXECUTE时指定时指定WITH RECOMPILE调用存储过程时重新编译。这样只重新编译一次。语法：Execute procedure_name WITH RECOMPILE9.4 查看和修改存储过程查看和修改存储过程 存储过程建立后，可以对存储过程进行查看、修改和删除。 9.4.1 查看存储过程查看存储过程 可以通过企业管理器查看存储过程。 有3个系统存储过程，可以显示有关存储过程的信息： sp_help procedure_name : 显示存储过程的所有者和创建时间 sp_helptext procedure_name：显示存储过程的源代码 sp_depends procedure_name：显示该存储过程引用的对象清单 如果丢失了用来创建存储过程的源代码，那么sp_helptext系统存储过程就非常有用了。9.4.2 修改存储过程修改存储过程 存储过程建立后可对其进行修改，既可修改其名称，也可以修改其定义、有效性等。1修改存储过程名称语法格式：sp_rename old_procedure_name, new_procedure_name参数说明：old_procedure_name, new_procedure_name分别表示存储过程的旧名称与新名称。2修改存储过程的定义（1）使用T-SQL修改存储过程的定义。语法格式如下：ALTER PROCEDURE procedure_name parameter data_type = default OUTPUT ,.n WITH RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION AS sql_statement .n 更改已创建的存储过程，但不会更改权限，也不影响相关的存储过程。（2）使用企业管理器修改存储过程的定义启动SQL SERVER企业管理器，依次单击控制台树上的【服务器组】【服务器】【数据库】【要创建存储过程的数据库】【存储过程】，在窗体右边的存储过程中双击要修改的存储过程名称。在 “存储过程属性”对话框中，修改存储过程的T-SQL文本，然后单击【确定】， 其他操作与创建存储过程类似。9.5 重命名和删除存储过程重命名和删除存储过程可以重命名存储过程，只能重命名自己拥有的存储过程，但数据库所有者可以更改任何用户的存储过程名称，并且要重命名的存储过程必须位于当前数据库中。可使用下面两种方法重命名存储过程。 9.5.1 重命名存储过程重命名存储过程1使用sp_rename重命名已存在的存储过程语法格式如下：sp_rename objname = object_name , newname = new_name , objtype = object_type 参数说明：objname:：用户对象（表、视图、列、存储过程、触发器、默认值、数据库、对象或规则）或数据类型的当前名称。newname：是指定对象的新名称。objtype：是要重命名的对象的类型。如果需要将创建的存储过程p_topic_by_sid重命名为p_topic_by_sid0，可使用：sp_rename p_topic_by_sid, p_topic_by_sid0提示提示：重命名存储过程图后，请清空过程高速缓存以确保所有相关的存储过程都重新编译。2使用企业管理器重命名已存在的存储过程在图9-10所示的“存储过程列表”中，右击要重命名的存储过程名称，然后单击【重命名】按钮，输入新名称，单击回车即可。9.5.2 删除存储过程删除存储过程当不再需要某个存储过程时，可将其删除。1使用T-SQL删除存储过程语法格式如下：DROP PROCEDURE procedure_name ,.n 2使用企业管理器删除存储过程在图9-11所示的“存储过程列表”中，右击要删除的存储过程名称，然后单击【删除】按钮并确认。9.6 常用的系统存储过程常用的系统存储过程系统存储过程的名称都以“sp_”开头。系统存储过程位于MASTER数据库中，系统管理员拥有这些存储过程，用户可以在任何数据库中调用系统存储过程。表9-2列出了常用的系统存储过程。例如，若需要将登录帐户sa原来的空密码修改为aaa, 可以使用：EXEC sp_password NULL, aaa , sa另外，还有一个常用的扩展存储过程：xp_cmdshell，它可以执行DOS命令下的一些的操作，如：创建文件夹、列出文件列表等。 调用语法：EXEC xp_cmdshell DOS命令 NO_OUTPUT例如，若希望创建的数据库保存在E:DATA文件夹下，可在查询分析器中运行下列命令创建一个文件夹：EXEC xp_cmdshell DOS mkdir e:data9.7 综合实例综合实例实例说明目前，许多企业都有积分奖励计划，它是企业为答谢并鼓励各位会员的一项回馈计划。利用积分，会员可以享受企业更多优惠增值服务。积分计算可以采用存贮过程实现。【例9-7】创建存储过程，计算某用户统计时段的积分，积分的计算规则如下：（1）用户发贴 2 （2）回帖5 【分析】：（1）统计时段默认为当年统计。 （2）设置输出参数point返回该用户需增加的积分。9.8 上机实训上机实训实训目的实训目的 利用向导创建存储过程； 利用企业管理器创建存储过程； 利用T-SQL创建存储过程； 执行存储过程; 重新编译存储过程; 使用常用的系统存储过程;实训要求实训要求1复习T-SQL程序设计有关知识。2复习DML语句。实训内容实训内容1创建存储过程练习（1）使用企业管理器创建存储过程 （2）使用向导创建存储过程 （3）使用T-SQL创建存储过程 2操作存储过程练习（1）重新编译存储过程titles_sum （2）查看存储过程 3常用的系统存储过程练习（1）列出当前环境中的所有存储过程 （2）添加或修改登录帐户的密码 4综合实训练习本章小结本章小结存储过程的应用很广泛，重要的是理解存储过程的概念，在实际的数据库应用系统合理设计存储过程来简化、优化系统开发。存储过程是一组预编译的SQL语句。存储过程的优点在于：简化用户操作、执行效率更高、减少网络流量、提高系统安全性、允许模块化程序设计。存储过程分为两大类：系统存储过程和用户自定义存储过程。存储过程的参数分为：输入参数、输出参数。