存储过程的创建与使用.ppt

《存储过程的创建与使用.ppt》由会员分享，可在线阅读，更多相关《存储过程的创建与使用.ppt（75页珍藏版）》请在三一办公上搜索。

1、数据库基础与应用第9章存储过程的创建和使用,自定义函数的创建与使用？,CREATE FUNCTION 函数名(参数表)RETURNS 返回值的类型 AS BEGIN 函数体 RETURN 返回的表达式END调用：SET result=dbo.fun_SumCount(maxprice,minprice),存储过程与自定义函数的区别？,存储过程是使用EXEC命令独立调用的，而用户自定义函数是在另一个SQL语句中调用的。存储过程是允许用户和程序去使用存储的程序，而不是允许其存取表，这样能够增强程序安全性。与标准的SQL Server相比，存储程序限制用户行动权限方面更为细化。例如，如果你有一个货存

2、表，每次卖出一个货物收银员都要对表进行更新一次(从货存中把该货品减去一件)。你可以给收银员设置权限，允许其使用decrement_item存储过程，而不是允许他们有任意修改表的权限。函数必须始终返回一个值(一个标量值或一个表格)。而存储过程可以返回一个标量值、一个表值或无需返回值。总而言之，存储程序对SQL Server开发员来说是最有价值的宝物之一，用于数据库中，能够大大的提高工作效率，增强安全性，绝对超值。,本章主要内容,存贮过程概述创建存储过程执行存储过程查看和修改存储过程常用的系统存储过程,本章核心知识点,利用T-SQL创建存储过程存贮过程的执行存贮过程的重新编译,本章重点难点,在存贮

3、过程中使用参数重新编译存贮过程,本章教学目标,了解存储过程的概念和优点,能够在实际应用中设计合适的存储过程理解存储过程的类型，掌握一定数量的系统存储过程掌握如何创建和执行存储过程。掌握管理存储过程的方法。,第9章 存储过程的创建和使用,顾名思义，存储过程就是存储在SQL SERVER服务器中的一组编译成单个执行计划的T-SQL语句。,9.1 概述,在创建SQL Server数据库应用程序时，Transact-SQL语言是应用程序和SQL Server数据库之间的主要编程接口。可用如下两种方法存储和执行Transact-SQL语句。(1)SQL语句嵌入在开发语言中，执行时向SQL Server发

4、送命令并处理结果的应用程序。）(2)可以将Transact-SQL程序保存在SQL Server中，即存储过程，开发语言中直接调用该存储过程。,理解存储过程的概念,存储过程是SQL语句和可选控制流语句的预编译集合，以一个名称存储并作为一个单元处理，其中可包含程序流、逻辑以及对数据库的查询，存储过程可以包含一条SELECT语句，也可包含一系列使用控制流的SQL语句，如图9-1所示。它们还可接受参数、返回输出参数、返回状态值，主要用来执行管理任务或实现应用复杂的业务规则。,。,9.1.2 理解存储过程的优点,使用存储过程与本地的Transact-SQL程序相比有如下好处：1简化用户操作存储过程将一

5、系列复杂的T-SQL代码封装在一起，作为数据库对象存放于数据库服务器内，用户使用时不必思考复杂的T-SQL语句而只需调用存储过程即可得到所需结果，简化了用户操作。2执行效率更高存储过程已被编译，执行时不再花费编译与优化时间。这不同于T-SQL语句，每次运行时都要从客户端重复发送，并由SQL SERVER进行编译和优化。在需要大量T-SQL代码或需重复执行时，存储过程比T-SQL批代码的执行要快。,3减少网络流量特别对于复杂的商业业务计算，例如计算用户的每月应交电费，如采用T-SQL实现，则需要大量的T-SQL语句和中间数据在客户机与SQL SERVER服务器之间的网络通道上传送；采用存储过程实

6、现，则只需由一条执行存储过程的单独语句就可实现，大大减少网络流量。4提高系统安全性可作为安全机制使用。通过适当的权限设置，可以使系统的安全性得到有效保障。5允许模块化程序设计存储过程可以封装企业的功能模块，这种企业的功能模块也为商业规则或者商业策赂，可以只创建一次并将其存储在数据库中，以后即可在程序中调用该过程任意次，而且可以统一修改。,9.1.3 掌握存储过程的分类,存储过程分为两大类：系统存储过程和用户自定义存储过程。系统存储过程：由系统定义的存储过程，存放在master数据库中，类似C语言中的系统函数。系统存储过程的名称都以“sp_”开头开头，可以在任何数据库中执行系统存储过程。用户自定

7、义存储过程：由用户在自己的数据库中创建的存储过程，类似C语言中的自定义函数，只能在特定的数据库中执行。,用户自定义存储过程可根据不同的标准予以分类：（1）临时存储过程。是以存储过程名、开头的存储过程。存放在tempdb数据库中，当用户断开连接时将自动删除临时存储过程。（2）本地存储过程。用户创建并存放在用户当前连接的SQL SERVER数据库中的存储过程,是我们通常意义上的存储过程。（3）扩展存储过程。是利用高级语言（如DELPHI和C+等）编写的存储过程。实际是包含在一个WIn32.DLL中的一个函数。只有那些可以独立创建DLL和EXE文件的高级程序语言可用来创建扩展存储过程。,9.2 创建

8、存储过程,在当前数据库创建的用户自定义本地存储过程，不包括扩展存储过程。创建存储过程的方法共有三种：使用企业管理器使用T-SQL语句使用创建存储过程向导,利用企业管理器创建存储过程,可使用企业管理器创建存储过程。,（1）启动SQL SERVER企业管理器，依次单击控制台树上的【服务器组】【服务器】【数据库】【要创建存储过程的数据库】右击【存储过程】，在弹出的快捷菜单中单击【新建存储过程】，如图所示。,（2）弹出存储过程属性对话框，如图所示。,（3）在“文本”框中输入存储过程。包括修改存储过程名,并在”AS”后输入存储过程内容。（4）单击【检查语法】按钮，检查所输入的SQL语句语法的是否正确。（

9、5）单击【确定】按钮，创建该存储过程。,9.2.2 使用Transact-SQL语言创建存储过程,用CREATE PROCEDURE语句创建存储过程。语法格式:CREATE PROCEDURE 参数表AS,1创建带有复杂 SELECT 语句的简单过程【例9-1】创建存储过程，返回所有帖子信息。这些帖子信息要求显示编号的意义。如：发帖人不使用用户编号，而使用发帖人的昵称；帖子所在版块不使用版块编号，而显示版块名。【分析】：（1）使用CREATE PROCEDURE语句创建存储过程，该存储过程无输入参数、输出参数。（2）帖子信息存放于TOPIC表，发帖人信息存放于USERS表，版块信息存放于SEC

10、TION表,三张表作联接查询。,代码如下：USE bbsdbGOCREATE PROCEDURE p_topic_all AS SELECT TID,TSID,Users.UName,TReplyCount,TEmotin,TTopic,TContents,TTime,TClickCount,TFlag,TlastClickT,SECTION.SNAME FROM Topic,Users,SECTION Where Topic.TUID=Users.UID and Topic.TSID=SECTION.SIDGO,执行上述T-SQL便可创建存储过程，创建成功的存储过程可由SQL的EXECUTE

11、调用，也可由前端开发语言（DELPHI,C#,JAVA等）通过数据库接口(如OLD DB)的方法调用。p_topic_all存储过程可以通过以下方法执行：USE bbsdbGOEXECUTE p_topic_all-或EXEC p_topic_allGO,练习：,（1）在student数据库中，创建名为p_tj1的存储过程，查询选修每门课程的学生人数、最高成绩、最低成绩和平均成绩；,2创建带有参数的简单存储过程【例9-2】创建存储过程，除【例9-1】的要求（不显示代码,需要显示代码意义）外，还要求只返回指定某版块编号的帖子信息。【分析】：（1）创建存储过程，该存储过程有输入参数“版块编号”，无

12、输出参数。（2）帖子信息存放于TOPIC表，发帖人信息存放于USERS表，版块信息存放于SECTION表,三张表作联接查询，增加条件过滤。,代码如下：USE bbsdbGOCREATE PROCEDURE p_topic_by_sid SID INTASSELECT TID,TSID,Users.UName,TReplyCount,TEmotion,TTopic,TContents,TTime,TClickCount,TFlag,TlastClickT,SECTION.SNAMEFROM Topic INNER JOIN Users ON TUID=Users.UIDINNER JOIN SE

13、CTION ON TSID=SECTION.SID WHERE SECTION.SID=SIDGO,p_topic_by_sid存储过程可以通过以下方法执行：USE bbsdbGOEXEC p_topic_by_sid 2-OrEXEC p_topic_by_sid 2,练习：,（2）在student数据库中，创建名为p_tj2的存储过程，查询选修某门指定课程的学生人数、最高成绩、最低成绩和平均成绩；执行该存储过程，例如，查询选修C1课程的信息,3使用 OUTPUT 参数创建带有输出参数的存储过程【例9-3】创建存储过程，除【例9-1】,【例9-2】的要求（不显示代码,需要显示代码意义；返回指

14、定版块编号的帖子信息）外，还要求返回指定版块的发帖总页数，以及该版块第1页的帖子信息(假设版块帖子数很多)。【分析】：（1）创建存储过程，该存储过程有输入参数“版块编号”，有输出参数“页数”。（2）帖子信息存放于TOPIC表，发帖人信息存放于USERS表，版块信息存放于SECTION表,三张表作联接查询，增加条件过滤。（3）页数帖子总数/每页的帖子数N（N可考虑使用输入参数指定），并取整（使用函数CEILING）。（4）该版块第1页的帖子信息,即前面的N行，这样，需要动态构建SQL,并执行。,CREATE PROCEDURE p_topic_by_sid1SID INT,Pages INT o

15、utput,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,TEmotin,TTopic,+TContents,TTime,+TClickCount,TFlag,TlastClickT,SECTION.SN

16、AME+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存储过程可以通过以下方法执行：USE bbsdbGODECLARE cnt intEXEC p_topic_by_sid1 2,cnt OUTPUT,20-存储过程执行时，也带OUTPUTPrint cnt,练习：,（3）在student数据库中，创建名为p_tj3的存储过程，查询选

17、修某门指定课程的学生人数、最高成绩、最低成绩和平均成绩，并用参数输出学生人数、平均成绩；执行该存储过程，例如，查询选修C1课程的信息,4输入参数的默认值上述【例9-3】确实比较方便，调用者可随时改变每页显示的帖子数。但通常情况下，系统每页显示20张帖子，调用者也需要在严格按照上述调用方法，这样就较麻烦。思考：能不能在不指定每页的帖子数时，系统用最常规的每页显示20来调用执行呢？答案是肯定的。如果当调用存储过程时没有提供参数，可以定义一个缺省的输入值。具体方法是：CREATE PROC 存储过程名 parameter data_type=默认值,AS,如上述问题的解决,可修改上例。【例9-4】使

18、用默认值调用存储过程。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,TReplyCou

19、nt,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 1,cnt OUT

20、PUT-默认值，每页20条帖子,练习：,（4）将p_tj3存储过程的输入参数默认值设为c1；执行该存储过程.,5使用状态返回值存储过程可返回一个整数值，以判断存储过程的执行状态。使用RETURN语句可指定返回值。与OUTPUT参数相似，执行该存储过程时，必须将返回值保存到变量中，以便进一步判断，如下例所示。【例9-5】创建存储过程，除【例9-4】的要求外，要求检查输入的版块号，以及判断执行结果等，返回代码要求如表9-1所示。,代码如下：CREATE PROCEDURE p_topic_by_sid3 SID INT,Pages INT output,RowsOfPage INT=20 AS-检

21、查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,U

22、sers.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)endendGO,p_topic_by_sid3存储过程可以通

23、过以下方法执行：USE bbsdbGODeclare cnt int,result intEXECUTE result=p_topic_by_sid3 2,cnt OUTPUTPrint cnt,6处理错误语句【例9-6】创建存储过程，除【例9-5】的要求外，要求检查输入的版块号，如版块号0,则引发错误。代码如下：CREATE PROCEDURE p_topic_by_sid SID INT,PagesINT output,RowsOfPage INT AS-检查版块号是否0。If SID0RAISERROR(对不起,版块号错误,请指定大于0的版块号,16,1)GO,使用向导创建存储过程,1、

24、在企业管理器中打开“创建存储过程向导”对话框。方法一：启动企业管理器，单击控制台树上的【服务器组】【服务器】【数据库】【展开要创建视图的数据库】，单击【工具】菜单【向导】，如图所示。,在出现的“选择向导”对话框中单击“数据库”选项左侧的加号，在下一级选项中就可以看到“创建存储过程向导”选项，如图所示。方法二：启动SQL SERVER企业管理器，单击工具栏中的图标，就可以直接打开“选择向导”对话框。,（2）选择希望在其中创建存储过程的数据库，,（3）为要创建存储过程选择一个或多个操作，,（4）修改用缺省方式创建的存储过程，,（5）在“编辑存储过程属性”窗体中，如图所示，单击【编辑SQLE】。,（

25、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个为输出参

26、数，则不能这样调用：Declare cnt intEXECUTE p_topic_by_sid,cnt OUTPUT-错误,提示：因为有缺省值的参数主要是可选参数，所以可把它们放在参数列表的末尾。这更便于在调用存储过程时，省略相应的变量。当然，也可以使用另一方法调用存储过程。即在调用时，指定相应的参数名称。它的好处是：不要求变量按照创建存储过程时指定的参数顺序来指定。下面说明该种方法的使用。如上例，可以这样来调用：Declare cnt intEXECUTE p_topic_by_sid Pages=cnt OUTPUT,9.3.2 重新编译存储过程,存储过程和触发器所用的查询只在编译时进行优

27、化。对数据库进行了索引或其它会影响数据库统计的更改后，已编译的存储过程和触发器可能会失去效率。通过对存储过程和作用于表上的触发器进行重新编译，可以重新优化查询。SQL SERVER提供三种重新编译存储过程的方法。,1使用系统存储过程sp_recompile该过程将使存储过程和触发器在下次运行时重新编译。语法：sp_recompile objname=object参数说明：objname=object：是当前数据库中的存储过程、触发器、表或视图的限定的或非限定的名称。如果 object 是存储过程或触发器的名称，那么该存储过程或触发器将在下次运行时重新编译。如果 object 是表或视图的名称，

28、那么所有引用该表或视图的存储过程都将在下次运行时重新编译。,2使用CREATE PROCEDURE时指定WITH RECOMPILE在创建存储过程时使用 WITH RECOMPILE选项，新的执行计划将不进行高速缓存或存储在内存中，每次执行都进行重新编译，会降低执行效率。语法：CREATE PROCEDURE procedure_nameParameter_name data_typeWITH RECOMPILEAS,3使用EXECUTE时指定WITH RECOMPILE调用存储过程时重新编译。这样只重新编译一次。语法：Execute procedure_name WITH RECOMPILE

29、,9.4 查看和修改存储过程,存储过程建立后，可以对存储过程进行查看、修改和删除。,查看存储过程,可以通过企业管理器查看存储过程。有3个系统存储过程，可以显示有关存储过程的信息：sp_help procedure_name:显示存储过程的所有者和创建时间 sp_helptext procedure_name：显示存储过程的源代码 sp_depends procedure_name：显示该存储过程引用的对象清单 如果丢失了用来创建存储过程的源代码，那么sp_helptext系统存储过程就非常有用了。,修改存储过程,存储过程建立后可对其进行修改，既可修改其名称，也可以修改其定义、有效性等。1修改存

30、储过程名称语法格式：sp_rename 原存储过程名,新存储过程名,2修改存储过程的定义（1）使用T-SQL修改存储过程的定义。语法格式如下：ALTER PROCEDURE 存储过程名 参数名 类型=默认值 OUTPUT,.n AS SQL语句更改已创建的存储过程，但不会更改权限，也不影响相关的存储过程。,（2）使用企业管理器修改存储过程的定义启动SQL SERVER企业管理器，依次单击控制台树上的【服务器组】【服务器】【数据库】【要创建存储过程的数据库】【存储过程】，在窗体右边的存储过程中双击要修改的存储过程名称。在“存储过程属性”对话框中，修改存储过程的T-SQL文本，然后单击【确定】，其

31、他操作与创建存储过程类似。,9.5 删除存储过程,当不再需要某个存储过程时，可将其删除。1使用T-SQL删除存储过程语法格式如下：DROP PROCEDURE 存储过程名,.n 2使用企业管理器删除存储过程在图9-11所示的“存储过程列表”中，右击要删除的存储过程名称，然后单击【删除】按钮并确认。,练习：,（1）在Student数据库中，创建一个新表n，包含列class、num、man、woman，用于统计表s中各班的总人数、男生人数以及女生人数。（2）编写一个存储过程total用于由表s统计出相应数据插入表n。（3）利用语句将存储过程total改名为tj;（4）执行存储过程tj,由表s统计出

32、数据插入表n。,CREATE PROCEDURE totalAS begindelete from n-将n表清空-插入班级、总人数信息 insert into n(class,num)select class,count(*)from s group by class-计算每个班级的男生人数 select class,count(*)as n into temp from s where sex=男 group by class-更新n表中相应班级的男生人数 update n set man=n from temp where n.class=temp.class drop table te

33、mp-删除临时表temp-计算每个班级的女生人数，同上。select class,count(*)as n into temp from s where sex=女 group by class update n set woman=n from temp where n.class=temp.class drop table temp end,9.6 常用的系统存储过程,系统存储过程的名称都以“sp_”开头。系统存储过程位于MASTER数据库中，系统管理员拥有这些存储过程，用户可以在任何数据库中调用系统存储过程。表9-2列出了常用的系统存储过程。,例如，若需要将登录帐户sa原来的空密码修改为

34、aaa,可以使用：EXEC sp_password NULL,aaa,sa另外，还有一个常用的扩展存储过程：xp_cmdshell，它可以执行DOS命令下的一些的操作，如：创建文件夹、列出文件列表等。调用语法：EXEC xp_cmdshell DOS命令 NO_OUTPUT例如，若希望创建的数据库保存在E:DATA文件夹下，可在查询分析器中运行下列命令创建一个文件夹：EXEC xp_cmdshell mkdir e:data,9.7 综合实例,实例说明目前，许多企业都有积分奖励计划，它是企业为答谢并鼓励各位会员的一项回馈计划。利用积分，会员可以享受企业更多优惠增值服务。积分计算可以采用存贮过程

35、实现。,【例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语句。存储过程的优点在于：简化用户操作、执行效率更高、减少网络流量、提高系统安全性、允许模块化程序设计。存储过程分为两大类：系统存储过程和用户自定义存储过程。存储过程的参数分为：输入参数、输出参数。,