《数据库原理与应用》09.存储过程的创建和使用.ppt

《《数据库原理与应用》09.存储过程的创建和使用.ppt》由会员分享，可在线阅读，更多相关《《数据库原理与应用》09.存储过程的创建和使用.ppt（39页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、孙孙孙孙 发发发发 勤勤勤勤 扬州大学新闻与传媒学院扬州大学新闻与传媒学院扬州大学新闻与传媒学院扬州大学新闻与传媒学院YY数据库原理与应用数据库原理与应用第九讲第九讲 存储过程的创建和使用存储过程的创建和使用1、创建存储过程、创建存储过程2、执行存储过程、执行存储过程3、查看和修改存储过程、查看和修改存储过程4、重命名和删除存储过程重命名和删除存储过程 本讲主要内容 存储过程的概念SQL ServerSQL Server提供了一种方法，它可以将一些固定的提供了一种方法，它可以将一些固定的操作集中起来由操作集中起来由SQL ServerSQL Server数据库服务器来完成，以实数据库服务器来完

2、成，以实现某个任务，这种方法就是存储过程。现某个任务，这种方法就是存储过程。类似于类似于DOS一下一下的批处理。存储过程是的批处理。存储过程是SQL语句和可选控制流程语句的语句和可选控制流程语句的预编译集合。是一种封装重复任务操作的方法，以一个预编译集合。是一种封装重复任务操作的方法，以一个名称存储，作为一个单元处理。存储过程属于服务器方名称存储，作为一个单元处理。存储过程属于服务器方软件，可立即访问数据库软件，可立即访问数据库在在SQL SQL ServerServer中中存存储储过过程程分分为为两两类类：即即系系统统提提供供的的存存 储储 过过 程程 和和 用用 户户 自自 定定 义义 的

3、的 存存 储储 过过 程程。存储过程存储在数据库内，可由应用程序通过一个存储过程存储在数据库内，可由应用程序通过一个调用来执行，而且充许用户声明变量。同时，存储过程调用来执行，而且充许用户声明变量。同时，存储过程可以接收和输出参数、返回执行存储过程的状态值，也可以接收和输出参数、返回执行存储过程的状态值，也可以嵌套调用。可以嵌套调用。注意注意:存储过程与函数不同，因为存储过程并不返回存储过程与函数不同，因为存储过程并不返回取代其名称的值，也不能直接在表达式中使用。取代其名称的值，也不能直接在表达式中使用。在在SQLServer中存储过程分为两类中存储过程分为两类:系统提供的存储过程和用户存系统

4、提供的存储过程和用户存储过程。系统过程主要存储在储过程。系统过程主要存储在Master数据库中，并以数据库中，并以SP_为前缀，并且为前缀，并且系统存储过程主要是从系统表中获取信息，从而为系统管理员管理系统存储过程主要是从系统表中获取信息，从而为系统管理员管理SQLServer提供支持。通过系统存储过程，提供支持。通过系统存储过程，SQLServer中的许多管理性或信息性的活动中的许多管理性或信息性的活动:如了解数据库对象、数据库信息如了解数据库对象、数据库信息都可以被顺利有效地完成。尽管这些系统存储过程被放在都可以被顺利有效地完成。尽管这些系统存储过程被放在master数据库数据库中，但是仍

5、可以在其它数据库中对其进行调用，在调用时不必在存储过中，但是仍可以在其它数据库中对其进行调用，在调用时不必在存储过程名前加上数据库名，而且当创建一个新数据库时，一些系统存储过程程名前加上数据库名，而且当创建一个新数据库时，一些系统存储过程会在新数据库中被自动创建。用户自定义存储过程是由用户创建并能完会在新数据库中被自动创建。用户自定义存储过程是由用户创建并能完成某一特定功能成某一特定功能(如查询用户所需数据信息如查询用户所需数据信息)的存储过程。的存储过程。存储过程时存放在存储过程时存放在SQLServer中的特别快的数据库对象，当首次运中的特别快的数据库对象，当首次运行存储过程时，它按以下方

6、式进行行存储过程时，它按以下方式进行:1、该过程被划分成部件片断。、该过程被划分成部件片断。2、检查引用数据库中其它对蒙、检查引用数据库中其它对蒙(表、视图等表、视图等)的部件，确保引用的对的部件，确保引用的对象是存在的，这也被称为分解。象是存在的，这也被称为分解。3、一旦分解完成，该过程的名字将存放倒、一旦分解完成，该过程的名字将存放倒sysobjects表中，而创建表中，而创建存储过程的代码存放在存储过程的代码存放在syscomments表中表中4、然后编译，并且，编译过程中将创建如何运行查询的蓝本。该蓝、然后编译，并且，编译过程中将创建如何运行查询的蓝本。该蓝本通称称为常规计划或查询树，

7、查询树存放在本通称称为常规计划或查询树，查询树存放在sysProcetlures表中。表中。5、存储过程首次运行时，读出查询计划并完全编译成过程计划，然、存储过程首次运行时，读出查询计划并完全编译成过程计划，然后运行。这样，节约了每次运行存成过程的语法检查、分解和编译查询后运行。这样，节约了每次运行存成过程的语法检查、分解和编译查询树的时间。树的时间。当利用当利用SQLServer创建一个应用程序时，创建一个应用程序时，T-SQL是一种主是一种主要的编程语言。若运用要的编程语言。若运用T一一SQL来进行编程有两种方法来进行编程有两种方法:其一是在本地存储其一是在本地存储T-SQL程序并创建应用

8、程序，向程序并创建应用程序，向SQL-Server发送命令米对结果进行发送命令米对结果进行T-SQL编写的程序作为存储编写的程序作为存储过程其二是可以把部分用在过程其二是可以把部分用在SQLServer中，并创建应用程中，并创建应用程序来调用存储过程，对数据结果进行处理。存储过程能够序来调用存储过程，对数据结果进行处理。存储过程能够通过接收参数向调用者返回结果集，结果集的格式由调用通过接收参数向调用者返回结果集，结果集的格式由调用者确定者确定;返回状态值给调用者，指明调用是成功或是失败返回状态值给调用者，指明调用是成功或是失败:包括针对数据库的操作语句，并且可以在一个存储过程中包括针对数据库的

9、操作语句，并且可以在一个存储过程中调用另一存储过程。调用另一存储过程。我们通常更偏爱于使用第二种方法，即在我们通常更偏爱于使用第二种方法，即在SQLServer中使中使用存储过程，而不是在客户计算机上调用用存储过程，而不是在客户计算机上调用T一一QL编写的一编写的一段程序原因在于存储过程具有以下优点段程序原因在于存储过程具有以下优点:存储过程的优点可用存储过程封装事务规则。一旦封装完成，这些规可用存储过程封装事务规则。一旦封装完成，这些规则就可用于多个应用，从而有一个一致的数据接口，则就可用于多个应用，从而有一个一致的数据接口，因此，只需改变过程的功能，只需在一个地方对其进因此，只需改变过程的

10、功能，只需在一个地方对其进行修改，而不必对每个应用都进行修改。行修改，而不必对每个应用都进行修改。存储过程允许标准组件式编程存储过程允许标准组件式编程:存储过程在被创存储过程在被创建以后，可以在程序中被多次调用而不必重新编建以后，可以在程序中被多次调用而不必重新编写该存储过程的写该存储过程的SQL语句语句;而且数据库专业人员而且数据库专业人员可随时对存储过程进行修改，但对应用程序源代可随时对存储过程进行修改，但对应用程序源代码毫无影响，因为应用程序源代码只包含存储过码毫无影响，因为应用程序源代码只包含存储过程的调用语句，从而极大地提高了程序的可移植程的调用语句，从而极大地提高了程序的可移植性。

11、性。存储过程能够实现较快的执行速度存储过程能够实现较快的执行速度:如果某一操作包含大如果某一操作包含大量的量的TSQL代码或分别被多次执行，那么存储过程要比代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的，在批处理的执行速度快很多。因为存储过程是预编译的，在首次运行一个存储过程时，查询优化器对其进行分析优化，首次运行一个存储过程时，查询优化器对其进行分析优化，并给出最终被存在系统表中的执行计划并给出最终被存在系统表中的执行计划;而批处理的而批处理的T-SQL语句在每次运行时都要进行编译和优化，因此速度相语句在每次运行时都要进行编译和优化，因此速度相对要慢一些

12、。对要慢一些。存储过程能够减少网络流量存储过程能够减少网络流量:对于同一个针对数据数据库对于同一个针对数据数据库对象的操作对象的操作(如查询修改如查询修改)，如果这一操作所涉及到的，如果这一操作所涉及到的T-SQL语句被组织成一存储过程，那么当在客户计算机上语句被组织成一存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句调用该存储过程时，网络中传送的只是该调用语句;否则否则将是多条将是多条SQL语句，从而大大增加了网络流量，降低网语句，从而大大增加了网络流量，降低网络负载。络负载。存储过程可被作为一种安全机制来充分利用存储过程可被作为一种安全机制来充分利用:系统管理系统

13、管理员通过对执行某一存储过程的权限进行限制，从而能够员通过对执行某一存储过程的权限进行限制，从而能够实现对相应的数据访问权限的限制，避免非授权用户对实现对相应的数据访问权限的限制，避免非授权用户对数据的访问，保证数据的安全。数据的访问，保证数据的安全。创建存储过程的规则几乎任何可以写成批处理的几乎任何可以写成批处理的T一一SQL代码都可用于创建存代码都可用于创建存储过程，但是在设计存储过程时，需要遵循下列规则储过程，但是在设计存储过程时，需要遵循下列规则:名字必须符合名字必须符合SQLServer命名规则。命名规则。引用的对象必须在创建存储过程前就存在引用的对象必须在创建存储过程前就存在不能在

14、单个存储过程中创建后去掉或再创建同名的对象。不能在单个存储过程中创建后去掉或再创建同名的对象。存储过程最后能有存储过程最后能有255各参数。各参数。创建存储过程的规则再自己的存储过程中可以引用临时表，局部临时表再过程结束时将再自己的存储过程中可以引用临时表，局部临时表再过程结束时将会消失。会消失。再存储过程中不能有如下的再存储过程中不能有如下的SQL创建语句创建语句:CreateDefault、CreateProcedure、CreateRule、CreateTrigger、CreateView.可在过程中嵌套过程。可在过程中嵌套过程。创建存储过程的文本不能超过创建存储过程的文本不能超过64K

15、字节，以为字节，以为SQL存放再存放再syscomments表中。表中。若在存储过程中使用了若在存储过程中使用了Select*，而底层表中加入了新的列，新的列，而底层表中加入了新的列，新的列再过程运行时无法显示。再过程运行时无法显示。1 创建存储过程 在在SQL SQL ServerServer中中，可可以以使使用用三三种种方方法法创创建建存存储过程储过程 ：使用创建存储过程向导创建存储过程。使用创建存储过程向导创建存储过程。利用利用SQL Server SQL Server 企业管理器创建存储过程。企业管理器创建存储过程。使使 用用 Transact-SQLTransact-SQL语语 句句

16、 中中 的的 CREATE CREATE PROCEDUREPROCEDURE命令创建存储过程。命令创建存储过程。需要确定存储过程的三个组成部分：所有的输入参数以及传给调用者的输出参数。所有的输入参数以及传给调用者的输出参数。被被执执行行的的针针对对数数据据库库的的操操作作语语句句，包包括括调调用用其其它存储过程的语句。它存储过程的语句。返返回回给给调调用用者者的的状状态态值值，以以指指明明调调用用是是成成功功还还是失败。是失败。1.使用创建存储过程向导创建存储过程 在在企企业业管管理理器器中中，选选择择工工具具菜菜单单中中的的向向导导选选项项，选选择择“创创建建存存储储过过程程向向导导”根根

17、据据提提示示可可完成创建存储过程。完成创建存储过程。2.使用SQL Server 企业管理器创建存储过程（）在在SQL SQL ServerServer企企业业管管理理器器中中，选选择择指指定定的的服服务务器器和和数数据据库库，用用右右键键单单击击要要创创建建存存储储过过程程的的数数据据库库，在在弹弹出出的的快快捷捷菜菜单单中中选选择择“新新建建”选选项项，再再选选择择下下一一级级菜单中的菜单中的“存储过程存储过程”选项选项（）在在文文本本框框中中可可以以输输入入创创建建存存储储过过程程的的T_SQLT_SQL语语句句，单单击击“检检查查语语法法”，则则可可以以检检查查语语法法是是否否正正确确

18、；单单击击“确确定定”按按钮钮，即即可可保保存存该该存存储储过过程程。如如果果要要设设置置权权限，单击限，单击“权限权限”按钮。按钮。3.使用Transact-SQL语句创建存储过程创建存储过程前，应该考虑下列几个事项：创建存储过程前，应该考虑下列几个事项：不不能能将将 CREATE CREATE PROCEDURE PROCEDURE 语语句句与与其其它它 SQL SQL 语语句句组组合到单个批处理中。合到单个批处理中。创创建建存存储储过过程程的的权权限限默默认认属属于于数数据据库库所所有有者者，该该所所有有者可将此权限授予其他用户。者可将此权限授予其他用户。存储过程是数据库对象，其名称必须

19、遵守标识符规则。存储过程是数据库对象，其名称必须遵守标识符规则。只能在当前数据库中创建存储过程。只能在当前数据库中创建存储过程。一个存储过程的最大尺寸为一个存储过程的最大尺寸为128M128M。CREATE PROCEDURE的语法形式：CREATEPROCEDUREprocedure_name;numberparameterdata_typeVARYING=defaultOUTPUT,.nWITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONFORREPLICATIONASsql_statement.nCREATE PROCEDURE的语法参数的意义：pr

20、ocedure_name：用于指定要创建的存储过程的名称。用于指定要创建的存储过程的名称。number：该参数是可选的整数，它用来对同名的存储该参数是可选的整数，它用来对同名的存储过程分组，以便用一条过程分组，以便用一条DROPPROCEDURE语句即可语句即可将同组的过程一起除去。将同组的过程一起除去。parameter：过程中的参数。在过程中的参数。在CREATEPROCEDURE语句中可以声明一个或多个参数。语句中可以声明一个或多个参数。data_type：用于指定参数的数据类型。用于指定参数的数据类型。VARYING：用于指定作为输出用于指定作为输出OUTPUT参数支持的结参数支持的结

21、果集。果集。Default：用于指定参数的默认值。用于指定参数的默认值。OUTPUT：表明该参数是一个返回参数。表明该参数是一个返回参数。RECOMPILE：表明表明SQLServer不会保存该存储过程不会保存该存储过程的执行计划的执行计划。ENCRYPTION：表示表示SQLServer加密了加密了syscomments表，该表的表，该表的text字段是包含字段是包含CREATEPROCEDURE语句的存储过程文本。语句的存储过程文本。FORREPLICATION：用于指定不能在订阅服务器上执用于指定不能在订阅服务器上执行为复制创建的存储过程。行为复制创建的存储过程。AS：用于指定该存储过程

22、要执行的操作。用于指定该存储过程要执行的操作。sql_statement：是存储过程中要包含的任意数目和类是存储过程中要包含的任意数目和类型的型的Transact-SQL语句。语句。2 执行存储过程 直直接接执执行行存存储储过过程程可可以以使使用用EXECUTE命命令令来来执行，其语法形式如下：执行，其语法形式如下：EXECUTEreturn_status=procedure_name;number|procedure_name_varparameter=value|variableOUTPUT|DEFAULT,.nWITHRECOMPILE如果执行存储过程的语句是批中的第一个语句，可以省略如

23、果执行存储过程的语句是批中的第一个语句，可以省略execute关键字。关键字。returnstatus为整型局部变量，用于保存存储过程的返为整型局部变量，用于保存存储过程的返回值回值;procedure_name指定执行的存储过程的名称指定执行的存储过程的名称;number用来指定该存储过程与其它同名存储过程用来指定该存储过程与其它同名存储过程同组时的标识号。同组时的标识号。parameter:在创建过程时定义的过程参数。调用者向存在创建过程时定义的过程参数。调用者向存储过程所传递的参数值由储过程所传递的参数值由value参数或参数或variable变量提供，变量提供，或者使用或者使用defa

24、ult关键字指定使用该参数的默认值。关键字指定使用该参数的默认值。output参数说明指定参数为返回参数。参数说明指定参数为返回参数。注意注意:如果按如果按paramete=value|variable的形式为存储的形式为存储过程提供参数，可以不考虑创建存储过程时的参数顺序，过程提供参数，可以不考虑创建存储过程时的参数顺序，但是必须以这种方式提供该存储过程的全部参数。如果不但是必须以这种方式提供该存储过程的全部参数。如果不以这种方式提供参数，则必须按照创建存储过程时参数的以这种方式提供参数，则必须按照创建存储过程时参数的顺序提供参数。顺序提供参数。WithRecompile指定在实行存储过程时

25、重新编译执行计指定在实行存储过程时重新编译执行计划。划。例：CreateprocedureGetAvgnamevarchar(10),avgintoutputAsDeclareErrorSaveintSetErrorSave=0Selectavgp=AVG(项目项目)FromprojectaspINNERJOINpmanageraspmonp.负责人负责人ID=Pm.负责人负责人IDWherepm.姓名姓名=nameif(Error0)SetErrorSave=ErrorReturnErrorSavego例：使使用用EXECUTE命命令令传传递递单单个个参参数数，它它执执行行showind存存

26、储储过过程程，以以titles为为参参数数值值。showind存存储储过过程程需需要要参参数数(tabname)，它是一个表的名称。其程序清单如下：，它是一个表的名称。其程序清单如下：EXECshowindtitles当然，在执行过程中变量可以显式命名：当然，在执行过程中变量可以显式命名：EXECshowindtabname=titles如如果果这这是是isql脚脚本本或或批批处处理理中中第第一一个个语语句句，则则EXEC语语句句可以省略：可以省略：showindtitles或者或者showindtabname=titles3 查看和修改存储过程 查看存储过程查看存储过程存存储储过过程程被被创

27、创建建之之后后，它它的的名名字字就就存存储储在在系系统统表表sysobjects中中，它它 的的 源源 代代 码码 存存 放放 在在 系系 统统 表表syscomments中中。可可以以使使用用使使用用企企业业管管理理器器或或系系统统存存储过程来查看用户创建的存储过程。储过程来查看用户创建的存储过程。（1）使用企业管理器查看用户创建的存储过程 在企业管理器中，打开指定的服务器和数在企业管理器中，打开指定的服务器和数据库项，选择要创建存储过程的数据库，单击存据库项，选择要创建存储过程的数据库，单击存储过程文件夹，此时在右边的页框中显示该数据储过程文件夹，此时在右边的页框中显示该数据库的所有存储过

28、程。用右键单击要查看的存储过库的所有存储过程。用右键单击要查看的存储过程，从弹出的快捷菜单中选择属性选项，此时便程，从弹出的快捷菜单中选择属性选项，此时便可以看到存储过程的源代码。可以看到存储过程的源代码。（2）使用系统存储过程来查看用户创建的存储过程 可供使用的系统存储过程及其语法形式如下：可供使用的系统存储过程及其语法形式如下：sp_help：用于显示存储过程的参数及其数据类型：用于显示存储过程的参数及其数据类型sp_helpobjname=name参数参数name为要查看的存储过程的名称。为要查看的存储过程的名称。sp_helptext：用于显示存储过程的源代码：用于显示存储过程的源代码

29、sp_helptextobjname=name参数参数name为要查看的存储过程的名称。为要查看的存储过程的名称。sp_depends：用于显示和存储过程相关的数据库对象：用于显示和存储过程相关的数据库对象sp_dependsobjname=object参数参数object为要查看依赖关系的存储过程的名称。为要查看依赖关系的存储过程的名称。sp_stored_procedures：用于返回当前数据库中的存储过程列表：用于返回当前数据库中的存储过程列表2.修改存储过程存存储储过过程程可可以以根根据据用用户户的的要要求求或或者者基基表表定定义义的的改改变变而而改改变变。使使用用ALTER PROC

30、EDURE语语 句句 可可 以以 更更 改改 先先 前前 通通 过过 执执 行行 CREATEPROCEDURE语语句句创创建建的的过过程程，但但不不会会更更改改权权限限，也也不不影影响响相相关关的的存储过程或触发器。其语法形式如下：存储过程或触发器。其语法形式如下：ALTERPROCEDUREprocedure_name;numberparameterdata_typeVARYING=defaultOUTPUT,.nWITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONFORREPLICATIONASsql_statement.n4 重命名和删除存储过程1

31、.重命名存储过程重命名存储过程修修改改存存储储过过程程的的名名称称可可以以使使用用系系统统存存储储过过程程sp_rename，其语法形式如下：，其语法形式如下：sp_rename原原存存储储过过程程名名称称，新新存存储储过过程程名称名称另外，通过企业管理器也可以修改存储过程另外，通过企业管理器也可以修改存储过程的名称。的名称。2.删除存储过程删删除除存存储储过过程程可可以以使使用用DROP命命令令，DROP命命令令可可以以将将一一个个或或者者多多个个存存储储过过程程或或者者存存储储过过程程组组从从当前数据库中删除，其语法形式如下：当前数据库中删除，其语法形式如下：dropprocedurepr

32、ocedure,n当然，利用企业管理器也可以很方便地删除存储当然，利用企业管理器也可以很方便地删除存储过程。过程。在存储过程中使用参数例例:下面的存储过程有下面的存储过程有5个传入参数，求其平均值，而后将个传入参数，求其平均值，而后将平均值赋给平均值赋给output。CreateProcedurescoresscore1smallint,score2smallint,score3smallint,score4smallint,score5smallint,myAvgsmallintOutputAsselectmyAvg=(scarel+scare2+scare3+scare4+scare5)/

33、5要调用要调用myAvg的值，一首先要定义一个变量，然的值，一首先要定义一个变量，然后才能运行该程序。后才能运行该程序。DeclareAvgScoresmallintExecscores113,9,8,8,113,AvgScnreOutputSelectTheAverageScoreis:，AvgScnrego在将值传递给存储过程时，可以按位置顺序传入在将值传递给存储过程时，可以按位置顺序传入(称为按位置传入称为按位置传入)，也可按引用转参数，即用参数名称值的方式传入参数。当按引，也可按引用转参数，即用参数名称值的方式传入参数。当按引用传入参数时，可按任何次序传入参数。用传入参数时，可按任何次

34、序传入参数。例例:declareAvgscoresmallintExecscoresscore1=10,score3=9,score2=8,score4=8,score5=10,myAvg=AvgScoreOutputSelectTheAveragescoreis,AvgScoreGo注意注意:如果开始时己经按引用传入，则整个过程调用必须按引如果开始时己经按引用传入，则整个过程调用必须按引用传入，不能在存储过程调用中在按位置传入和按参数传入二者用传入，不能在存储过程调用中在按位置传入和按参数传入二者之间掉换。之间掉换。也可用也可用return关键字来将信息返回给调用过程。关键字来将信息返回给调

35、用过程。这将会直接将一个变量返回给调用过程，而无需这将会直接将一个变量返回给调用过程，而无需要求有存储过程定义及调用过程都要有的要求有存储过程定义及调用过程都要有的output声明。声明。例例:createprocMyReturnt1smallint,t2smallint,retvalsmallintAsselectretval=t1+t2Returnretval创建该过程后，可输入以下内容对其调用创建该过程后，可输入以下内容对其调用:DeclaremyReturnValuesmallintExecmyReturnValue=myReturn9,9,0SelectThereturnvaluei

36、s，myReturnValueWith Recompile可以在可以在CreateProcedure语句或语句或Execprocedure语句后增加语句后增加WithRecompile语句，其语句，其位置将影响存储过程的处理和运行方式。位置将影响存储过程的处理和运行方式。用用WithRecompile创建过程创建过程:在在CreateProcedure中使用中使用WithRecompile后，执行划后，执行划将不被存入将不被存入Catch.每次运行时都要重新编译整个每次运行时都要重新编译整个过程，这气标准查询的处理方式很相似，这种方过程，这气标准查询的处理方式很相似，这种方式在存储过程带有使常规执行效率较低的参数时式在存储过程带有使常规执行效率较低的参数时是很有帮助的，通过每次重新编译，过程可针对是很有帮助的，通过每次重新编译，过程可针对新参数进行优化执行。新参数进行优化执行。