SQL存储过程

原文地址：http://www.xiaohao.com/bfart/view.asp?id=39

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

　 呵呵，这里我要提到上次说道的：我拒绝使用触发器。这里我要开始猛批一顿触发器了。 

　 在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 
　　　　　　　　 
　　妈 的我怎么这么背啊我？？什么时候不死机，偏偏在这时！！丢了不少……：（：（ 
　　下面默哀3分钟…… 

　　 1…… 
　　 2…… 
　　 3…… 
　　 
　　好了，继续！回忆刚才写的内容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。 

　　　---------------------------------------------------------------------- 
　　　以上代码没有测试，顺手写写的。希望不会出错：）　 
　　　　　　　　　　　　　　　　　　　　　　　　　 --懒虫 # SapphireStudio 

　　　　　　　　 精彩世界，尽在3腿软件网(www.chair3.com)！！ 
　　　-----------------------------------------------------------------------　　　　　　　　　　　　　　　　　　　　　　　 
　 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之内的单数与双数。 
　　　　　　　　　　　　　　　　　　　　　　　　　　　　 --懒虫 # SapphireStudio 
　　　　　　　　　　　　　精彩世界，尽在3腿软件网(www.chair3.com)！！ 
　　　　　　　----------------------------------------------------------------------- 
　 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命令