[转帖]《高性能的数据库》第四讲 编程细节（下部分）

4、存储过程。
   存储过程是数据库编程里面最重要的表现方式了。
   呵呵，这里我要提到上次说道的：我拒绝使用触发器。这里我要开始猛批一顿触发器了。
   在SQL 2000里，说实话，我实在找不出触发器可以存在的理由。回忆一下：触发器是一种特殊的存储过程。它在一定的事件（Insert,Update,Delete 等）里自动执行。我建议使用sp和级联来代替触发器。
   在SQL 7 里面，触发器通常用于更新、或删除相关表的数据，以维护数据的完整。SQL 7里面，没有级联删除和级联修改的功能。 只能建立起关系。既然SQL 2000里面提供了级联，那么触发器就没有很好的存在理由。更多的情况下是作为一个向下兼容的技术而存在。
   当然，也有人喜欢把触发器作为处理数据逻辑，甚至是业务逻辑的自动存储过程。 这种方法并不足取。这里列举以下使用触发器的一些坏处：
  a、“地下”运行 。
      触发器没有很好的调试、管理环境。调试一段触发器，要比调试一段sp更耗费时间与精力。
  b、类似于goto语句。（过分自由的另外一个说法是：无政府主义！）
     一个表，可以写入多个触发器，包括同样for Update的10个触发器！同样for Delete的10个触发器。也就是说，你每次要对这个表进行写操作的时候，你要一个一个检查你的触发器，看看他们是做什么的，有没有冲突。
     或许，你会很牛B的对我说：我不会做那么傻B的事情，我记得住我做了些什么！3个月以后呢？10个月以后呢？你还会对我说你记得住么
  c、嵌套触发器、递归触发器
     你敢说你这么多的触发器中不会存在Table1更新了Table2表，从而触发Table2表更新TAble3，TAble3的触发器再次触发Table1更新Table2
     或许还会发生这种情况：你的程序更新了Table1.Fd1，触发器立马更新Table1.fd1，再次触发事件，触发器再次更新Table1.fd1……
     当然，SQL Server可以设置和避免应用程序进入死循环，可是，得到的结果，或许就不是你想要的。
   我想不出触发器更多的坏处了，因为我早就抛弃了它。算了，不批它了，酸是各人爱好把！我建议使用完全存储过程来实现数据逻辑和事务逻辑！
   先讲讲sp的编写格式（我个人的编程习惯）。良好的习惯有助于日后的维护。
    Create Proc spBuyBook(                     --@@存储过程头，包括名字、参数、说明文档
      @iBookID int,      --书的ID              --@@参数
      @iOperatorID int   --操作员ID
    )
    -------------------------------------------------------    @@说明文档
    --Name  :  spBuyBook                                       @@名字       
    --func  :  购买一本书的业务逻辑                            @@存储过程的功能                      
    --Return:  0,正确;-1,没找到该书;-2,更新Book表出错;-3.....  @@返回值解释
    --Use   :  spDoSomething,spDoSomething2....                @@引用了那些外部程序，比如sp,fn,vw等
    --User  :  懒虫                                            @@该存储过程的使用者
    --Author:  懒虫 # SapphireStudio (www.chair3.com)          @@作者
    --Date  :  2003-5-4                                        @@最后更新日期
    --Memo  :  临时写写的，给大家作个Sample。没有调试阿。      @@备注
    -------------------------------------------------------
    As                                                       --@@程序开始
    begin     
      Begin Tran                                             --@@激活事务
        Exec spDoSomething                                   --@@调用其他sp
        if @@Error<>0                                        --@@判断是否错误
        begin
          Rollback Tran                                      --@@回滚事务
          RaisError ('SQL SERVER,spBuyBook: 调用spDoSomeThing发生错误。', 16, 1) with Log  --@@记录日志
          Return -1                                          --@@返回错误号
        end       
      .... --更多其他代码
      Commit Tran                                            --@@提交事务
    end                
      好了，继续！回忆刚才写的内容ing ……

    AA、存储过程的几个要素： a. 参数  b.变量 c.语句 d.返回值 e.管理存储过程
    BB、更高级的编程要素：   a.系统存储过程  b.系统表  c.异常处理 d.临时表 e.动态SQL f.扩展存储过程 g.DBCC命令
    AA.a 参数：  知识要点包括：输入参数，输出参数，参数默认值
      Sample:
        Create Proc spTest(
          @i int =0 ,       --输入参数
          @o int output     --输出参数
        )
        As
          Set @o=@i*2       --对输出参数付值
           Use the Sample:
        Declare @o int
        Exec spTest 33,@o output
        Select @o                    --此时@o应该等于33*2=66。
      ----------------------------------------------------------------------
      以上代码没有测试，顺手写写的。希望不会出错：）  
   
   AA.b 变量：AA.a中已经有声明变量的例子了，就是Declare @o int
   AA.c 语句：在Sql Server 中，如果仅仅使用标准SQL语句将是不可想象的，通常认为，标准的SQL 语句就那么几条，如：      
               Select, Update, Delete
              因此，我们需要引入更多更强大的功能，那就是T-SQL语句：
    
              赋值语句：Set           
              循环语句：While  
              分支语句：if , Case ( Case语句不能单独使用，与一般高级语言的不同）
              一起举个例子吧：
              Sample :
              Declare @i int
              Set @i=0 

              While @i<100 
              begin
                if @i<=20
                begin
                  Select Case Cast(@i As Float)/2 When (@i/2) then Cast(@i As varchar(3)) + '是双数'
                                                  else             Cast(@i As varchar(3)) + '是单数'
                          end
                 end
                Set @i=@i+1
              end       
              ----------------------------------------------------------------------
              以上代码判断20之内的单数与双数。
                      ----------------------------------------------------------------------- 
   AA.d 返回值 
        Sample:

          Create Proc spTest2 
          As
            Return 22

        Use the Sample
          Declare @i int
          Exec @i=spTest2
          Select @i  
   AA.e 管理存储过程: 创建，修改，删除。
        分别为：
        Create Proc ...  ,  Alter Proc ...  , Drop Proc ...
  BB、更高级的编程要素：   a.系统存储过程  b.系统表  c.异常处理 d.临时表 e.动态SQL f.扩展存储过程 g.DBCC命令
      哈哈，以下课程收费！！（玩笑，实际上打算放到后面去讲了。）
3、函数。
   函数是SQL 2000的新功能。一般的编程语言都有函数，我就不用解释函数是什么东东了。：）
   或许不少朋友会问：我用存储过程不就可以了么，我为什么要使用函数？
   这里特别指出的一点：fn可以嵌套在Select语句中使用，而sp不可以。
   这里不打算大批特批一番游标了，当然，在我的程序里面，基本抛弃了游标（这里特别说明，是“基本”！因为还是有很多地方费用导游表不可的。），转而采用了fn。游标太消耗资源了。受不了……我快要感动得要流泪了…
   fn其实要比sp要简单得多。因为它的不确定性，从而也使他受到了不少的限制。
   举个函数的小粒子：
       Create Function fnTest ( @i int )
         Returns bit
       As
       begin
         Declare @b bit 
         if (Cast(@i As Float)/2)=(@i/2) 
           Set @b= 1
         else
           Set @b= 0
         Return @b             
       end
              ----------------------------------------------------------------------
              以上代码判断@i是单数还是双数。
                Use the Sample:
          Create Table #TT( fd1 int)
          Declare @i int
          Set @i=0
          While @i<=20
          begin
            Insert Into #tt values(@i)
            Set @i=@i+1
          end
          Select fd1,
                 '是否双数'=dbo.fnTest(fd1)    --在这里调用了函数，注意哈：函数之前一定要加上他的owner.
          From #tt

          Drop Table #tt
              ----------------------------------------------------------------------
              以上代码虚拟一段数据，然后判断数据表中是单数还是双数。
       有了sp的编程基础，写fn也就不是什么很难的事情了。刚才我提到了，fn受到限制颇多，这里稍稍列举：
          chair1. 只能调用确定性函数，不可以调用不确定函数。 比如，不可以调用GetDate()，以及自己定义的不确定性函数。
          chair2. 不可以使用动态SQL 。如：Execute, sp_ExecuteSQL （这是我最痛苦的事情了，痛哭中……）
          chair3. 不可以调用扩展存储过程
          chair4. 不可以调用Update语句对表进行更新
          chair5. 不可以在函数内部创建表(Create TAble )，修改表（Alter TAble）
          等等……头脑发昏中……反正稍微一些不可预测后果，无法返回后果的都不能用。
   5.事务 
     什么叫事务？ 这些就是数据库特有的术语了。懒虫在这里口头解释：就是把多件事情当做一件事情来处理。也就是大家同在一条船上，要活一起活，要over一起over ！
      我为什么要使用事务？ 俺这里再举个很俗很俗的例子：
          俺到银行存钱，于是有这么几个步骤： 
             1、把钱交给工作人员；2、工作人员填单；3、将单子给我签字；4、工作人员确认并输入电脑。
      要是，要是我把钱交给工作人员之后，进行到3我签字了。那哥们突然心脏病发作，over掉了，那，我的钱还没有输入电脑，但我却交了钱又签字确认了，而并没有其他任何记录。我岂不是要亏死了？？？我的血汗钱啊！赶紧退给我！！
      于是，在数据库里产生了这么一个术语：事务(Transaction)，也就是要么成功，要么失败，并恢复原状。
      还是写程序把：
      Create Proc sp我去存款(@M Money , @iOperator Int)
      As
      Begin
        Declare @i int
        Begin Tran                      --激活事务
          Exec @i=sp交钱 @m,@iOperator
          if @i<>0                      --这里一般用系统错误号 @@Error。 我这里为了举例子没有用到。需要根据实际情况。
          begin
            Rollback Tran                                      --回滚事务
            RaisError ('银行的窗口太少了，我懒得排队，不交了！：（ ', 16, 1) with Log  --记录日志
            Return -1                                          --返回错误号 
          end
          Exec @i=sp填单 @m,@iOperator
          if @i<>0
          begin
            Rollback Tran                                      --回滚事务
            RaisError ('银行的哥们打印机出了点毛病，打印不出单子来，把钱退回来给我吧？？', 16, 1) with Log 
            Return -2                                          
          end
          Exec @i=sp签字 @m
          if @i<>0
          begin
            Rollback Tran                                      --回滚事务
            RaisError ('我 靠？什么烂银行，换了3支笔都写不出水来！！老子不存了！！不签！', 16, 1) with Log  
            Return -3                                          
          end
          Exec @i=sp输入电脑 @m,@iOperator
          if @i<>0
          begin
            Rollback Tran                                      --回滚事务
            RaisError ('什么意思？磁盘空间已满？好了好了，把钱给我，我到旁边的这家银行！', 16, 1) with Log  
            Return -4                                          
          end
        Commit Tran               --提交事务
        Return 0
    End