注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

北漂的小羊

Java编程,开发者,程序员,软件开发,编程,代码。新浪微博号:IT国子监

 
 
 

日志

 
 
关于我

在这里是面向程序员的高品质IT技术学习社区,是程序员学习成长的地方。让我们更好地用技术改变世界。请关注新浪微博号: IT国子监(http://weibo.com/itguozijian)

网易考拉推荐

触发器、游标、存储过程、函数  

2011-05-14 08:59:24|  分类: SQL Server |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

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

  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

  As

   Delete BorrowRecord

    From BorrowRecord br , Delted d

    Where br.StudentID=d.StudentID

  从这两个例子我们可以看到了触发器的关键:A.2个临时的表;B.触发机制。

  这里我们只讲解最简单的触发器。复杂的容后说明。

  事实上,我不鼓励使用触发器。触发器的初始设计思想,已经被“级联”所替代。

2.游标

  在SQL 2000之前,游标可谓是SQL Server心中的痛: 老牛般的速度(CPU),河马般的胃口(内存)。可你却不能不用他。

  什么叫游标呢?说白了就是像高级语言一样,是存放数据集,并逐条访问的一种机制。

  比如在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循环一遍将“所有借书总费用”重算 。

   --声明一个游标

   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 里面 ,函数已经能够实现绝大部分游标的功能。 

3、存储过程。

  存储过程是数据库编程里面最重要的表现方式了。

  在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

  )

  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 

  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命令

4、函数。

  函数是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

   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)

  评论这张
 
阅读(373)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2016