5存储过程与触发器教学课件

《5存储过程与触发器教学课件》由会员分享，可在线阅读，更多相关《5存储过程与触发器教学课件（67页珍藏版）》请在装配图网上搜索。

1、5存储过程与触发器存储过程与触发器服从真理，就能征服一切事物1.1、存储过程的概念、存储过程的概念4、存储过程的缺点、存储过程的缺点l l不不能能实实现现复复杂杂的的逻逻辑辑操操作作：这这是是因因为为SQLSQL语语言言本本身身就就不不支支持持复复杂杂的的程程序序设设计计结结构构，所所以以各各种种程程序序设设计计语语言言都都有自己对数据库进行操作处理的功能。有自己对数据库进行操作处理的功能。l l用用存存储储过过程程实实现现数数据据库库的的全全部部功功能能比比较较困困难难：不不同同用用户户的的需需求求不不同同，当当涉涉及及特特殊殊管管理理要要求求时时，很很难难全全面面满满足足要要求求，若若将将

2、所所有有需需求求都都定定义义为为存存储储过过程程，其其数数量量将将相相当当可观，记忆和掌握这些存储过程是很困难。可观，记忆和掌握这些存储过程是很困难。存存储储过过程程分分为为系系统统存存储储过过程程和和用用户户自自定定义义存存储储过过程程。系系统统存存储储过过程程可可直直接接使使用用，如如定定义义和和绑绑定定规规则则对对象象、默默认值对象等。用户自定义存储过程必须先定义后使用。认值对象等。用户自定义存储过程必须先定义后使用。1.21.2、用、用CREATE PROCCREATE PROC创建存储过程创建存储过程 创建存储过程应遵守的规则：创建存储过程应遵守的规则：l l名称标识符的长度最大为名

3、称标识符的长度最大为128128个字符，且必须惟一。个字符，且必须惟一。l l每个存储过程最多可以使用每个存储过程最多可以使用1,0241,024个参数。个参数。l l存储过程的最大容量有一定的限制。存储过程的最大容量有一定的限制。l l存储过程支持多达存储过程支持多达3232层嵌套。层嵌套。l l在对存储过程命名时最好和系统存储过程名区分。在对存储过程命名时最好和系统存储过程名区分。CREATE CREATE PROCPROCEDURE EDURE 存储过程名存储过程名 ;整数整数 形参变量形参变量 数据类型数据类型varying=varying=默认值默认值 output,n output

4、,n WITH recompile WITH recompile|encryptionencryption|recompile,encryptionrecompile,encryption FOR replication FOR replication AS AS SQL SQL 语句系列语句系列1.21.2、用、用CREATE PROCCREATE PROC创建存储过程创建存储过程说明：说明：l l该该语语句句可可以以创创建建永永久久存存储储过过程程，也也可可以以创创建建一一个个在在一一个个会会话话中中临临时时使使用用的的局局部部存存储储过过程程（名名称称前前加加一一个个#），还还可可以以创

5、创建建一一个个在在所所有会话中临时使用的全局存储过程（名称前加两个有会话中临时使用的全局存储过程（名称前加两个#）。）。l l整整数数：可可作作为为同同名名过过程程分分组组的的后后缀缀序序号号（如如OP1OP1，OP2OP2可可定定义义属属于于一一组组），同同组组的的过过程程将将来来可可以以用用一一条条DROP DROP PROCEDUREPROCEDURE删删除除命命令令全全部删除掉。部删除掉。l l形形参参变变量量：指指定定接接收收调调用用参参数数或或返返回回值值的的变变量量，默默认认状状态态下下只只表表示示单单一一数数值值，不不能能代代表表表表名名、列列名名或或其其他他对对象象名名，形形

6、参参变变量量的的作作用用域域为为该该存存储储过过程程；所所有有数数据据类类型型（nextnext、imageimage）都都可可以以作作为为过过程的参数类型。程的参数类型。n nVaryingVarying：仅仅适适用用于于游游标标参参数数，指指定定形形参参变变量量可可作作为为支支持结果集的返回参数。持结果集的返回参数。n n默默认认值值：调调用用过过程程语语句句时时不不提提供供参参数数时时，形形参参变变量量则则取取该该默默认值。默认值只能是常量或认值。默认值只能是常量或NULLNULL。n nOutputOutput：指指定定形形参参变变量量是是返返回回给给调调用用语语句句的的参参数数，可可

7、以以是所有数据类型，也可以是游标占位符。是所有数据类型，也可以是游标占位符。1.21.2、用、用CREATE PROCCREATE PROC创建存储过程创建存储过程l lRECOMPILERECOMPILE：执执行行完完存存储储过过程程后后不不保保留留存存储储过过程程的的备备份份，每次执行时都需要对存储过程重新编译。每次执行时都需要对存储过程重新编译。l l ENCRYPTIONENCRYPTION：存存储储过过程程作作为为数数据据库库对对象象在在系系统统的的syscommentssyscomments表表中中留留下下完完整整的的代代码码信信息息，并并对对访访问问这这些些数数据的入口进行加密。

8、据的入口进行加密。注注意意：在在数数据据库库内内创创建建的的每每个个对对象象（约约束束、默默认认值值、日日志志、规规则则、存存储储过过程程等等）都都会会作作为为该该库库系系统统表表SysobjectsSysobjects中的一条记录占一行，该表的结构如表中的一条记录占一行，该表的结构如表8-18-1。表表8-1 Sysobjects8-1 Sysobjects系统表的结构系统表的结构 列名列名数据类型数据类型存储内容说明存储内容说明namenamesysnamesysname对象名对象名ididintint对象标识号对象标识号xtypextype或或typetypechar(2)char(2)

9、对象类型。其中的主要类型：对象类型。其中的主要类型：C=CHECK C=CHECK 约束约束 P=P=存储过程存储过程D=D=默认值或默认值或 DEFAULT DEFAULT 约束约束 TR=TR=触发器触发器F=FOREIGN KEY F=FOREIGN KEY 约束约束 V=V=视图视图K=PRIMARY KEY K=PRIMARY KEY 或或 UNIQUE UNIQUE 约束约束 R=R=规则规则FN=FN=标量函数（自定义函数）标量函数（自定义函数）U=U=用户表用户表L=L=日志日志 S=S=系统表系统表crdatecrdatedatetimedatetime对象的创建日期。对象的

10、创建日期。1.21.2、用、用CREATE PROCCREATE PROC创建存储过程创建存储过程【例例8-18-1】在在diannaoxsdiannaoxs数数据据库库中中建建立立一一个个名名为为“计计算算机机_pro_pro”的的存存储储过过程程，用用于于在在“进进货货表表20062006”“”“供供货货商商表表”“”“商商品品一一览览表表”中中查查询询“计算机计算机”产品的供货商及进货信息。产品的供货商及进货信息。先先判判断断sysobjectssysobjects系系统统表表中中是是否否存存在在名名为为“计计算算机机_pro_pro”的的存存储储过过程，如果存在则删除原有的存储过程，然

11、后再创建。程，如果存在则删除原有的存储过程，然后再创建。USE diannaoxs USE diannaoxs IF exists(SELECT name FROM sysobjects IF exists(SELECT name FROM sysobjects WHERE name=WHERE name=计算机计算机_pro AND type=p)_pro AND type=p)DROP procedure DROP procedure 计算机计算机_pro_pro GO GO CREATE procedure CREATE procedure 计算机计算机_pro -_pro -创建存储过

12、程创建存储过程 AS AS SELECT SELECT 进货日期进货日期,供货商供货商,j.,j.货号货号,货名货名,数量数量,进价进价 FROM FROM 进货表进货表2006 AS j,2006 AS j,供货商表供货商表 AS g,AS g,商品一览表商品一览表 s s WHERE j.WHERE j.供货商供货商ID=g.ID=g.供货商供货商ID AND j.ID AND j.货号货号=s.=s.货号货号 AND s.AND s.货名货名=计算机计算机 GO GO EXECUTE EXECUTE 计算机计算机_pro -_pro -调用执行存储过程调用执行存储过程 其其中中“进进价价

13、”可可使使用用【实实例例练练习习7-77-7】创创建建的的自自定定义义函函数数“货货币币格格式式（）（）”【实例练习【实例练习1】建建立立一一个个名名为为“商商品品_pro1_pro1”的的存存储储过过程程，带带有有一一个个参参数数接接收收指指定定的的商商品品名名称称，在在“进进货货表表20062006”“”“供供货货商商表表”“”“商商品品一一览览表表”中中查查询询该产品的供货商及进货信息。该产品的供货商及进货信息。USE diannaoxs USE diannaoxs IF exists(SELECT name FROM sysobjects IF exists(SELECT name F

14、ROM sysobjects WHERE name=WHERE name=商品商品_pro1 AND type=p)_pro1 AND type=p)DROP procedure DROP procedure 商品商品_pro1_pro1 GO GO CREATE procedure CREATE procedure 商品商品_pro1 _pro1 商品名商品名 nvarchar(8)nvarchar(8)AS AS SELECT SELECT 进货日期进货日期,供货商供货商,j.,j.货号货号,货名货名,数量数量,进价进价 FROM FROM 进货表进货表2006 j join 2006 j

15、 join 供货商表供货商表 g g ON j.ON j.供货商供货商ID=g.ID=g.供货商供货商ID join ID join 商品一览表商品一览表 s s ON j.ON j.货号货号=s.=s.货号货号 WHERE s.WHERE s.货名货名=商品名商品名 GO GO EXECUTE EXECUTE 商品商品_pro1 _pro1 计算机计算机-运行结果与图运行结果与图8-18-1相同相同再输入代码：再输入代码：EXEC EXEC 商品商品_pro1 _pro1 显示器显示器-结果如图结果如图8-2 8-2 1.31.3、用、用EXECUTEEXECUTE执行存储过程执行存储过程

16、语法格式：语法格式：EXECEXECUTEUTE整型变量整型变量=存储过程名存储过程名;标识号标识号 形参变量形参变量=值值|变量变量 output output|default,n default,n WITH recompile WITH recompile 简单格式：简单格式：EXECEXECUTE UTE 整型变量整型变量=存储过程名存储过程名 值值|变量变量 output output|default ,n default ,n 说明：说明：l l整整型型变变量量：用用于于接接收收存存储储过过程程的的返返回回状状态态值值，必必须须是是在此之前已经定义的，若不需要返回状态则省略。在此之

17、前已经定义的，若不需要返回状态则省略。l l标标识识号号：指指定定同同名名存存储储过过程程分分组组的的后后缀缀序序号号，未未分分组组则省略。则省略。1.31.3、用、用EXECUTEEXECUTE执行存储过程执行存储过程 l l形形参参变变量量：指指定定创创建建存存储储过过程程时时定定义义的的形形参参变变量量，如如果果使使用用该该项项参参数数则则此此处处的的顺顺序序与与创创建建时时的的顺顺序序可可以以不不一一致致。省省略略“形形参参变变量量”则则此此处处提提供供的的参参数数个个数数、用用途途、顺序必须与定义时一致。顺序必须与定义时一致。n n变量用于存放参数值或者接收存储过程的返回值。变量用于

18、存放参数值或者接收存储过程的返回值。n nOutputOutput：与与定定义义时时一一致致，指指定定该该项项为为存存储储过过程程的的返返回值。回值。n ndefaultdefault：根根据据存存储储过过程程的的定定义义，为为参参数数提提供供默默认认值。值。l lWITH RECOMPILEWITH RECOMPILE：强制编译新计划，建议尽量不用。：强制编译新计划，建议尽量不用。一般常用的简单格式：一般常用的简单格式：EXEC EXEC 存存储储过过程程名名 值值|变变量量outputoutput|default default ,n,n【实例练习【实例练习2】创创建建一一个个既既有有参参

19、数数，又又有有返返回回状状态态值值的的存存储储过过程程“商商品品_pro2_pro2”，在在“销销售售表表20062006”“”“进进货货表表20062006”“”“供供货货商商表表”“”“商商品品一一览览表表”中中查查询询某某个个供供货货厂厂家家所所提提供供产产品品的的销销售售信信息息，如如果果从从指指定定厂厂家家有有进进货货而而且且有有销销售售则则返返回回数数字字1 1并并显显示示销销售售信信息，否则返回息，否则返回0 0。IF exists(SELECT name FROM sysobjects IF exists(SELECT name FROM sysobjects WHERE na

20、me=WHERE name=商品商品_pro2 AND type=p)_pro2 AND type=p)DROP procedure DROP procedure 商品商品_pro2_pro2 GO GO CREATE procedure CREATE procedure 商品商品_pro2 _pro2 厂家厂家 nvarchar(15)nvarchar(15)AS AS IF exists(SELECT*IF exists(SELECT*FROM FROM 销售表销售表2006 x,2006 x,进货表进货表2006 j,2006 j,供货商表供货商表 g,g,商品一览表商品一览表 s s

21、WHERE j.WHERE j.供货商供货商ID=g.ID=g.供货商供货商ID AND j.ID AND j.货号货号=s.=s.货号货号 AND x.AND x.货号货号=j.=j.货号货号 AND g.AND g.供货商供货商 like like 厂家厂家+%)+%)BEGIN BEGIN SELECT SELECT 销售日期销售日期,x.,x.货号货号,x.,x.货名货名,供货商供货商,x.,x.数量数量 FROM FROM 销售表销售表2006 x,2006 x,进货表进货表2006 j,2006 j,供货商表供货商表 g,g,商品一览表商品一览表 s s WHERE j.WHERE

22、 j.供货商供货商ID=g.ID=g.供货商供货商ID AND j.ID AND j.货号货号=s.=s.货号货号 AND x.AND x.货号货号=j.=j.货号货号 AND g.AND g.供货商供货商 like like 厂家厂家+%+%RETURN 1 RETURN 1 END END ELSE ELSE RETURN 0 RETURN 0GO【实例练习【实例练习2】如如果果只只想想知知道道某某个个厂厂家家是是否否有有进进货货有有销销售售，则则第第二二个个SELECTSELECT查询语句可以省略。存储过程的调用如下：查询语句可以省略。存储过程的调用如下：DECLARE x int DE

23、CLARE x int EXECUTE x=EXECUTE x=商品商品_pro2 _pro2 上海电脑上海电脑 IF x=1 IF x=1 PRINT PRINT 所查找厂家的产品有进货也有销售。所查找厂家的产品有进货也有销售。ELSE ELSE PRINT PRINT 所所查查找找厂厂家家不不存存在在或或者者没没有有进进货货或或者者产产品品没没有销售！有销售！【实例练习【实例练习2】因因为为如如果果查查到到信信息息会会显显示示出出来来，所所以以调调用用存存储储过过程程查查询可以写为：询可以写为：DECLARE x int DECLARE x int EXECUTE x=EXECUTE x=

24、商品商品_pro2 _pro2 北京联想北京联想 IF IF x=0 x=0 PRINT PRINT 所所查查找找厂厂家家不不存存在在或或者者没没有有进进货货或或者产品没有销售！者产品没有销售！【实例练习【实例练习2】若输入以下代码：若输入以下代码：DECLARE x int DECLARE x int EXECUTE x=EXECUTE x=商品商品_pro2 _pro2 北京科技北京科技 IF IF x=0 x=0 PRINT PRINT 所所查查找找厂厂家家不不存存在在或或者者没没有有进进货货或或者者产产品品没没有有销销售售！因因为为没没有有“北北京京科科技技”的的供供货货厂厂家家，运运

25、行行结结果果则则会会显显示示：“所所查查找找厂厂家不存在或者没有进货或者产品没有销售！家不存在或者没有进货或者产品没有销售！”若输入以下代码：若输入以下代码：DECLARE x int DECLARE x int EXECUTE x=EXECUTE x=商品商品_pro2 _pro2 山东科技山东科技 IF IF x1 x1 PRINT PRINT 所所查查找找厂厂家家不不存存在在或或者者没没有有进进货货或或者者产产品品没没有有销销售！售！虽虽然然供供货货商商表表有有“山山东东科科技技”的的供供货货厂厂家家，但但是是该该厂厂家家即即没没有有进进货货也也没没有有销销售售，所所以以运运行行结结果果

26、也也会会显显示示：“所所查查找找厂厂家家不不存存在在或或者者没没有有进货或者产品没有销售！进货或者产品没有销售！”【实例练习【实例练习3】建建立立有有一一个个输输入入参参数数并并返返回回两两个个输输出出参参数数的的存存储储过过程程“商商品品_pro3_pro3”，根根据据“商商品品一一览览表表”统统计计公公司司某某一一类类商商品品的的库库存总数量和成本。存总数量和成本。IF exists(SELECT name FROM sysobjects IF exists(SELECT name FROM sysobjects WHERE name=WHERE name=商品商品_pro3 AND ty

27、pe=p)_pro3 AND type=p)DROP procedure DROP procedure 商品商品_pro3_pro3 GO GO CREATE procedure CREATE procedure 商品商品_pro3 hm nvarchar(8),_pro3 hm nvarchar(8),kuzs bigint output,cb money output kuzs bigint output,cb money output AS AS SELECT kuzs=sum(SELECT kuzs=sum(库存量库存量),cb=sum(),cb=sum(平均进价平均进价*库存量库存量

28、)FROM FROM 商品一览表商品一览表 WHERE WHERE 货名货名=hm=hm GO GO【实例练习【实例练习3】也也可可以以使使用用likelike按按货货号号第第一一位位数数字字统统计计某某一一类类商商品品。如如果果按按货货号号统统计计某某一一种种商商品品则则不不需需要要使使用用集集合合函函数数。存存储储过过程程“商品商品_pro3_pro3”的调用：的调用：DECLARE aa nvarchar(8),bb bigint,cc money DECLARE aa nvarchar(8),bb bigint,cc money SET aa=SET aa=计算机计算机 EXECUTE

29、 EXECUTE 商品商品_pro3 aa,bb output,cc output_pro3 aa,bb output,cc output SELECT SELECT 货品名称：货品名称：+aa,+aa,总库存数量：总库存数量：+cast(bb AS char(4),+cast(bb AS char(4),总成本：总成本：+cast(cc AS varchar(12)+cast(cc AS varchar(12)运运行行结结果果如如图图8-58-5所所示示。请请读读者者为为查查询询结结果果添添加加列列标标题题别别名再运行该代码。名再运行该代码。1.41.4、用、用EXECUTEEXECUTE执

30、行执行SQLSQL语句字符串语句字符串 EXECUTEEXECUTE语句一般用于执行存储过程，如果把语句一般用于执行存储过程，如果把SQLSQL语句作语句作为字符串或者预先存放在字符串变量中时，也可以使用它为字符串或者预先存放在字符串变量中时，也可以使用它来执行字符串中的来执行字符串中的SQLSQL语句。语句。语法格式：语法格式：EXECEXECUTE(UTE(字符串变量字符串变量|NSQLNSQL语句字符串语句字符串,n),n)说明：说明：l l 字字符符串串变变量量：存存放放SQLSQL语语句句的的变变量量，可可以以是是charchar、varcharvarchar、ncharnchar、

31、nvarcharnvarchar类类型型，最最大大长长度度可可以以是是服服务务器器可用内存的大小。可用内存的大小。l l NSQLNSQL语语句句字字符符串串：使使用用N N则则字字符符串串被被解解释释为为nvarcharnvarchar类型，否则认为是类型，否则认为是varcharvarchar类型。类型。l l可可以以有有多多个个字字符符串串或或字字符符串串变变量量，必必须须用用逗逗号号隔隔开开，必必须全部放在圆括号中。须全部放在圆括号中。1.41.4、用、用EXECUTEEXECUTE执行执行SQLSQL语句字符串语句字符串 【例【例8-28-2】用】用EXECUTEEXECUTE语句执

32、行字符串中的语句执行字符串中的SQLSQL语句语句 EXECUTE EXECUTE(NSELECT NSELECT 姓姓名名,性性别别,部部门门 FROM FROM 员员工工表表 )或者：或者：EXECUTE(SELECT EXECUTE(SELECT 姓名姓名,性别性别,部门部门 FROM FROM 员工表员工表)该该语语句句即即相相当当于于执执行行语语句句：SELECT SELECT 姓姓名名 性性别别 部部门门 FROM FROM 员工表员工表 或者使用字符串变量：或者使用字符串变量：DECLARE sql varchar(36)DECLARE sql varchar(36)SET sq

33、l=SELECT SET sql=SELECT 姓名姓名,性别性别,部门部门 FROM FROM 员工表员工表 EXECUTE(sql)EXECUTE(sql)运行结果如图运行结果如图8-68-6所示。所示。1.51.5、用企业管理器创建存储过程、用企业管理器创建存储过程 在企业管理器进行存储过程的创建，步骤如下：在企业管理器进行存储过程的创建，步骤如下：（1 1）在在控控制制台台根根目目录录中中展展开开要要建建立立存存储储过过程程的的数数据据库库，选选择择存存储储过过程程节节点点，则则右右边边窗窗口口会会列列出出数数据据库库中中目目前前所所有有的的存存储储过过程程，右右键键单单击击存存储储过

34、过程程节节点点，或或单单击击“操操作作菜菜单单”选选择择“新新建建存存储储过过程程”命命令令，系系统统弹弹出出“存存储储过过程属性程属性”对话框。对话框。1.51.5、用企业管理器创建存储过程、用企业管理器创建存储过程（2 2）在）在“文本文本”文本框中输入正确的文本框中输入正确的SQLSQL语句。语句。（3 3）可以单击）可以单击“语法检查语法检查”按钮，由系统检查语法错误。按钮，由系统检查语法错误。（4 4）单击）单击“确定确定”按钮完成存储过程的创建。按钮完成存储过程的创建。一一般般存存储储过过程程可可以以使使用用SQLSQL语语句句创创建建，修修改改则则使使用用企企业业管理器比较方便。

35、管理器比较方便。2存储过程的查看、编辑和删除存储过程的查看、编辑和删除 2.12.1、用企业管理器查看编辑存储过程、用企业管理器查看编辑存储过程1、在企业管理器中查看、修改存储过程的定义、在企业管理器中查看、修改存储过程的定义（1）在企业管理器根目录中展开存储过程所在数据库，）在企业管理器根目录中展开存储过程所在数据库，选中存储过程节点，右键单击要查看的存储过程，选择选中存储过程节点，右键单击要查看的存储过程，选择“属性属性”，弹出，弹出“存储过程属性存储过程属性”对话框，如图对话框，如图8-8所示。所示。2.12.1、用企业管理器查看编辑存储过程、用企业管理器查看编辑存储过程（2 2）用用户

36、户可可以以在在“文文本本”框框中中查查看看、修修改改存存储储过过程程的的SQLSQL语语句句（若若用用SQLSQL语语句句创创建建时时使使用用了了WITH WITH ENCRYPTIONENCRYPTION则则在在企企业管理器看见的是乱码）。业管理器看见的是乱码）。（3 3）编编辑辑修修改改之之后后，可可单单击击“检检查查语语法法”以以查查看看检检查查语语法法的正确性。的正确性。（4 4）单单击击“权权限限”打打开开“对对象象属属性性”对对话话框框，可可以以指指定定查查看看修修改改该该存存储储过过程程的的权权限限，设设置置修修改改完完毕毕，单单击击“应应用用”“确确定定”。（5 5）单击）单击

37、“确定确定”保存并关闭保存并关闭“存储过程属性存储过程属性”对话框。对话框。注注意意：只只能能对对存存储储过过程程的的SQLSQL语语句句及及参参数数进进行行修修改改，不不能能修修改改存存储储过过程程的的名名字字，可可以以另另外外单单独独对对存存储储过过程程“重重命命名名”。2、在企业管理器中查看存储过程的相关属性、在企业管理器中查看存储过程的相关属性（1 1）右右键键单单击击要要查查看看的的存存储储过过程程，选选择择“所所有有任任务务”中中的的“显显示示相相关关性性”命命令令，弹弹出出如如图图8-98-9所所示示的的“相相关关性性”对对话话框框，可可查查看看依依赖赖于于该该存存储储过过程程的

38、的其其他他对对象象和和该该存存储储过程所依赖的对象。过程所依赖的对象。（2 2）单击）单击“关闭关闭”关闭对话框。关闭对话框。3、在企业管理器对存储过程重新命名、在企业管理器对存储过程重新命名 右右键键单单击击要要查查看看的的存存储储过过程程，选选择择“重重命命名名”命命令令，或或者者选选择择“操操作作”菜菜单单中中的的“重重命命名名”命命令令，直直接接输输入入存储过程的新名字，打回车即可。存储过程的新名字，打回车即可。4、在企业管理器中删除存储过程、在企业管理器中删除存储过程 右右键键单单击击要要删删除除的的存存储储过过程程，选选择择“删删除除”命命令令，或或按下按下DELETEDELETE

39、键，弹出键，弹出“除去对象除去对象”对话框：对话框：单单击击“显显示示相相关关性性”按按钮钮，可可在在删删除除前前查查看看该该存存储储过过程与其他对象的关系、删除后对其他对象的影响。程与其他对象的关系、删除后对其他对象的影响。单击单击“全部除去全部除去”按钮，自动关闭对话框，删除完成。按钮，自动关闭对话框，删除完成。注注意意：如如果果存存储储过过程程已已经经分分组组，则则无无法法删删除除组组内内的的单单个个存存储储过过程程，删删除除其其中中一一个个会会将将同同组组的的全全部部存存储储过过程程一一同删除。同删除。2.22.2、用、用EXECUTEEXECUTE查看存储过程的定义与相查看存储过程的

40、定义与相关性关性1、用系统存储过程、用系统存储过程sp_helptext查看存储过程的定义查看存储过程的定义 语法格式：语法格式：EXECEXECUTE sp_helptext UTE sp_helptext 存储过程名存储过程名 注注意意：如如果果在在创创建建存存储储过过程程时时，使使用用了了 WITH WITH ENCRYPTION ENCRYPTION 参参数数，则则使使用用sp_helptextsp_helptext将将无无法法看看到到有有关关存储过程的信息。存储过程的信息。【例例8-38-3】在在查查询询分分析析器器输输入入代代码码：sp_helptext sp_helptext 商

41、商品品_pro2_pro2 运行结果如图运行结果如图8-108-10所示。所示。2 2、查看存储过程的参数及一般信息、查看存储过程的参数及一般信息 语法格式：语法格式：EXECUTE sp_help EXECUTE sp_help 存储过程名存储过程名3 3、查看存储过程的相关性、查看存储过程的相关性 语法格式：语法格式：EXECUTE sp_depends EXECUTE sp_depends 存储过程名存储过程名2.32.3、用、用SQLSQL语句修改、删除存储过程语句修改、删除存储过程1、用、用ALTERPROCEDURE语句修改存储过程语句修改存储过程 语法格式：语法格式：ALTER

42、ALTER PROCPROCEDURE EDURE 存储过程名存储过程名 WITH RECOMPILE WITH RECOMPILEENCRYPTIONENCRYPTION RECOMPILE,ENCRYPTION RECOMPILE,ENCRYPTION AS AS SQL SQL语句语句 在在企企业业管管理理器器中中修修改改存存储储过过程程可可以以参参照照原原来来SQLSQL语语句句直直接接进进行行修修改改，所所以以修修改改存存储储过过程程在在企企业业管管理理器器中中更更加加方便。方便。2、重命名存储过程、重命名存储过程 语法格式：语法格式：EXECUTE sp_rename EXECUT

43、E sp_rename 存储过程原名，存储过程新名存储过程原名，存储过程新名 注注意意：更更改改存存储储过过程程名名称称后后，必必须须对对应应用用程程序序中中调调用用该该存存储储过过程程的的SQLSQL语语句句作作相相应应的的修修改改，否否则则会会使使应应用用程程序序或依附该存储过程的对象找不到存储过程而产生错误。或依附该存储过程的对象找不到存储过程而产生错误。3、删除存储过程、删除存储过程 语法格式：语法格式：DROP DROP PROCPROCEDURE EDURE 存储过程名存储过程名,n,n 说明：说明：l lDROP procedureDROP procedure语句可一次删除多个存

44、储过程。语句可一次删除多个存储过程。l l如如果果存存储储过过程程已已经经分分组组，则则无无法法删删除除组组内内的的单单个个存存储储过程，删除其中一个会将同组的全部存储过程一同删除。过程，删除其中一个会将同组的全部存储过程一同删除。3触发器的创建与使用触发器的创建与使用3.13.1、触发器的概念、触发器的概念1、触发器、触发器 触触发发器器是是一一段段能能自自动动执执行行的的程程序序，是是一一种种特特殊殊的的存存储储过过程程，其其特殊性在于：特殊性在于：l l不允许使用参数，没有返回值。不允许使用参数，没有返回值。l l不不允允许许用用户户调调用用，当当对对表表进进行行插插入入、删删除除、修修

45、改改操操作作时时由由系系统统自动调用并执行（相当于事件方法）。自动调用并执行（相当于事件方法）。触发器可以实现比较复杂的完整性约束：触发器可以实现比较复杂的完整性约束：l l扩展约束、默认值和规则对象的完整性检查。扩展约束、默认值和规则对象的完整性检查。l l自动生成数据。自动生成数据。l l检检查查数数据据的的修修改改，防防止止对对数数据据不不正正确确的的修修改改，保保证证数数据据表表之之间间数据的正确性和一致性。数据的正确性和一致性。l l自定义复杂的安全权限。自定义复杂的安全权限。触触发发器器作作为为一一种种数数据据库库对对象象，在在syscommentsyscomment系系统统表表中

46、中存存储储其其完完整的定义信息，在整的定义信息，在sysobjectsysobject系统表中有该对象的记录。系统表中有该对象的记录。3.13.1、触发器的概念、触发器的概念2、触发器的用途及优点、触发器的用途及优点l l实实现现数数据据库库中中多多个个表表的的级级联联修修改改：当当修修改改删删除除某某张张表表的的数数据据时时，其其他他表表的的相相应应数数据据能能自自动动修修改改或或删删除除，以以保保证证数数据据的的一一致致性性（也也可可在在设设置置外外键键约约束束时时设设置置相相应应的的选选项，而且效率更高）。项，而且效率更高）。l l检检查查数数据据输输入入的的正正确确性性：CHECKCH

47、ECK约约束束在在限限制制数数据据输输入入时时不不能能参参照照其其他他表表中中的的数数据据。如如销销售售金金额额=数数量量*单单价价的的自自动动计计算算、销销售售数数量量不不允允许许超超过过库库存存量量等等，用用CHECKCHECK约约束束是是无无法法实实现现的的，用用触触发发器器即即可可实实现现比比CHECKCHECK更更复复杂杂的的约束检查。约束检查。l l检检查查数数据据修修改改的的正正确确性性：综综合合以以上上两两种种情情况况，当当对对表表中中受受触触发发器器保保护护的的数数据据修修改改时时，触触发发器器不不但但会会自自动动更更新新其其他他表表与与其其相相关关的的数数据据，还还可可以以

48、自自动动检检查查这这些些数数据据，只只要有一个不符合条件，则修改数据失败。要有一个不符合条件，则修改数据失败。3、触发器的触发方式、触发器的触发方式 为为数数据据表表中中某某个个字字段段设设置置触触发发器器后后，当当该该字字段段的的数数据据被被INSERTINSERT插插入入、DELETEDELETE删删除除、UPDATEUPDATE修修改改更更新新时时，触触发发器便被激活并自动执行。器便被激活并自动执行。SQL SQL ServerServer按按触触发发器器被被激激活活的的时时机机可可分分为为“后后触触发发”和和“替代触发替代触发”两种触发方式。两种触发方式。（1 1）后触发）后触发 若若

49、引引发发触触发发器器执执行行的的语语句句通通过过了了各各种种约约束束检检查查，成成功功执执行行后后才才激激活活并并执执行行触触发发器器程程序序，这这种种触触发发方方式式称称为为“后触发后触发”。后触发的特点：。后触发的特点：l l若若引引发发触触发发器器执执行行的的语语句句违违反反了了某某种种约约束束，该该语语句句不不会执行，则后触发方式的触发器也不被激活。会执行，则后触发方式的触发器也不被激活。l l后触发方式只能创建在数据表上，不能创建在视图上。后触发方式只能创建在数据表上，不能创建在视图上。l l一个表可以有多个后触发触发器。一个表可以有多个后触发触发器。3、触发器的触发方式、触发器的触

50、发方式（2 2）替代触发）替代触发 若若激激活活触触发发器器的的语语句句仅仅仅仅起起到到激激活活触触发发器器的的作作用用，一一旦旦激激活活触触发发器器后后该该语语句句即即停停止止执执行行，立立即即转转去去执执行行触触发发器器的的程程序序激激活活触触发发器器的的语语句句并并不不被被执执行行，相相当当于于禁禁止止某种操作。这种触发方式称为某种操作。这种触发方式称为“替代触发替代触发”。替代触发的特点：替代触发的特点：l l替代触发可以创建在表上，也可以创建在视图上。替代触发可以创建在表上，也可以创建在视图上。l l一个表只能有一个替代触发的触发器。一个表只能有一个替代触发的触发器。4 4、触发器使

51、用的、触发器使用的insertedinserted临时表和临时表和deleteddeleted临临时表时表l l不不论论后后触触发发或或替替代代触触发发，每每个个触触发发器器被被激激活活时时，系系统统都自动为它们创建两个临时表：都自动为它们创建两个临时表：insertedinserted和和deleteddeleted表。表。l l两个表的结构与激活触发器的原数据表结构相同。两个表的结构与激活触发器的原数据表结构相同。l l用用INSERTINSERT语语句句插插入入记记录录激激活活触触发发器器时时，系系统统在在原原表表插插入记录的同时，也自动把记录插入到入记录的同时，也自动把记录插入到ins

52、ertedinserted临时表。临时表。l l用用SELECTSELECT语语句句删删除除记记录录激激活活触触发发器器时时，系系统统在在原原表表删删除记录的同时，会把删除的记录添加到除记录的同时，会把删除的记录添加到deleteddeleted临时表。临时表。l l用用UPDATEUPDATE语语句句修修改改数数据据激激活活触触发发器器时时，系系统统先先在在原原表表删删除除原原有有记记录录，删删除除的的记记录录被被添添加加到到deleteddeleted临临时时表表，然后再插入新记录，并同时插入到然后再插入新记录，并同时插入到insertedinserted临时表。临时表。l l用用户户可可

53、以以用用SELECTSELECT语语句句查查询询这这两两个个临临时时表表，但但不不允允许许进行修改。进行修改。l l触发器一旦执行完成，这两个表将被自动删除。触发器一旦执行完成，这两个表将被自动删除。3.23.2、用、用CREATE TRIGGERCREATE TRIGGER语句创建触发器语句创建触发器 语法格式：语法格式：CREATE TRIGGER CREATE TRIGGER 拥有者拥有者.触发器名触发器名 ON ON 拥有者拥有者.表名表名|视图名视图名 for for|afterafter|instead ofinsert,update,deleteinstead ofinsert,

54、update,delete WITH encryption NOT FOR replication WITH encryption NOT FOR replication AS AS SET NOCOUNT -SET NOCOUNT -不返回给变量赋值的结果不返回给变量赋值的结果 SQL SQL语句系列语句系列 ROLLBACK TRANSACTION -ROLLBACK TRANSACTION -事务回滚事务回滚l lON ON 表名表名|视图名视图名：指定激活触发器被操作的表或视图。：指定激活触发器被操作的表或视图。l lforfor与与 afterafter：指指定定所所创创建建的的触触

55、发发器器为为后后触触发发方方式式，forfor与与 afterafter完全相同，完全相同，forfor是为了与以前老版本兼容而保留。是为了与以前老版本兼容而保留。l linstead ofinstead of：指定所创建的触发器为替代触发方式。：指定所创建的触发器为替代触发方式。l linsert,update,deleteinsert,update,delete：指指定定激激活活该该触触发发器器的的具具体体操操作作，可以指定一项，也可三项同时指定，但必须以逗号隔开。可以指定一项，也可三项同时指定，但必须以逗号隔开。3.23.2、用、用CREATE TRIGGERCREATE TRIGGER

56、语句创建触发器语句创建触发器 l lWITH encryptionWITH encryption：指定对触发器文本加密，禁止查阅修改。：指定对触发器文本加密，禁止查阅修改。l lNOT FOR replicationNOT FOR replication：指定在复制过程中不激活触发器操作。：指定在复制过程中不激活触发器操作。l lSET SET NOCOUNTNOCOUNT：触触发发器器一一般般不不能能有有返返回回值值，所所以以也也不不应应有有SELECTSELECT语语句句进进行行查查询询或或给给变变量量赋赋值值（获获得得被被操操作作数数据据的的语语句句除除外外），如如果果必必须须使用变量赋

57、值语句，可在开头使用该语句避免返回结果。使用变量赋值语句，可在开头使用该语句避免返回结果。l lSQLSQL语语句句系系列列：即即触触发发器器被被指指定定操操作作激激活活后后要要执执行行的的SQLSQL代代码码，其其中中可包含获得被操作数据的可包含获得被操作数据的SELECTSELECT语句：语句：n n后触发方式被操作数据一定在后触发方式被操作数据一定在insertedinserted或或deleteddeleted临时表中。临时表中。n n如果被操作的数据是多值的，可用如果被操作的数据是多值的，可用ININ判断是否被包含在其中：判断是否被包含在其中：被操作数据被操作数据 IN(SELECT

58、 IN(SELECT 被操作字段被操作字段 FROM FROM 临时表临时表)n n如果被操作的数据是单值的，可用以下语句获得：如果被操作的数据是单值的，可用以下语句获得：SELECT SELECT 变量变量=被操作字段被操作字段 FROM FROM 临时表临时表l lROLLBACK ROLLBACK TRANSACTIONTRANSACTION：事事务务回回滚滚语语句句。对对于于后后触触发发方方式式，语语句句已已经经执执行行完完毕毕才才执执行行触触发发器器，如如果果发发现现操操作作不不符符合合规规则则，可可用用该该语语句句取取消操作。消操作。3.23.2、用、用CREATE TRIGGER

59、CREATE TRIGGER语句创建触发器语句创建触发器 注意：注意：uuCREATE TRIGGERCREATE TRIGGER语句必须是一个批处理的第一条语句。语句必须是一个批处理的第一条语句。uu创建触发器的权限默认属于表的所有者，且不能授权给其他人。创建触发器的权限默认属于表的所有者，且不能授权给其他人。uu触触发发器器不不能能在在临临时时表表或或系系统统表表上上创创建建，后后触触发发也也不不能能创创建建在在视视图图上。上。uu一一个个触触发发器器只只能能创创建建在在一一个个表表上上；一一个个表表可可以以有有一一个个替替代代触触发发器器和多个后触发器（可以是同一种操作类型，可同时触发）

60、。和多个后触发器（可以是同一种操作类型，可同时触发）。uu由由于于TRUNCATE TRUNCATE TABLETABLE语语句句删删除除记记录录时时不不被被记记入入事事务务日日志志，所所以以该语句不能激活该语句不能激活deletedelete删除操作的触发器。删除操作的触发器。uu如如果果外外键键所所引引用用的的父父表表已已经经创创建建了了对对子子表表级级联联修修改改或或删删除除的的触触发发器，则子表不允许创建具有相同动作的替代触发器。器，则子表不允许创建具有相同动作的替代触发器。uu触触发发器器的的定定义义语语句句中中不不能能有有任任何何用用CREATECREATE创创建建、用用ALTER

61、ALTER修修改改数数据据库库或或各各种种对对象象的的语语句句，不不允允许许使使用用任任何何DROPDROP删删除除语语句句。也也不不允允许许使用以下语句：使用以下语句：GRANT/RESTORE DATABASE/RESTORE LOG REVOKE GRANT/RESTORE DATABASE/RESTORE LOG REVOKETRUNCATE TABLETRUNCATE TABLE3.23.2、用、用CREATE TRIGGERCREATE TRIGGER语句创建触发器语句创建触发器 【例例8-48-4】假假设设diannaoxsdiannaoxs数数据据库库有有一一个个“商商品品表表

62、”和和“销销售售合合同同表表20062006”我我们们为为“商商品品表表”创创建建一一个个名名为为“删删除除商商品品”的的触触发发器器，当当删删除除“商商品品表表”中中的的某某个个商商品品时时，需需要要把把这这些些商商品品在在“销销售售合合同同表表20062006”中中的的销销售售合合同同同同时时全全部部删除，实现删除，实现“商品表商品表”和和“销售合同表销售合同表20062006”的级联删除。的级联删除。CREATE TRIGGER CREATE TRIGGER 删除商品删除商品 ON ON 商品表商品表 after delete after delete AS AS DELETE DELE

63、TE 销售合同表销售合同表2006 WHERE 2006 WHERE 货号货号 in(SELECT in(SELECT 货号货号 FROM deleted)FROM deleted)该该语语句句为为“商商品品表表”创创建建了了一一个个由由删删除除动动作作激激活活的的“删删除除商商品品”触触发发器器，当当“商商品品表表”中中有有记记录录被被删删除除之之后后（deleteddeleted表表中中有有被被删删除除的的记记录录），该该触触发发器器即即会会自自动动执执行。行。3.23.2、用、用CREATE TRIGGERCREATE TRIGGER语句创建触发器语句创建触发器 “删除商品删除商品”触发

64、器的执行过程：触发器的执行过程：（1 1）创创建建insertedinserted和和deleteddeleted临临时时表表，“商商品品表表”被被删删除除的的记记录录存存放在放在deleteddeleted表中。表中。（2 2）从）从deleteddeleted临时表中查询并得到被删除记录的临时表中查询并得到被删除记录的“货号货号”。（3 3）将将“销销售售合合同同表表20062006”中中所所有有“货货号号”与与被被删删除除“货货号号”相相等等的记录删除。的记录删除。创建触发器之后，如果对数据库有以下操作语句：创建触发器之后，如果对数据库有以下操作语句：DELETE DELETE 商品表商

65、品表 WHERE WHERE 货号货号=1005 -=1005 -删除删除10051005号商品记录号商品记录 IF not exists(SELECT *FROM IF not exists(SELECT *FROM 销售合同表销售合同表2006 2006 WHERE WHERE 货号货号=1005)=1005)PRINT PRINT 相关记录已经从相关记录已经从“销售合同表销售合同表20062006”中删除了！中删除了！当当第第一一条条语语句句将将“商商品品表表”中中第第10051005号号商商品品的的记记录录成成功功删删除除后后，触触发发器器被被激激活活删删除除“销销售售合合同同表表20

66、062006”中中有有关关10051005号号商商品品的的记记录录。IFIF语句找不到语句找不到10051005号商品的记录则会显示信息：号商品的记录则会显示信息：相关记录已经从相关记录已经从“销售合同表销售合同表20062006”中删除了！中删除了！3.23.2、用、用CREATE TRIGGERCREATE TRIGGER语句创建触发器语句创建触发器 【例例8-58-5】为为“销销售售合合同同表表20062006”创创建建一一个个名名字字为为“统统计计被被修修改改记记录录数数”的的后后触触发发器器，当当对对“销销售售合合同同表表20062006”的的数据进行更新时，可以自动统计并显示修改的总行数。数据进行更新时，可以自动统计并显示修改的总行数。创创建建触触发发器器之之前前，可可用用SQLSQL语语句句先先在在sysobjectssysobjects系系统统表表中中检检测测是是否否存存在在名名字字为为“统统计计被被修修改改记记录录数数”、类类型型为为“trtr”的的触触发发器器，如如果果存存在在就就把把它它删删除除，避避免免调调试试时时的的麻麻烦。烦。USE diannaoxs US