第8章 存储过程.pptVIP

第8章 存储过程 使用SQL Server创建应用程序时，SQL编程语言是应用程序和SQL Server数据库之间的主要编程接口。使用SQL程序时，可用两种方法存储和执行程序。 可以将程序存储在本地，并创建向SQL Server发送命令并处理结果的应用程序。 也可以将程序作为存储过程存储在SQL Server中，并创建执行存储过程并处理结果的应用程序。 8.1 了解存储过程 在SQL Server中有多种可用的存储过程：系统存储过程，用户自定义的存储过程和扩展存储过程。本节了解这些存储过程的作用和用法。 8.1.1 存储过程的作用 存储过程类似于程序中的一个方法，方法中可以包含多行代码。存储过程也是封装多条SQL语句的方法。开发人员可以在以下情况下使用存储过程。 （1）包含输入参数和输出参数，执行多个批处理语句。 （2）包含多条SQL语句，用来控制或操作数据库。 （3）向调用程序返回状态值，以指示成功或失败，如果失败，还给出失败的原因。 8.1.2 存储过程的种类 存储过程主要分为：用户定义的存储过程、扩展存储过程和系统存储过程。 1．用户定义的存储过程 2．扩展存储过程 3．系统存储过程 8.2 创建存储过程 可以使用SQL Server Management Studio中的对象资源管理器创建SQL存储过程，也可以使用SQL语句CREATE PROCEDURE来创建存储过程。因为还没有详细介绍SQL语句，所以本节主要介绍使用SQL Server Management Studio来创建存储过程。 创建存储过程时，应指定： 所有输入参数和返回的输出参数。 执行数据库操作（包括调用其他过程）的编程语句。 返回至调用过程以标识成功或失败的状态值。 捕获和处理潜在的错误所需的任何错误处理语句。 8.2.1 命名存储过程 因为默认的系统存储过程是以sp_开头，所以在创建存储过程时要注意，前缀可以是公司简介、数据库简称等，尽量不要用sp。如果用户自定义的存储过程与系统存储过程名称相同，则该存储过程将永远不会执行，系统始终执行的是系统存储过程。 8.2.2 用SQL Server Management Studio创建存储过程 本节介绍如何使用SQL Server Management Studio中的对象资源管理器创建SQL存储过程，并提供一个在AdventureWorks数据库中创建简单存储过程的示例。 1．创建存储过程 2．创建存储过程的示例 8.3 执行存储过程 若要执行存储过程，可以使用EXECUTE语句。如果存储过程是批处理中的第一条语句，那么不使用EXECUTE也可以执行存储过程。 8.3.1 执行系统存储过程 系统存储过程以字符sp_开头，笔者建议使用sys架构名称对所有系统存储名称进行限定，以防止名称冲突。以下示例说明执行系统存储过程的推荐方法。 EXEC sys.sp_who; 8.3.2 执行用户定义存储过程 执行用户定义存储过程时，笔者建议至少用架构名称限定存储过程名称。以下示例说明执行用户定义存储过程的推荐方法。 USE AdventureWorks; GO EXEC dbo.uspGetEmployeeManagers 50; 或者： EXEC AdventureWorks.dbo.uspGetEmployeeManagers 50; GO 8.3.3 指定存储过程的参数 指定存储过程的参数，通常包括指定参数、指定参数顺序和使用参数的默认值。本节将学会使用输入参数和输出参数。 如果编写的存储过程允许接受参数，则在执行存储过程时，需要为其指定参数。参数值必须是常量或变量，不能将函数、视图等指定为参数值。变量可以是用户定义变量或系统变量，如@@id。 8.4 修改和删除存储过程 修改和删除存储过程是实现存储过程的重要部分，当创建完一个存储过程，可根据系统的需要，而重新进行修改，甚至在不需要该存储过程的情况下，删除存储过程。 8.4.1 修改存储过程 在SQL Server Management Studio中使用对象资源管理器修改SQL存储过程的操作步骤如下： （1）在【对象资源管理器】窗口中，连接到某个数据库引擎实例，再展开该实例。 （2）依次展开【数据库】｜【存储过程所属的数据库】｜【可编程性】节点。 （3）展开【存储过程】节点，右击需要修改的存储过程，在弹出的快捷菜单中选择【修改】命令，打开修改存储过程脚本的窗口。 （4）修改存储过程的脚本（SQL语句）。 8.4.1 修改存储过程 （5）若要测试语法，选择【查询】|【分析】命令，进行语法测试。 （6）若要修改存储过程，选择【查询】|【执行】命令，执行存储过程。 （7）若要保存脚本，选择【文件】|【保存】命令，完成脚本修改的保存。