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

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

1、数据库原理与应用数据库原理与应用第九讲第九讲 存储过程的创建和使用存储过程的创建和使用1、创建存储过程、创建存储过程2、执行存储过程、执行存储过程3、查看和修改存储过程、查看和修改存储过程4、重命名和删除存储过程、重命名和删除存储过程 本讲主要内容 存储过程的概念SQL ServerSQL Server提供了一种方法，它可以将一些固定的提供了一种方法，它可以将一些固定的操作集中起来由操作集中起来由SQL ServerSQL Server数据库服务器来完成，以实数据库服务器来完成，以实现某个任务，这种方法就是存储过程。现某个任务，这种方法就是存储过程。类似于类似于DOS一下一下的批处理。存储过程

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

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

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

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

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

7、中。表中。5、存储过程首次运行时，读出查询计划并完全编译成过程计划，然、存储过程首次运行时，读出查询计划并完全编译成过程计划，然后运行。这样，节约了每次运行存成过程的语法检查、分解和编译查询后运行。这样，节约了每次运行存成过程的语法检查、分解和编译查询树的时间。树的时间。当利用当利用SQL Server创建一个应用程序时，创建一个应用程序时，T-SQL是一种主是一种主要的编程语言。若运用要的编程语言。若运用T一一SQL来进行编程有两种方法来进行编程有两种方法:其一是在本地存储其一是在本地存储T-SQL程序并创建应用程序，向程序并创建应用程序，向SQL-Server发送命令米对结果进行发送命令米

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

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

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

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

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

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

14、或再创建同名的对象。存储过程最后能有存储过程最后能有255各参数。各参数。创建存储过程的规则再自己的存储过程中可以引用临时表，局部临时表再过程结束时将再自己的存储过程中可以引用临时表，局部临时表再过程结束时将会消失。会消失。再存储过程中不能有如下的再存储过程中不能有如下的SQL创建语句创建语句:Create Default、Create Procedure、Create Rule、Create Trigger、Create View.可在过程中嵌套过程。可在过程中嵌套过程。创建存储过程的文本不能超过创建存储过程的文本不能超过64K字节，以为字节，以为SQL存放再存放再syscomments表中

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

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

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

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

19、其名称必须遵守标识符规则。只能在当前数据库中创建存储过程。只能在当前数据库中创建存储过程。一个存储过程的最大尺寸为一个存储过程的最大尺寸为128M128M。CREATE PROCEDURE的语法形式：CREATEPROCEDUREprocedure_name;number p a r a m e t e r d a t a _ t y p e V A R Y I N G =d e f a u l t O U T P U T,.n WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION F O R R E P L I C A T I O N AS sql_s

20、tatement .n CREATE PROCEDURE的语法参数的意义：procedure_name：用于指定要创建的存储过程的名称。：用于指定要创建的存储过程的名称。number：该参数是可选的整数，它用来对同名的存储：该参数是可选的整数，它用来对同名的存储过程分组，以便用一条过程分组，以便用一条 DROP PROCEDURE 语句即可语句即可将同组的过程一起除去。将同组的过程一起除去。parameter：过程中的参数。在：过程中的参数。在 CREATE PROCEDURE 语句中可以声明一个或多个参数。语句中可以声明一个或多个参数。data_type：用于指定参数的数据类型。：用于指定参

21、数的数据类型。VARYING：用于指定作为输出：用于指定作为输出OUTPUT参数支持的结参数支持的结果集。果集。Default：用于指定参数的默认值。：用于指定参数的默认值。OUTPUT：表明该参数是一个返回参数。：表明该参数是一个返回参数。RECOMPILE：表明：表明 SQL Server 不会保存该存储过程不会保存该存储过程的执行计划的执行计划。ENCRYPTION：表示：表示 SQL Server 加密了加密了 syscomments 表，该表的表，该表的text字段是包含字段是包含 CREATE PROCEDURE 语句的存储过程文本。语句的存储过程文本。FOR REPLICATIO

22、N：用于指定不能在订阅服务器上执：用于指定不能在订阅服务器上执行为复制创建的存储过程。行为复制创建的存储过程。AS：用于指定该存储过程要执行的操作。：用于指定该存储过程要执行的操作。sql_statement：是存储过程中要包含的任意数目和类：是存储过程中要包含的任意数目和类型的型的 Transact-SQL 语句。语句。2 执行存储过程 直接执行存储过程可以使用直接执行存储过程可以使用EXECUTE命令来命令来执行，其语法形式如下：执行，其语法形式如下：EXECUTE return_status=procedure_name;number|procedure_name_var paramet

23、er=value|variableOUTPUT|DEFAULT ,.n WITH RECOMPILE 如果执行存储过程的语句是批中的第一个语句，可以省略如果执行存储过程的语句是批中的第一个语句，可以省略execute关键字。关键字。return status为整型局部变量，用于保存存储过程的返为整型局部变量，用于保存存储过程的返回值回值;procedure_name指定执行的存储过程的名指定执行的存储过程的名称称;number用来指定该存储过程与其它同名存储过程用来指定该存储过程与其它同名存储过程同组时的标识号。同组时的标识号。parameter:在创建过程时定义的过程参数。调用者向在创建过程

24、时定义的过程参数。调用者向存储过程所传递的参数值由存储过程所传递的参数值由value参数或参数或variable变量变量提供，或者使用提供，或者使用default关键字指定使用该参数的默认值。关键字指定使用该参数的默认值。output参数说明指定参数为返回参数。参数说明指定参数为返回参数。注意注意:如果按如果按paramete=value|variable的形式为存储的形式为存储过程提供参数，可以不考虑创建存储过程时的参数顺序，过程提供参数，可以不考虑创建存储过程时的参数顺序，但是必须以这种方式提供该存储过程的全部参数。如果不但是必须以这种方式提供该存储过程的全部参数。如果不以这种方式提供参数

25、，则必须按照创建存储过程时参数的以这种方式提供参数，则必须按照创建存储过程时参数的顺序提供参数。顺序提供参数。With Recompile指定在实行存储过程时重新编译执行计指定在实行存储过程时重新编译执行计划。划。例：Create procedure GetAvgname varchar(10),avg int outputAs DeclareErrorSave intSetErrorSave=0Select avgp=AVG(项目项目)From project as p INNER JOIN pmanager as pmWhere pm.姓名姓名=nameif(Error 0)SetErro

26、rSave=ErrorReturnErrorSavego例：使用使用 EXECUTE 命令传递单个参数，它执行命令传递单个参数，它执行 showind 存存储过程，以储过程，以 titles 为参数值。为参数值。showind 存储过程需要参数存储过程需要参数(tabname)，它是一个表的名称。其程序清单如下：，它是一个表的名称。其程序清单如下：EXEC showind titles当然，在执行过程中变量可以显式命名：当然，在执行过程中变量可以显式命名：EXEC showind tabname=titles如果这是如果这是 isql 脚本或批处理中第一个语句，则脚本或批处理中第一个语句，则

27、EXEC 语句语句可以省略：可以省略：showind titles或者或者showind tabname=titles3 查看和修改存储过程 查看存储过程查看存储过程 存储过程被创建之后，它的名字就存储在系统表存储过程被创建之后，它的名字就存储在系统表s y s o b j e c t s 中，它 的 源 代 码 存 放 在 系 统 表中，它 的 源 代 码 存 放 在 系 统 表syscomments中。可以使用使用企业管理器或系统存中。可以使用使用企业管理器或系统存储过程来查看用户创建的存储过程。储过程来查看用户创建的存储过程。（1）使用企业管理器查看用户创建的存储过程 在企业管理器中，打

28、开指定的服务器和数在企业管理器中，打开指定的服务器和数据库项，选择要创建存储过程的数据库，单击存据库项，选择要创建存储过程的数据库，单击存储过程文件夹，此时在右边的页框中显示该数据储过程文件夹，此时在右边的页框中显示该数据库的所有存储过程。用右键单击要查看的存储过库的所有存储过程。用右键单击要查看的存储过程，从弹出的快捷菜单中选择属性选项，此时便程，从弹出的快捷菜单中选择属性选项，此时便可以看到存储过程的源代码。可以看到存储过程的源代码。（2）使用系统存储过程来查看用户创建的存储过程 可供使用的系统存储过程及其语法形式如下：可供使用的系统存储过程及其语法形式如下：sp_help：用于显示存储过

29、程的参数及其数据类型：用于显示存储过程的参数及其数据类型sp_help objname=name参数参数name为要查看的存储过程的名称。为要查看的存储过程的名称。sp_helptext：用于显示存储过程的源代码：用于显示存储过程的源代码 sp_helptext objname=name参数参数name为要查看的存储过程的名称。为要查看的存储过程的名称。sp_depends：用于显示和存储过程相关的数据库对象：用于显示和存储过程相关的数据库对象sp_depends objname=object参数参数object为要查看依赖关系的存储过程的名称。为要查看依赖关系的存储过程的名称。sp_stor

30、ed_procedures：用于返回当前数据库中的存储过程列表：用于返回当前数据库中的存储过程列表2.修改存储过程存储过程可以根据用户的要求或者基表定义的改变而改变。使用存储过程可以根据用户的要求或者基表定义的改变而改变。使用ALTER PROCEDURE语句可以更改先前通过执行语句可以更改先前通过执行 CREATE PROCEDURE 语句创建的过程，但不会更改权限，也不影响相关的语句创建的过程，但不会更改权限，也不影响相关的存储过程或触发器。其语法形式如下：存储过程或触发器。其语法形式如下：ALTERPROCEDUREprocedure_name;number p a r a m e t

31、e r d a t a _ t y p e V A R Y I N G =d e f a u l t O U T P U T ,.n WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONFORREPLICATION AS sql_statement .n 4 重命名和删除存储过程1.重命名存储过程重命名存储过程修改存储过程的名称可以使用系统存储过程修改存储过程的名称可以使用系统存储过程sp_rename，其语法形式如下：，其语法形式如下：sp_rename 原存储过程名称，新存储过程原存储过程名称，新存储过程名称名称另外，通过企业管理器也可以修改存储过程

32、另外，通过企业管理器也可以修改存储过程的名称。的名称。2.删除存储过程删除存储过程可以使用删除存储过程可以使用DROP命令，命令，DROP命令命令可以将一个或者多个存储过程或者存储过程组从可以将一个或者多个存储过程或者存储过程组从当前数据库中删除，其语法形式如下：当前数据库中删除，其语法形式如下：drop procedure procedure,n当然，利用企业管理器也可以很方便地删除存储当然，利用企业管理器也可以很方便地删除存储过程。过程。在存储过程中使用参数例例:下面的存储过程有下面的存储过程有5个传入参数，求其平均值，而后将个传入参数，求其平均值，而后将平均值赋给平均值赋给output。

33、Create Procedure scoresscore1 smallint,score2 smallint,score3 smallint,score4 smallint,score5 smallint,myAvg smallint OutputAs selectmyAvg=(scarel+scare2+scare3+scare4+scare5)/5要调用要调用myAvg的值，一首先要定义一个变量，然的值，一首先要定义一个变量，然后才能运行该程序。后才能运行该程序。Declare AvgScore smallintExec scores 113,9,8,8,113,AvgScnre Outp

34、utSelect The Average Score is:，AvgScnrego在将值传递给存储过程时，可以按位置顺序传入在将值传递给存储过程时，可以按位置顺序传入(称为按位置传入称为按位置传入)，也可按引用转参数，即用参数名称值的方式传入参数。当按引用也可按引用转参数，即用参数名称值的方式传入参数。当按引用传入参数时，可按任何次序传入参数。传入参数时，可按任何次序传入参数。例例:declare Avgscore smallintExec scoresscore1=10,score3=9,score2=8,score4=8,score5=10,myAvg=AvgScore OutputSel

35、ect The Average score is,AvgScoreGo注意注意:如果开始时己经按引用传入，则整个过程调用必须按引如果开始时己经按引用传入，则整个过程调用必须按引用传入，不能在存储过程调用中在按位置传入和按参数传入二者用传入，不能在存储过程调用中在按位置传入和按参数传入二者之间掉换。之间掉换。也可用也可用return关键字来将信息返回给调用过程。关键字来将信息返回给调用过程。这将会直接将一个变量返回给调用过程，而无需这将会直接将一个变量返回给调用过程，而无需要求有存储过程定义及调用过程都要有的要求有存储过程定义及调用过程都要有的output声明。声明。例例:create proc

36、 MyReturnt1 smallint,t2 smallint,retval smallintAs selectretval=t1+t2Return retval创建该过程后，可输入以下内容对其调用创建该过程后，可输入以下内容对其调用:DeclaremyReturnValue smallintExec myReturnValue=myReturn 9,9,0SelectThe return value is，myReturnValueWith Recompile可以在可以在Create Procedure语句或语句或Exec procedure语句后增加语句后增加With Recompile

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