《高性能的数据库》第四讲 编程细节

 1、触发器。

　　 定义： 何为触发器？在SQL Server里面也就是对某一个表的一定的操作，触发某种条件，从而执行的一段程序。触发器是一个特殊的存储过程。
　　 常见的触发器有三种：分别应用于Insert , Update , Delete 事件。(SQL Server 2000定义了新的触发器，这里不提）

　　 我为什么要使用触发器？比如，这么两个表：

　　 Create Table Student(　　　　　　 --学生表
　　　 StudentID int primary key,　　　--学号
　　　 ....
　　　)

　　 Create Table BorrowRecord(　　　　　　　--学生借书记录表
　　　 BorrowRecord　int identity(1,1),　　　--流水号　
　　　 StudentID　　 int ,　　　　　　　　　 --学号
　　　 BorrowDate　　datetime,　　　　　　　 --借出时间
　　　 ReturnDAte　　Datetime,　　　　　　　 --归还时间
　　　 ...
　　 )

　　用到的功能有:
　　　 1.如果我更改了学生的学号,我希望他的借书记录仍然与这个学生相关(也就是同时更改借书记录表的学号);
　　　 2.如果该学生已经毕业，我希望删除他的学号的同时，也删除它的借书记录。
　　等等。

　　这时候可以用到触发器。对于1，创建一个Update触发器：

　　Create Trigger truStudent
　　　On Student
　　　for Update
　　--------------------------
　　--Name:truStudent
　　--func:更新BorrowRecord 的StudentID，与Student同步。
　　--Use :None
　　--User:System
　　--Author:　懒虫 # SapphireStudio (www.chair3.com)
　　--Date　:　2003-4-16
　　--Memo　:　临时写写的，给大家作个Sample。没有调试阿。
　　-------------------------------------
　　As
　　　if Update(StudentID)
　　　begin

　　　　Update BorrowRecord
　　　　　Set br.StudentID=i.StudentID
　　　　　From BorrowRecord br , Deleted　d ,Inserted i
　　　　　Where br.StudentID=d.StudentID

　　　end　　　
　　　　　　　　
　　理解触发器里面的两个临时的表：Deleted , Inserted 。注意Deleted 与Inserted分别表示触发事件的表“旧的一条记录”和“新的一条记录”。
　　一个Update 的过程可以看作为：生成新的记录到Inserted表，复制旧的记录到Deleted表，然后删除Student记录并写入新纪录。

　　对于2，创建一个Delete触发器
　　Create trigger trdStudent
　　　On Student
　　　for Delete
　　----------------------------------------------
　　--Name:trdStudent
　　--func:同时删除 BorrowRecord 的数据
　　--Use :None
　　--User:System
　　--Author:　懒虫 # SapphireStudio (www.chair3.com)
　　--Date　:　2003-4-16
　　--Memo　:　临时写写的，给大家作个Sample。没有调试阿。
　　----------------------------
　　As
　　　Delete BorrowRecord
　　　　From BorrowRecord br , Delted d
　　　　Where br.StudentID=d.StudentID

　　从这两个例子我们可以看到了触发器的关键：A.2个临时的表；B.触发机制。
　　这里我们只讲解最简单的触发器。复杂的容后说明。
　　事实上，我不鼓励使用触发器。触发器的初始设计思想，已经被“级联”所替代。


　2.游标
　
　　在SQL 2000之前，游标可谓是SQL Server心中的痛： 老牛般的速度（CPU），河马般的胃口（内存）。可你却不能不用他。
　　什么叫游标呢？说白了就是像高级语言一样，是存放数据集，并逐条访问的一种机制。
　　比如在Delphi里面，要实现类似于这样的功能：（呵呵，不好意思，我只会Delphi，所以只能举一个Delphi的例子）
　
　　//这是一段Delphi的源代码
　　adoDataSet1.Close;
　　adoDataSet1.CommandText:=' Select * From Student order by StudentID ';
　　adoDataSet1.Open;
　　While Not adoDAtaSet1.Eof Do
　　Begin
　　
　　　YourVar:=adoDAtaSet1.FieldByName('StudentID').AsInteger;
　　　DoSomeThing();
　　　....　
　　　adoDataSet1.Next;
　　End
　　
　　在SQL Server 并没有很好的数据逐条访问机制，如果有，那就是游标。

　　还是举例子：

　　对于表

　　 Create Table BorrowRecord(　　　　　　　--学生借书记录表
　　　 BorrowRecord　int identity(1,1),　　　--流水号　
　　　 StudentID　　 int ,　　　　　　　　　 --学号
　　　 StudentFeeID　int ,　　　　　　　　　 --费用结算号　 （外键）
　　　 BorrowDate　　datetime,　　　　　　　 --借出时间
　　　 ReturnDAte　　Datetime,　　　　　　　 --归还时间
　　　 Fee　　　　　 Money　　　　　　　　　 --借书费用
　　　 ...
　　 )

　　 Create Table StudentFee(　　　　　　　　--学生费用结算表
　　　 StudentFeeID　int　primarykey ,　　　 --费用结算号　 （主键）
　　　 StudentID int ,　　　　　　　　　　　 --学号
　　　 BorrowBookAllFee　　　　　 Money,　　 --所有借书总费用　
　　　 ...
　　 )

　　 两者关系为多对一的关系，关联字段为StudentFeeID

　　 由于某种原因StudentFee表的数据遭到了破坏，我想StudentFee循环一遍将“所有借书总费用”重算 。

　　-------------------------------------------------
　　-----------------------------
　　--Name:一部分代码
　　--func:更新学生借书总费用
　　--Use :
　　--User:
　　--Author:　懒虫 # SapphireStudio (www.chair3.com)
　　--Date　:　2003-4-16
　　--Memo　:　临时写写的，给大家作个Sample。没有调试阿。
　　--------------------------------

　　 --声明一个游标
　　 Declare curStudentFee Cursor
　　　 for
　　　 Select StudentFeeID From StudentFee　　　

　　 --声明两个费用变量
　　 Declare @mBorrowBookAllFee Money　--总费用
　　 Declare @iStudentFeeID　　 Int　　--借书结算号

　　 --初始化　
　　 Set @mBorrowBookAllFee=0
　　 Set @iStudentFeeID=0

　　 --打开游标
　　 Open curStudentFee　

　　 --循环并提取记录
　　 Fetch Next From curStudentFee Into @iStudentFeeID　　
　　 While ( @@Fetch_Status=0 )　　
　　 begin

　　　 --从借书记录中计算某一学生的借书总记录的总费用
　　　 Select @mBorrowBookAllFee=Sum(BorrowBookAllFee)
　　　　 From BorrowRecord
　　　　 Where StudentFeeID=@iStudentFeeID　　

　　　 --更新到汇总表。
　　　 Update StudentFee Set BorrowBookAllFee=@mBorrowBookAllFee
　　　　 Where StudentFeeID=@iStudnetFeeID　　　　　

　　　 Fetch Next From curStudentFee Into @mFee
　　 end

　　 --关闭游标　　
　　 Close curStudentFee

　　 --释放游标
　　 Deallocate curStudentFee

　 --------
-----------------------------
　　关注游标的要点：1、声明、打开、关闭、释放 ； 2、@@Fetch_Status 游标提取状态标志，0表示正确

　　这里，我也要提到，我不鼓励使用游标。更多的情况下，在SQL 2000 里面 ，函数已经能够实现绝大部分游标的功能。
　　

　　好累，好不容易算是将1、2点讲完，算是上部分把。后面的几点等会再说了……：（：（。

　　　第四讲 编程细节（下部分）

　1、触发器
　2、游标
　3、函数
　4、存储过程
　5、事务


　　上次得到不少朋友的捧场，信心大增了：），决定趁着五一期间把它写完，反正外面非典厉害，也不好出去玩。这里讲到3、4、5了。

　　这里决定把4与3调换一下，先讲解4（存储过程，以下用sp来简称；函数，以下用fn来简称）

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
　　　　　　　　


　　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


　　　　　　　----------------------------------------------------------------------
　　　　　　　以上代码虚拟一段数据，然后判断数据表中是单数还是双数。
　　　　　　　　　　　　　　　　　　　　　　　　　　　　 --懒虫 # SapphireStudio
　　　　　　　　　　　　　精彩世界，尽在3腿软件网(www.chair3.com)！！
　　　　　　　-----------------------------------------------------------------------

　　　 有了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
　

　　　　　　　----------------------------------------------------------------------
　　　　　　　以上是伪代码，模拟我去存款的过程。
　　　　　　　 -----------------------------------------------------------------------

　 事务的几个要点 Begin Tran , @@Error（我这里没有用到，见上面的注释） , Rollback Tran , Commit Tran。
　 另：事务可以嵌套使用。这个时候需要命名。请参见sql server online help 。