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

本章教学内容: 在大型数据库系统中，存储过程和触发器具有很重要的作用。 存储过程是SQL语句和控制流语句组成的集合； 触发器是一种特殊的存储过程； 9.1 概述 9.1.1 存储过程概述 9.1.2 触发器概述 9.1.1 存储过程概述 定义： 将某些需要多次调用的实现某个特定任务的代码段编写成一个过程，将其保存在数据库中，并由SQL Server服务器通过过程名来调用它们，这些过程就叫做存储过程。 使用存储过程的优点： 实现了模块化编程。 可在单个存储过程中执行一系列SQL语句。 可从自己的存储过程内引用其他存储过程，简化复杂语句。 可能接受输入参数并返回输出值。 具有对数据库立即访问功能。 可以减少网络流量。 提高数据库的安全性。 存储过程既有参数又有返回值，其与函数的区别： 存储过程的返回值只是指明执行是否成功； 函数的返回值可直接被用在表达式中； 分类： 系统存储过程 扩展存储过程 用户自定义存储过程 系统存储过程： 由系统自动创建，主要存储在master数据库，一般以sp_作为前缀。 主要是从系统表中获取信息。 用于管理SQL Server和显示数据库和用户信息。 可以随时调用，不必在其前加上数据库名。 扩展存储过程： 属于动态链接库，SQL Server可以动态加载与执行，通常是Ｃ或Ｃ＋＋语言写成的。 一般以xp_作为前缀。 用户自定义存储过程： 由用户创建并能完成某一特定功能的存储过程。 9.1.2 触发器概述 是一种特殊的存储过程。 存储过程是通过存储过程名被直接调用，触发器主要是通过事件进行触发而被执行。 是一个功能强大的工具，可使每个站点可在有数据修改时自动强制执行其业务规则。 可以用于完整性检查，可以实现更加复杂的数据完整性。 分类： After触发器 Instead of触发器 After触发器 在数据变动（insert、update、delete）完成以后才被触发。 只能在表上定义。 同一个数据表中可以创建多个After触发器。 Instead of触发器 在数据变动以前被触发，并取代数据的操作（insert、update、delete） ，而去执行触发器定义的操作。 可以在表或视图上定义。 关于触发器的规定： 只在触发它的语句完成后执行。 如果语句在表中执行违反条件约束或引起错误，触发器不会触动。 一个语句只能触动一次触发器。 9.2 存储过程 9.2.1 创建存储过程 9.2.2 管理存储过程 9.2.3 执行存储过程 9.2.4 删除存储过程 9.2.1 创建存储过程 使用方法： 使用向导 使用企业管理器 使用查询分析器 使用查询分析器： 不能将Create Procedure语句和其它SQL语句组合到单个批处理中 只能在当前数据库中创建存储过程 存储过程是数据库对象，必须遵守标识符规则 权限默认属于数据库所有者，该所有者可将此权限授予其他用户 创建存储过程的语法格式为： 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：要创建的存储过程的名称。如果要创建全局临时存储过程，则需要在名称前面加##；如果要创建局部临时存储过程，则需要在名称前面加#。 number ：用于对同名的过程进行分组的整数，以方便批量删除。 @parameter：语句中声明的参数，如果没有给参数定义默认值，则用户必须在执行过程时提供参数的值，最多可以有1024个参数。 Data_type：参数的数据类型。其中cursor数据类型只能用于OUTPUT参数，且必须同时指定VARYING 和OUTPUT关键字 VARYING：指定作为OUTPUT参数支持的结果集，仅使用于cursor。 Default：参数的默认值，必须是常量或NULL，可以包含通配符。 OUTPUT：表明参数将返回值。此值可以返回给EXEC[UTE]。 RECOMPILE：表示不在缓存中保存存储过程的执行计划。可以在使用临时值不希望覆盖缓存中的执行计划时使用。 ENCRYPTION：表示对存储在表syscomments中的存储过程文本进行加密，以防止其他用户查看或修改。 例9-1 创建包含SELECT语句的存储过程。 use 实例 go create procedure 查询表1 as select * from