存储过程与触发器(超详细).docx

第八章 存储过程与触发器 在 SQL Server 2008 中存储过程和触发器是两个重要的数据库对象。使用存储过程， 可以将 Transact-SQL 语句和控制流语句预编译到集合并保存到服务器端，它使得管理数据 库、显示关于数据库及其用户信息的工作更为容易。 而触发器是一种特殊类型的存储过程，在用户使用一种或多种数据修改操作来修改指 定表中的数据时被触发并自动执行，通常用于实现复杂的业务规则，更有效地实施数据完 整性。 本章学习目标  了解存储过程的作用及类型  掌握存储过程的创建及应用  熟悉存储过程的管理  了解触发器的作用及分类  熟悉各种类型触发器的创建  了解嵌套、递归触发器  熟悉触发器的管理 8.1 认识存储过程 Transact-SQL 语句是应用程序与 SQL Server 数据库之间的主要编程接口，大量的时间将 花费在 Transact-SQL 语句和应用程序代码上。在很多情况下，许多代码被重复使用多次， 每次都输入相同的代码不但繁琐，更由于在客户机上的大量命令语句逐条向 SQL Server 发 送将降低系统运行效率。因此，SQL Server 提供了一种方法，它将一些固定的操作集中起 来由 SQL Server 数据库服务器来完成，应用程序只需调用它的名称，将可实现某个特定的 任务，这种方法就是存储过程。 下面将详细介绍存储过程的概念、特点、创建、执行等内容。 8.1.1 存储过程概述 SQL Server 中 T-SQL 语言为了实现特定任务而将一些需要多次调用的固定的操作编写成 子程序并集中以一个存储单元的形式存储在服务器上，由 SQL Server 数据库服务器通过子 程序名来调用它们，这些子程序就是存储过程。 存储过程是一种数据库对象，存储在数据库内，可由应用程序通过一个调用执行，而 且允许用户声明变量、有条件执行，具有很强的编程功能。存储过程可以使用 CUTE 语 句来运行。 在 SQL Server 中使用存储过程而不使用存储在客户端计算机本地的 T-SQL 程序有以下 几个方面的好处。  加快系统运行速度 存储程序只在创建时进行编译，以后每次执行存储过程都不需再重新编译 ，而一般 SQL 语句每执行一次就编译一次，所以使用存储过程可提高数据库执行速度。 封装复杂操作 当对数据库进行复杂操作时（如对多个表进行更新，删除时） ，可用存储过程 将此复杂操作封装起来与数据库提供的事务处理结合一起使用。  实现代码重用 可以实现模块化程序设计，存储过程一旦创建，以后即可在程序中调用任意多 次，这可以改进应用程序的可维护性，并允许应用程序统一访问数据库。  增强安全性 可设定特定用户具有对指定存储过程的执行权限而不具备直接对存储过程中引用 的对象具有权限。可以强制应用程序的安全性，参数化存储过程有助于保护应用程序不受 SQL 注入式攻击。  减少网络流量 因为存储过程存储在服务器上，并在服务器上运行。一个需要数百行 T-SQL 代 码的操作可以通过一条执行过程代码的语句来执行，而不需要在网络中发送数百行代码，这样 就可以减少网络流量。 8.1.2 存储过程的分类 存储过程是一个被命名的存储在服务器上的 Transact-SQL 语句的集合，是封装重复性 工作的一种方法，它支持用户声明的变量、条件执行和其他强大的编程功能。 在 SQL Server 2008 中存储过程可以分为两类系统存储过程、用户存储过程和扩展性存储 过程。 1．系统存储过程 系统存储过程是由 SQL Server 系统提供的存储过程，可以作为命令执行各种操作。 系统存储过程主要用来从系统表中获取信息，为系统管理员管理 SQL Server 提供帮助 ，为用户查看数据库对象提供方便。例如，执行 SP_HELPTEXT 系统存储过程可以显示规则 、默认值、未加密的存储过程、用户函数、触发器或视图的文本信息；执行 sp_depends 系 统存储过程可以显示有关数据库对象相关性的信息；执行 sp_rename 系统存储过程可以更 改当前数据库中用户创建对象的名称。SQL Server 中许多管理工作是通过执行系统存储过 程来完成的，许多系统信息也可以通过执行系统存储过程而获得。 系统存储过程定义在系统数据库 master 中，其前缀是 sp_。在调用时不必在存储过程 前加上数据库名。 2．用户存储过程 用户存储过程是指用户根据自身需要，为完成某一特定功能，在用户数据库中创建的 存储过程。用户创建存储过程时，存储过程名的前面加上“” ，是表示创建全局临时存储 过程。在存储过程名前面加上“” ，是表示创建局部临时存储过程。局部临时存储过程只 能在创建它的会话中可用，当前会话结束时除去。全局临时存储过程可以在所有会话中使 用，即所有用户均可以访问该过程。它们都在 tempdb 数据库上。 存储过程可以接受输入参数、向客户端返回表格或者标量结果和消息、调用数据定义语言（DDL）和数据操作语言（DML） ，然后返回输出参数。在 SQL Server 2008 中，用户 定义的存储过程有两种类型Transact-SQL 或者 CLR，如表 8-1 所示。 表 8-1 用户定义存储过程的两种类型 存储过程类型 说明 Transact-SQL Transact-SQL 存储过程是指保存的 Transact-SQL 语句集合，可以 接受和返回用户提供的参数。存储过程也可能从数据库向客户端应用 程序返回数据。 CLR CLR 存储过程是指对 Microsoft .NET Framework 公共语言运行时 方法的引用，可以接受和返回用户提供的参数。他们在.NET Framework 程序集中是作为类的公共静态方法实现的 3．扩展存储过程 扩展存储过程以在 SQL Server 环境外执行的动态链接库（DLL，Dynamic-Link Librar-ies ）来实现。扩展存储过程通过前缀“xp_”来标识，它们以与存储过程相似的方式来执行。 8.2 使用存储过程 在使用存储过程之前，首先需要创建一个存储过程，这可以通过 T-SQL 语句 CREATE P ROCEDURE 来完成。在使用的过程中，包括对存储过程的执行、查看和修改以及删除操作 。 8.2.1 创建存储过程 在 SQL Server 2008kh ,可以使用 T-SQL 语句 CREATE PROCEDURE 来创建存储过程。在创 建存储过程时，应该指定所有的输入参数、执行数据库操作的编程语句、返回至调用过程 或批处理时以示成功或失败的状态值、捕获和处理潜在错误时的错误处理语句等。 需要强调的是，必须具有CREATE PROCEDURE权限才能创建存储过程，存储过程是架构 作用域中的对象，只能在本地数据库中创建存储过程。 1．创建存储过程的规则 在设计和创建存储过程时，应该满足一定的约束和规则。只有满足了这些约束和规则 才能创建有效的存储过程。  CREATE PROCEDURE 定义自身可以包括任意数量和类型的 SQL 语句，但表 8-2 中的语句除外。 因为不能在存储过程的任何位置使用