第九章 存储过程与触发器的创建与维护pptConvertor

1、第九章 存储过程与触发器的创建与维护 本章要点：了解存储过程和触发器的概念和特点 掌握存储过程和触发器的创建、管理、删除 掌握存储过程和触发器的主要区别本章教学内容:9.1 概述9.2 存储过程9.3 触发器9.4 存储过程与触发器的应用在大型数据库系统中，存储过程和触发器具有很重要的作用。存储过程是SQL语句和控制流语句组成的集合；触发器是一种特殊的存储过程；9.1 概述 定义：将某些需要多次调用的实现某个特定任务的代码段编写成一个过程，将其保存在数据库中，并由SQL Server服务器通过过程名来调用它们，这些过程就叫做存储过程。使用存储过程的优点：实现了模块化编程。可在单个存储过程中执行

2、一系列SQL语句。可从自己的存储过程内引用其他存储过程，简化复杂语句。可能接受输入参数并返回输出值。具有对数据库立即访问功能。可以减少网络流量。提高数据库的安全性。存储过程既有参数又有返回值，其与函数的区别：存储过程的返回值只是指明执行是否成功；函数的返回值可直接被用在表达式中；分类：系统存储过程扩展存储过程用户自定义存储过程系统存储过程：由系统自动创建，主要存储在master数据库，一般以sp_作为前缀。主要是从系统表中获取信息。用于管理SQL Server和显示数据库和用户信息。可以随时调用，不必在其前加上数据库名。扩展存储过程：属于动态链接库，SQL Server可以动态加载与执行，通常

3、是或语言写成的。一般以xp_作为前缀。用户自定义存储过程：由用户创建并能完成某一特定功能的存储过程。是一种特殊的存储过程。存储过程是通过存储过程名被直接调用，触发器主要是通过事件进行触发而被执行。是一个功能强大的工具，可使每个站点可在有数据修改时自动强制执行其业务规则。可以用于完整性检查，可以实现更加复杂的数据完整性。分类：After触发器Instead of触发器After触发器在数据变动（insert、update、delete）完成以后才被触发。只能在表上定义。同一个数据表中可以创建多个After触发器。Instead of触发器在数据变动以前被触发，并取代数据的操作（insert、up

4、date、delete） ，而去执行触发器定义的操作。可以在表或视图上定义。关于触发器的规定：只在触发它的语句完成后执行。如果语句在表中执行违反条件约束或引起错误，触发器不会触动。一个语句只能触动一次触发器。9.2 存储过程使用方法：使用向导使用企业管理器使用查询分析器使用查询分析器：不能将Create Procedure语句和其它SQL语句组合到单个批处理中只能在当前数据库中创建存储过程存储过程是数据库对象，必须遵守标识符规则权限默认属于数据库所有者，该所有者可将此权限授予其他用户创建存储过程的语法格式为：CREATE PROCEDUREprocedure_name;number param

5、eter data_typeVARYING=defaultOUTPUT,nWITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONFOR REPLICATIONAS sql_statementn 使用T-SQL语句创建存储过程 procedure_name：要创建的存储过程的名称。如果要创建全局临时存储过程，则需要在名称前面加#；如果要创建局部临时存储过程，则需要在名称前面加#。number ：用于对同名的过程进行分组的整数，以方便批量删除。parameter：语句中声明的参数，如果没有给参数定义默认值，则用户必须在执行过程时提供参数的值，最多可以有1024个

6、参数。Data_type：参数的数据类型。其中cursor数据类型只能用于OUTPUT参数，且必须同时指定VARYING 和OUTPUT关键字VARYING：指定作为OUTPUT参数支持的结果集，仅使用于cursor。Default：参数的默认值，必须是常量或NULL，可以包含通配符。OUTPUT：表明参数将返回值。此值可以返回给EXECUTE。RECOMPILE：表示不在缓存中保存存储过程的执行计划。可以在使用临时值不希望覆盖缓存中的执行计划时使用。ENCRYPTION：表示对存储在表syscomments中的存储过程文本进行加密，以防止其他用户查看或修改。 例9-1 创建包含SELECT语

7、句的存储过程。 use 实例 go create procedure 查询表1 as select * from 学生表 go 成功创建存储过程后，存储过程名存储在sysobjects系统表中，程序文本存储在syscomments中。一、查看存储过程在企业管理器中查看存储过程的定义在企业管理器中查看存储过程的相关性使用系统存储过程来查看用户创建的存储过程sp_help：查看有关数据库对象、用户定义数据类型或SQL Server所提供的数据类型的信息sp_helptext：显示规则、默认值、未加密的存储过程、用户定义函数、触发器或视图文本。sp_depends：显示有关数据库对象相关性的信息。s

8、p_stored_procedures：返回当前环境中的存储过程列表。例9-2查看存储过程信息 use 实例 go sp_help 查询表1 go sp_helptext 查询表1 go sp_depends 查询表1 go sp_stored_procedures go如果运行单个系统存储过程，结果如何？运行多个系统存储过程时，如果去掉存储过程间的go，运行结果将会如何？二、重命名存储过程定义sp_rename 原存储过程名，新存储过程名例9-3 sp_rename 查询表1 ,查询表使用EXECUTE语句可以在任何情况下执行存储过程，如果存储过程是批处理中的第一条语句，或是批处理操作的唯一

9、语句，那么可以省略EXECUTE关键字。use 实例exec 查询表go 或 查询表go如果需要在启动SQL Server时，系统自动执行存储过程，可以使用sp_procoption进行设置。 执行存储过程语句的语法格式为：EXECUTE return_staus=procedure_name;number|procedure_name_varparameter=value|variableOUTPUT|DEFAULT,nWITH RECOMPILE手动执行存储过程参数说明returm_status：用于保存存储过程返回状态的整型变量。这个变量在用于EXECUTE语句前，必须在批处理、存储过程

10、或函数中声明过。procedure_name_var：局部定义变量名，代表存储过程名称。Value：存储过程的参数值。variable：用来保存参数或者返回参数的变量。DEFAULT：表示参数值取默认值。 权限存储过程的所有者默认拥有对该存储过程指定EXECUTE的权限，该权限可以转让。 如果每次启动 SQL Server 2000时都启动某个存储过程，可以使用sp_procoption进行设置，这对于需要作为后台进程运行的存储过程十分有效。每个自动执行存储过程都占用一个连接，所以可以使用过程嵌套执行存储过程，以减少资源使用。 sp_procoptionProcName=procedure ,

11、OptionName=option ,OptionValue=value 自动执行存储过程参数说明ProcName=procedure：存储过程的名称。Procedure为nvarchar(766)类型，无默认值。OptionName=option：设置存储过程的选项名称。其唯一值是startup，表示是否将存储过程设为每次自动执行。OptionValue=value：表示选项状态。其值可以分开（true或on）或者关（false或off）。Value为varchar(12)类型，无默认值。 权限sysadmin固定服务器角色的成员默认拥有p_procoption执行权限。 删除存储过程可以将

12、一个、多个存储过程或存储过程组从当前数据库中删除。对于sp_开头的系统存储过程，将在master数据库中删除。删除存储过程的语法格式为：DROP PROCEDUREprocedure,n参数说明procedure：要删除的存储过程或存储过程组的名称。权限存储过程的所有者默认拥有DROP PROCEDURE权限，该权限不可转让。db_owner和db_ddladmin固定数据库角色成员和sysadmin固定服务器角色成员可以通过在DROP PROCEDURE内指定所有者除去任何对象。 9.3 触发器规定：触发器只在触发它的语句完成后执行如果语句在表中执行违反条件约束或引起错误，触发器不会触动触发

13、器视为单一事务中的一部分，因此可以由原触发器复原事务，如果在事务过程中检测到严重错误，则会自动复原整个事务。一个语句只能触动一次触发器使用企业管理器创建触发器使用T-SQL语句创建触发器只能在当前数据库中创建触发器创建时可以相用其他数据库中的对象。只有表的所有者创建触发器，且不能将该权限转给其他用户。使用方法：使用企业管理器使用查询分析器使用查询分析器CREATE TRIGGER trigger_name ON table|viewWITH ENCRYPTION FOR|AFTER|INSTEAD OF DELETE, INSERT, UPDATE WITH APPEND NOT FOR RE

14、PLICATIONAS IF UPDATE(column) AND|ORUPDATE(column) n | IF(COLUMNS_UPDATED()bitwise_operator updated_bitmask) comparison_operator column_bitmaskn sql_statementn trgger_name：触发器名称。Table|view：执行触发器的表或视图。WITH ENCRYPTION：指定SQL Server在syscomments表中对触发器定义文本加密后存储。AFTER：表示当所有操作，包括约束执行完成后再激发触发器。该关键字不能用于视图。INS

15、TEAD OF：指定由执行触发器代替执行触发SQL语句。在表或视图上，每个INSERT、UPDATE或DELETE语句最多可以定义一个INSTEAD OF触发器。DELETE ,INSERT , UPDATE:指定在表或视图上激活触发器的语句。至少指定一个选项，如果指定多个选项，则需要用逗号分隔。WITH APPENG：说明当前定义的触发器类型载表中已经存在。该参数不能与INSTEAD OF 触发器或AFTER触发器一起使用。这个参数注意为了兼容早期版本，在SQL Server2000 中不必使用。NOT FOR REPLICATION：表示当复制进程修改触发器所在表时，不应执行触发器。AS：

16、触发器要执行的操作。Sql_statement：触发器执行条件和操作。IF UPDATE():检测在指定列上进行插入或修改操作,该参数不能用于删除操作.可以指定多列。 Column:IF UPDATE子句进行检测的列名.可以是除计算机列以外的其他任何一种数据类型。IF （COLUMNS_UPDATED()）：检测指定列是否被插入或修改，不能用于删除操作。Bitwise_operator：用于比较运算的位运算符。Updated_bitmask：整型位掩码，表示实际更新或插入的列。Comparison_operator：比较运算符。使用等于比较运算符（=）检查updated_bitmask中指定的

17、所有列是否都实际进行了更新。使用大于运算符（>）检查 updated_bitmask中指定的任何一列或某些列是否已更新。Column_bitmask：要检测列的整型位掩码，用来检查是否已更新或插入了这些列。例9-3创建简单的触发器，当向学生表中插入、更新或删除数据时，该触发器向客户端显示一条消息。use 实例gocreate trigger 更新表2on 学生表for insert,update,deleteasprint '插入,更新或删除学生表'使用企业管理器查看触发器信息使用企业管理器查看触发器的相关性使用系统存储过程查看触发器sp_helptrigger 表名：返

18、回指定表中定义的当前数据库的触发器类型。sp_help触发器名 ：用于查看触发器的一般信息。 sp_helptext触发器名 sp_depends触发器名表名用DROP TRIGGER语句可以删除不再需要的触发器，此时原来的触发表以及表中的数据不影响。如果删除表，则表中所有的触发器都将被自动删除。删除触发器语法格式为：DROP TRIGGERtriggern参数说明trigger：要删除触发器的名称。9.4 存储过程与触发器的应用变量的使用值的回传使用return回传值存储过程执行到return时停止，并回到调用程序中的下一个语句，return也可传回整数值。使用select回传值例：建立参数

19、性别_1。当执行存储过程时，输入性别，该存储过程就会根据性别显示学生信息。（使用参数） use 实例 go create proc 根据性别显示学生信息 性别_1 char(4) as select 学号,姓名 from 学生表 where 性别=性别_1 return 改进在建立存储过程前，要先确定是否有重复的名称存在，必须用未存在的名称命名存储过程，或是先删除已经存在的名称后再重新命名。use 实例 if exists(select name from sysobjects where name='根据性别显示学生信息' and type='p') drop procedure 根据性别显示学生信息 go create proc 根据性别显示学生信息 性别_1 char(4) as select 学号,姓名 from 学生表 wh