存储过程与触发器

存储过程的优缺点
 优点：
执行速度更快。存储过程只在创造时进行编译， 而一般SQL语句每执行一次就编译一次,所以使用存储过程执行速度更快。
存储过程用于处理复杂的操作时，程序的可读性更强、网络的负担更小。
             使用存储过程封装事务性能更佳。
             能有效的放注入，安全性更好。
             可维护性高，在一些业务规则发生变化时，有时只需调整存储过程即可，而不用改动和重编辑程序。
             更好的代码重用。
 缺点：
             存储过程将给服务器带来额外的压力。
             存储过程多多时维护比较困难。
             移植性差，在升级到不同的数据库时比较困难。
             调试麻烦，SQL语言的处理功能简单。

 总之复杂的操作或需要事务操作的SQL建议使用存储过程，而参数多且操作简单SQL语句不建议使用存储过程。

存储过程定义

        存储过程是一组 Transact-SQL 语句，它们只需编译一次，以后即可多次执行。因为 Transact-SQL 语句不需要重新编译，所以执行存储过程可以提高性能。
触发器是一种特殊的存储过程，不由用户直接调用。创建触发器时，将其定义为在对特定表或列进行特定类型的数据修改时激发。

存储过程的设计规则
         CREATE PROCEDURE 定义自身可以包括任意数量和类型的 SQL 语句，但以下语句除外。不能在存储过程的任何位置使用这些语句。
  CREATE AGGREGATE、 CREATE RULE、CREATE DEFAULT、 CREATE SCHEMA、CREATE 或 ALTER FUNCTION、
  CREATE 或 ALTER TRIGGER、CREATE 或 ALTER PROCEDURE、 CREATE 或 ALTER VIEW、SET PARSEONLY、
  SET SHOWPLAN_ALL、SET SHOWPLAN_TEXT、 SET SHOWPLAN_XML、USE database_name
  
         其他数据库对象均可在存储过程中创建。可以引用在同一存储过程中创建的对象，只要引用时已经创建了该对象即可。
         可以在存储过程内引用临时表。
         如果在存储过程内创建本地临时表，则临时表仅为该存储过程而存在；退出该存储过程后，临时表将消失。
         如果执行的存储过程将调用另一个存储过程，则被调用的存储过程可以访问由第一个存储过程创建的所有对象，包括临时表在内。
         如果执行对远程 Microsoft SQL Server 2005 实例进行更改的远程存储过程，则不能回滚这些更改。远程存储过程不参与事务处理。
         存储过程中的参数的最大数目为 2100。
         存储过程中的局部变量的最大数目仅受可用内存的限制。
         根据可用内存的不同，存储过程最大可达 128 MB

实现存储过程
         

CREATE  {  PROC   |   PROCEDURE  }  [ schema_name. ]  procedure_name  [  ; number  ]  
              [  { @parameter [ type_schema_name.  ]  data_type }  [  VARYING  ]   [  = default  ]   [  [ OUT [ PUT  ]  ]  -- 名称、类型、默认值、方向
              [  ,...n  ]  
          [  WITH <procedure_option> [ ,...n  ]
          [  FOR REPLICATION  ]  
          AS  
         {  < sql_statement >   [ ; ][  ...n  ]   |   < method_specifier >  }  -- SQL语句
          [ ; ]
          < procedure_option >  :: =  
              [  ENCRYPTION  ]
              [  RECOMPILE  ]   -- 运行时编译
              [  EXECUTE_AS_Clause  ]
          < sql_statement >  :: =  {  [  BEGIN  ]  statements  [  END  ]  }
          < method_specifier >  :: =  EXTERNAL NAME assembly_name.class_name.method_name

执行存储过程
        使用 Transact-SQL EXECUTE 语句。如果存储过程是批处理中的第一条语句，那么不使用 EXECUTE 关键字也可以执行存储过程
         使用 sp_procoption 让SQLSERVER 自动执行存储过程 
         sp_procoption [ @ProcName = ] 'procedure' , [ @OptionName = ] 'option'  , [ @OptionValue = ] 'value' --过程的名称、option 的唯一值为 startup、设置为开启（true 或 on）还是关闭（false 或 off）。

用TSQL语句编写存储过程

一、变量和参数
         DECLARE 语句通过以下操作初始化 Transact-SQL 变量：
         指定名称。名称的第一个字符必须为一个 @。
         指定系统提供的或用户定义的数据类型和长度。对于数值变量还指定精度和小数位数。对于 XML 类型的变量，可以指定一个可选的架构集合。
         将值设置为 NULL。
         如：DECLARE @MyCounter int
         第一次声明变量时，其值设置为 NULL。若要为变量赋值，请使用 SET 语句。这是为变量赋值的首选方法。也可以通过 SELECT 语句的选择列表中当前所引用值为变量赋值。
         参数用于在存储过程和函数以及调用存储过程或函数的应用程序或工具之间交换数据： 
         输入参数允许调用方将数据值传递到存储过程或函数。
         输出参数允许存储过程将数据值或游标变量传递回调用方。用户定义函数不能指定输出参数。
         每个存储过程向调用方返回一个整数返回代码。如果存储过程没有显式设置返回代码的值，则返回代码为 0。
二、流程控制语句
         1、BEGIN 和 END 语句
             BEGIN 和 END 语句用于将多个 Transact-SQL 语句组合为一个逻辑块。在控制流语句必须执行包含两条或多条 Transact-SQL 语句的语句块的任何地方，都可以使用 BEGIN 和 END 语句。
 如：
 IF (@@ERROR <> 0)
 BEGIN
    SET @ErrorSaveVariable = @@ERROR
    PRINT 'Error encountered, ' +
   CAST(@ErrorSaveVariable AS VARCHAR(10))
 END
         2、GOTO 语句
             GOTO 语句使 Transact-SQL 批处理的执行跳至标签。不执行 GOTO 语句和标签之间的语句。
     IF(1=1)
  GOTO calculate_salary
  print 'go on' --条件成立则跳过此句。
     calculate_salary:
   print 'go to'
         3、IF...ELSE 语句
             IF 语句用于条件的测试。得到的控制流取决于是否指定了可选的 ELSE 语句：
  if(1=1)
   print 1
  else if(2=2)
   print 2
  else if(3=3)
   print 3
  else
   print 0
         4、RETURN 语句
               RETURN 语句无条件终止查询、存储过程或批处理。存储过程或批处理中 RETURN 语句后面的语句都不执行。当在存储过程中使用 RETURN 语句时，此语句可以指定返回给调用应用程序、批处理或过程的整数值。如果 RETURN 未指定值，则存储过程返回 0
         5、WAITFOR 语句
               WAITFOR 语句挂起批处理、存储过程或事务的执行，直到发生以下情况：
   已超过指定的时间间隔。
   到达一天中指定的时间。
   指定的 RECEIVE 语句至少修改一行或并将其返回到 Service Broker 队列。
               WAITFOR 语句由下列子句之一指定：
           DELAY 关键字后为 time_to_pass，是指完成 WAITFOR 语句之前等待的时间。完成 WAITFOR 语句之前等待的时间最多为 24 小时。
   如：
    WAITFOR DELAY '00:00:02'
    SELECT EmployeeID FROM  Employee;
           TIME 关键字后为 time_to_execute，指定 WAITFOR 语句完成所用的时间。
    GO
    BEGIN
        WAITFOR TIME '22:00';
        DBCC CHECKALLOC;
    END;
    GO
           RECEIVE 语句子句，从 Service Broker 队列检索一条或多条消息。使用 RECEIVE 语句指定 WAITFOR 时，如果当前未显示任何消息，该语句将等待消息到达队列。
           TIMEOUT 关键字后为 timeout，指定 Service Broker 等待消息到达队列的时间长度（毫秒）。可以在 RECEIVE 语句或 GET CONVERSATION GROUP 语句中指定 TIMEOUT。
         6、WHILE...BREAK 或 CONTINUE 语句
               只要指定的条件为 True 时，WHILE 语句就会重复语句或语句块。REAK 或 CONTINUE语句通常和WHILE一起使用。BREAK 语句退出最内层的 WHILE 循环，CONTINUE 语句则重新开始 WHILE 循环。

   go  
   declare   @Num   int
   declare   @ID   int
   declare   @i   int
   set   @i = 1
   while ( exists ( select   *   from  T  where  Num < 5   ))  -- 获取数量小于5的记录
   begin
    select   @Num = Num, @ID = ID  from  T  where  Num < 5   order   by  ID  desc
    print   Str ( @i ) +   ' 编号： ' + Str ( @ID ) +   '  值 ' + str ( @Num )
    update  T  set  Num = Num * 2   where  ID = @ID
    set   @i = @i + 1
    if ( @i > 3 )
      break   -- 退出循环
   
   end

 
         7、CASE 语句
          CASE 函数用于计算多个条件并为每个条件返回单个值。CASE 函数通常的用途是将代码或缩写替换为可读性更强的值

-- 用法一：
   select  ID,
  Grade = Case  Num  
   when    1   then   ' 不及格 '   
   when    2   then   ' 不及格 '  
   when    3   then   ' 不及格 '
   when    4   then   ' 良好 '
   else   ' 优秀 '
   end   
   from  T
   -- -用法二：
   select  ID,
  Grade = Case    
   when   Num < 3   then   ' 不及格 '   
   when   Num = 3   then   ' 及格 '  
   when   Num = 4   then   ' 良好 '
   when   Num > 4   then   ' 优秀 '
   end   
   from  T

三、运行时生成语句

         Transact-SQL 支持使用下列两种方法于运行时在 TTransact-SQL 脚本、存储过程和触发器中生成 SQL 语句：
 使用 sp_executesql 系统存储过程执行 Unicode 字符串。sp_executesql 支持与 RAISERROR 语句类似的参数替换。
         使用 EXECUTE 语句执行字符串。EXECUTE 语句不支持已执行字符串中的参数替换。

四、处理数据库引擎错误
        在 Transact-SQL 中有两种方式可以获取错误信息：
        1、在 TRY...CATCH 构造的 CATCH 块的作用域内，您可以使用以下系统函数：
          ERROR_LINE()，返回出现错误的行号。
          ERROR_MESSAGE()，返回将返回给应用程序的消息文本。该文本包括为所有可替换参数提供的值，如长度、对象名或时间。
          ERROR_NUMBER() 返回错误号。
          ERROR_PROCEDURE()，返回出现错误的存储过程或触发器的名称。如果在存储过程或触发器中未出现错误，该函数返回 NULL。
          ERROR_SEVERITY() 返回严重性。
          ERROR_STATE()，返回状态。
         2、在执行任何 Transact-SQL 语句之后，您可以立即使用 @@ERROR 函数测试错误并检索错误号。
         RAISERROR
           RAISERROR 用于将与 SQL Server Database Engine 生成的系统错误或警告消息使用相同格式的消息返回到应用程序中。
         3、PRINT 
           PRINT 语句用于将消息返回到应用程序。PRINT 采用字符或 Unicode 字符串表达式作为参数，并将字符串作为消息返回到应用程序。

触发器

        触发器是数据库服务器中发生事件时自动执行的特种存储过程。如果用户要通过数据操作语言 (DML) 事件编辑数据，则执行 DML 触发器。DML 事件是针对表或视图的 INSERT、UPDATE 或 DELETE 语句。DDL 触发器用于响应各种数据定义语言 (DDL) 事件。这些主要是 CREATE、ALTER 和 DROP 语句。通过 Transact-SQL 语句或使用 Microsoft .NET Framework 公共语言运行时 (CLR) 创建的程序集的方法，可以在 SQL Server 2005 Database Engine 中直接创建 DML 和 DDL 触发器，将其上传给一个 SQL Server 实例。SQL Server 允许为任何特定语句创建多个触发器。

 DML 触发器的类型
         AFTER 触发器：在执行了 INSERT、UPDATE 或 DELETE 语句操作之后执行 AFTER 触发器
         INSTEAD OF 触发器：执行 INSTEAD OF 触发器代替通常的触发动作。
         CLR 触发器：CLR 触发器可以是 AFTER 触发器或 INSTEAD OF 触发器。CLR 触发器还可以是 DDL 触发器。CLR 触发器将执行在托管代码中编写的方法，而不用执行 Transact-SQL 存储过程。

创建触发器

  CREATE   TRIGGER   [  schema_name .  ] trigger_name 
  ON  {  table   |   view  } 
  [  WITH <dml_trigger_option> [ ,...n  ]  ]
 {  FOR   |  AFTER  |  INSTEAD  OF  }  -- 类型
 {  [  INSERT  ]   [  ,  ]   [  UPDATE  ]   [  ,  ]   [  DELETE  ]  }  -- 操作类型
  [  WITH APPEND  ]  
  [  NOT FOR REPLICATION  ]  
  AS  { sql_statement   [  ;  ]   [  ...n  ]   |  EXTERNAL NAME  < method specifier  [  ;  ]   >  }
  < dml_trigger_option >  :: =
      [  ENCRYPTION  ]   -- 加密
      [  EXECUTE AS Clause  ]   -- 安全上下文
  < method_specifier >  :: =  assembly_name.class_name.method_name

使用插入的和删除的表
 DML 触发器语句使用两种特殊的表：删除的表（Deleted）和插入的表(Inserted)。
 SQL Server 2005 会自动创建和管理这两种表。您可以使用这两种驻留内存的临时表来测试特定数据修改的影响以及设置 DML 触发器操作条件。但不能直接修改表中的数据或对表执行数据定义语言 (DDL) 操作
 删除的表用于存储 DELETE 和 UPDATE 语句所影响的行的副本。在执行 DELETE 或 UPDATE 语句的过程中，行从触发器表中删除，并传输到删除的表中。删除的表和触发器表通常没有相同的行。2007-9-12
 插入的表用于存储 INSERT 和 UPDATE 语句所影响的行的副本。在插入或更新事务期间，新行将同时被添加到插入的表和触发器表。插入的表中的行是触发器表中的新行的副本。
 示例：

  CREATE   TRIGGER  tr_emp
  ON  Product  FOR   UPDATE   AS
  DECLARE   @row_num   INT
  SELECT   *   FROM  DELETED
  SELECT   *   FROM  INSERTED

UPDATE()语句
 UPDATE(Column)返回一个布尔值，指示是否对表或视图的指定列进行了 INSERT 或 UPDATE 尝试。可以在 Transact-SQL INSERT 或 UPDATE 触发器主体中的任意位置使用 UPDATE()，以测试触发器是否应执行某些操作
 示例：
 

GO
  IF   EXISTS  ( SELECT  name  FROM  sys.objects
        WHERE  name  =   ' reminder '   AND  type  =   ' TR ' )
     DROP   TRIGGER  Person.reminder;
  GO
  CREATE   TRIGGER  reminder
  ON  Person.Address
 AFTER  UPDATE  
  AS  
  IF  (  UPDATE  (StateProvinceID)  OR   UPDATE  (PostalCode) )
  BEGIN
  RAISERROR  ( 50009 ,  16 ,  10 )
  END ;
  GO
  --  Test the trigger.
  UPDATE  Person.Address
  SET  PostalCode  =   99999
  WHERE  PostalCode  =   ' 12345 ' ;
  GO