《SQLServer数据 库第 9章》由会员分享，可在线阅读，更多相关《SQLServer数据 库第 9章（62页珍藏版）》请在金锄头文库上搜索。
1、第9章 .存储过程和触发器,9,本章要点,存储过程概述 创建和执行存储过程 修改和删除存储过程 参数化存储过程 存储过程中的错误处理 触发器概述 管理触发器 触发器的工作原理 Instead Of 触发器 触发器的应用 触发器的高级应用,9.1 存储过程概述,存储过程是预先编译好的一组Transact-SQL语句，这些语句作为一个单元存储。SQL Server中的存储过程与其他编程语言中的过程类似，可以接受输入参数，并以输出参数的形式将单个值或多个值返回给调用过程或批处理。存储过程中的语句，包含执行数据库操作以及调用其他过程的语句，向调用过程或批处理返回状态值，返回成功信息或错误码。 存储过程
2、在被创建时，会被进行语法分析，判断语法的准确性。如果没有语法问题，存储过程的名称会被保存到sysobjects系统表中，存储过程的内容保存到syscomments系统表中。如果发现语法错误，就不会创建存储过程。存储过程在第一次被执行时，会被优化编译并且保存在高速缓冲中。,存储过程的种类,局部存储过程 局部存储过程由数据库用户创建。创建存储过程的权限默认属于数据库所有者，该所有者可将此权限授予其他用户。 系统存储过程 在SQL Server中很多的管理活动都可以通过系统存储过程执行，系统存储过程名称有前缀“sp_”，强烈建议不要以“sp_”为前缀创建任何存储过程。“sp_”前缀是SQL Serv
3、er用来指定系统存储过程的。自定义的名称可能会与以后的某些系统存储过程发生冲突。如果应用程序引用了不符合架构的名称，而自定义的存储过程名称与系统存储过程名称相冲突，则该名称将绑定到系统存储过程而非自定义的存储过程，这将导致应用程序中断。 临时存储过程 临时存储过程可以在过程名称前添加“#”和“#”前缀的方法进行创建。“#”表示本地临时存储过程，“#”表示全局临时存储过程。SQL Server关闭后，这些存储过程将不再存在。局部临时存储过程在创建它的会话中可用，全局临时存储过程在所有的会话中可用。,CLR存储过程 在SQL Server 2005中，可以用Microsoft.Net Framew
4、ork支持的公共语言运行库（Common Language Runtime，CLR）的编程语言创建存储过程。这种存储过程的用法类似于Transact-SQL用户自定义存储过程的用法。它们能够利用由CLR提供的众多编程模型的数据库对象，返回表格形式的结果、整数返回值或输出参数，并可以修改数据和某些数据库对象。 扩展存储过程 在SQL Server环境外部执行的DLL称为扩展存储过程。扩展存储过程名称有前缀“xp_”，可将参数传递给扩展存储过程。扩展存储过程可返回结果，也可返回状态。,存储过程的优势,模块化程序设计 用户在创建存储过程后便可以将其保存在数据库中，以后可以反复调用，并进行后期的修改和
5、维护，提高开发效率和开发质量。 提高执行速度 当需要执行大量Transact-SQL代码时，存储过程的执行速度要比大量Transact-SQL代码的执行速度快。因为存储过程会被进行分析和优化，在执行时使用的是在高速缓冲中的内容，而客户端的Transact-SQL语句每次要被发送、编译和优化，效率较低。,减少网络流量 当需要执行大量Transact-SQL代码时，对于存储过程，只有调用命令和执行的结果在网络中传输，用户端不需要在网络中传输大量的代码，也不需要将数据库中的数据传输到本地进行计算，所以使用存储过程可以减少网络中的数据流量。 提供安全机制 用户可以被授予执行存储过程的权限，即使用户没有
6、存储过程中引用到的表或视图的权限。既可以保证用户能够通过存储过程操作数据库中的数据，又可以保证用户不能直接访问与存储过程相关的表，从而保证表中数据的安全性。,9.2 创建和执行存储过程,创建存储过程 可使用CREATE PROCEDURE语句在当前数据库中创建存储过程，其中可以将PROCEDURE简写成PROC。存储过程名称需要符合标识符规范，并且对于数据库中的对象名是唯一的，存储过程名不可与已经存在的存储过程重名，也不可与已经存在的表和视图等其他数据库对象重名。如果创建局部临时过程，可以在存储过程名前面加前缀“#”；如果要创建全局临时过程，可以在存储过程名前面加前缀“#”。完整的名称（包括“
7、#”或“#”）不能超过128个字符,创建存储过程,语法 CREATE PROCEDURE 存储过程名 (参数定义部分) AS (主体部分) 使用SQL Server Management Studio中的对象资源管理器创建Transact-SQL存储过程,调用存储过程,可使用EXECUTE语句执行存储过程，其中可以将EXECUTE简写成EXEC。执行存储过程的用户必须被授予该存储过程上的EXECUTE权限。 语法 EXECUTE 存储过程名(参定义部分) 例如： USE Northwind; GO EXEC GetEmployees LastName= NDavolio, FirstName=
8、NNancy; GO,9.3 修改存储过程,使用ALTER PROCEDURE语句可以修改已创建存储过程的参数和内容。 可以先删除再重新创建存储过程。 如果先删除再重新创建存储过程，与该存储过程相关的权限都会被删除。如果使用修改存储过程，与该存储过程的相关权限都会被保留。 语法： ALTER PROCEDURE 存储过程名 (参数定义部分) AS (主体部分),修改存储过程,ALTER PROCEDURE dbo.GetEmployees - Add the parameters for the stored procedure here LastName nvarchar(50) = NUL
9、L, . FirstName nvarchar(50) = NULL . AS . BEGIN . - SET NOCOUNT ON added to prevent extra result sets from . - interfering with SELECT statements. . SET NOCOUNT ON; . . - Insert statements for procedure here . SELECT LastName, FirstName, Title . FROM Employees . WHERE LastName like LastName + % . OR
10、 FirstName like FirstName + %; . END,删除存储过程,使用DROP PROCEDURE语句可以删除已创建存储过程 语法： DROP PROCEDURE 存储过程名 例如： USE Northwind; GO DROP PROC GetEmployees GO,9.4 参数化存储过程,使用参数可以在存储过程和调用存储过程的应用程序之间交换数据。 输入参数可以将外部数据传递到存储过程的内部，存储过程也可以通过输出参数将数据返回到外部。,带输入参数的存储过程,每个存储过程参数都必须用唯一的名称进行定义。 与标准的Transact-SQL变量相同，存储过程名称必须以单
11、个“”字符开头，并且必须遵从对象标识符规则。 可在存储过程中使用参数名称以获得参数值并更改它。 存储过程中的参数要定义数据类型，这与表中的字段的数据类型几乎一样。,例如 . ALTER PROCEDURE dbo.GetEmployees . - Add the parameters for the stored procedure here . LastName nvarchar(50) = NULL, . FirstName nvarchar(50) = NULL . AS 执行该存储过程，得到LastName为Davolio，FirstName为Nancy的雇员数据。 EXEC GetE
12、mployees LastName= NDavolio, FirstName=NNancy;,指定存储过程参数的默认值,通过为可选参数指定默认值，可创建带有可选参数的存储过程。 执行该存储过程时，如果未指定其他值，则使用默认值。如果在存储过程中没有指定参数的默认值，并且调用程序也没有在执行存储过程时为该参数提供值，那么会返回系统错误，因此指定默认值是比较重要的。 如果不能为参数指定合适的默认值，则可以指定NULL作为参数的默认值，并在未提供参数值而执行存储过程的情况下，进行合适的处理。,例如： EXEC GetEmployees LastName=NULL, FirstName=NULL; 在
13、执行该存储过程时，以下代码的执行结果是一样的。 EXEC GetEmployees;,带输出参数的存储过程,如果存储过程为参数指定OUTPUT关键字，存储过程退出时可以将保存在参数变量中的值返回给调用函数，并且调用程序也必须指定OUTPUT关键字。 例如： CREATE PROC my_GetCompanyName SupplierID Integer, CompanyName nvarchar(40) OUTPUT-输出参数公司名称 AS SELECT CompanyName = CompanyName FROM Suppliers WHERE SupplierID = SupplierID
14、 GO,执行该存储过程 DECLARE CName NVarChar(40) EXEC my_GetCompanyName 1,CName OUTPUT PRINT CName GO 存储过程的OUTPUT参数，也可以指定输入值，这样调用程序可以传递给存储过程一个值，存储过程可以接收这个值，修改这个值，然后返回给调用程序。 如果执行时指定了OUTPUT关键字，而在存储过程中没有为参数定义OUTPUT关键字，那么在调用时会得到一个错误信息。,在执行带有OUTPUT参数的存储过程时，没有指定OUTPUT关键字，执行后参数的值不会被修改 DECLARE CName NVarChar(40) Sele
15、ct CName = no company name EXEC my_GetCompanyName 1,CName 没有指定OUTPUT关键字 PRINT CName GO 最后CName中的内容仍然是no company name。,存储过程中的错误处理,在存储过程对错误进行处理时，可以使用全局变量ERROR。 ERROR返回最后执行的Transact-SQL语句的错误代码。ERROR的类型为integer，在master.dbo.sysmessages系统表中可以查看与ERROR错误代码对应的错误的文本信息。 ERROR在每一个Transact-SQL语句执行后都会被重置，如果最后的语句执
16、行成功，则返回0；如果最后执行的语句产生错误，则返回错误号。 在处理中可使用两种方法，语句执行后，马上检查ERROR，或者在语句执行完后将ERROR保存的一个整型变量中，供以后错误处理中使用。 在SQL Server中，批处理、存储过程和触发器唯一能使用的错误信息就是ERROR。同时ERROR只由错误产生，不由警告产生，因此，批处理、存储过程和触发器对警告没有可见性。,错误处理,例如： CREATE PROC my_UpdatePrice ProductID Integer, UnitPrice Money AS UPDATE Products SET UnitPrice = UnitPrice WHERE ProductID = ProductID 执行“EXEC my_UpdatePrice 1,-10”语句时，显示错误信息： 服务器: 消息 547，级别
