SQL Server 2012学习笔记 (六) ------ SQL Server 存储过程和触发器

1、存储过程的概念

  存储过程（Stored Procedure）是预编译SQL语句的集合，这些语句存储在一个名称下并作为一个单元来处理。存储过程代替了传统的逐条执行SQL语句的方式。一个存储过程中可包含查询、插入、删除、更新等操作的一系列SQL语句，当这个存储过程被调用执行时，这些操作也会同时执行。

  存储过程与其他编程语言中的过程类似。它可以接受输入参数并以输出参数的格式向调用过程或批处理返回多个值；包含用于在数据库中执行操作（包括调用其他过程）的编程语句；向调用过程或批处理返回状态值，以指明成功或失败（以及失败的原因）。

  SQL Server中供了3种类型的存储过程。各类型存储过程如下：

  1) 用来管理SQL Server和显示有关数据库和用户的信息的存储过程，这些存储过程被称为系统存储过程。

  系统存储过程是SQL Server 2012系统创建的存储过程，它的目的在于能够方便地从系统表中查询信息，或者完成与更新数据库表相关的管理任务或其他的系统管理任务。Transact-SQL语句是SQL Server 2012数据库与应用程序之间的编程接口。在很多情况下，一些代码会被开发者重复编写多次，如果每次都编写相同功能的代码，不但繁琐，容易出错，而且由于SQL Server 2012逐条地执行语句会降低系统的运行效率。

  2) 用户在SQL Server中通过采用SQL语句创建存储过程，这类存储过程被称为用户自定义存储过程。

  3) 通过编程语言（例如，C）创建外部例程，并将这个例程在SQL Server中作为存储过程使用，这类存储过程称为扩展存储过程。

  扩展存储过程使用户能够在编程语言（例如，C、C++）中创建自己的外部例程。扩展存储过程的显示方式和执行方式与常规存储过程一样。可以将参数传递给扩展存储过程，而且扩展存储过程也可以返回结果和状态

2、存储过程的优点

  （1）存储过程可以嵌套使用，支持代码重用。

  （2）存储过程可以接受与使用参数动态执行其中的SQL语句。

  （3）存储过程比一般的SQL语句执行速度快。存储过程在创建时已经被编译，每次执行时不需要从新编译。而SQL语句每次执行都需要编译。

  （4）存储过程具有安全特性（例如权限）和所有权链接，以及可以附加到它们的证书。用户可以被授予权限来执行存储过程而不必直接对存储过程中引用的对象具有权限。

  （5）存储过程允许模块化程序设计。存储过程一旦创建，以后即可在程序中调用任意多次。这可以改进应用程序的可维护性，并允许应用程序统一访问数据库。

  （6）存储过程可以减少网络通信流量。一个需要数百行SQL语句代码的操作可以通过一条执行过程代码的语句来执行，而不需要在网络中发送数百行代码。

  （7）存储过程可以强制应用程序的安全性。参数化存储过程有助于保护应用程序不受SQL Injection攻击。

3、创建存储过程

  可以使用企业管理器创建存储过程和使用Transact-SQL语句创建存储过程。

  使用Transact-SQL语句创建存储过程，其语法格式如下：

CREATE PROC [ EDURE ] procedure_name [ ; number ]
    [ { @parameter data_type }
        [ VARYING ] [ = default ] [ OUTPUT ]
    ] [ ,...n ] 
AS sql_statement

  示例：

USE AdventureWorks2012;  
GO  
CREATE PROCEDURE HumanResources.uspGetEmployeesTest2   
    @LastName nvarchar(50),   
    @FirstName nvarchar(50)   
AS   
    SET NOCOUNT ON;  
    SELECT FirstName, LastName, Department  
    FROM HumanResources.vEmployeeDepartmentHistory  
    WHERE FirstName = @FirstName AND LastName = @LastName  
    AND EndDate IS NULL;  
GO  

4、管理存储过程

  1、执行存储过程：

    存储过程创建完成后，可以通过EXECUTE执行，可简写为EXEC。

  示例：

EXECUTE HumanResources.uspGetEmployeesTest2 N'Ackerman', N'Pilar';  
-- Or  
EXEC HumanResources.uspGetEmployeesTest2 @LastName = N'Ackerman', @FirstName = N'Pilar';  
GO  
-- Or  
EXECUTE HumanResources.uspGetEmployeesTest2 @FirstName = N'Pilar', @LastName = N'Ackerman';  
GO 

  2、查看存储过程：

    使用 目录视图 sys.sql_modules 查看存储过程的定义

USE AdventureWorks2012;  
GO  
SELECT definition  
FROM sys.sql_modules  
WHERE object_id = (OBJECT_ID(N'AdventureWorks2012.dbo.uspLogError'));  

    使用 系统函数 OBJECT_DEFINITION 查看存储过程的定义

USE AdventureWorks2012;  
GO  
SELECT OBJECT_DEFINITION (OBJECT_ID(N'AdventureWorks2012.dbo.uspLogError'));

    使用 系统存储过程 sp_helptext 查看存储过程的定义

USE AdventureWorks2012;  
GO  
EXEC sp_helptext N'AdventureWorks2012.dbo.uspLogError';  

  3、修改存储过程：

    使用ALTER PROCEDURE语句修存储过程。该语句修改存储过程时不会更改权限，也不影响相关的存储过程或触发器。

ALTER PROCEDURE Purchasing.uspVendorAllInfo  
    @Product varchar(25)   
AS  
    SET NOCOUNT ON;  
    SELECT LEFT(v.Name, 25) AS Vendor, LEFT(p.Name, 25) AS 'Product name',   
    'Rating' = CASE v.CreditRating   
        WHEN 1 THEN 'Superior'  
        WHEN 2 THEN 'Excellent'  
        WHEN 3 THEN 'Above average'  
        WHEN 4 THEN 'Average'  
        WHEN 5 THEN 'Below average'  
        ELSE 'No rating'  
        END  
    , Availability = CASE v.ActiveFlag  
        WHEN 1 THEN 'Yes'  
        ELSE 'No'  
        END  
    FROM Purchasing.Vendor AS v   
    INNER JOIN Purchasing.ProductVendor AS pv  
      ON v.BusinessEntityID = pv.BusinessEntityID   
    INNER JOIN Production.Product AS p   
      ON pv.ProductID = p.ProductID   
    WHERE p.Name LIKE @Product  
    ORDER BY v.Name ASC;  
GO  

  4、重命名存储过程：

    使用sp_rename系统存储过程重新命名存储过程。

EXEC sp_rename 'HumanResources.uspGetAllEmployeesTest', 'uspEveryEmployeeTest'; 

  5、删除存储过程：

     执行DROP PROCEDURE语句删除存储过程。

DROP PROCEDURE <stored procedure name>;  
GO 

5、触发器概念

  触发器是一种特殊类型的存储过程，当指定表中的数据发生变化时触发器自动生效。它与表紧密相连，可以看做是表定义的一部分。触发器不能通过名称被直接调用，更不允许设置参数。

  触发器是一个在修改指定表值的数据时执行的存储过程，不同的是执行存储过程要使用EXEC语句来调用，而触发器的执行不需要使用EXEC语句来调用，通过创建触发器可以保证不同表中的逻辑相关数据的引用完整性或一致性。

  在SQL Server中一张表可以有多个触发器。用户可以跟据INSERT、UPDATE或DELETE语句对触发器进行设置，也可以对一张表上的特定操作设置多个触发器。触发器可以包含复杂的Transact-SQL语句。不论触发器所进行的操作有多复杂，触发器都只作为一个独立的单元被执行，被看作是一个事务。如果在执行触发器的过程中发生了错误，则整个事务将会自动回滚。

6、触发器的优点

  触发器的主要作用就是其能够实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性，它能够对数据库中的相关表进行级联修改，能提供比CHECK约束更复杂的数据完整性，并自定义错误信息。

7、触发器的种类

  SQL Server 包括三种常规类型的触发器：DML 触发器、DDL 触发器和登录触发器。

  当数据库中发生数据操作语言 (DML) 事件时将调用 DML 触发器。DML 事件包括在指定表或视图中修改数据的 INSERT 语句、UPDATE 语句或 DELETE 语句。DML 触发器可以查询其他表，还可以包含复杂的 Transact-SQL 语句。 可以设计以下类型的 DML 触发器：

	AFTER 触发器、 INSTEAD OF 触发器、 CLR 触发器

  DDL 触发器是一种特殊的触发器，它在响应数据定义语言 (DDL) 语句时触发。它们可以用于在数据库中执行管理任务，例如，审核以及规范数据库操作。

  登录触发器将为响应 LOGON 事件而激发存储过程。与 SQL Server 实例建立用户会话时将引发此事件。登录触发器将在登录的身份验证阶段完成之后且用户会话实际建立之前激发。可以使用登录触发器来审核和控制服务器会话，例如通过跟踪登录活动、限制 SQL Server 的登录名或限制特定登录名的会话数。

8、创建触发器

	 1.创建DML触发器
	 2.创建DDL触发器
     3.创建登录触发器

9、管理触发器

	1．查看触发器
	2.修改触发器
	3.重命名触发器
	4.禁用和启用触发器
	5.删除触发器