存储过程，触发器的创建与使用

存储过程，触发器的创建与使用 8.2 存储过程的查看,编辑和删除 8.3 触发器的创建与使用 8.4 触发器的查看,编辑,重命名和删除 8.5 实训要求与习题 第8章 存储过程与触发器 学习目的与要求 存储过程与触发器是数据库的又一重要组成部分.存储过程可以把对数据库的复 杂操作封装为独立的程序模块,相当于其他编程语言的函数,过程,方法,具有"编写一次处处调用"的特点,便于程序的维护和减少网络通讯量.而触发器则是自动调用执行的程序模块,可以实现规则,默认值等约束对象不能完成的复杂约束功 能,可以检查数据输入与修改的正确性,保证数据库的数据完整性.通过本章学习,读者应学会如何创建,查看,编辑,删除存储过程和触发器,掌握存储过程和触发 器在数据库中的应用. 8.1 存储过程的创建与使用 8.1.1,存储过程的概念 1,存储过程 存储过程(stored procedure)是由一系列对数据库进行复杂操作的SQL语句,流程控制语句或函数组成的,并且将代码事先编译好之后,象规则,视图那样作为一个独立的数据库对象进行存储管理. 存储过程可作为一个单元被用户直接调用.相当于其他编程语言的函数,过程,方法. 2,存储过程的特点 存储过程具有参数传递,判断,声明变量,返回信息并扩充标准SQL语言的功能,其特点是: l 存储过程可以接收参数,并可以返回多个参数值,也可以返回存储过程的执行 状态值以反映存储过程的执行情况. l存储过程可以包含存储过程(嵌套),可以在数据库查询,修改语句中调用存储 过程,也可在存储过程中调用存储过程. 8.1.1,存储过程的概念 3,使用存储过程的优点 l 执行速度快:存储过程在创建时已经通过语法检查和编译,调用时则直接执行,程序的运行效率高,其执行速度要比标准SQL语句快得多.含有大量SQL语句的批处理需要重复多次执行时,定义为存储过程可大大提高运行效率. l 有利于模块化程序 设计 领导形象设计圆作业设计ao工艺污水处理厂设计附属工程施工组织设计清扫机器人结构设计 :存储过程创建后,即可以无数次随时任意繁荣调用.可根据不同的功能模式设计不同的存储过程以供调用. l 便于程序的维护管理:当用户对数据库使用的功能改变时,只需对相应的存储 过程进行修改而不用修改应用程序. l 减少网络通信量:存储过程可包含大量对数据库进行复杂操作的SQL语句,它的存储执行都在SQL Server服务器(数据库)端,网络用户使用时只需发送一个调 用语句就可以实现,大大减少了网络上SQL语句的传输. l 保证系统的安全性:可以在存储过程中设置用户对数据的访问权限,只允许用户调用存储过程而不允许直接对数据进行访问,充分发挥安全机制的作用. 8.1.1,存储过程的概念 4,存储过程的缺点 l_不能实现复杂的逻辑操作:这是因为SQL语言本身就不支持复杂的程序设计结 构,所以各种程序设计语言都有自己对数据库进行操作处理的功能. l 用存储过程实现数据库的全部功能比较困难:不同用户的需求不同,当涉及特殊管理要求时,很难全面满足要求,若将所有需求都定义为存储过程,其数量将相当可观,记忆和掌握这些存储过程是很困难. 存储过程分为系统存储过程和用户自定义存储过程.系统存储过程可直接使用,如定义和绑定规则对象,默认值对象等.用户自定义存储过程必须先定义后使用. 8.1.2,用CREATE PROC创建存储过程 创建存储过程应遵守的规则: l 名称标识符的长度最大为128个字符,且必须惟一. l 每个存储过程最多可以使用1,024个参数. l 存储过程的最大容量有一定的限制. l 存储过程支持多达32层嵌套. l 在对存储过程命名时最好和系统存储过程名区分. CREATE PROCEDURE 存储过程名 [ ; 整数] [@形参变量 数据类型[varying][=默认值] [output]] [,…n] [WITH recompile|encryption| {recompile,encryption}] [FOR replication ] AS SQL 语句系列 8.1.2,用CREATE PROC创建存储过程 说明: l_该语句可以创建永久存储过程,也可以创建一个在一个会话中临时使用的局部 存储过程(名称前加一个#),还可以创建一个在所有会话中临时使用的全局存储 过程(名称前加两个##). l_整数:可作为同名过程分组的后缀序号(如OP1,OP2可定义属于一组),同组的过程将来可以用一条DROP PROCEDURE删除命令全部删除掉. l_@形参变量:指定接收调用参数或返回值的变量,默认状态下只 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf 示单一数值,不能代表表名,列名或其他对象名,形参变量的作用域为该存储过程;所有数据类型(next,image)都可以作为过程的参数类型. n_Varying:仅适用于游标参数,指定形参变量可作为支持结果集的返回参数. n_默认值:调用过程语句时不提供参数时,形参变量则取该默认值.默认值只能是常量或NULL. n_Output:指定形参变量是返回给调用语句的参数,可以是所有数据类型,也可以是游标占位符. 8.1.2,用CREATE PROC创建存储过程 l RECOMPILE:执行完存储过程后不保留存储过程的备份,每次执行时都需要对存储过程重新编译. l ENCRYPTION:存储过程作为数据库对象在系统的syscomments表中留下完整的代码信息,并对访问这些数据的入口进行加密. 注意:在数据库内创建的每个对象(约束,默认值,日志,规则,存储过程等)都会作 为该库系统表Sysobjects中的一条记录占一行,该表的结构如表8-1. 表8-1 Sysobjects系统表的结构 列名 数据类型 存储内容说明 name sysname 对象名 id int 对象标识号 _ _ xtype或type _ _ _ char(2) _ 对象类型.其中的主要类型: C = CHECK 约束 P = 存储过程 D = 默认值或 DEFAULT 约束 TR = 触发器 F = FOREIGN KEY 约束 V = 视图 K = PRIMARY KEY 或 UNIQUE 约束 R = 规则 FN = 标量函数(自定义函数) U = 用户表 L = 日志 S = 系统表 crdate datetime 对象的创建日期. 8.1.2,用CREATE PROC创建存储过程 先判断sysobjects系统表中是否存在名为"_pro"的存储过程,如果存在 则删除原有的存储过程,然后再创建. USE diannaoxs IF exists( SELECT name FROM sysobjects WHERE name='_pro' AND type='p') DROP procedure _pro GO CREATE procedure _pro -- 创建存储过程 AS SELECT 进货日期, 供货商, j.货号, 货名, 数量, 进价 FROM 进货表2006 AS j, 供货商表 AS g, 商品一览表 s WHERE j.供货商ID=g.供货商ID AND j.货号=s.货号 AND s.货名='' GO EXECUTE _pro -- 调用执行存储过程 其中"进价"可使用【实例练习7-7】创建的自定义函数"货币格式()" USE diannaoxs IF exists( SELECT name FROM sysobjects WHERE name='商品_pro1' AND type='p') DROP procedure 商品_pro1 GO CREATE procedure 商品_pro1 @商品名 nvarchar(8) AS SELECT 进货日期, 供货商, j.货号, 货名, 数量, 进价 FROM 进货表2006 j join 供货商表 g ON j.供货商ID=g.供货商ID join 商品一览表 s ON j.货号=s.货号 WHERE s.货名=@商品名 GO EXECUTE 商品_pro1 '' -- 运行结果与图8-1相同 再输入代码:EXEC 商品_pro1 '显示器' --结果如图8-2 8.1.3,用EXECUTE执行存储过程 语法格式: [EXECUTE]{[@整型变量=]{存储过程名[; 标识号 ] } { [ [@形参变量=] { 值|@变量 [output|default]}]} [,…n ] [ WITH recompile ] 简单格式: [EXECUTE] [@整型变量=] 存储过程名 { 值| @变量 [output|default ] } [ , …n ] 说明: l @整型变量:用于接收存储过程的返回状态值,必须是在此之前已经定义的,若 不需要返回状态则省略. l 标识号:指定同名存储过程分组的后缀序号,未分组则省略. 8.1.3,用EXECUTE执行存储过程 l @形参变量:指定创建存储过程时定义的@形参变量,如果使用该项参数则此处 的顺序与创建时的顺序可以不一致.省略"@形参变量"则此处提供的参数个数,用 途,顺序必须与定义时一致. n_@变量用于存放参数值或者接收存储过程的返回值. n Output:与定义时一致,指定该项为存储过程的返回值. n default:根据存储过程的定义,为参数提供默认值. l WITH RECOMPILE:强制编译新计划,建议尽量不用. 一般常用的简单格式: [EXEC] 存储过程名 {值|@变量[output|default]} [ ,…n ] 【实例练习8-2】 创建一个既有参数,又有返回状态值的存储过程"商品_pro2",在"销售表2006""进货表2006""供货商表""商品一览表"中查询某个供货厂家所提供产品的销售 信息,如果从指定厂家有进货而且有销售则返回数字1并显示销售信息,否则返回0. IF exists(SELECT name FROM sysobjects WHERE name='商品_pro2' AND type='p') DROP procedure 商品_pro2 GO CREATE procedure 商品_pro2 @厂家 nvarchar(15) AS IF exists(SELECT * FROM 销售表2006 x,进货表2006 j,供货商表 g,商品一览表 s WHERE j.供货商ID=g.供货商ID AND j.货号=s.货号 AND x.货号=j.货号 AND g.供货商 like @厂家+'%') BEGIN SELECT 销售日期, x.货号, x.货名, 供货商, x.数量 FROM 销售表2006 x,进货表2006 j,供货商表 g,商品一览表 s WHERE j.供货商ID=g.供货商ID AND j.货号=s.货号 AND x.货号=j.货号 AND g.供货商 like @厂家+'%' RETURN 1 END ELSE RETURN 0 GO 【实例练习8-2】 如果只想知道某个厂家是否有进货有销售,则第二个SELECT查询语句可以省略. 存储过程的调用如下: DECLARE @x int EXECUTE @x=商品_pro2 '上海电脑' IF @x=1 PRINT '所查找厂家的产品有进货也有销售.' ELSE PRINT '所查找厂家不存在或者没有进货或者产品没有销售!' 【实例练习8-2】 因为如果查到信息会显示出来,所以调用存储过程查询可以写为: DECLARE @x int EXECUTE @x=商品_pro2 '北京联想' IF @x=0 PRINT '所查找厂家不存在或者没有进货或者产品没有销售!' 【实例练习8-2】 若输入以下代码: DECLARE @x int EXECUTE @x=商品_pro2 '北京科技' IF @x=0 PRINT '所查找厂家不存在或者没有进货或者产品没有销售!' 因为没有"北京科技"的供货厂家,运行结果则会显示:"所查找厂家不存在或者没有进货或者产品没有销售!" 若输入以下代码: DECLARE @x int EXECUTE @x=商品_pro2 '山东科技' IF @x1 PRINT '所查找厂家不存在或者没有进货或者产品没有销售!' 虽然《供货商表》有"山东科技"的供货厂家,但是该厂家即没有进货也没有销售, 所以运行结果也会显示:"所查找厂家不存在或者没有进货或者产品没有销售!" 【实例练习8-3】 建立有一个输入参数并返回两个输出参数的存储过程"商品_pro3",根据"商品一览表"统计公司某一类商品的库存总数量和成本. IF exists(SELECT name FROM sysobjects WHERE name='商品_pro3' AND type='p') DROP procedure 商品_pro3 GO CREATE procedure 商品_pro3 @hm nvarchar(8), @kuzs bigint output, @cb money output AS SELECT @kuzs=sum(库存量),@cb=sum(平均进价*库存量) FROM 商品一览表 WHERE 货名=@hm GO 【实例练习8-3】 也可以使用like按货号第一位数字统计某一类商品.如果按货号统计某一种商品则不需要使用集合函数.存储过程"商品_pro3"的调用: DECLARE @aa nvarchar(8), @bb bigint, @cc money SET @aa='' EXECUTE 商品_pro3 @aa, @bb output, @cc output SELECT '货品名称:'+@aa, '总库存数量:' +cast( @bb AS char(4)),'总成本:' +cast( @cc AS varchar(12)) 运行结果如图8-5所示.请读者为查询结果添加列标题别名再运行该代码. 8.1.4,用EXECUTE执行SQL语句字符串 EXECUTE语句一般用于执行存储过程,如果把SQL语句作为字符串或者预先存放 在字符串变量中时,也可以使用它来执行字符串中的SQL语句. 语法格式: EXECUTE ({@字符串变量|[N]'SQL语句字符串'}[,…n]) 说明: l @字符串变量:存放SQL语句的变量,可以是char,varchar,nchar,nvarchar类型,最大长度可以是服务器可用内存的大小. l [N]'SQL语句字符串':使用N则字符串被解释为nvarchar类型,否则认为是varchar类型. l 可以有多个字符串或字符串变量,必须用逗号隔开,必须全部放在圆括号中. 8.1.4,用EXECUTE执行SQL语句字符串 【例8-2】用EXECUTE语句执行字符串中的SQL语句 EXECUTE ( N'SELECT 姓名, 性别, 部门 FROM 员工表' ) 或者: EXECUTE ( 'SELECT 姓名, 性别, 部门 FROM 员工表' ) 该语句即相当于执行语句:SELECT 姓名 性别 部门 FROM 员工表 或者使用字符串变量: DECLARE @sql varchar(36) SET @sql=' SELECT 姓名, 性别, 部门 FROM 员工表' EXECUTE (@sql) 运行结果如图8-6所示. 8.1.5,用企业管理器创建存储过程 在企业管理器进行存储过程的创建,步骤如下: (1)在控制台根目录中展开要建立存储过程的数据库,选择存储过程节点,则右边窗口会列出数据库中目前所有的存储过程,右键单击存储过程节点,或单击"操作菜单"选择"新建存储过程"命令,系统弹出"存储过程属性"对话框. 8.1.5,用企业管理器创建存储过程 (2)在"文本"文本框中输入正确的SQL语句. (3)可以单击"语法检查"按钮,由系统检查语法错误. (4)单击"确定"按钮完成存储过程的创建. 一般存储过程可以使用SQL语句创建,修改则使用企业管理器比较方便. 8.2 存储过程的查看,编辑和删除 8.2.1,用企业管理器查看编辑存储过程 1,在企业管理器中查看,修改存储过程的定义 (1)在企业管理器根目录中展开存储过程所在数据库,选中存储过程节点,右键单击要查看的存储过程,选择"属性",弹出"存储过程属性"对话框,如图8-8所示. 8.2.1,用企业管理器查看编辑存储过程 (2)用户可以在"文本"框中查看,修改存储过程的SQL语句(若用SQL语句创建时 使用了WITH ENCRYPTION则在企业管理器看见的是乱码). (3)编辑修改之后,可单击"检查语法"以查看检查语法的正确性. (4)单击"权限"打开"对象属性"对话框,可以指定查看修改该存储过程的权限,设 置修改完毕,单击"应用"?"确定". (5)单击"确定"保存并关闭"存储过程属性"对话框. 注意:只能对存储过程的SQL语句及参数进行修改,不能修改存储过程的名字,可 以另外单独对存储过程"重命名". 2,在企业管理器中查看存储过程的相关属性 (1)右键单击要查看的存储过程,选择"所有任务"中的"显示相关性"命令,弹出如图8-9所示的"相关性"对话框,可查看依赖于该存储过程的其他对象和该存储过 程所依赖的对象. (2)单击"关闭"关闭对话框. 3,在企业管理器对存储过程重新命名 右键单击要查看的存储过程,选择"重命名"命令,或者选择"操作"菜单中的"重命名"命令,直接输入存储过程的新名字,打回车即可. 4,在企业管理器中删除存储过程 右键单击要删除的存储过程,选择"删除"命令,或按下DELETE键,弹出"除去对象"对话框: 单击"显示相关性"按钮,可在删除前查看该存储过程与其他对象的关系,删除后对其他对象的影响. 单击"全部除去"按钮,自动关闭对话框,删除完成. 注意:如果存储过程已经分组,则无法删除组内的单个存储过程,删除其中一个会将同组的全部存储过程一同删除. 8.2.2,用EXECUTE查看存储过程的定义与相关性 1,用系统存储过程sp_helptext查看存储过程的定义 语法格式:[EXECUTE] sp_helptext 存储过程名 注意:如果在创建存储过程时,使用了 WITH ENCRYPTION 参数,则使用sp_helptext将无法看到有关存储过程的信息. 【例8-3】在查询分析器输入代码: sp_helptext 商品_pro2 运行结果如图8-10所示. 2,查看存储过程的参数及一般信息 语法格式:[EXECUTE] sp_help 存储过程名 3,查看存储过程的相关性 语法格式:[EXECUTE] sp_depends 存储过程名 8.2.3,用SQL语句修改,删除存储过程 1,用ALTER PROCEDURE语句修改存储过程 语法格式: ALTER PROCEDURE 存储过程名 [WITH RECOMPILE|ENCRYPTION |{ RECOMPILE, ENCRYPTION } ] AS SQL语句 在企业管理器中修改存储过程可以参照原来SQL语句直接进行修改,所以修改存储过程在企业管理器中更加方便. 2,重命名存储过程 语法格式: [EXECUTE] sp_rename 存储过程原名,存储过程新名 注意:更改存储过程名称后,必须对应用程序中调用该存储过程的SQL语句作相应的修改,否则会使应用程序或依附该存储过程的对象找不到存储过程而产生错 误. 3,删除存储过程 语法格式:DROP PROCEDURE 存储过程名[,…n] 说明: l DROP procedure语句可一次删除多个存储过程. l如果存储过程已经分组,则无法删除组内的单个存储过程,删除其中一个会将同组的全部存储过程一同删除. 8.3.1,触发器的概念 1,触发器 触发器是一段能自动执行的程序,是一种特殊的存储过程,其特殊性在于: l 不允许使用参数,没有返回值. l 不允许用户调用,当对表进行插入,删除,修改操作时由系统自动调用并执行 (相当于事件方法). 触发器可以实现比较复杂的完整性约束: l_ 扩展约束,默认值和规则对象的完整性检查. l 自动生成数据. l 检查数据的修改,防止对数据不正确的修改,保证数据表之间数据的正确性和 一致性. l 自定义复杂的安全权限. 触发器作为一种数据库对象,在syscomment系统表中存储其完整的定义信息,在sysobject系统表中有该对象的记录. 8.3.1,触发器的概念 2,触发器的用途及优点 l 实现数据库中多个表的级联修改:当修改删除某张表的数据时,其他表的相应数据能自动修改或删除,以保证数据的一致性(也可在设置外键约束时设置相应 的选项,而且效率更高). l_检查数据输入的正确性:CHECK约束在限制数据输入时不能参照其他表中的数 据.如销售金额=数量*单价的自动计算,销售数量不允许超过库存量等,用CHECK约束是无法实现的,用触发器即可实现比CHECK更复杂的约束检查. l 检查数据修改的正确性:综合以上两种情况,当对表中受触发器保护的数据修 改时,触发器不但会自动更新其他表与其相关的数据,还可以自动检查这些数据,只要有一个不符合条件,则修改数据失败. 3,触发器的触发方式 为数据表中某个字段设置触发器后,当该字段的数据被INSERT插入,DELETE删除,UPDATE修改更新时,触发器便被激活并自动执行. SQL Server按触发器被激活的时机可分为"后触发"和"替代触发"两种触发方式. (1)后触发 若引发触发器执行的语句通过了各种约束检查,成功执行后才激活并执行触发器 程序,这种触发方式称为"后触发".后触发的特点: l 若引发触发器执行的语句违反了某种约束,该语句不会执行,则后触发方式的触发器也不被激活. l 后触发方式只能创建在数据表上,不能创建在视图上. l_一个表可以有多个后触发触发器. 3,触发器的触发方式 (2)替代触发 若激活触发器的语句仅仅起到激活触发器的作用,一旦激活触发器后该语句即停 止执行,立即转去执行触发器的程序—激活触发器的语句并不被执行,相当于禁止某种操作.这种触发方式称为"替代触发". 替代触发的特点: l_替代触发可以创建在表上,也可以创建在视图上. l_一个表只能有一个替代触发的触发器. 4,触发器使用的inserted临时表和deleted临时表 l 不论后触发或替代触发,每个触发器被激活时,系统都自动为它们创建两个临 时表:inserted和deleted表. l 两个表的结构与激活触发器的原数据表结构相同. l 用INSERT语句插入记录激活触发器时,系统在原表插入记录的同时,也自动把记录插入到inserted临时表. l 用SELECT语句删除记录激活触发器时,系统在原表删除记录的同时,会把删除的记录添加到deleted临时表. l 用UPDATE语句修改数据激活触发器时,系统先在原表删除原有记录,删除的记录被添加到deleted临时表,然后再插入新记录,并同时插入到inserted临时表. l 用户可以用SELECT语句查询这两个临时表,但不允许进行修改. l 触发器一旦执行完成,这两个表将被自动删除. 8.3.2,用CREATE TRIGGER语句创建触发器 语法格式: CREATE TRIGGER [拥有者.]触发器名 ON [拥有者.]{ 表名|视图名 } {for|after|instead of}[insert,update,delete] [WITH encryption] [ NOT FOR replication ] AS [SET NOCOUNT] -- 不返回给变量赋值的结果 SQL语句系列 [ROLLBACK TRANSACTION] -- 事务回滚 l ON {表名|视图名}:指定激活触发器被操作的表或视图. l for与 after:指定所创建的触发器为后触发方式,for与 after完全相同,for是为了与以前老版本兼容而保留. l instead of:指定所创建的触发器为替代触发方式. l insert,update,delete:指定激活该触发器的具体操作,可以指定一项,也可三项同时指定,但必须以逗号隔开. 8.3.2,用CREATE TRIGGER语句创建触发器 l_WITH encryption:指定对触发器文本加密,禁止查阅修改. l_NOT FOR replication:指定在复制过程中不激活触发器操作. l SET NOCOUNT:触发器一般不能有返回值,所以也不应有SELECT语句进行查询或给变量赋值(获得被操作数据的语句除外),如果必须使用变量赋值语句,可在开头使用该语句避免返回结果. lSQL语句系列:即触发器被指定操作激活后要执行的SQL代码,其中可包含获得被操作数据的SELECT语句: n 后触发方式被操作数据一定在inserted或deleted临时表中. n 如果被操作的数据是多值的,可用IN判断是否被包含在其中: 被操作数据 IN (SELECT 被操作字段 FROM 临时表) n 如果被操作的数据是单值的,可用以下语句获得: SELECT @变量=被操作字段 FROM 临时表 l ROLLBACK TRANSACTION:事务回滚语句.对于后触发方式,语句已经执行完毕才执行触发器,如果发现操作不符合规则,可用该语句取消操作. 8.3.2,用CREATE TRIGGER语句创建触发器 注意: u CREATE TRIGGER语句必须是一个批处理的第一条语句. u 创建触发器的权限默认属于表的所有者,且不能授权给其他人. u 触发器不能在临时表或系统表上创建,后触发也不能创建在视图上. u 一个触发器只能创建在一个表上;一个表可以有一个替代触发器和多个后触发 器(可以是同一种操作类型,可同时触发). u 由于TRUNCATE TABLE语句删除记录时不被记入事务日志,所以该语句不能激活delete删除操作的触发器. u 如果外键所引用的父表已经创建了对子表级联修改或删除的触发器,则子表不允许创建具有相同动作的替代触发器. u 触发器的定义语句中不能有任何用CREATE创建,用ALTER修改数据库或各种对象的语句,不允许使用任何DROP删除语句.也不允许使用以下语句: GRANT / RESTORE DATABASE / RESTORE LOG REVOKE TRUNCATE TABLE 8.3.2,用CREATE TRIGGER语句创建触发器 【例8-4】假设diannaoxs数据库有一个"商品表"和"销售合同表2006"我们为"商品表"创建一个名为"删除商品"的触发器,当删除"商品表"中的某个商品时,需要把这些商品在"销售合同表2006"中的销售合同同时全部删除,实现"商品表"和"销售合同表2006"的级联删除. CREATE TRIGGER 删除商品 ON 商品表 after delete AS DELETE 销售合同表2006 WHERE 货号 in (SELECT 货号 FROM deleted ) 该语句为"商品表"创建了一个由删除动作激活的"删除商品"触发器,当"商品表"中有记录被删除之后(deleted表中有被删除的记录),该触发器即会自动执行. 8.3.2,用CREATE TRIGGER语句创建触发器 "删除商品"触发器的执行过程: (1)创建inserted和deleted临时表,"商品表"被删除的记录存放在deleted表中. (2)从deleted临时表中查询并得到被删除记录的"货号". (3)将"销售合同表2006"中所有"货号"与被删除"货号"相等的记录删除. 创建触发器之后,如果对数据库有以下操作语句: DELETE 商品表 WHERE 货号='1005' -- 删除1005号商品记录 IF not exists( SELECT * FROM 销售合同表2006 WHERE 货号='1005' ) PRINT '相关记录已经从"销售合同表2006"中删除了!' 当第一条语句将"商品表"中第1005号商品的记录成功删除后,触发器被激活删除"销售合同表2006"中有关1005号商品的记录.IF语句找不到1005号商品的记录则会显示信息: 相关记录已经从"销售合同表2006"中删除了! 8.3.2,用CREATE TRIGGER语句创建触发器 【例8-5】为"销售合同表2006"创建一个名字为"统计被修改记录数"的后触发器,当对"销售合同表2006"的数据进行更新时,可以自动统计并显示修改的总行数. 创建触发器之前,可用SQL语句先在sysobjects系统表中检测是否存在名字为"统计被修改记录数",类型为"tr"的触发器,如果存在就把它删除,避免调试时的麻烦. USE diannaoxs IF exists (SELECT name FROM sysobjects WHERE name='统计被修改记录数' AND type='tr') DROP TRIGGER 统计被修改记录数 GO 8.3.2,用CREATE TRIGGER语句创建触发器 创建触发器的语句如下: CREATE 统计被修改记录数 ON 销售合同表2006 after update AS DECLARE @msg varchar(100) SELECT @msg=str( @@rowcount )+'diannaoxs被修改描述' PRINT @msg RETURN GO 该例题为"销售合同表2006"创建了一个由update更新动作激活的后触发器"统计被修改记录数",当"销售合同表2006"中有记录被成功更新之后,该触发器即会自动执行: n 创建inserted和deleted临时表,"销售合同表2006"被删除的记录存入deleted表,新插入的记录存入inserted表. n 将保存有被修改记录个数的全局变量@@rowcount转化为字符串输出. 8.3.3,综合举例练习 【实例练习8-4】为"员工表"创建一个名为"修改姓名"的后触发器,当修改某个员工姓名时,需要把"销售表2006"的"销售员","进货表2006"的"收货人"同时进行全部修改,实现"员工表"和"销售表2006""进货表2006"的级联修改. USE diannaoxs IF exists ( SELECT name FROM sysobjects WHERE name='修改姓名' AND type='tr') DROP TRIGGER 修改姓名 GO CREATE TRIGGER 修改姓名 ON 员工表 after update AS DECLARE @xm1 varchar(8) , @xm2 varchar(8) SELECT @xm1=姓名 FROM deleted -- 从deleted表得到被删除的原姓名 SELECT @xm2=姓名 FROM inserted -- 从inserted表得到被更新的新姓名 UPDATE 销售表2006 SET 销售员=@xm2 WHERE 销售员=@xm1 UPDATE 进货表2006 SET 收货人=@xm2 WHERE 收货人=@xm1 GO 【实例练习8-4】 该例题为"员工表"创建了一个由更新动作激活的"修改姓名"后触发器,当"员工表"中有记录被成功更新之后,该触发器即会自动执行. 也可以不定义局部变量,在更新表达式及条件中直接使用临时表的字段,但直接在表中查询数据不如使用局部变量效率高,且不容易出错. 运行代码创建触发器成功后显示:命令已成功完成. 如果将公司员工"高宏"改名为"高立宏",则可对"员工表"进行修改: UPDATE 员工表 SET 姓名='高立宏' WHERE 姓名='高宏' SELECT * FROM 员工信息 如果查询一下"销售表2006",则所有原来的销售员"高宏"也全部被触发器自动更新为"高立宏"了. 【实例练习8-5】 为"进货表2006"创建一个名字为"进货检查"的后"触发器",当从厂家购进某种商品添加一条新记录时,能自动执行以下操作: (1)对《商品一览表》中"平均进价"字段进行自动更新: 平均进价=(进价*数量+平均进价*库存量)/(数量+库存量) 我们可用【实例练习7-6】中定义的自定义函数"平均价格()": 平均进价=平均价格(原平均进价, 库存,进价, 数量) (2)对《商品一览表》中"库存量"字段进行自动更新: 库存量=库存量+数量 注意顺序:因为计算平均进价要使用原来的库存量,所以必须先更新平均进价,再 更新库存(本题使用局部变量可以不考虑). USE diannaoxs IF exists (SELECT name FROM sysobjects WHERE name='进货检查'AND type='tr') DROP TRIGGER 进货检查 【实例练习8-5】 GO CREATE TRIGGER 进货检查 ON 进货表2006 after insert AS DECLARE @hh char(4), @sl int, @jj Smallmoney, @pj Smallmoney, @kc bigint SELECT @hh=货号, @sl=数量, @jj=进价 FROM inserted SELECT @pj=平均进价, @kc=库存量 FROM 商品一览表 WHERE 货号=@hh UPDATE 商品一览表 SET 平均进价=平均价格(@pj,@kc,@jj,@sl) WHERE 货号=@hh UPDATE 商品一览表 SET 库存量=@kc+@sl WHERE 货号=@hh GO 对字段更新的表达式中可以使用字段名,用局部变量效率更高. 运行代码成功创建触发器后显示:命令已成功完成. 假设公司刚从厂家代号为BJLX的"北京联想科技股份有限公司"按每台5180元的 价格购进"1001"号10台,由公司员工于丽负责验货入库. 我们必须在"进货表2006"中插入一条记录,其中:进货日期可使用当前日期的默认值,序号,厂家名称不需要输入,只输入供货商ID即可,实际上是按进货表全部字段输入. 【实例练习8-5】 注意:先请查询"商品一览表",并记住"商品一览表"中该商品平均进价(5275元), 和原有库存量(13台).由于对"进货表2006"的"收货人"设置了外键约束引用"员 工表"的"姓名",员工"于 丽"必须与"员工表"一致. 输入代码: INSERT 进货表2006 VALUES(default,'1001',10,5180.00,'BJLX','于 丽' ) SELECT * FROM 进货信息视图2006 运行结果如图8-12所示. 再打开"商品一览表"可以看到,1001商品的平均进价已自动修改(5233.7元),库 存量也已自动修改(23台). 【实例练习8-6】 为"销售表2006"创建一个名字为"销售检查"的后触发器,当销售某种商品添加 一条新记录时,能自动执行以下操作: n 自动检查销售"数量"不允许大于《商品一览表》中的"库存量". n自动检查"单价"下浮或上调不允许超出《商品一览表》公司所制定"参考价格" 的5%范围. n 根据"货号"自动从《商品一览表》中获得相应的"货名"数据. n 自动计算"金额=单价*数量". n 对《商品一览表》中"库存量"进行自动更新. USE diannaoxs IF exists (SELECT name FROM sysobjects WHERE name='销售检查'AND type='tr') DROP TRIGGER 销售检查 GO CREATE TRIGGER 销售检查 ON 销售表2006 after insert AS DECLARE @xh BigInt,@hh char(4),@sl int,@dj Smallmoney, @hm Nvarchar(8), @ckjg Smallmoney,@kc bigint SELECT @xh=序号,@hh=货号,@sl=数量,@dj=单价 FROM inserted SELECT @hm=货名, @ckjg=参考价格, @kc=库存量 FROM 商品一览表 WHERE 货号=@hh 【实例练习8-6】 IF @sl =@ckjg*0.95 AND @dj<=@ckjg*1.05 BEGIN UPDATE 销售表2006 -- 条件不能使用货号 SET 货名= @hm, 金额=@sl*@dj WHERE 序号=@xh UPDATE 商品一览表 SET 库存量=@kc-@sl WHERE 货号=@hh END ELSE BEGIN PRINT'单价超出参考价格' +cast(@ckjg AS varchar(10))+'的%5范围,不能销售' ROLLBACK TRANSACTION -- 事务回滚,撤消插入 END END ELSE BEGIN PRINT '销售量大于库存量'+cast(@kc AS varchar(4)) + ',库存不足不能销售' ROLLBACK TRANSACTION END GO 【实例练习8-6】 注意: u UPDATE更新"销售表2006"货名,金额时,不能使用"WHERE 货号=@hh",因为同一 货号的记录不止一条,必须使用关键字"序号". u 对字段更新的表达式可以使用字段名,但使用局部变量效率更高. u_因为是后触发,新记录已通过其他约束规则被插入到数据表中,所以当数量,价格不满足要求时必须使用ROLLBACK TRANSACTION事务回滚撤消. 运行代码创建触发器成功后显示:命令已成功完成. 假设客户"济南商业电脑商城"来购买"1002"号商品""2台,由章晓晓按单价5500元销售.可在"销售表2006"中插入一条记录. 注意:先请查询"商品一览表",记住公司制定的该商品参考价格(5600)和现有库存量(8台). INSERT 销售表2006(销售日期,客户名称,货号,数量,单价,销售员) VALUES(default,'济南商业电脑商城','1002',2,5500,'章晓晓') SELECT * FROM 销售信息视图2006 运行结果如图8-13所示. 打开"商品一览表"可看到1002商品库存量已经自动修改(6台). 【实例练习8-6】 如果将单价改为5000,再输入以下代码增加一条销售记录: INSERT 销售表2006(销售日期,客户名称,货号,数量,单价,销售员) VALUES(default,'济南商业电脑商城','1002',2,5000,'章晓晓') 运行结果显示:"价超出参考价格5600.00的%5范围,不能销售" 再查询"销售表2006"或"销售信息视图2006"可以看到没有增加记录,"商品一览表"中的库存量也没有变化. 如果将单价改为6000,输入以下代码增加一条销售记录: INSERT 销售表2006(销售日期,客户名称,货号,数量,单价,销售员) VALUES(default,'济南商业电脑商城','1002',2,6000,'章晓晓') 运行结果与前一条SQL语句一样,没有增加记录. 如果改销售数量为7台(注意现有6台),输入以下代码增加一条销售记录: INSERT 销售表2006(销售日期,客户名称,货号,数量,单价,销售员) VALUES(default,'济南商业电脑商城','1002',7,5500,'章晓晓') 运行结果显示:"销售量大于库存量6,库存不足不能销售",各表数据均没有变化. 【实例练习8-7】 为"商品一览表"创建一个名为"修改商品"的后触发器,禁止修改"平均进价"和"库存量";当修改某个商品的货名时,需要把"销售表2006"中相应的数据同时全部修改,实现"商品一览表"和"销售表2006"的级联修改. 注意:货号已被"销售表2006""进货表2006"设置了外键的引用,本身已不允许修改,如果有必要修改时必须解除"销售表2006""进货表2006"的外键约束. USE diannaoxs IF exists (SELECT name FROM sysobjects WHERE name='修改商品' AND type='tr') DROP TRIGGER 修改商品 GO CREATE TRIGGER 修改商品 ON 商品一览表 after update AS DECLARE @hh char(4), @hm nvarchar(8), @pj1 Smallmoney, @pj2 Smallmoney, @kc1 bigint, @kc2 bigint SELECT @hh=货号,@pj1=平均进价,@kc1=库存量 FROM deleted SELECT @hm=货名,@pj2=平均进价,@kc2=库存量 FROM inserted 【实例练习8-7】 IF @pj1@pj2 BEGIN PRINT '平均进价不允许修改!' ROLLBACK TRANSACTION -- 事务回滚,撤消修改 END ELSE BEGIN IF @kc1@kc2 BEGIN PRINT '库存量不允许修改!' ROLLBACK TRANSACTION -- 事务回滚,撤消修改 END ELSE UPDATE 销售表2006 SET 货名=@hm WHERE 货号=@hh END GO 运行代码创建触发器成功后显示:命令已成功完成. 【实例练习8-7】 如果使用以下语句对"商品一览表"的"平均进价"进行修改: UPDATE 商品一览表 SET 平均进价=8000 WHERE 货号='1001' 运行后显示:"平均进价不允许修改!",查询1001号商品的平均进价没有变化. 如果使用以下语句对"商品一览表"的"库存量"进行修改: UPDATE 商品一览表 SET 库存量=8000 WHERE 货号='1001' 运行后显示:"库存量不允许修改!",查询1001号商品的库存量没有变化. 如果使用以下语句对"商品一览表"的"货名"进行修改: UPDATE 商品一览表 SET 货名='品牌' WHERE 货号='1001' SELECT * FROM 商品一览表 运行结果如图8-14所示. 再查询一下"销售表2006"或"进货信息视图2006",可以看到所有原来1001号商 品名称全部被更新成了"品牌". 最后请使用以下语句将"商品一览表"数据恢复过来: UPDATE 商品一览表 SET 货名='' WHERE 货号='1001' 【实例练习8-8】 为"销售表2006"创建一个"修改销售"的后触发器,销售记录超过5天后则不准修 改. USE diannaoxs IF exists ( SELECT name FROM sysobjects WHERE name='修改销售' AND type='tr') DROP TRIGGER 修改销售 GO CREATE TRIGGER 修改销售 ON 销售表2006 after update AS DECLARE @rq Smalldatetime SELECT @rq=销售日期 FROM deleted IF Datediff(dd, @rq,getdate())>5 BEGIN PRINT '销售记录已超过5天,不准修改!' ROLLBACK TRANSACTION -- 事务回滚,撤消修改 END GO 注意:条件表达式不能使用Day(getdate())-Day(@rq)>5,这样则月底和下个月初的日期计算不对. 运行代码创建触发器成功后显示:"命令已成功完成." 如果将序号17销售记录的客户名称"济南商业电脑商城"改为"济南商业电脑城", 可使用以下语句进行修改: UPDATE 销售表2006 SET 客户名称='济南商业电脑城' WHERE 序号=17 SELECT * FROM 销售表2006 运行后可以看到数据已得到了修改,重新设置一下系统时间为5天以后,再输入 以下语句: UPDATE 销售表2006 SET 客户名称='济南商业电脑公司' WHERE 序号=17 运行结果显示:销售记录已超过5天,不准修改! 【实例练习8-9】 设置替代触发器"禁止修改",不允许对"供货商表"的厂家记录进行修改,删除. USE diannaoxs IF exists ( SELECT name FROM sysobjects WHERE name='禁止修改' AND type='tr') DROP TRIGGER 禁止修改 GO CREATE TRIGGER 禁止修改 ON 供货商表 instead of update, delete -- 修改或删除激活 AS PRINT '请原谅,《供货商表》不允许对任何数据修改和删除.' GO 该触发器为替代触发,只要对"供货商表"进行任何修改,删除操作,则立即停止并取消该SQL语句对"供货商表"的操作,激活并执行触发器,所以不需要事务回滚语句. 在创建该触发器之前,可以将"供货商表"编号为SDKJ的厂家名称"山东科技市场 销售处"改为"山东科技市场销售中心": UPDATE 供货商表 SET 供货商 ='山东科技市场销售中心' WHERE 供货商ID='SDKJ' SELECT * FROM 供货商表 运行后可以看到数据已得到了修改,创建触发器以后,再输入修改语句结果就会 显示: "请原谅,《供货商表》不允许对任何数据修改和删除." 注意:如果外键引用的父表已创建了对子表级联修改或删除的触发器,则子表不允许创建具有相同动作的替代触发器. 例如我们已经创建了《商品一览表》《员工表》《供货商表》对《进货表2006》《销售表2006》的级联UPDATE触发器,则《进货表2006》《销售表2006》可以创建由UPDATE动作激活的后触发器,但不允许创建由UPDATE动作激活的替代触发器. 8.3.4,禁用/启用触发器 当某个表设置触发器禁止对某个字段进行修改而又必须对该表进行修改时,可以使用禁用触发器命令,使触发器不起作用,修改以后重新启用. 例如:【实例练习8-9】对"供货商表"已设置替代触发器"禁止修改",如果有必要修改时必须禁用触发器,修改后再启用该触发器. 假设"员工表"与"销售表2006""进货表2006"用触发器实现了级联删除,若某个员工调离需要从"员工表"中删除但是原来他所经手的销售或进货记录不能删除,则可以禁用触发器,"员工表"删除记录以后再启用该触发器. 再比如:销售价格被触发器限制在公司制定的参考价格5%范围内浮动,如果遇到特殊情况做特价处理时,则可以禁用触发器,特价销售以后再启用该触发器. 禁用触发器的语句格式: ALTER TABLE 表名 DISABLE TRIGGER 触发器名 启用触发器的语句格式: ALTER TABLE 表名 ENABLE TRIGGER 触发器名 【实例练习8-10】 禁止使用"供货商表"创建的替代触发器"禁止修改",允许对厂家记录进行修改,删除. ALTER TABLE供货商表 DISABLE TRIGGER 禁止修改 UPDATE 供货商表 SET 供货商 ='山东科技市场销售处' WHERE 供货商ID='SDKJ' SELECT * FROM 供货商表 运行后可以看到数据已得到了修改,可见"禁止修改"触发器已经不起作用,可以对《供货商表》进行修改和删除.之后使用以下语句再启用触发器. ALTER TABLE供货商表 ENABLE TRIGGER 禁止修改 8.3.5,用企业管理器创建触发器 在企业管理器创建触发器的步骤如下: (1)在控制台根目录下展开数据库?展开数据表节点,选中要创建触发器的表,右 键单击鼠标,从快捷菜单中选择"所有任务"?"管理触发器"命令,弹出"触发器属 性"对话框.如图8-15所示. 8.3.5,用企业管理器创建触发器 (2)在"触发器属性"对话框中进行以下操作: n 在"名称"下拉列表中选择"新建"(默认). n 在"文本"框中输入触发器的SQL语句代码. n 单击"语法检查"按钮,可检查SQL语句的语法是否正确. (3)单击"确定"按钮完成触发器的创建. 注意: u_不能在系统表和临时表上创建任何触发器,不能在视图上创建后触发器. u_可以在触发器中引用视图或临时表,但不能引用系统表. 8.4 触发器的查看,编辑,重命名和删除 8.4.1,用企业管理器查看编辑触发器 如同在企业管理器中创建触发器相同: 在控制台根目录下展开数据库?展开数据表节点,选中要查看,修改,删除触发器 的表,右键单击鼠标,从快捷菜单中选择"所有任务"?"管理触发器"命令,弹出" 触发器属性"对话框,可进行下列操作: n 在"名称"下拉列表中选择要查看,修改或删除的触发器. n 在"文本"框中可查看,修改,删除触发器的SQL代码 n 可单击"语法检查"按钮检查SQL语句的语法是否正确; n 单击"删除"按钮可删除该触发器. 最后单击"确定"按钮完成触发器的操作. 8.4.2,用EXECUTE查看触发器的定义与相关性 1,查看触发器基本信息 语法格式:[EXECUTE] sp_help 触发器名 2,查看触发器定义 语法格式:[EXECUTE] sp_helptext 触发器名 3,查看触发器的依赖关系(相关性) 语法格式:[EXECUTE] sp_depends 触发器名 4,查看指定表上指定类型的触发器信息 语法格式: [EXECUTE] sp_help TRIGGER 表名[,INSERT|UPDATE|DELETE ] 如果省略触发类型,则返回定义在该表上的所有触发器的信息. 8.4.3,用SQL语句修改和删除触发器 1,用ALTER TIGGER语句修改触发器 ALTER TIGGER 触发器名 ON 表名 [WITH ENCRYPTION] FOR { [DELETE] [,] [ UPDATE ] [,] [ INSERT ] } [NOT FOR REPLICATION] AS SQL 语句 在企业管理器中修改触发器可以参照原来SQL语句直接进行修改,所以修改触发 器在企业管理器中更加方便. 2,重命名触发器 [EXECUTE] sp_rename 原触发器名, 新触发器名 3,删除触发器 DROP TRIGGER 触发器名[,…n] 8.5 实训要求 实训要求 1,理解存储过程的意义,掌握存储过程的创建,查看,修改,删除与应用. 2,理解触发器的意义,掌握触发器的创建,查看,修改,禁用/启用,删除与应用. 4,根据教学进度,认真按照【实例练习8-1】~【实例练习8-10】的要求进行操作, 掌握存储过程和触发器在数据库中的应用. 首先介绍一下什么是存储过程：存储过程就是将常用的或很复杂的工作，预先用 SQL语句写好并用一个指定的名称存储起来，并且这样的语句是放在数据库中 的，还可以根据条件执行不同SQL语句， 那么以后要叫数据库提供与已定义好 的存储过程的功能相同的服务时，只需调用execute,即可自动完成命令。 请大家来了解一下存储过程的语法。 CREATE PROC [ EDURE ] procedure_name [ ; number ] [ { @parameter data_type } [ VARYING ] [ = default ] [ OUTPUT ] ] [ ,...n ] [ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] [ FOR REPLICATION ] AS sql_statement [ ...n ] 参数： 新存储过程的名称。过程名必须符合标识符规则，且对于数据库及其所有者 必须唯一。 要创建局部临时过程，可以在 procedure_name 前面加一个编号符 (#procedure_name)，要创建全局临时过程，可以在 procedure_name 前面加两个编号符 (##procedure_name)。完整的名称（包括 # 或 ##）不能超过 128 个字符。指定过程所有者的名称是可选的。 是可选的整数，用来对同名的过程分组，以便用一条 DROP PROCEDURE 语句即可将同组的过程一起除去。例如，名为 orders 的应用程序使用的过程可以命 名为 orderproc;1、orderproc;2 等。DROP PROCEDURE orderproc 语句将除去整个组。如果名称中包含定界标识符，则数字不应包含在标识符中，只应在 procedure_name 前后使用适当的定界符。 过程中的参数。在 CREATE PROCEDURE 语句中可以声明一个或多个参数。用 户必须在执行过程时提供每个所声明参数的值（除非定义了该参数的默认值）。 存储过程最多可以有 2100 个参数。 使用@符号作为第一个字符来指定参数名称。参数名称必须符合标识符的规 则。每个过程的参数仅用于该过程本身；相同的参数名称可以用在其它过程中。 默认情况下，参数只能代替常量，而不能用于代替表名、列名或其它数据库对象 的名称。 参数的数据类型。所有数据类型（包括 text、ntext 和 image）均可以用作存储过程的参数。不过，cursor 数据类型只能用于 OUTPUT 参数。如果指定的数据类型为 cursor，也必须同时指定 VARYING 和 OUTPUT 关键字。 说明：对于可以是cursor 数据类型的输出参数，没有最大数目的限制。 指定作为输出参数支持的结果集（由存储过程动态构造，内容可以变化）。 仅适用于游标参数。 参数的默认值。如果定义了默认值，不必指定该参数的值即可执行过程。默 认值必须是常量或 NULL。如果过程将对该参数使用 LIKE 关键字，那么默认值中可以包含通配符（%、_、[] 和 [^]）。 表明参数是返回参数。该选项的值可以返回给 EXEC[UTE]。使用 OUTPUT 参数可将信息返回给调用过程。Text、ntext 和 image 参数可用作 OUTPUT 参数。使用 OUTPUT 关键字的输出参数可以是游标占位符。 表示最多可以指定 2100 个参数的占位符。 {RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION} RECOMPILE 表明 SQL Server 不会缓存该过程的计划，该过程将在运行时重新编 译。在使用非典型值或临时值而不希望覆盖缓存在内存中的执行计划时，请使用 RECOMPILE 选项。 ENCRYPTION 表示 SQL Server 加密 syscomments 表中包含 CREATE PROCEDURE 语句文本的条目。使用 ENCRYPTION 可防止将过程作为 SQL Server 复制的一部分发布。 说明：在升级过程中，SQL Server 利用存储在 syscomments 中的加密注释来重新创建加密过程。 指定不能在订阅服务器上执行为复制创建的存储过程。.使用 FOR REPLICATION 选项创建的存储过程可用作存储过程筛选，且只能在复制过程中执 行。本选项不能和 WITH RECOMPILE 选项一起使用。 指定过程要执行的操作。 过程中要包含的任意数目和类型的 Transact-SQL 语句。但有一些限制。 是表示此过程可以包含多条 Transact-SQL 语句的占位符。 存储过程的最大大小为 128 MB。 存储过程的优点都有哪些呢？ 1.存储过程只在创造时进行编译即可，以后每次执行存储过程都不需再重新 编译，而我们通常使用的SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。 2.经常会遇到复杂的业务逻辑和对数据库的操作，这个时候就会用SP来封装数据库操作。当对数据库进行复杂操作时(如对多个表进行 Update,Insert,Query,Delete时），可将此复杂操作用存储过程封装起来与数 据库提供的事务处理结合一起使用。可以极大的提高数据库的使用效率，减少程 序的执行时间，这一点在较大数据量的数据库的操作中是非常重要的。在代码上 看，SQL语句和程序代码语句的分离，可以提高程序代码的可读性。 3.存储过程可以设置参数，可以根据传入参数的不同重复使用同一个存储过 程，从而高效的提高代码的优化率和可读性。 4.安全性高,可设定只有某此用户才具有对指定存储过程的使用权存储过程 的种类： （1）系统存储过程：以sp_开头,用来进行系统的各项设定.取得信息.相关管理工作,如 sp_help就是取得指定对象的相关信息。 （2）扩展存储过程 以XP_开头,用来调用操作系统提供的功能 exec master..xp_cmdshell 'ping 10.8.16.1' （3）用户自定义的存储过程,这是我们所指的存储过程常用格式 模版：Create procedure procedue_name [@parameter data_type][output] [with]{recompile|encryption} as sql_statement 解释：output：表示此参数是可传回的 with {recompile|encryption} recompile:表示每次执行此存储过程时都重 新编译一次;encryption:所创建的存储过程的内容会被加密。 表银行存款表（bankMoney）的内容如下 Id userID Sex Money 001 Zhangsan 男 30 002 Wangwu 男 50 003 Zhangsan 男 40 要求1：查询表bankMoney的内容的存储过程 create procedure sp_query_bankMoney as select * from bankMoney go exec sp_query_bankMoney 加入一笔记录到表bankMoney，并查询此表中userID= Zhangsan的所有存款的 总金额。 Create proc insert_bank @param1 char(10),@param2 varchar(20),@param3 varchar(20),@param4 int,@param5 int output with encryption ---------加密 as insert bankMoney (id,userID,sex,Money) Values(@param1,@param2,@param3, @param4) select @param5=sum(Money) from bankMoney where userID='Zhangsan' go 在SQL Server查询分析器中执行该存储过程的方法是： declare @total_price int exec insert_bank '004','Zhangsan','男',100,@total_price output print '总余额为'+convert(varchar,@total_price) go 在这里再啰嗦一下存储过程的3种传回值（方便正在看这个例子的朋友不用再去 查看语法内容）: 1.以Return传回整数 2.以output格式传回参数 3.Recordset 传回值的区别: output和return都可在批次程式中用变量接收,而recordset则传回到执行批次的客户端中。 下面的存储过程从四个表的联接中返回所有作者（提供了姓名）、出版的书 籍以及出版社。该存储过程不使用任何参数。 USE pubs IF EXISTS (SELECT name FROM sysobjects WHERE name = 'au_info_all' AND type = 'P') DROP PROCEDURE au_info_all GO CREATE PROCEDURE au_info_all AS SELECT au_lname, au_fname, title, pub_name FROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id INNER JOIN titles t ON t.title_id = ta.title_id INNER JOIN publishers p ON t.pub_id = p.pub_id GO au_info_all 存储过程可以通过以下方法执行： EXECUTE au_info_all -- Or EXEC au_info_all 如果该过程是批处理中的第一条语句，则可使用： au_info_all CREATE PROCEDURE au_info @lastname varchar(40), @firstname varchar(20) AS SELECT au_lname, au_fname, title, pub_name FROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id INNER JOIN titles t ON t.title_id = ta.title_id INNER JOIN publishers p ON t.pub_id = p.pub_id WHERE au_fname = @firstname AND au_lname = @lastname GO au_info 存储过程可以通过以下方法执行： EXECUTE au_info 'Dull', 'Ann' -- Or EXECUTE au_info @lastname = 'Dull', @firstname = 'Ann' -- Or EXECUTE au_info @firstname = 'Ann', @lastname = 'Dull' -- Or EXEC au_info 'Dull', 'Ann' -- Or EXEC au_info @lastname = 'Dull', @firstname = 'Ann' -- Or EXEC au_info @firstname = 'Ann', @lastname = 'Dull' 如果该过程是批处理中的第一条语句，则可使用： au_info 'Dull', 'Ann' -- Or au_info @lastname = 'Dull', @firstname = 'Ann' -- Or au_info @firstname = 'Ann', @lastname = 'Dull' CREATE PROCEDURE au_info2 @lastname varchar(30) = 'D%', @firstname varchar(18) = '%' AS SELECT au_lname, au_fname, title, pub_name FROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id INNER JOIN titles t ON t.title_id = ta.title_id INNER JOIN publishers p ON t.pub_id = p.pub_id WHERE au_fname LIKE @firstname AND au_lname LIKE @lastname GO au_info2 存储过程可以用多种组合执行。下面只列出了部分组合： EXECUTE au_info2 -- Or EXECUTE au_info2 'Wh%' -- Or EXECUTE au_info2 @firstname = 'A%' -- Or EXECUTE au_info2 '[CK]ars[OE]n' -- Or EXECUTE au_info2 'Hunter', 'Sheryl' -- Or EXECUTE au_info2 'H%', 'S%' = 'proc2