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

数据库基础与应用 第9章存储过程的创建和使用,自定义函数的创建与使用？,CREATE FUNCTION 函数名 ( 参数表 ) RETURNS 返回值的类型 AS BEGIN 函数体 RETURN 返回的表达式 END 调用： SET result=dbo.fun_SumCount(maxprice,minprice),存储过程与自定义函数的区别？,存储过程是使用EXEC命令独立调用的，而用户自定义函数是在另一个SQL语句中调用的。 存储过程是允许用户和程序去使用存储的程序，而不是允许其存取表，这样能够增强程序安全性。与标准的SQL Server相比，存储程序限制用户行动权限方面更为细化。例如，如果你有一个货存表，每次卖出一个货物收银员都要对表进行更新一次(从货存中把该货品减去一件)。你可以给收银员设置权限，允许其使用decrement_item存储过程，而不是允许他们有任意修改表的权限。 函数必须始终返回一个值(一个标量值或一个表格)。而存储过程可以返回一个标量值、一个表值或无需返回值。 总而言之，存储程序对SQL Server开发员来说是最有价值的宝物之一，用于数据库中，能够大大的提高工作效率，增强安全性，绝对超值。,本章主要内容,存贮过程概述 创建存储过程 执行存储过程 查看和修改存储过程 常用的系统存储过程,本章核心知识点,利用T-SQL创建存储过程 存贮过程的执行 存贮过程的重新编译,本章重点难点,在存贮过程中使用参数 重新编译存贮过程,本章教学目标,了解存储过程的概念和优点,能够在实际应用中设计合适的存储过程 理解存储过程的类型，掌握一定数量的系统存储过程 掌握如何创建和执行存储过程。 掌握管理存储过程的方法。,第9章 存储过程的创建和使用,顾名思义，存储过程就是存储在SQL SERVER服务器中的一组编译成单个执行计划的T-SQL语句。,9.1 概述,在创建SQL Server数据库应用程序时，Transact-SQL语言是应用程序和SQL Server数据库之间的主要编程接口。可用如下两种方法存储和执行Transact-SQL语句。 (1) SQL语句嵌入在开发语言中，执行时向SQL Server发送命令并处理结果的应用程序。） (2) 可以将Transact-SQL程序保存在SQL Server中，即存储过程，开发语言中直接调用该存储过程。,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_”开头开头，可以在任何数据库中执行系统存储过程。 用户自定义存储过程：由用户在自己的数据库中创建的存储过程，类似C语言中的自定义函数，只能在特定的数据库中执行。,用户自定义存储过程可根据不同的标准予以分类： （1）临时存储过程。是以存储过程名、开头的存储过程。存放在tempdb数据库中，当用户断开连接时将自动删除临时存储过程。 （2）本地存储过程。用户创建并存放在用户当前连接的SQL SERVER数据库中的存储过程, 是我们通常意义上的存储过程。 （3）扩展存储过程。是利用高级语言（如DELPHI和C+等）编写的存储过程。实际是包含在一个WIn32.DLL中的一个函数。只有那些可以独立创建DLL和EXE文件的高级程序语言可用来创建扩展存储过程。,9.2 创建存储过程,在当前数据库创建的用户自定义本地存储过程，不包括扩展存储过程。 创建存储过程的方法共有三种： 使用企业管理器 使用T-SQL语句 使用创建存储过程向导,9.2.1 利用企业管理器创建存储过程,可使用企业管理器创建存储过程。,（1）启动SQL SERVER企业管理器，依次单击控制台树上的【服务器组】【服务器】【数据库】【要创建存储过程的数据库】右击【存储过程】，在弹出的快捷菜单中单击【新建存储过程】，如图所示。,（2）弹出存储过程属性对话框，如图所示。,（3）在“文本”框中输入存储过程。包括修改存储过程名, 并在”AS”后输入存储过程内容。 （4）单击【检查语法】按钮，检查所输入的SQL语句语法的是否正确。 （5）单击【确定】按钮，创建该存储过程。,9.2.2 使用Transact-SQL语言创建存储过程,用CREATE PROCEDURE语句创建存储过程。 语法格式: CREATE PROCEDURE 参数表 AS ,1创建带有复杂 SELECT 语句的简单过程 【例9-1】创建存储过程，返回所有帖子信息。这些帖子信息要求显示编号的意义。如：发帖人不使用用户编号，而使用发帖人的昵称；帖子所在版块不使用版块编号，而显示版块名。 【分析】： （1）使用CREATE PROCEDURE语句创建存储过程，该存储过程无输入参数、输出参数。 （2）帖子信息存放于TOPIC表，发帖人信息存放于USERS表，版块信息存放于SECTION表, 三张表作联接查询。,代码如下： USE bbsdb GO CREATE 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.SID GO,执行上述T-SQL便可创建存储过程，创建成功的存储过程可由SQL的EXECUTE调用，也可由前端开发语言（DELPHI, C#, JAVA等）通过数据库接口(如OLD DB)的方法调用。 p_topic_all存储过程可以通过以下方法执行： USE bbsdb GO EXECUTE p_topic_all - 或 EXEC p_topic_all GO,练习：,（1）在student数据库中，创建名为p_tj1的存储过程，查询选修每门课程的学生人数、最高成绩、最低成绩和平均成绩 ；,2创建带有参数的简单存储过程 【例9-2】创建存储过程，除【例9-1】的要求（不显示代码,需要显示代码意义）外， 还要求只返回指定某版块编号的帖子信息。 【分析】： （1）创建存储过程，该存储过程有输入参数“版块编号”，无输出参数。 （2）帖子信息存放于TOPIC表，发帖人信息存放于USERS表，版块信息存放于SECTION表, 三张表作联接查询，增加条件过滤。,代码如下： USE bbsdb GO CREATE PROCEDURE p_topic_by_sid SID INT AS SELECT 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 = SID GO,p_topic_by_sid存储过程可以通过以下方法执行： USE bbsdb GO EXEC p_topic_by_sid 2 - Or EXEC p_topic_by_sid 2,练习：,（2）在student数据库中，创建名为p_tj2的存储过程，查询选修某门指定课程的学生人数、最高成绩、最低成绩和平均成绩 ； 执行该存储过程，例如，查询选修C1课程的信息,3使用 OUTPUT 参数创建带有输出参数的存储过程 【例9-3】创建存储过程，除【例9-1】, 【例9-2】的要求（不显示代码,需要显示代码意义；返回指定版块编号的帖子信息）外，还要求返回指定版块的发帖总页数，以及该版块第1页的帖子信息(假设版块帖子数很多)。 【分析】： （1）创建存储过程，该存储过程有输入参数“版块编号”，有输出参数“页数”。 （2）帖子信息存放于TOPIC表，发帖人信息存放于USERS表，版块信息存放于SECTION表, 三张表作联接查询，增加条件过滤。 （3）页数帖子总数/每页的帖子数N（N可考虑使用输入参数指定），并取整（使用函数CEILING）。 （4）该版块第1页的帖子信息, 即前面的N行，这样，需要动态构建SQL, 并执行。,CREATE PROCEDURE p_topic_by_sid1 SID INT, Pages INT output, RowsOfPage INT AS -获取该版块的页数 SELECT Pages = ceiling(count(*)*1.0/RowsOfPage) FROM Topic WHERE 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.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存储过程可以通过以下方法执行： USE bbsdb GO DECLARE cnt int EXEC p_topic_by_sid1 2, cnt OUTPUT, 20 -存储过程执行时，也带OUTPUT Print cnt,练习：,（3）在student数据库中，创建名为p_tj3的存储过程，查询选修某门指定课程的学生人数、最高成绩、最低成绩和平均成绩 ，并用参数输出学生人数、平均成绩 ； 执行该存储过程，例如，查询选修C1课程的信息,4输入参数的默认值 上述【例9-3】确实比较方便，调用者可随时改变每页显示的帖子数。但通常情况下，系统每页显示20张帖子，调用者也需要在严格按照上述调用方法，这样就较麻烦。 思考：能不能在不指定每页的帖子数时，系统用最常规的每页显示20来调用执行呢？ 答案是肯定的。如果当调用存储过程时没有提供参数，可以定义一个缺省的输入值。具体方法是： CREATE PROC 存储过程名 parameter data_type = 默认值, AS ,如上述问题的解决, 可修改上例。 【例9-4】使用默认值调用存储过程。 USE bbsdb GO CREATE PROCEDURE p_topic_by_sid2 SID INT, Pages INT output, RowsOfPage INT=20 AS -获取该版块的页数 SELECT Pages = ceiling(count(*)*1.0/RowsOfPage) FROM Topic WHERE 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 int EXECUTE p_topic_by_sid2 1, cnt OUTPUT -默认值，每页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 -检查SECTION表中有无输入的版块号。 IF NOT exists(SELECT sid from section where sid=sid) Return(1) else Begin -获取该版块的页数 SELECT Pages = ceiling(count(*)*1.0/RowsOfPage) FROM Topic WHERE TSID = SID If error 0 Return(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 0 Return(3) else Return(0) end end GO,p_topic_by_sid3存储过程可以通过以下方法执行： USE bbsdb GO Declare cnt int, result int EXECUTE result=p_topic_by_sid3 2, cnt OUTPUT Print cnt,6处理错误语句 【例9-6】创建存储过程，除【例9-5】的要求外，要求检查输入的版块号，如版块号0, 则引发错误。 代码如下： CREATE PROCEDURE p_topic_by_sid SID INT, Pages INT output, RowsOfPage INT AS -检查版块号是否0。 If SID0 RAISERROR (对不起,版块号错误,请指定大于0的版块号, 16,1) GO,9.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 int EXECUTE p_topic_by_sid , cnt OUTPUT - 错误,提示：因为有缺省值的参数主要是可选参数，所以可把它们放在参数列表的末尾。这更便于在调用存储过程时，省略相应的变量。 当然，也可以使用另一方法调用存储过程。即在调用时，指定相应的参数名称。它的好处是：不要求变量按照创建存储过程时指定的参数顺序来指定。下面说明该种方法的使用。 如上例，可以这样来调用： Declare cnt int EXECUTE 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_name Parameter_name data_type WITH RECOMPILE AS ,3使用EXECUTE时指定WITH RECOMPILE 调用存储过程时重新编译。这样只重新编译一次。 语法： Execute procedure_name WITH RECOMPILE,9.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 原存储过程名, 新存储过程名,2修改存储过程的定义 （1）使用T-SQL修改存储过程的定义。 语法格式如下： ALTER PROCEDURE 存储过程名 参数名 类型 = 默认值 OUTPUT ,.n AS SQL语句 更改已创建的存储过程，但不会更改权限，也不影响相关的存储过程。,（2）使用企业管理器修改存储过程的定义 启动SQL SERVER企业管理器，依次单击控制台树上的【服务器组】【服务器】【数据库】【要创建存储过程的数据库】【存储过程】，在窗体右边的存储过程中双击要修改的存储过程名称。 在 “存储过程属性”对话框中，修改存储过程的T-SQL文本，然后单击【确定】， 其他操作与创建存储过程类似。,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统计出数据插入表n。,CREATE PROCEDURE total AS begin delete 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 temp -删除临时表temp -计算每个班级的女生人数，同上。 select class,count(*) as