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

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

2、QLSQL语句和可选控制流程语句的预编语句和可选控制流程语句的预编译集合。是一种封装反复义务操作的方法，以一个称号译集合。是一种封装反复义务操作的方法，以一个称号存储，作为一个单元处置。存储过程属于效力器方软件，存储，作为一个单元处置。存储过程属于效力器方软件，可立刻访问数据库可立刻访问数据库在在SQL ServerSQL Server中存储过程分为两类：即系统提供的中存储过程分为两类：即系统提供的存储过程和用户自定义的存储过程。存储过程和用户自定义的存储过程。 存储过程存储在数据库内，可由运用程序经过一个存储过程存储在数据库内，可由运用程序经过一个调用来执行，而且充许用户声明变量。同时，存储

3、过程调用来执行，而且充许用户声明变量。同时，存储过程可以接纳和输出参数、前往执行存储过程的形状值，也可以接纳和输出参数、前往执行存储过程的形状值，也可以嵌套调用。可以嵌套调用。留意留意:存储过程与函数不同，由于存储过程并不前往存储过程与函数不同，由于存储过程并不前往取代其称号的值，也不能直接在表达式中运用。取代其称号的值，也不能直接在表达式中运用。 在在SQL Server中存储过程分为两类中存储过程分为两类:系统提供的存储过程和用户存系统提供的存储过程和用户存储过程。系统过程主要存储在储过程。系统过程主要存储在Master数据库中，并以数据库中，并以SP_为前缀，并且为前缀，并且系统存储过程

4、主要是从系统表中获取信息，从而为系统管理员管理系统存储过程主要是从系统表中获取信息，从而为系统管理员管理SQL Server提供支持。经过系统存储过程，提供支持。经过系统存储过程，SQLServer中的许多管理性或信息性的活动中的许多管理性或信息性的活动:如了解数据库对象、数据库信息如了解数据库对象、数据库信息都可以被顺利有效地完成。虽然这些系统存储过程被放在都可以被顺利有效地完成。虽然这些系统存储过程被放在master数据库数据库中，但是仍可以在其它数据库中对其进展调用，在调用时不用在存储过中，但是仍可以在其它数据库中对其进展调用，在调用时不用在存储过程名前加上数据库名，而且当创建一个新数据

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

6、视图等表、视图等)的部件，确保援用的对的部件，确保援用的对象是存在的，这也被称为分解。象是存在的，这也被称为分解。3、一旦分解完成，该过程的名字将存放倒、一旦分解完成，该过程的名字将存放倒sysobjects表中，而创表中，而创建存储过程的代码存放在建存储过程的代码存放在syscomments表中表中4、然后编译，并且，编译过程中将创建如何运转查询的蓝本。该、然后编译，并且，编译过程中将创建如何运转查询的蓝本。该蓝本通称称为常规方案或查询树，查询树存放在蓝本通称称为常规方案或查询树，查询树存放在sysProcetlures表中。表中。5、存储过程初次运转时，读出查询方案并完全编译成过程方案，、

7、存储过程初次运转时，读出查询方案并完全编译成过程方案，然后运转。这样，节约了每次运转存成过程的语法检查、分解和编译查然后运转。这样，节约了每次运转存成过程的语法检查、分解和编译查询树的时间。询树的时间。当利用当利用SQL Server创建一个运用程序时，创建一个运用程序时，T-SQL是一种主是一种主要的编程言语。假设运用要的编程言语。假设运用T一一SQL来进展编程有两种方法来进展编程有两种方法:其一是在本地存储其一是在本地存储T-SQL程序并创建运用程序，向程序并创建运用程序，向SQL-Server发送命令米对结果进展发送命令米对结果进展T-SQL编写的程序作为存储编写的程序作为存储过程其二是

8、可以把部分用在过程其二是可以把部分用在SQL Server中，并创建运用程中，并创建运用程序来调用存储过程，对数据结果进展处置。存储过程可以序来调用存储过程，对数据结果进展处置。存储过程可以经过接纳参数向调用者前往结果集，结果集的格式由调用经过接纳参数向调用者前往结果集，结果集的格式由调用者确定者确定;前往形状值给调用者，指明调用是胜利或是失败前往形状值给调用者，指明调用是胜利或是失败:包括针对数据库的操作语句，并且可以在一个存储过程中包括针对数据库的操作语句，并且可以在一个存储过程中调用另一存储过程。调用另一存储过程。我们通常更偏爱于运用第二种方法，即在我们通常更偏爱于运用第二种方法，即在S

9、QL Server中运中运用存储过程，而不是在客户计算机上调用用存储过程，而不是在客户计算机上调用T一一QL编写的一编写的一段程序缘由在于存储过程具有以下优点段程序缘由在于存储过程具有以下优点:存储过程的优点可用存储过程封装事务规那么。一旦封装完成，这些可用存储过程封装事务规那么。一旦封装完成，这些规那么就可用于多个运用，从而有一个一致的数据接规那么就可用于多个运用，从而有一个一致的数据接口，因此，只需改动过程的功能，只需在一个地方对口，因此，只需改动过程的功能，只需在一个地方对其进展修正，而不用对每个运用都进展修正。其进展修正，而不用对每个运用都进展修正。存储过程允许规范组件式编程存储过程允

10、许规范组件式编程:存储过程在被创存储过程在被创建以后，可以在程序中被多次调用而不用重新编建以后，可以在程序中被多次调用而不用重新编写该存储过程的写该存储过程的SQL语句语句;而且数据库专业人员而且数据库专业人员可随时对存储过程进展修正，但对运用程序源代可随时对存储过程进展修正，但对运用程序源代码毫无影响，由于运用程序源代码只包含存储过码毫无影响，由于运用程序源代码只包含存储过程的调用语句，从而极大地提高了程序的可移植程的调用语句，从而极大地提高了程序的可移植性。性。存储过程可以实现较快的执行速度存储过程可以实现较快的执行速度:假设某一操作包含大假设某一操作包含大量的量的TSQL代码或分别被多次

11、执行，那么存储过程要比代码或分别被多次执行，那么存储过程要比批处置的执行速度快很多。由于存储过程是预编译的，在批处置的执行速度快很多。由于存储过程是预编译的，在初次运转一个存储过程时，查询优化器对其进展分析优化，初次运转一个存储过程时，查询优化器对其进展分析优化，并给出最终被存在系统表中的执行方案并给出最终被存在系统表中的执行方案;而批处置的而批处置的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 PROCEDUREPROCEDURE命令创建存储过程。命令创建存储过

16、程。 需求确定存储过程的三个组成部分：一切的输入参数以及传给调用者的输出参数。一切的输入参数以及传给调用者的输出参数。被执行的针对数据库的操作语句，包括调用其被执行的针对数据库的操作语句，包括调用其它存储过程的语句。它存储过程的语句。前往给调用者的形状值，以指明调用是胜利还前往给调用者的形状值，以指明调用是胜利还是失败。是失败。1. 运用创建存储过程导游创建存储过程 在企业管理器中，选择工具菜单中的导游在企业管理器中，选择工具菜单中的导游选项，选择选项，选择“创建存储过程导游创建存储过程导游 根据提示可根据提示可完成创建存储过程。完成创建存储过程。2. 运用SQL Server 企业管理器创建

17、存储过程 在在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_statement .n CREATE

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

21、：用于指定作为输出：用于指定作为输出OUTPUT参数支持的结参数支持的结果集。果集。 Default：用于指定参数的默许值。：用于指定参数的默许值。 OUTPUT：阐明该参数是一个前往参数。：阐明该参数是一个前往参数。 RECOMPILE：阐明：阐明 SQL Server 不会保管该存储过程不会保管该存储过程的执行方案的执行方案 。ENCRYPTION ：表示：表示 SQL Server 加密了加密了 syscomments 表，该表的表，该表的text字段是包含字段是包含 CREATE PROCEDURE 语句的存储过程文本。语句的存储过程文本。 FOR REPLICATION：用于指定不能

22、在订阅效力器上：用于指定不能在订阅效力器上执行为复制创建的存储过程。执行为复制创建的存储过程。 AS：用于指定该存储过程要执行的操作。：用于指定该存储过程要执行的操作。sql_statement：是存储过程中要包含的恣意数目和类：是存储过程中要包含的恣意数目和类型的型的 Transact-SQL 语句。语句。2 执行存储过程 直接执行存储过程可以运用直接执行存储过程可以运用EXECUTE命令来命令来执行，其语法方式如下：执行，其语法方式如下：EXECUTE r e t u r n _ s t a t u s = procedure_name;number|procedure_name_var

23、parameter=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 pmon p.担任人担任人ID=Pm.担任人担任人IDWh

26、ere pm.姓名姓名=nameif(Error 0) SetErrorSave=ErrorReturnErrorSavego例： 运用运用 EXECUTE 命令传送单个参数，它执行命令传送单个参数，它执行 showind 存存储过程，以储过程，以 titles 为参数值。为参数值。showind 存储过程需求参数存储过程需求参数 (tabname)，它是一个表的称号。其程序清单如下：，它是一个表的称号。其程序清单如下：EXEC showind titles当然，在执行过程中变量可以显式命名：当然，在执行过程中变量可以显式命名：EXEC showind tabname = titles假设这是

27、假设这是 isql 脚本或批处置中第一个语句，那么脚本或批处置中第一个语句，那么 EXEC 语语句可以省略：句可以省略：showind titles或者或者showind tabname = titles3 查看和修正存储过程 查看存储过程查看存储过程 存储过程被创建之后，它的名字就存储在系统表存储过程被创建之后，它的名字就存储在系统表sysobjects中，它的源代码存放在系统表中，它的源代码存放在系统表syscomments中。可以运用运用企业管理器或系统存中。可以运用运用企业管理器或系统存储过程来查看用户创建的存储过程。储过程来查看用户创建的存储过程。1运用企业管理器查看用户创建的存储过

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

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

30、过程的称号。sp_stored_procedures：用于前往当前数据库中的存储过程列表：用于前往当前数据库中的存储过程列表2. 修正存储过程存储过程可以根据用户的要求或者基表定义的改动而改动。运用存储过程可以根据用户的要求或者基表定义的改动而改动。运用ALTER PROCEDURE语句可以更改先前经过执行语句可以更改先前经过执行 CREATE PROCEDURE 语句创建的过程，但不会更改权限，也不影响相关的语句创建的过程，但不会更改权限，也不影响相关的存储过程或触发器。其语法方式如下：存储过程或触发器。其语法方式如下：ALTERPROCEDUREprocedure_name;number

31、parameterdata_type 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个传入参数，求其平均值，而后将个传入参数，求其平均值，而后将平均值赋给平均值

33、赋给output。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 OutputSelect The Average Score is:，AvgScnrego在将值传送给存储过程时，可以按位置顺序传入在将值传送给存储过程时，可以按位置顺序传入(称为按位置传称为按位置传入入)