数据库 SQL触发器实例讲解

《数据库 SQL触发器实例讲解》由会员分享，可在线阅读，更多相关《数据库 SQL触发器实例讲解（18页珍藏版）》请在装配图网上搜索。

1、SQL触发器实例讲解1SQL触发器实例1定义：何为触发器？在SQLServer里面也就是对某一个表的一定的操作，触发某种条件，从而执行的一段程序。触发器是一个特殊的存储过程。常见的触发器有三种：分别应用于Insert,Update,Delete事件。我为什么要使用触发器？比如，这么两个表：CreateTableStudent（-学生表StudentIDintprimarykey,-学号）-学生借书记录表-流水号CreateTableBorrowRecord(BorrowRecordintidentity(1,1),StudentIDBorrowDateReturnDAteint,datetim

2、e,Datetime,-学号-借出时间-归还时间）用到的功能有:1.如果我更改了学生的学号,我希望他的借书记录仍然与这个学生相关（也就是同时更改借书记录表的学号）;2.如果该学生已经毕业，我希望删除他的学号的同时，也删除它的借书记录。这时候可以用到触发器。对于1，创建一个Update触发器：CreateTriggertruStudentOnStudent-在Student表中创建触发器forUpdate-为什么事件触发As-事件触发后所要做的事情ifUpdate(StudentID)beginUpdateBorrowRecordSetStudentID=i.StudentIDFromBorro

3、wRecordbr,Deletedd,Insertedi-Deleted和Inserted临时表Wherebr.StudentID=d.StudentIDend理解触发器里面的两个临时的表：Deleted,Inserted。注意Deleted与Inserted分别表示触发事件的表“旧的一条记录”和“新的一条记录”。一个数据库系统中有两个虚拟表用于存储在表中记录改动的信息，分别是：虚拟表Inserted虚拟表Deleted在表记录新增时修改时删除时存放新增的记录存放用来更新的新记录不存储记录不存储记录存放更新前的记录存放被删除的记录一个Update的过程可以看作为：生成新的记录到Inserted

4、表，复制旧的记录到Deleted表，然后删除Student记录并写入新纪录。对于2，创建一个Delete触发器CreatetriggertrdStudentOnStudentforDeleteAsDeleteBorrowRecordFromBorrowRecordbr,DelteddWherebr.StudentID=d.StudentID从这两个例子我们可以看到了触发器的关键：A.2个临时的表；B.触发机制。SQL触发器实例2/*建立虚拟测试环境，包含：表卷烟库存表，表卷烟销售表。请大家注意跟踪这两个表的数据，体会触发器到底执行了什么业务逻辑，对数据有什么影响。为了能更清晰的表述触发器的作用

5、，表结构存在数据冗余，且不符合第三范式，这里特此说明。*/USEMasterGOIFEXISTS(SELECTNAMEFROMSYSOBJECTSWHEREXTYPE=UANDNAME=卷烟库存表)DROPTABLE卷烟库存表GOIFEXISTS(SELECTNAMEFROMSYSOBJECTSWHEREXTYPE=UANDNAME=卷烟销售表)DROPTABLE卷烟销售表GO-业务规则：销售金额=销售数量*销售单价业务规则。CREATETABLE卷烟销售表(卷烟品牌VARCHAR(40)PRIMARYKEYNOTNULL,购货商VARCHAR(40)NULL,销售数量INTNULL,销售单价

6、MONEYNULL,销售金额MONEYNULL)GO-业务规则：库存金额=库存数量*库存单价业务规则。CREATETABLE卷烟库存表(卷烟品牌VARCHAR(40)PRIMARYKEYNOTNULL,库存数量INTNULL,库存单价MONEYNULL,库存金额MONEYNULL)GO-创建触发器，示例1/*创建触发器T_INSERT_卷烟库存表,这个触发器较简单。说明：每当卷烟库存表发生INSERT动作，则引发该触发器。触发器功能：强制执行业务规则,保证插入的数据中,库存金额=库存数量*库存单价注意：INSERTED、DELETED为系统表，不可创建、修改、删除，但可以调用。重要：这两个系统

7、表的结构同插入数据的表的结构。*/IFEXISTS(SELECTNAMEFROMSYSOBJECTSWHEREXTYPE=TRANDNAME=T_INSERT卷烟库存表)DROPTRIGGERT_INSERT_卷烟库存表GOCREATETRIGGERT_INSERT_卷烟库存表ON卷烟库存表FORINSERTAS-提交事务处理BEGINTRANSACTION-强制执行下列语句，保证业务规则UPDATE卷烟库存表SET库存金额=库存数量*库存单价WHERE卷烟品牌IN（SELECT卷烟品牌fromINSERTED）COMMITTRANSACTIONGO/*针对卷烟库存表，插入测试数据：注意，第一

8、条数据（红塔山新势力）中的数据符合业务规则，第二条数据（红塔山人为峰）中，库存金额空，不符合业务规则，第三条数据（云南映像）中，库存金额不等于库存数量乘以库存单价，不符合业务规则第四条数据库存数量为0。请注意在插入数据后，检查卷烟库存表中的数据是否库存金额=库存数量*库存单价。*/INSERTINTO卷烟库存表（卷烟品牌,库存数量,库存单价,库存金额）SELECT红塔山新势力,100,12,1200UNIONALLSELECT红塔山人为峰,100,22,NULLUNIONALLSELECT云南映像,100,60,500UNIONALLSELECT玉溪,0,30,0GO-查询数据SELECT*F

9、ROM卷烟库存表GO/*结果集RecordId卷烟品牌库存数量库存单价库存金额红塔山新势力红塔山人为峰云南映像玉溪（所影响的行数为4行）*/-触发器示例2/*创建触发器T_INSERT_卷烟销售表,该触发器较复杂。说明:每当卷烟库存表发生INSERT动作，则引发该触发器。触发器功能：实现业务规则。业务规则:如果销售的卷烟品牌不存在库存或者库存为零,则返回错误。否则则自动减少卷烟库存表中对应品牌卷烟的库存数量和库存金额。*/IFEXISTS（SELECTNAMEFROMSYSOBJECTSWHEREXTYPE=TRANDNAME=T_INSERT卷烟销售表）DROPTRIGGERT_INSERT

10、_卷烟销售表GOCREATETRIGGERT_INSERT_卷烟销售表ON卷烟销售表FORINSERTASBEGINTRANSACTION-检查数据的合法性：销售的卷烟是否有库存,或者库存是否大于零IFNOTEXISTS（SELECT库存数量FROM卷烟库存表WHERE卷烟品牌IN（SELECT卷烟品牌FROMINSERTED）BEGIN-返回错误提示RAISERROR（错误！该卷烟不存在库存，不能销售。,16,1）-回滚事务ROLLBACKRETURNENDIFEXISTS（SELECT库存数量FROM卷烟库存表WHERE卷烟品牌IN（SELECT卷烟品牌FROMINSERTED）AND库存

11、数量=0）BEGIN-返回错误提示RAISERROR（错误！该卷烟库存小于等于0不能销售。,16,1）-回滚事务ROLLBACKRETURNEND-对合法的数据进行处理-强制执行下列语句，保证业务规则UPDATE卷烟销售表SET销售金额=销售数量*销售单价WHERE卷烟品牌IN（SELECT卷烟品牌FROMINSERTED）DECLARE卷烟品牌VARCHAR（40）SET卷烟品牌=（SELECT卷烟品牌FROMINSERTED）DECLARE销售数量MONEYSET销售数量=（SELECT销售数量FROMINSERTED）UPDATE卷烟库存表SET库存数量=库存数量-销售数量,库存金额=（

12、库存数量-销售数量）*库存单价WHERE卷烟品牌=卷烟品牌COMMITTRANSACTIONGO-请大家自行跟踪卷烟库存表和卷烟销售表的数据变化。-针对卷烟销售表，插入第一条测试数据，该数据是正常的。INSERTINTO卷烟销售表（卷烟品牌,购货商,销售数量,销售单价,销售金额）SELECT红塔山新势力,某购货商,10,12,1200GO-针对卷烟销售表，插入第二条测试数据，该数据销售金额不等于销售单价*销售数量-触发器将自动更正数据，使销售金额等于销售单价*销售数量。INSERTINTO卷烟销售表（卷烟品牌,购货商,销售数量,销售单价,销售金额）SELECT红塔山人为峰,某购货商,10,22

13、,2000GO-针对卷烟销售表，插入第三条测试数据，该数据中的卷烟品牌在卷烟库存表中找不到对应-触发器将报错。INSERTINTO卷烟销售表（卷烟品牌,购货商,销售数量,销售单价,销售金额）SELECT红河V8,某购货商,10,60,600GO/*结果集服务器：消息50000,级别16,状态1,过程T_INSERT卷烟销售表，行15错误！该卷烟不存在库存，不能销售。*/-针对卷烟销售表，插入第三条测试数据，该数据中的卷烟品牌在卷烟库存表中库存为0。-触发器将报错。INSERTINTO卷烟销售表(卷烟品牌,购货商,销售数量,销售单价,销售金额)SELECT玉溪,某购货商,10,30,300GO/

14、*结果集服务器：消息50000,级别16,状态1,过程T_INSERT卷烟销售表，行29错误！该卷烟库存小于等于0，不能销售。*/-查询数据SELECT*FROM卷烟库存表SELECT*FROM卷烟销售表GO/*补充：1、本示例主要通过一个简单的业务规则实现来进行触发器使用的说明,具体的要根据需要灵活处理；2、关于触发器要理解并运用好INSERTED,DELETED两个系统表；3、本示例创建的触发器都是FORINSERT,具体的语法可参考：Trigger语法CREATETRIGGERtrigger_nameONtable|viewWITHENCRYPTION-用于加密触发器FOR|AFTER|

15、INSTEADOFINSERT,UPDATEWITHAPPENDNOTFORREPLICATIONASIFUPDATE(column)AND|ORUPDATE(column).n|IF(COLUMNS_UPDATED()bitwise_operatorupdated_bitmask)comparison_operatorcolumn_bitmask.nsql_statement.n4、关于触发器，还应该注意(1)、DELETE触发器不能捕获TRUNCATETABLE语句。(2)、触发器中不允许以下Transact-SQL语句：ALTERDATABASECREATEDATABASEDISKINI

16、TDISKRESIZEDROPDATABASELOADDATABASELOADLOGRECONFIGURERESTOREDATABASERESTORELOG(3)、触发器最多可以嵌套32层。*/-修改触发器-实质上，是将CREATETRIGGER.修改为ALTERTRIGGER.即可。-删除触发器DROPTRIGGERxxxGO-删除测试环境DROPTABLE卷烟库存表GODROPTABLE卷烟销售表GODROPTRIGGERT_INSERT_卷烟库存表GODROPTRIGGERT_INSERT_卷烟销售表GO#触发器的基础知识和例子：createtriggertr_nameontable/v

17、iewfor|after|insteadofupdate,insert,deletewithencryptionasbatch|ifupdate(col_name)and|orupdate(col_name)说明：1tr_name：触发器名称ontable/view：触发器所作用的表。一个触发器只能作用于一个表for和after：同义4after与insteadof:sql2000新增项目afrer与insteadof的区别After在触发事件发生以后才被激活,只可以建立在表上Insteadof代替了相应的触发事件而被执行,既可以建立在表上也可以建立在视图上5insert、update、del

18、ete：激活触发器的三种操作，可以同时执行，也可选其一6ifupdate(col_name)：表明所作的操作对指定列是否有影响，有影响，则激活触发器。此外，因为delete操作只对行有影响，所以如果使用delete操作就不能用这条语句了(虽然使用也不出错，但是不能激活触发器，没意义)。7触发器执行时用到的两个特殊表：deleted,inserteddeleted和inserted可以说是一种特殊的临时表，是在进行激活触发器时由系统自动生成的，其结构与触发器作用的表结构是一样的，只是存放的数据有差异。续下面表格说明deleted与inserted数据的差异deleted与inserted数据的差

19、异Inserted存放进行insert和update操作后的数据Deleted存放进行delete和update操作前的数据注意：update操作相当于先进行delete再进行insert，所以在进行update操作时，修改前的数据拷贝一条到deleted表中，修改后的数据在存到触发器作用的表的同时，也同时生成一条拷贝到insered表中接(一)-触发器示例2/*创建触发器T_INSERT卷烟销售表,该触发器较复杂。说明:每当卷烟库存表发生INSERT动作，则引发该触发器。触发器功能：实现业务规则。业务规则:如果销售的卷烟品牌不存在库存或者库存为零，则返回错误。否则则自动减少卷烟库存表中对应品

20、牌卷烟的库存数量和库存金额。*/IFEXISTS(SELECTNAMEFROMSYSOBJECTSWHEREXTYPE=TRANDNAME=T_INSERT卷烟销售表)DROPTRIGGERT_INSERT_卷烟销售表GOCREATETRIGGERT_INSERT_卷烟销售表ON卷烟销售表FORINSERTASBEGINTRANSACTION-检查数据的合法性：销售的卷烟是否有库存，或者库存是否大于零IFNOTEXISTS（SELECT库存数量FROM卷烟库存表WHERE卷烟品牌IN（SELECT卷烟品牌FROMINSERTED）BEGIN-返回错误提示RAISERROR（错误！该卷烟不存在库

21、存，不能销售。,16,1）-回滚事务ROLLBACKRETURNENDIFEXISTS（SELECT库存数量FROM卷烟库存表WHERE卷烟品牌IN（SELECT卷烟品牌FROMINSERTED）AND库存数量=0）BEGIN-返回错误提示RAISERROR（错误！该卷烟库存小于等于0不能销售。,16,1）-回滚事务ROLLBACKRETURNEND-对合法的数据进行处理-强制执行下列语句，保证业务规则UPDATE卷烟销售表SET销售金额=销售数量*销售单价WHERE卷烟品牌IN（SELECT卷烟品牌FROMINSERTED）DECLARE卷烟品牌VARCHAR（40）SET卷烟品牌=（SEL

22、ECT卷烟品牌FROMINSERTED）DECLARE销售数量MONEYSET销售数量=（SELECT销售数量FROMINSERTED）UPDATE卷烟库存表SET库存数量=库存数量-销售数量,库存金额=（库存数量-销售数量）*库存单价WHERE卷烟品牌=卷烟品牌COMMITTRANSACTIONGO-请大家自行跟踪卷烟库存表和卷烟销售表的数据变化。-针对卷烟销售表，插入第一条测试数据，该数据是正常的。INSERTINTO卷烟销售表（卷烟品牌,购货商,销售数量,销售单价,销售金额）SELECT红塔山新势力,某购货商,10,12,1200GO-针对卷烟销售表，插入第二条测试数据，该数据销售金额不

23、等于销售单价*销售数量。-触发器将自动更正数据，使销售金额等于销售单价*销售数量。INSERTINTO卷烟销售表（卷烟品牌,购货商,销售数量,销售单价,销售金额）SELECT红塔山人为峰,某购货商,10,22,2000GOSQL拥有很多可用于计数和计算的内建函数。函数的语法内建SQL函数的语法是：SELECTfunction（歹U）FROM表函数的类型在SQL中，基本的函数类型和种类有若干种。函数的基本类型是 Aggregate函数Scalar函数合计函数(Aggregatefunctions)Aggregate函数的操作面向一系列的值，并返回一个单一的值。注释：如果在SELECT语句的项目列

24、表中的众多其它表达式中使用SELECT语句，则这个SELECT必须使用GROUPBY语句！Personstable（在大部分的例子中使用过）NameAgeAdams,John38Bush,George33Carter,Thomas28MSAccess中的合计函数函数描述AVG(column)返回某列的平均值COUNT(column)返回某列的行数（不包括NULL值）COUNT(*)返回被选行数FIRST(column)返回在指定的域中第一个记录的值LAST(column)返回在指定的域中最后一个记录的值MAX(column)返回某列的最高值MIN(column)返回某列的最低值STDEV(co

25、lumn)STDEVP(column)SUM(column)返回某列的总和VAR(column)VARP(column)在SQLServer中的合计函数函数描述AVG(column)返回某列的行数BINARY_CHECKSUMCHECKSUMCHECKSUM_AGGCOUNT(column)返回某列的行数（不包括NULL值）COUNT(*)返回被选行数COUNT(DISTINCTcolumn)返回相异结果的数目FIRST(column)返回在指定的域中第一个记录的值（SQLServer2000不支持）LAST(column)返回在指定的域中最后一个记录的值（SQLServer2000不支持）M

26、AX(column)返回某列的最高值MIN(column)返回某列的最低值STDEV(column)STDEVP(column)SUM(column)返回某列的总和VAR(column)VARP(column)SQLServer游标简介与使用说明游标(Cursor)是处理数据的一种方法，为了查看或者处理结果集中的数据，游标提供了在结果集中一次以行或者多行前进或向后浏览数据的能力。我们可以把游标当作一个指针，它可以指定结果中的任何位置，然后允许用户对指定位置的数据进行处理。1. 游标的组成游标包含两个部分：一个是游标结果集、一个是游标位置。游标结果集：定义该游标得SELECT语句返回的行的集合。

27、游标位置：指向这个结果集某一行的当前指针。2. 游标的分类游标共有3类：API服务器游标、Transaction-SQL游标和API客户端游标。其中前两种游标都是运行在服务器上的，所以又叫做服务器游标。API服务器游标API服务器游标主要应用在服务上，当客户端的应用程序调用API游标函数时，服务器会对API函数进行处理。使用API函数和方法可以实现如下功能：(1) 打开一个连接。(2) 设置定义游标特征的特性或属性，API自动将游标影射到每个结果集。(3) 执行一个或多个Transaction-SQL语句。(4) 使用API函数或方法提取结果集中的行。API服务器游标包含以下四种：静态游标、动

28、态游标、只进游标、键集驱动游标(Primarykey)静态游标的完整结果集将打开游标时建立的结果集存储在临时表中，(静态游标始终是只读的)。静态游标具有以下特点：总是按照打开游标时的原样显示结果集；不反映数据库中作的任何修改，也不反映对结果集行的列值所作的更改；不显示打开游标后在数据库中新插入的行；组成结果集的行被其他用户更新，新的数据值不会显示在静态游标中；但是静态游标会显示打开游标以后从数据库中删除的行。动态游标与静态游标相反，当滚动游标时动态游标反映结果集中的所有更改。结果集中的行数据值、顺序和成员每次提取时都会改变。只进游标不支持滚动，它只支持游标从头到尾顺序提取数据行。注意：只进游标

29、也反映对结果集所做的所有更改。键集驱动游标同时具有静态游标和动态游标的特点。当打开游标时，该游标中的成员以及行的顺序是固定的，键集在游标打开时也会存储到临时工作表中，对非键集列的数据值的更改在用户游标滚动的时候可以看见，在游标打开以后对数据库中插入的行是不可见的，除非关闭重新打开游标。Transaction-SQL游标该游标是基于DeclareCursor语法，主要用于Transaction-SQL脚本、存储过程以及触发器中。Transaction-SQL游标在服务器处理由客户端发送到服务器的Transaction-SQL语句。在存储过程或触发器中使用Transaction-SQL游标的过程为

30、：(1) 声明Transaction-SQL变量包含游标返回的数据。为每个结果集列声明一个变量。声明足够大的变量来保存列返回的值，并声明变量的类型为可从数据类型隐式转换得到的数据类型。(2) 使用DeclareCursor语句将Transaction-SQL游标与Select语句相关联。还可以利用DeclareCursor定义游标的只读、只进等特性。(3) 使用Open语句执行Select语句填充游标。(4) 使用FetchInto语句提取单个行，并将每列中得数据移至指定的变量中。注意：其他Transaction-SQL语句可以引用那些变量来访问提取的数据值。Transaction-SQL游标

31、不支持提取行块。(5) 使用Close语句结束游标的使用。注意：关闭游标以后，该游标还是存在，可以使用Open命令打开继续使用，只有调用Deallocate语句才会完全释放。客户端游标该游标将使用默认结果集把整个结果集高速缓存在客户端上，所有的游标操作都在客户端的高速缓存中进行。注意：客户端游标只支持只进和静态游标。不支持其他游标。3.游标的生命周期游标的生命周期包含有五个阶段：声明游标、打开游标、读取游标数据、关闭游标、释放游标。声明游标是为游标指定获取数据时所使用的Select语句，声明游标并不会检索任何数据，它只是为游标指明了相应的Select语句。Declare游标名称Cursor参数

32、声明游标的参数(1) Local与Global:Local表示游标的作用于仅仅限于其所在的存储过程、触发器以及批处理中、执行完毕以后游标自动释放。Global表示的是该游标作用域是整个会话层。由连接执行的任何存储过程、批处理等都可以引用该游标名称，仅在断开连接时隐性释放。(2) Forward_only与Scroll:前者表示为只进游标，后者表示为可以随意定位。默认为前者。(3) Static、Keyset与Dynamic:第一个表示定义一个游标，其数据存放到一个临时表内，对游标的所有请求都从临时表中应答，因此，对该游标进行提取操作时返回的数据不反映对基表所作的修改，并且该游标不允许修改。Ke

33、yset表示的是，当游标打开时，键集驱动游标中行的身份与顺序是固定的，并把其放到临时表中。Dynamic表示的是滚动游标时，动态游标反映对结果集内所有数据的更改。(4) Read_only、Scroll_Locks与Optimistic:第一个表示的是只读游标，第二个表示的是在使用的游标结果集数据上放置锁，当行读取到游标中然后对它们进行修改时，数据库将锁定这些行，以保证数据的一致性。Optimistic的含义是游标将数据读取以后，如果这些数据被更新了，则通过游标定位进行的更新与删除操作将不会成功。标准游标:DeclareMyCursorCursorForSelect*FromMaster_Go

34、ods只读游标DeclareMyCusrorCursorForSelect*FromMaster_GoodsForReadOnly可更新游标DeclareMyCusrorCursorForSelect*FromMaster_GoodsForUpDate打开游标使用Open语句用于打开Transaction-SQL服务器游标，执行Open语句的过程中就是按照Select语句进行填充数据，打开游标以后游标位置在第一行。打开游标全局游标：OpenGlobalMyCursor局部游标：OpenMyCursor读取游标数据：在打开游标以后，使用Fetch语句从Transaction-SQL服务器游标中检

35、索特定的一行。使用Fetch操作，可以使游标移动到下一个记录，并将游标返回的每个列得数据分别赋值给声明的本地变量。FetchNext|Prior|First|Last|Absoluten|RelativenFromMyCursorIntoGoodsID,GoodsName其中：Next表示返回结果集中当前行的下一行记录，如果第一次读取则返回第一行。默认的读取选项为NextPrior表示返回结果集中当前行的前一行记录，如果第一次读取则没有行返回，并且把游标置于第一行之前。First表示返回结果集中的第一行，并且将其作为当前行。Last表示返回结果集中的最后一行，并且将其作为当前行。Absolut

36、en如果n为正数，则返回从游标头开始的第n行，并且返回行变成新的当前行。如果n为负，则返回从游标末尾开始的第n行，并且返回行为新的当前行，如果n为0，则返回当前行。Relativen如果n为正数，则返回从当前行开始的第n行，如果n为负,则返回从当前行之前的第n行，如果为0,则返回当前行。关闭游标调用的是Close语句，方式如下：CloseGlobalMyCursorCloseMyCursor释放游标调用的是Deallocate语句，方法如下：DeallocateGlboalMyCursorDeallocateMyCursor游标实例:DeclareMyCusrorCursorScrollFor

37、Select*FromMaster_GoodsOrderByGoodsIDOpenMyCursorFetchnextFromMyCursorIntoGoodsCode,GoodsNameWhile(Fetch_Status=0)BeginBeginSelectGoodsCode=Convert(Char(20),GoodsCode)SelectGoodsName=Convert(Char(20),GoodsName)PRINTGoodsCode+:+GoodsNameEndFetchnextFromMyCursorIntoGoodsCode,GoodsNameEndCloseMyCursorD

38、eallocateMyCursor修改当前游标的数据方法如下:UpDateMaster_GoodsSetGoodsName=yangyang8848WhereCurrentOfMyCursor;删除当前游标行数据的方法如下:DeleteFromMaster_GoodsWhereCurrentOfMyCursorSelectCURSOR_ROWS可以得到当前游标中存在的数据行数。注意：此变量为一个连接上的全局变量，因此只对应最后一次打开的游标。SQLServer存储过程简介与使用方法SqlServer的存储过程是一个被命名的存储在服务器上的Transacation-Sql语句集合,是封装重复性工

39、作的一种方法,它支持用户声明的变量、条件执行和其他强大的编程功能。存储过程相对于其他的数据库访问方法有以下的优点：（1）重复使用。存储过程可以重复使用，从而可以减少数据库开发人员的工作量。（2）提高性能。存储过程在创建的时候就进行了编译，将来使用的时候不用再重新编译。一般的SQL语句每执行一次就需要编译一次，所以使用存储过程提高了效率。（3）减少网络流量。存储过程位于服务器上，调用的时候只需要传递存储过程的名称以及参数就可以了，因此降低了网络传输的数据量。（4）安全性。参数化的存储过程可以防止SQL注入式的攻击，而且可以将Grant、Deny以及Revoke权限应用于存储过程。存储过程一共分为

40、了三类：用户定义的存储过程、扩展存储过程以及系统存储过程。其中，用户定义的存储过程又分为Transaction-SQL和CLR两种类型。Transaction-SQL存储过程是指保存的Transaction-SQL语句集合，可以接受和返回用户提供的参数。CLR存储过程是指对.NetFramework公共语言运行时（CLR）方法的引用，可以接受和返回用户提供的参数。他们在.NetFramework程序集中是作为类的公共静态方法实现的。（本文就不作介绍了）创建存储过程的语句如下：田日Codeschema_name:代表的是存储过程所属的架构的名称例如：CreateSchemayangyang884

41、8GoCreateProcyangyang8848.AllGoodsAsSelect*FromMaster_GoodsGo执行：ExecAllGoods发生错误。执行：Execyangyang8848.AllGoods正确执行。；Number:用于对同名过程进行分组的可选整数。使用一个DROPPROCEDURE语句可将这些分组过程一起删除。例如：CreateProcS1;1ASSelect*FromMaster_GoodsGoCreateProcS1;2AsSelect*FromMaster_LocationGo创建完毕了两个存储过程。它们在同一个组S1里，如果执行ExecS1则存储过程默认执

42、行ExecS1;1。如果我们想得到所有据点信息则需要执行ExecS1;2。当我们要删除存储过程的时候，只能执行DropExecS1则该组内所有的存储过程被删除。parameter:存储过程中的参数，除非将参数定义的时候有默认值或者将参数设置为等于另一个参数，否则用户必须在调用存储过程的时候为参数赋值。存储过程最多有2100个参数。例如：CreateProcyangyang8848.OneGoodsGoodsCodevarchar(10)AsSelect*FromMaster_GoodsWhereGoodsCode=GoodsCodeGo调用的代码：DeclareCodevarchar(10)S

43、etCode=0004Execyangyang8848.OneGoodsCode在参数的后边加入Output表明该参数为输出参数。CreateProcyangyang8848.OneGoodsGoodsCode2varchar(10)output,GoodsCodevarchar(10)=0011AsSelect*FromMaster_GoodsWhereGoodsCode=GoodsCodeSetGoodsCode2=0005Go调用方法：DeclareVV2varchar(10)Execyangyang8848.OneGoodsCodeout注意：如果存储过程的两个参数一个有默认值一个没有

44、，那么我们要把有默认值得放在后边，不然会出问题哦细心的朋友，可能看到上边的语句有一些不同，比如，存储过程用的是output，而调用语句用的是out。我要告诉您，两者是一样的。RECOMPILE：指示数据库引擎不缓存该过程的计划，该过程在运行时编译。如果指定了FORREPLICATION，则不能使用此选项。对于CLR存储过程，不能指定RECOMPILE。这个说一个非常好用的函数OBJECT_ID：返回架构范围内对象的数据库对象标识号。例如：我们创建存储过程时，可以如下写代码IfObject_ID(yangyang8848.OneGoods)IsNotNullDropProcyangyang884

45、8.OneGoodsGoCreateProcyangyang8848.OneGoodsGoodsCode2varchar(10)out,GoodsCodevarchar(10)=0011AsSelect*FromMaster_GoodsWhereGoodsCode=GoodsCodeSetGoodsCode2=0005Go针对于上边的这个存储过程，我们调用以下SQL查询SelectdefinitionFromsys.sql_modulesWhereobject_id=Object_ID(yangyang8848.OneGoods);我们是可以查到结果的。可是如果我们对该存储过程加入ENCRYP

46、TION那么你将无法看到任何结果IfObject_ID(yangyang8848.OneGoods)IsNotNullDropProcyangyang8848.OneGoodsGoCreateProcyangyang8848.OneGoodsGoodsCode2varchar(10)out,GoodsCodevarchar(10)=0011WithEncryptionAsSelect*FromMaster_GoodsWhereGoodsCode=GoodsCodeSetGoodsCode2=0005Go然后我们查询sys.sql_modules目录视图，将返回给你Null。然后我们执行以下SQ

47、L:Execsp_helptextyangyang8848.OneGoods你将得到以下结果：Thetextforobjectyangyang8848.OneGoodsisencrypted.说到这里你应该明白了，参数ENCRYPTION：是一种加密的功能，将CREATEPROCEDURE语句的原始文本转换为模糊格式。模糊代码的输出在SQLServer2005的任何目录视图中都不能直接显示。对系统表或数据库文件没有访问权限的用户不能检索模糊文本。但是，可通过DAC端口访问系统表的特权用户或直接访问数据库文件的特权用户可使用此文本。此外，能够向服务器进程附加调试器的用户可在运行时从内存中检索已解

48、密的过程。前两天写了一篇关于游标的介绍文章，下边写一个例子，将游标与存储过程一起使用上：IfObject_ID(dbo.GetMasterGoods)IsNotNullDropProcdbo.GetMasterGoodsGoCreateProcGetMasterGoodsMyCursorCursorVaryingOutputWithEncryptionAsSetMyCursor=CursorForSelectGoodsCode,GoodsNameFromMaster_GoodsOpenMyCursorGo-下边建立另外一个存储过程，用于遍历游标输出结果CreateProcGetAllGoods

49、IDAndNameAsDeclareGoodsCodevarchar(18)DeclareGoodsNamenvarchar(20)DeclareMasterGoodsCursorCursorExecGetMasterGoodsMasterGoodsCursoroutFetchNextFromMasterGoodsCursorInToGoodsCode,GoodsNameWhile(Fetch_Status=0)BeginBeginPrintGoodsCode+:+GoodsNameEndFetchNextFromMasterGoodsCursorInToGoodsCode,GoodsNameEndCloseMasterGoodsCursorDeallocateMasterGoodsCursorGo最后执行ExecGetAllGoodsIDAndName结果为以下内容品0003品0004123123品0006品0007品0008品0009品0010品0011品0012品0013品