SQL Server2005数据库实用教程 作者 常军林 第九章.pptVIP

SQL Server 2005数据库实用教程 了解存储过程的定义、优势及执行方法 掌握触发器的设计、实现及管理方法 能够创建、执行、修改及删除存储过程 能够使用触发器解决问题 9.1 存储过程 9.2 存储过程的定义和执行 9.3 存储过程的管理 9.4触发器 9.5 触发器的管理 9.6 实训 存储过程和触发器的创建和应用 9.1 存储过程 9.1.1 什么是存储过程 存储过程是一组Transact-SQL语句，将一些固定的操作集中起来交给SQL Server数据库服务器完成，以实现某个任务。存储过程经编译后存储在数据库服务器中，可以接受参数并返回状态值和参数值。它们只需编译一次，以后即可多次执行。因为 Transact-SQL 语句不需要重新编译，所以执行存储过程可以提高性能。 9.1.2 为什么使用存储过程 开发SQL Server数据库时，T-SQL程序语言是介于应用程序与SQL Server数据库之间的程序接口（Programming Interface）。使用T-SQL程序时，有两种不同的方法可存储和执行程序。一种是本书之前使用的查询窗口，读者可在此窗口输入T-SQL程序代码；另一种就是保存为存储过程，鉴于存储过程的特点，使T-SQL程序代码更为灵活，以供日后使用。 使用存储过程和数据库的好处是，可以充分利用数据库资源，减少程序代码，程序员的工作将更简便，写出来的代码也更简洁明了。使用存储过程的优点有： （1）能实现模块化程序设计。存储过程是根据实际功能的需要创建的一个程序模块，并被存储在数据库中。以后用户要完成该功能，只要在程序中直接调用该存储过程即可，而无需再编写重复的程序代码。存储过程可由数据库编程方面的专门人员创建，并可独立于程序源代码而进行修改和扩展。 （2）提高性能。在服务器端执行，速度快。执行一次后，其执行规划就驻留在高速缓冲存储器，以后再次执行，就直接运行已经编译好的二进制代码，提高了系统性能。 （3） 提供安全机制。管理员可以不授予用户访问存储过程中涉及的表的权限，而只授予执行存储过程的权限。这样，既可以保证用户通过存储过程操纵数据库中的数据，又可以保证用户不能直接访问存储过程中涉及的表。用户通过存储过程来访问表，所能进行的操作是有限制的，从而保证了表中数据的安全性。 （4）减少网络流量。一个需要数百行T-SQL代码的操作，如果将其创建成存储过程，那么使用一条调用存储过程的语句就可完成该操作。这样就可避免在网络上发送数百行代码，从而减少了网络负荷。 9.1.3 存储过程的类型 存储过程可以分为系统存储过程、本地存储过程、临时存储过程、远程存储过程和扩展存储过程等，在不同情况下需要执行不同的存储过程。 （1）系统存储过程：这些存储过程存储在master数据库中，其前缀是sp_。它允许系统管理员执行修改系统表的数据库管理任务，可以在任何一个数据库中执行。如图9-1所示。 图9-1 系统数据库中的存储过程 （2）本地存储过程：是指在用户数据库中创建的存储过程，完成特定的数据库操作任务，其前缀不能是sp_。 （3）临时存储过程：属于本地存储过程。如果存储过程名称前面有“＃”，说明其是局部临时存储过程，这种存储过程只能在一个用户会话中使用；如果其前面有“＃＃”，说明其是全局临时存储过程，可以在所有用户会话中使用。 （4）远程存储过程：从远程服务器上调用。 （5）扩展存储过程：在SQL Server环境之外执行的动态链接库称为扩展存储过程，其前缀是sp_。使用时需要先加载到SQL Server系统中，并且按照使用存储过程的方法执行。 图9-1显示的是master系统数据库的内容，在“可编程性”中有“存储过程”、“扩展存储过程”及“数据库触发器”等。只有系统数据库才有扩展存储过程，它是比存储过程更为“低级”的工具，可使用C语言等创建。扩展存储过程就像DLL链接库一样，可以扩展SQL Server的功能。 9.2 存储过程的定义和执行 9.2.1 创建简单存储过程 简单存储过程类似于给一组SQL语句起个名字，然后就可以在需要时反复调用。复杂一些的则要有输入和输出参数。 创建存储过程的基本语法如下： CREATE PROCEDURE 存储过程名称 [WITH ENCRYPTION] [WITH RECOMPILE] AS SQL 语句 参数说明： WITH ENCRYPTION：对存储过程进行加密。 WITH RECOMPILE：对存储过程重新编译。 【例9.1】创建一个查询学生“汪洋”的借书信息的“借书查询”存储过程。 创建代码如下： USE 学生图书管理系统 GO CREATE PROC 借书查询 AS SELECT 姓名, 图书编号,借书日期,还书日期