存储过程和触发器

一、章节重点：
   存储过程是存储在服务器上的一组预编译的SQL语句的集合，触发器可看成特殊的存储过程。本章通过大量实例说明了存储过程和触发器的使用方法。读者通过本章的学习，应掌握各种存储过程的定义、修改、删除及调用方法。触发器是在数据更新后执行的后置过滤器，当有操作影响到触发器保护的数据时，触发器就会自动触发执行，而规则、约束、默认值是在数据更新前进行的检查。如果触发器的操作与规则、约束、默认值检查发生冲突，则不执行触发器的操作。
  对于触发器部分，读者应掌握各类触发器的使用及应注意之处。
二、存储过程
  存储过程是存储在服务器上的预编译好的SQL语句集。你可以将存储过程类比为SQL Server提供的用户自定义函数，你可以在后台或前台调用它们。实际上，存储过程是T-SQL对ANSI-92 SQL标准的扩充。它允许多个用户访问相同的代码。它提供了一种集中且一致的实现数据完整性逻辑的方法。存储过程用于实现频繁使用的查询、业务规则、被其他过程使用的公共例行程序。
 存储过程的处理过程：
    当SQL Server接收到创建一个存储过程的命令时，由SQL Server的查询处理器对该存储过程中的SQL语句进行语法分析，检查其是否合乎语法规范。并将该存储过程的源代码存放在当前数据库的系统表syscomments中。也在sysobjects表中存放该存储过程的名字。当存储过程第一次运行时， SQL Server首先对该存储过程进行预编译，即为该存储过程建立一棵查询树，这个过程被称为“resolution”—分解SQL语句中的对象，并为该存储过程建立一个规范化的查询树。然后， SQL Server为这个存储过程完成编译（compilation）。该步骤分成两步：
   查询优化（optimization）和在高速缓存（procedure cache）中建立查询计划。最后，系统就可以执行这个存储过程了。
  这也是一般SQL语句处理的步骤。
 1 存储过程的分类：
   1）系统存储过程--存储过程定义在master库中，前缀是sp_，可以在任何数据库中执行；
   2）本地存储过程--在用户数据库中创建，前缀不能是sp_，只能在当前数据库中执行；
   3）临时存储过程--属于本地存储过程，前缀是#则代表局部临时存储过程，若前缀是##则代表全局临时存储过程；
   4）远程存储过程--从远程服务器上调用的存储过程；
   5）扩展存储过程--在SQL Server环境之外执行的动态链接库成为扩展存储过程，先加载到SQL Server系统再使用；
 2 用户存储过程的创建和执行
   1）定义中不能使用的对象创建语句：create view(default,rule,procedure,trigger) ；
   2）基本语法格式和各参数说明：（参见教材212-214页）；
   3）设计简单的存储过程：（参见教材214页）；
   4）使用带参数的存储过程：输入参数是指由调用程序向存储过程传递的参数。它们在创建存储过程语句中被定义，而在执行该存储过程中给出相应的变量值。
      具体语法如下：@parameter_name dataype[=default]
      其中：@parameter_name是存储过程的输入参数名，必须以@符号为前缀。当执行该存储过程时，应该向输入参数提供相应的值。
             Datatype是该参数的数据类型说明，它可以是系统提供的数据类型，也可以是用户定义的数据类型。
             DEFAULT 如果执行存储过程时未提供该参数值，则使用DEFAULT值。
    执行存储过程。SQL Server提供了以下两种方法传递参数。
    （1）. 按位置传送：这种方法是在执行存储过程语句中，直接给出参数的传递值。当有多个参数时，值的顺序与创建存储过程语句中定义参数的顺序相一致。也就是说，参数传递的顺序就是参数定义的顺序。其格式是：
      [EXECUTE] proc_name [value...]
     其中：PROC_NAM E是存储过程名字， value是传递给该输入参数的值。
     如： EXEC addadult  ' Your Last Name '，' Your First Name '，NULL，' YourStreetAddress', 'Your City ' , ' Your State ' ,' Your Postal Code ' , NULL
    （2）. 通过参数名传送：这种方法是在执行存储过程中，指出创建该存储过程语句中的参数名字和传递给它的值。其格式如下：
      [EXECUTE] proc_name [@parameter=value]
     其中：proc_name是存储过程名字，parameter是输入参数的名字，value是传递给该输入参数的值。
   5）使用带有统配符参数的存储过程：
   6）使用带output参数的存储过程：我们可以从存储过程中返回一个或多个值。这是通过在创建存储过程的语句中定义输出参数来实现的。
     具体语法如下：@parameter name dataype[=default] OUTPUT         
     其中：@ parameter _ nam e是存储过程的输出参数名，必须以@符号为前缀。
           Datatype是该参数的数据类型说明，它可以是系统提供的数据类型，也可以是用户定义的数据类型。
           保留字OUTPUT指明这是一个输出参数。值得注意的是，输出参数必须位于所有输入参数说明之后。
   7）使用output游标参数的存储过程：返回存储过程的局部游标；
   8）使用with encrryption选项：对用户隐藏存储过程文本；
 3  用户存储过程的编辑修改
 4  用户存储过程的的删除
 5 以下是创建存储过程所推荐的4个步骤：
  1) 写SQL语句：如：查看书的个数。SELECT COUNT(*) FROM  titles
  2 ) 测试SQL语句：执行这些SQL语句。确认符合要求。
  3) 若得到所需结果,则创建过程如果发现符合要求，则按照存储过程的语法，定义该存储过程。
      CREATE PROC count_title
      AS SELECT COUNT(*)FROM titles
  4) 执行过程：执行存储过程，验证正确性。
    EXEC count_title
三、触发器
     特殊的一类存储过程，用于保护表中数据或实现多个表间数据的一致性，执行触发器的表或视图称为触发器表或视图；
 1 触发器的分类：insert（插入）、update（修改）、delete（删除）三类，同一表中可使用多个触发器或同一触发器可使用多个，；
 2 触发器的创建方法：使用企业管理器界面和使用SQL命令方式
 3 命令行创建
  1）语法格式见教材222页；
  2）参数含义：
       after 关键字，默认设置，不能用于视图；
       instead of  关键字，指定用触发器操作替代触发语句操作（insert, update, delete）；
  3）触发器中使用的特殊表：SQL server为每个触发器都创建了两个专用表：
     i n s e r t e d表和d e l e t e d表。这是两个逻辑表，由系统来维护，不允许用户直接对这两个表进行修改。它们存放于内存中，不存放在数据库中。这两个表的结构总是与被该触发器作用的表的结构相同。触发器工作完成后，与该触发器相关的这两个表也会被删除。
    inserted 逻辑表：存放由于insert或Update语句的执行而导致要加到该触发器作用的表中去的所有新行。
           即用于插入或更新表的新行值，在插入或更新表的同时，也将其副本存入insterted表中。
           因此，在insterted表中的行总是与作用表中的新行相同。；
    delete 逻辑表：存放由于DELETE或UPDATE语句的执行而导致要从被该触发器作用的表中删除的所有行。
           也就是说，把被作用表中要删除或要更新的旧行移到deleted表中。因此，delete表和被作用表不会有相同的行。
  注：修改记录相当于插入新记录同时删除旧记录两个操作（见223页）；
 4 使用触发器的限制：（见教材223-224页）
        每张表只有3种触发器动作: UPDATE 触发器、INSERT触发器和DELETE触发器。
    值得注意的是，触发器不能创建在临时表或视图上，而且一个触发器只能作用在一个表上。
    从事务角度来说，触发器与触发它的语句(如：INSERT语句)作为同一个事务的一部分来执行