《存储过程》PPT课件.ppt

《《存储过程》PPT课件.ppt》由会员分享，可在线阅读，更多相关《《存储过程》PPT课件.ppt（30页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、第第8 8章章 存储过程存储过程使用使用SQL Server创建应用程序时，创建应用程序时，SQL编程语言是编程语言是应用程序和应用程序和SQL Server数据库之间的主要编程接口。使用数据库之间的主要编程接口。使用SQL程序时，可用两种方法存储和执行程序。程序时，可用两种方法存储和执行程序。可以将程序存储在本地，并创建向可以将程序存储在本地，并创建向SQL Server发送命发送命令并处理结果的应用程序。令并处理结果的应用程序。也可以将程序作为存储过程存储在也可以将程序作为存储过程存储在SQL Server中，并中，并创建执行存储过程并处理结果的应用程序。创建执行存储过程并处理结果的应用程

2、序。8.1 8.1 了解存储过程了解存储过程在在SQL Server中有多种可用的存储过程：系统存储过中有多种可用的存储过程：系统存储过程，用户自定义的存储过程和扩展存储过程。本节了解这程，用户自定义的存储过程和扩展存储过程。本节了解这些存储过程的作用和用法。些存储过程的作用和用法。8.1.1 8.1.1 存储过程的作用存储过程的作用存储过程类似于程序中的一个方法，方法中可以包含存储过程类似于程序中的一个方法，方法中可以包含多行代码。存储过程也是封装多条多行代码。存储过程也是封装多条SQL语句的方法。开发语句的方法。开发人员可以在以下情况下使用存储过程。人员可以在以下情况下使用存储过程。（1）

3、包含输入参数和输出参数，执行多个批处理语）包含输入参数和输出参数，执行多个批处理语句。句。（2）包含多条）包含多条SQL语句，用来控制或操作数据库。语句，用来控制或操作数据库。（3）向调用程序返回状态值，以指示成功或失败，）向调用程序返回状态值，以指示成功或失败，如果失败，还给出失败的原因。如果失败，还给出失败的原因。8.1.2 8.1.2 存储过程的种类存储过程的种类存储过程主要分为：用户定义的存储过程、扩展存储存储过程主要分为：用户定义的存储过程、扩展存储过程和系统存储过程。过程和系统存储过程。1用户定义的存储过程用户定义的存储过程2扩展存储过程扩展存储过程3系统存储过程系统存储过程8.2

4、 8.2 创建存储过程创建存储过程可以使用可以使用SQL Server Management Studio中的对象中的对象资源管理器创建资源管理器创建SQL存储过程，也可以使用存储过程，也可以使用SQL语句语句CREATE PROCEDURE来创建存储过程。因为还没有详细来创建存储过程。因为还没有详细介绍介绍SQL语句，所以本节主要介绍使用语句，所以本节主要介绍使用SQL Server Management Studio来创建存储过程。来创建存储过程。创建存储过程时，应指定：创建存储过程时，应指定：所有输入参数和返回的输出参数。所有输入参数和返回的输出参数。执行数据库操作（包括调用其他过程）的

5、编程语句。执行数据库操作（包括调用其他过程）的编程语句。返回至调用过程以标识成功或失败的状态值。返回至调用过程以标识成功或失败的状态值。捕获和处理潜在的错误所需的任何错误处理语句。捕获和处理潜在的错误所需的任何错误处理语句。8.2.1 8.2.1 命名存储过程命名存储过程因为默认的系统存储过程是以因为默认的系统存储过程是以sp_开头，所以在创建开头，所以在创建存储过程时要注意，前缀可以是公司简介、数据库简称等，存储过程时要注意，前缀可以是公司简介、数据库简称等，尽量不要用尽量不要用sp。如果用户自定义的存储过程与系统存储过。如果用户自定义的存储过程与系统存储过程名称相同，则该存储过程将永远不会

6、执行，系统始终执程名称相同，则该存储过程将永远不会执行，系统始终执行的是系统存储过程。行的是系统存储过程。8.2.2 8.2.2 用用SQL Server Management StudioSQL Server Management Studio创创建存储过程建存储过程本节介绍如何使用本节介绍如何使用SQL Server Management Studio中的对象资源管理器创建中的对象资源管理器创建SQL存储过程，并提供一个在存储过程，并提供一个在AdventureWorks数据库中创建简单存储过程的示例。数据库中创建简单存储过程的示例。1创建存储过程创建存储过程2创建存储过程的示例创建存储过

7、程的示例8.3 8.3 执行存储过程执行存储过程若要执行存储过程，可以使用若要执行存储过程，可以使用EXECUTE语句。如果语句。如果存储过程是批处理中的第一条语句，那么不使用存储过程是批处理中的第一条语句，那么不使用EXECUTE也可以执行存储过程。也可以执行存储过程。8.3.1 8.3.1 执行系统存储过程执行系统存储过程系统存储过程以字符系统存储过程以字符sp_开头，笔者建议使用开头，笔者建议使用sys架构架构名称对所有系统存储名称进行限定，以防止名称冲突。以名称对所有系统存储名称进行限定，以防止名称冲突。以下示例说明执行系统存储过程的推荐方法。下示例说明执行系统存储过程的推荐方法。EX

8、EC sys.sp_who;8.3.2 8.3.2 执行用户定义存储过程执行用户定义存储过程执行用户定义存储过程时，笔者建议至少用架构名称执行用户定义存储过程时，笔者建议至少用架构名称限定存储过程名称。以下示例说明执行用户定义存储过程限定存储过程名称。以下示例说明执行用户定义存储过程的推荐方法。的推荐方法。USE AdventureWorks;GOEXEC dbo.uspGetEmployeeManagers 50;或者：或者：EXEC AdventureWorks.dbo.uspGetEmployeeManagers 50;GO8.3.3 8.3.3 指定存储过程的参数指定存储过程的参数指定

9、存储过程的参数，通常包括指定参数、指定参数指定存储过程的参数，通常包括指定参数、指定参数顺序和使用参数的默认值。本节将学会使用输入参数和输顺序和使用参数的默认值。本节将学会使用输入参数和输出参数。出参数。如果编写的存储过程允许接受参数，则在执行存储过如果编写的存储过程允许接受参数，则在执行存储过程时，需要为其指定参数。参数值必须是常量或变量，不程时，需要为其指定参数。参数值必须是常量或变量，不能将函数、视图等指定为参数值。变量可以是用户定义变能将函数、视图等指定为参数值。变量可以是用户定义变量或系统变量，如量或系统变量，如id。8.4 8.4 修改和删除存储过程修改和删除存储过程修改和删除存储

10、过程是实现存储过程的重要部分，当修改和删除存储过程是实现存储过程的重要部分，当创建完一个存储过程，可根据系统的需要，而重新进行修创建完一个存储过程，可根据系统的需要，而重新进行修改，甚至在不需要该存储过程的情况下，删除存储过程。改，甚至在不需要该存储过程的情况下，删除存储过程。8.4.1 8.4.1 修改存储过程修改存储过程在在SQL Server Management Studio中使用对象资源中使用对象资源管理器修改管理器修改SQL存储过程的操作步骤如下：存储过程的操作步骤如下：（1）在【对象资源管理器】窗口中，连接到某个数）在【对象资源管理器】窗口中，连接到某个数据库引擎实例，再展开该实

11、例。据库引擎实例，再展开该实例。（2）依次展开【数据库】【存储过程所属的数据）依次展开【数据库】【存储过程所属的数据库】【可编程性】节点。库】【可编程性】节点。（3）展开【存储过程】节点，右击需要修改的存储）展开【存储过程】节点，右击需要修改的存储过程，在弹出的快捷菜单中选择【修改】命令，打开修改过程，在弹出的快捷菜单中选择【修改】命令，打开修改存储过程脚本的窗口。存储过程脚本的窗口。（4）修改存储过程的脚本（）修改存储过程的脚本（SQL语句）。语句）。8.4.1 8.4.1 修改存储过程修改存储过程（5）若要测试语法，选择【查询】）若要测试语法，选择【查询】|【分析】命令，【分析】命令，进行

12、语法测试。进行语法测试。（6）若要修改存储过程，选择【查询】）若要修改存储过程，选择【查询】|【执行】命【执行】命令，执行存储过程。令，执行存储过程。（7）若要保存脚本，选择【文件】）若要保存脚本，选择【文件】|【保存】命令，【保存】命令，完成脚本修改的保存。完成脚本修改的保存。（8）接受文件名或使用新名称替换之，再单击【保）接受文件名或使用新名称替换之，再单击【保存】按钮，完成存储过程的修改。存】按钮，完成存储过程的修改。8.4.2 8.4.2 删除存储过程删除存储过程不需要存储过程时可将其删除，如果数据库中还有另不需要存储过程时可将其删除，如果数据库中还有另一个存储过程调用了已被删除的存储

13、过程，那么一个存储过程调用了已被删除的存储过程，那么SQL Server将在执行调用时提示错误消息。将在执行调用时提示错误消息。8.5 8.5 存储过程中的程序控制语句存储过程中的程序控制语句SQL Server2008中的存储过程支持一种编程模式，中的存储过程支持一种编程模式，可通过一些语句来控制程序的走向，如返回值大于可通过一些语句来控制程序的走向，如返回值大于1，则执，则执行行A选项，如果小于选项，如果小于1，则执行，则执行B选项。这是一个选择语句。选项。这是一个选择语句。本节就介绍存储过程的这些控制语句。本节就介绍存储过程的这些控制语句。8.5.1 8.5.1 控制语句概述控制语句概述

14、在在SQL Server中，程序控制语句用于控制中，程序控制语句用于控制Transact-SQL语句、语句块或存储过程的执行流程，其与常见的程语句、语句块或存储过程的执行流程，其与常见的程序设计语言类似。序设计语言类似。8.5.2 IFELSE8.5.2 IFELSE条件选择语句条件选择语句在在SQL Server中，为了控制程序的执行方向，提供了中，为了控制程序的执行方向，提供了IFELSE条件判断语句。条件判断语句。IFELSE语句的语法格式如下：语句的语法格式如下：IF ELSE 8.5.3 BEGINEND8.5.3 BEGINEND语句块语句块之所以把它称为语句块，是因为在之所以把它

15、称为语句块，是因为在BEGIN语句后，可语句后，可以书写大量代码，写完后再添加以书写大量代码，写完后再添加END语句。语句。位于位于BEGIN和和END之间的之间的Transact-SQL语句都属于语句都属于这个语句块，可视作一个单元来执行。执行这个语句块，可视作一个单元来执行。执行BEGIN END经常在条件语句（如经常在条件语句（如IFELSE）中使用，在）中使用，在BEGINEND中可嵌套，使用另外的中可嵌套，使用另外的BEGIN END 来定义来定义另一程序块。另一程序块。BEGINEND的语法格式如下：的语法格式如下：BEGIN END8.5.4 CASE8.5.4 CASE语句语句

16、CASE语句用于多条件分支选择，可完成计算多个条语句用于多条件分支选择，可完成计算多个条件并为每个条件返回单个值。件并为每个条件返回单个值。SQL Server的的CASE多分支多分支语句包括都支持可选语句包括都支持可选ELSE参数的两种格式：参数的两种格式：简单简单CASE表达式：该表达式将某个表达式与一组简表达式：该表达式将某个表达式与一组简单表达式进行比较以确定结果。单表达式进行比较以确定结果。搜索搜索CASE表达式：搜索计算一组布尔表达式以确定表达式：搜索计算一组布尔表达式以确定结果。结果。1简单简单CASE表达式表达式2搜索搜索CASE表达式表达式8.5.5 WHILE8.5.5 W

17、HILE循环语句循环语句WHILE语句有一个条件，只要符合这个条件，就重复语句有一个条件，只要符合这个条件，就重复执行执行SQL语句或语句块的循环内容。语句或语句块的循环内容。WHILE命令在设定的命令在设定的条件成立时，会重复执行条件成立时，会重复执行SQL语句或程序块。可以使用语句或程序块。可以使用BREAK和和CONTINUE关键字在循环内部控制关键字在循环内部控制WHILE循环循环中语句的执行。中语句的执行。WHILE语句也可以嵌套。语句也可以嵌套。WHILE循环结构的语法可表示如下：循环结构的语法可表示如下：WHILE BEGIN BREAK CONTINUE SQL语句或程序块语句

18、或程序块END8.5.6 WAITFOR8.5.6 WAITFOR等待语句等待语句WAITFOR语句指定延迟一段时间（时间间隔或一个语句指定延迟一段时间（时间间隔或一个时刻）来执行（触发）一个时刻）来执行（触发）一个Transact-SQL语句、语句块、语句、语句块、存储过程或事务。存储过程或事务。WAITFOR语句的语法如下：语句的语法如下：WAITFOR DELAY time_to_pass|TIME time_to_execute 8.6 8.6 存储过程必须注意的问题存储过程必须注意的问题合理地编写自己需要的存储过程，可以最大限度地利合理地编写自己需要的存储过程，可以最大限度地利用用S

19、QL Server的各种资源，作为一个初学者，在编写存储的各种资源，作为一个初学者，在编写存储过程时，可能会遇到很多的问题不能解决，或者对存储过过程时，可能会遇到很多的问题不能解决，或者对存储过程还有很多的疑问，这里说说在设计和使用存储过程中常程还有很多的疑问，这里说说在设计和使用存储过程中常会遇到的一些问题。会遇到的一些问题。8.6.1 8.6.1 从存储过程返回给定参数的值从存储过程返回给定参数的值已经建立的存储过程已经建立的存储过程myfirstprocedure返回了参数的返回了参数的值，根据返回值类型的不同，可以将存储过程分为值，根据返回值类型的不同，可以将存储过程分为3类：返类：返

20、回记录集的存储过程、返回数值的存储过程以及行为存储回记录集的存储过程、返回数值的存储过程以及行为存储过程。返回记录集的存储过程的执行结果是一个记录集，过程。返回记录集的存储过程的执行结果是一个记录集，典型的例子是从数据库中检索出符合某一个或几个条件的典型的例子是从数据库中检索出符合某一个或几个条件的记录；返回数值的存储过程执行完以后返回一个值；行为记录；返回数值的存储过程执行完以后返回一个值；行为存储过程仅仅是用来实现数据库的某个功能，而没有返回存储过程仅仅是用来实现数据库的某个功能，而没有返回值，例如在数据库中的更新和删除操作。值，例如在数据库中的更新和删除操作。8.6.2 8.6.2 处理

21、执行存储过程时的错误处理执行存储过程时的错误一般在写存储过程的时候，就应写上错误处理的语句一般在写存储过程的时候，就应写上错误处理的语句来处理错误。来处理错误。SQL Server有接近有接近3800个预定义错误代码，个预定义错误代码，错误定义的级别从错误定义的级别从025。20以上的错误代表重大错误，以上的错误代表重大错误，通常意味着该错误会导致存储进程立刻终止在访问存储进通常意味着该错误会导致存储进程立刻终止在访问存储进程的时候发生错误，可以通过程的时候发生错误，可以通过error系统函数返回错误代系统函数返回错误代码，通过查阅可以得到真实的错误信息和级别。码，通过查阅可以得到真实的错误信

22、息和级别。8.6.3 8.6.3 存储过程的语句要求存储过程的语句要求一般写存储过程给变量赋值要用一般写存储过程给变量赋值要用select或或set，两个都，两个都可以，如可以，如set name=cast(getdate()as varchar(20)。另外不要忘记另外不要忘记as，在建立存储过程中要用，在建立存储过程中要用as，如，如“create procedure example1 as”。SQL语句分行书写，每行语句最好有相应的注释，总语句分行书写，每行语句最好有相应的注释，总之规范书写格式，多一些注释，能让其他开发人员、维护之规范书写格式，多一些注释，能让其他开发人员、维护人员很容

23、易读懂代码、定位问题、提高工作效率。人员很容易读懂代码、定位问题、提高工作效率。8.6.4 8.6.4 执行存储过程无反应执行存储过程无反应执行存储过程的时候，没有一点反映，执行一直不结执行存储过程的时候，没有一点反映，执行一直不结束，是怎么回事？这种情况一般是发生了死锁现象，发生束，是怎么回事？这种情况一般是发生了死锁现象，发生死锁一般有如下几种可能：死循环、重复执行存储过程、死锁一般有如下几种可能：死循环、重复执行存储过程、表空间不够。表空间不够。8.7.1 8.7.1 存储过程和函数的区别存储过程和函数的区别存储过程是用户定义的一系列存储过程是用户定义的一系列SQL语句的集合，涉及语句的

24、集合，涉及特定表或其他对象的任务。用户可以调用存储过程，而函特定表或其他对象的任务。用户可以调用存储过程，而函数通常是数据库已定义的方法，它接收参数并返回某种类数通常是数据库已定义的方法，它接收参数并返回某种类型的值，函数并不涉及特定用户表。型的值，函数并不涉及特定用户表。本题主要针对一般的开发人员，因为存储过程和函数本题主要针对一般的开发人员，因为存储过程和函数在开发程序中使用的频率非常高。在开发程序中使用的频率非常高。8.7.2 8.7.2 存储过程是否也有输入和输出参数存储过程是否也有输入和输出参数存储过程是批处理地执行一段存储过程是批处理地执行一段SQL语句，这些语句中语句，这些语句中

25、可以定义变量，也可以定义输入参数和输出参数。通过这可以定义变量，也可以定义输入参数和输出参数。通过这些参数，存储过程可以方便地进行数据处理功能。对程序些参数，存储过程可以方便地进行数据处理功能。对程序开发人员设置此题，是为了让他们更好的学习如何通过存开发人员设置此题，是为了让他们更好的学习如何通过存储过程实现数据的输入和输出。储过程实现数据的输入和输出。8.8 8.8 小结小结存储过程是数据库操作中批量处理任务的最有效，也存储过程是数据库操作中批量处理任务的最有效，也是最为常用的对象之一。通过本章的学习，了解了存储过是最为常用的对象之一。通过本章的学习，了解了存储过程的概念和作用，也掌握了程的概念和作用，也掌握了SQL Server 2008数据库存储数据库存储过程对象的类型。本章的操作主要包括如何创建存储过程、过程对象的类型。本章的操作主要包括如何创建存储过程、修改和删除存储过程等。通过这些操作，读者可以亲自动修改和删除存储过程等。通过这些操作，读者可以亲自动手操作，加深对存储过程的了解。手操作，加深对存储过程的了解。