SQLServer2000网络数据库教程 第9章 存储过程和触发器.pdf

第9章存储过程和触发器第9章存储过程和触发器本章要点：了解存储过程的概念和特点。学会存储过程的创建方法。学会触发器的创建方法。9.1 存储过程9.2 触发器SQL Server 2000网络数据库教程第9章存储过程和触发器9.1存储过程存储过程类似于BAT文件（批处理文件），其中包含一组命名,用于完成某项任务。这样可以减少重复操作，提高TSQL语句 的运行性能和效率。使用存储过程而不使用客户计算机本地的T SQL程序的优点有:保证操作一致性：由于存储过程是一段封装的查询，从而对 于重复的操作将保持功能的一致性。提供安全机制：对于没有权限完成某些操作的用户，可以通 过执行存储过程（有执行该存储过程的权限）实现这些操作 减少网络传输：由于将大量的T SQL代码“压缩”也了一 条存储过程，网络传输量大大减少，这意味着既减少了网络 的流量，缓解网络压力，也减少了网络传输需要的时间。SQL Server 2000网络数据库教程第9章存储过程和触发器 SQL Server中有3种类型的存储过程：系统存储过程、临时存储过程和扩展存储过程。系统存储过程系统存储过程是指由系统提供的存储过程，可以直接用于执行 一些操作，SQL SREVER中的许多管理活动是通过它执行。系统存储过程存放在MASTER数据库，前缀为SP_,在任何数 据库中无需用MASTER限定就直接执行。SQL Server 2000网络数据库教程第9章存储过程和触发器（2 临时存储过程临时存储过程与临时表相似，也分为局部临时存储过程和 全局临时存储过程，且可以分别向该过程名称前面添加#或#前 缀表示。#表示本地临时存储过程，#表示全局临时存储过程。使用临时存储过程必须创建本地连接，当SQL Server关闭后，这些临时存储过程将自动被删除。由于SQL Server 2000支持 重新使用执行计划，所以连接至USQL Server 2000的应用程序 使用SP_EXEUTESQL系统存储过程，而不使用临时存储过程。SQL Server 2000网络数据库教程第9章存储过程和触发器（3 扩展存储过程扩展存储过程就是在SQL Server以外装载并执行的动态链接库 DLL,当加载到SQL Server系统中，它的使用方法与系统存储过程 一样。扩展存储过程只能存放到MASTER数据库中。SQL Server 2000网络数据库教程第9章存储过程和触发器9.1.1 创建存储过程在SQL Server 2000中可以使用以下两种方法 创建自定义存储过程：利用SQL Server企业管理器创建存储过程使用 Transact-SQL语句中的CREATE PROCEDURE 命令创建存储过程SQL Server 2000网络数据库教程第9章存储过程和触发器1.利用SQL Server企业管理器创建存储过程操作步骤如下：1 首先，展开服务器组，然后展开服务器。再展开“数据库”文件夹，然后展开要在其中创建过程的 数据库。右击“存储过程”，然后单击“新建存储 过程”命令。如图9T所示：刷新查看排列图标 对齐图标隹）帮助出）图9-1新建存储过程菜单画面SQL Server 2000网络数据库教程第9章存储过程和触发器2 在弹出的“存储过程属性一新建存储过程”窗口中 输入存储过程的文本。如图9-2所示：I存储过程星性一新建存储过建确定取消帮助图9-2存储过程属性一新建存储过程画面SQL Server 2000网络数据库教程第9章存储过程和触发器3 在这里，更改存储过程的名称，将OWNER.PROCEDURE NAME更改为Product。再输 入存储过未呈的内容，在AS后面输入Select*产品，即这个存储过程的作用是查询产品表中的全部内容。如图9-3所不。4 最后按“确定”按钮即可保存此存储过程。图9-3输入存储过程内容的画面SQL Server 2000网络数据库教程第9章存储过程和触发器2.使用Transact-SQL命令创建存储过程 使用CREATE PROCEDURE命令创建存储过程 在当前数据库中创建存储过程可以通过CREATEPROCEDURE语句实现。CREATE PROCEDURE语句可以 包含任意Transact-SQL语句，也可以包含其他存储过程，即 存储过程可以嵌套。但是不能和其他Transact-SQL语句混合 使用在一个批处理中。成功执行CREATE PROCEDURE语句后，创建的存储过程 名称存储在sysobjects系统表中，MCREATE PROCEDURE 语句的文本存储在syscomments中。SQL Server 2000网络数据库教程第9章存储过程和触发器创建存储过程的语法格式为：CREATE PROC EDUREprocedure_name;bynber parameter data_typeVARYING=default OUTPUT 一L 口WITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONFOR REPLICATIONAS sql_statement.nSQL Server 2000网络数据库教程第9章存储过程和触发器参数说明 procedure_name：要创建的存储过程的名称。如果要创建全局临时 存储过程；则需要在名称前面加#；如果要创建局部临时存储过程,则需要在名称前面加#。-；number：用于对同名的过程进行分组的整数，以方便批量删除。parameter：在CREATE PROCEDURE语句中声明的参数，可以 最多使用2100个参数。如果没有给参数定义默认值，则用户必须在 执行过程时提供参数的值。Data_type：参数的数据类型。其中cursor数据类型只能用语 OUTPUT参数，且必须同时指定VARYING和OUTPUT关键字。SQL Server 2000网络数据库教程第9章存储过程和触发器 VARYING：指定作为OUTPUT参数支持的结果集，仅使用 于cursor。Default：参数的默认值，必须是常量或NULL,可以包含通 配符。OUTPUT：表明参数将返回值。此值可以返回给EXECUTE。RECOMPILE：表示不在缓存中保存存储过程的执行计划。可以在使用临时值不希望覆盖缓存中的执行计划时使用。ENCRYPTION：表示对存储在表syscomments中的存储过程 文本进行加密，以防止其他用户查看或修改。SQL Server 2000网络数据库教程第9章存储过程和触发器 示例例9.1包含SELECT语句的存储过程。use dzswgocreate procedure product 1 as select*from 产品 where订购量0例9.2创建带精确匹配参数的存储过程。use dzswgocreate procedure product2dgl intasselect*from 产品where 订购量=dglSQL Server 2000网络数据库教程第9章存储过程和触发器例9.3创建带通配符参数的存储过程。use dzswgocreate procedure productscpmc varchar(10)=%油asselect*from 产品where 产品名称 like cpmcSQL Server 2000网络数据库教程第9章存储过程和触发器例9.4使用OUTPUT参数创建存储过程。use dzswgocreate procedure product4cpmc varchar(10)%油，sumprice int outputasselect*from 产品where 产品名称 like cpmcselect suniprice=sum(订购量)from 产品 where 产品名称 like cpmcSQL Server 2000网络数据库教程第9章存储过程和触发器权限sysadmin固定服务器角色成员和db_owner和dbddladmin固 定数据库角色成员默认拥有CREATE PROCEDURE权限。Sysadmin固定服务器角色成员和db_owner固定数据库角色成员转 让该权限给其他用户。SQL Server 2000网络数据库教程第9章存储过程和触发器9.1.2执行存储过程使用EXECUTE语句可以在任何情况下执行存储过程,如果存储过程是批处理中的第一条语句，那么可以省 略EXECUTE关键字。如果需要在启动SQL Server时，系统自动执行存储过程，可以使用sp_procoption进行 设置。SQL Server 2000网络数据库教程第9章存储过程和触发器1.手动执行存储过程 使用EXECUTE语句可以执行存储过程,其中EXECUTE可以 简写为EXEC,如果存储过程是一个批处理中的第一条语句，那么可以省略EXECUTE的关键字。在指定存储过程的参数时，如果使用parameter=value形式，那么可以按任何顺序提供参数；inMparameter=value,则必须按照CREATE PROCEDURE语句中给出的顺序提供参 数。如果出入了存储过程中没有定义或不匹配的参数，系统 将不会接受。对于以sp_开头的系统存储过程，系统将在master数据库中寻 找。如果执行用户自行定义的sp_开头的存储过程，就必须 用数据库名和所有者名限定数据库。SQL Server 2000网络数据库教程第9章存储过程和触发器 执行存储过程语句的语法格式为：EXECUTE(return_staus=procedurename;number|procedure_name_var parameter=value|variableOUTPUT|DEFAULTWITH RECOMPILESQL Server 2000网络数据库教程第9章存储过程和触发器参数说明 returm_status：用于保存存储过程返回状态的整型变量。这个变量在用于EXECUTE语句前，必须在批处理、存储过 程或函数中声明过。procedure_name_var：局部定义变量名，代表存储过程名 称。Value：存储过程的参数值。variable：用来保存参数或者返回参数的变量。DEFAULT：表示参数值取默认值。权限 存储过程的所有者默认拥有对该存储过程指定EXECUTE的 权限，该权限可以转让。SQL Server 2000网络数据库教程第9章存储过程和触发器2.自动执行存储过程 如果需要每次启动MS SQL Servers时都启动某个存储过程，可以使用sprocoption进行设置，这对于需要作为后台进程 运行的存储过程十分有效。但是由于每个自动执行存储过程 都占用一个连接，所以可以使用过程嵌套执行存储过程，以 减少资源使用。*自动执行存储过程的语法格式为：Sp_procoptionProcName=Aprocedure55 OptionName=5 option5,OptionValue=5 value5SQL Server 2000网络数据库教程第9章存储过程和触发器参数说明 ProcName=procedure：存储过程的名称。Procedure为nvarchar(766)类型,无默认值。OptionName=5option：设置存储过程的选项名称。其唯一值是startup,表示是否将存储过程设为每次自动执行。OptionValue=value：表示选项状态。其值可以分开(true或on)或者关(false或off)。Value为varchar(12)类型,无默认值。权限 sysadmin固定服务器角色的成员默认拥有sp_procoption执行权限。SQL Server 2000网络数据库教程第9章存储过程和触发器9.1.3删除存储过程 删除存储过程可以使用DROP命令。此命令可以将一 个或者多个存储过程或存储过程组从当前数据库中删 除。对于sp_开头的系统存储过程，将在master数据库 中删除。删除存储过程的语法格式为：DROP PROCEDURE procedure),.n参数说明 procedure：要删除的存储过程或存储过程组的名称。SQL Server 2000网络数据库教程第9章存储过程和触发器权限 存储过程的所有者默认拥有DROP PROCEDURE权限，该权 限不可转让。Db_owner和db ddladmin固定数据库角色成员 和sysadmin固定服务器角色成员可以通过在DROP PROCEDURE内指定所有者除去任何对象。SQL Server 2000网络数据库教程第9章存储过程和触发器 如果想删除扩展的存储过程就需要使用 sp_dropextendedproc删除扩展存储过程。删除扩展存储过程的语法格式为：sp_dropetendedprocfunctnam=5procedure5 参数说明 functnam=procedure：要删除的扩展存储过程名称。Procedure的薮施类型为nvarchar(517),没有默认值。权限 只有sysadmin固定服务器角色的成员才可以执行spdropextendedproc。返回SQL Server 2000网络数据库教程第9章存储过程和触发器9.2触发器触发器是一种特殊的存储过程，普通的存储过程可以由用户直 接调用执行，但是触发器只能通过某些操作触发，而不能直接 被调用执行。触发器与表是无法分开的，所以由表中数据的三种基本操作 一一插入、修改和删除衍生了触发器的三种类型：INSERT触发 器、UPDATE触发器和DELETE触发器。这三种类型的触发器分 别在表中执行插入数据（INSERT、修改数据（UPDATE 或 删除数据（DELETE 操作时触发执行。SQL Server 2000网络数据库教程第9章存储过程和触发器921创建触发器在SQL Server 2000中可以使用以下两种方法创 建触发器：利用SQL Server企业管理器创建触发器 使用Transact-SQL语句中的CREATE TRIGGER命令创建触 发器SQL Server 2000网络数据库教程第9章存储过程和触发器1.利用SQL Server企业管理器创建触发器操作步骤如下：1)首先，展开服务器组，然后展开服务器。再展开“数据库”文件夹，然后展开要在其中创建触发器 的数据库。右键单击后在弹出的菜单中选择“所有 任务”上再选择其中的“管理触发器”命令。如图 9-4所小：新建表 设计表 打开表9)全文索引表(f)元五五元元Z4K 2吴一匕5 S关玄管理索引M.我切(I)复制g删除9)通命名(世)dbodbodbodbodbo导人教露导出数().曾理权限().m()新注发布(由帮助出)生成SQL脚本9)显示相关性但图9-4管理触发器画面SQL Server 2000网络数据库教程第9章存储过程和触发器2 在弹出的“触发选属性”窗口中输入触发器的文 本。如图9-5所小：图9-5触发器属性窗口画面SQL Server 2000网络数据库教程第9章存储过程和触发器3)首先更改触发器的名称，将 TRIGGERNAME 更改为 Messagelo然后我们输入触发器的内容，在AS后面输入 RAISERROR(13001,16,10),即这个存储器的作用是当 表中的数据在发生INSERT、UPDATE.DELETE等操作 后会向客户端发送一条消息，此消息ID为13001,而此 消息的小目关信息是存放在Master库中的sysmessages表中 的。如图9-6所示：图9-6输入存储过程内容的画面SQL Server 2000网络数据库教程第9章存储过程和触发器2.使用Transact-SQL命令创建触发器 创建触发器的语句语法格式为：CREATE TRIGGER trigger_name ON table|view WITHENCRYPTION一 FOR|AFTER|INSTEAD OF INSERT5UPDATEWITH APPENDNOT FOR REPLICATIONASIF UPDATE(column)AND|ORUPDATE(column)n|IF(COLUMNS_UPDATED()bitwise_operator updated_bitmask)comparison_operator column_bitmask.n sql_statement.n SQL Server 2000网络数据库教程第9章存储过程和触发器参数说明 trgger_name：触发器名 称。Table|view：执行触发器的表或视图。WITH ENCRYPTION：指定SQL Server在syscomments表中 对触发器定义文本加密后存储。AFTER：表示当所有操作，包括约束执行完成后再激发触发 器。该关键字不能用于视图。INSTEAD OF：指定由执行触发器代替执行触发SQL语句。在表或视图上，每个INSERT、UPDATE或DELETE语句最多 可以定义一个INSTEAD OF触发器。SQL Server 2000网络数据库教程第9章存储过程和触发器DELETE JUNSERTH,UPDATE:指定在表或视图上激 活触发器的语句。至少指定一个选项，如果指定多个选项，则需要用逗号分隔。WITHAPPENG：说明当前定义的触发器类型载表中已经存 在。该参数不能与INSTEAD OF触发器或AFTER触发器一起 使用。这个参数注意为了兼容早期版本，在SQL Server2000 中不必使用。NOT FOR REPLICATION：表示当复制进程修改触发器所在 表时，不应执行触发器。AS：触发器要执行的操作。Sql_statement：触发器执行条件和操作。IF UPDATE。:检测在指定列上进行插入或修改操作,该参数 不能用于删除操作.可以指定多列。SQL Server 2000网络数据库教程第9章存储过程和触发器Colunm:IF UPDATE子句进行检测的列名.可以是除计算机列 以外的其他任何一种数据类型。IF COLUMNS_UPDATED ：检测指定列是否被插入或 修改，不能用于涮除操作。Bitwise_operator：用于比较运算的位运算符。Updated bitmask：整型位掩码，表示实际更新或插入的列。Comparison operator：比较运算符。使用等于比较运算符（=）检查updated_bitmask中指定的所有列是否都实际进行了更新。使用关于运算符（）检查updated_bitmask中指定的任何一 列或某些列是否已更新。Column_bitmask：要检测列的整型位掩码，用来检查是否已 更新或插入了这些列。SQL Server 2000网络数据库教程第9章存储过程和触发器:示例例9.5创建简单的触发器。use dzswgocreate trigger Messagel on 产品 for insertupdate5deleteasRAISERROR(13001,16,10)SQL Server 2000网络数据库教程第9章存储过程和触发器触发器虽然在强制数据完整性方面有着十分重要的地 位，但是在创建和使用中也有一些限制：1)CREATE TRIGGER语句只能作为批处理的第一条语句，且 只能用于一个表中。2)约束是在操作执行之前发生作用；触发器是在操作执行之后 发生作用。在表中如果既有约束又有触发器，则在执行中，约束优先于触发器。而以恶如果在操作中触发器与约束发生 冲突，触发器将不执行。SQL Server 2000网络数据库教程第9章存储过程和触发器3 触发器一定要建立在永久表上，而不能在视图和临时表中创 建，但是在触发器中可以引用视图或临时表。当触发器引用 视图或者临时表，并产生两个特殊的表：deleted表和inserted 表。这两个表由系统进行创建和管理，用户不能直接修改其 中的内容，其结构与触发器相同，可以用于触发器的条件测 试。执行INSERT语句或UPDATE语句向表中插入数据行时,插入到触发器表中的行作为复本同时将被插入到inserted表中;执行DELETE语句或UPDATE语句删除表中数据时，触发表 中被删除的行作为复本被插入到deleted表中；执行UPDATE 语句修改数据时，先删除触发表中的旧行，并将该旧行插入 deleted表中，再插入新行，同时将新行插入到inserted表中。SQL Server 2000网络数据库教程第9章存储过程和触发器4)AFTER触发器不能引用inserted和deleted表中的text、ntext或 image列；INSTEAD OF触发器可以引用这些列。5)一个表中可以为其它数据库的对象，但是只能在当前数据 库中创建。6)虽然触发器与存储过程一样，其中可以包含返回结果集的 语句，但是最好不要让触发器返回数据值。7)触发器不允许带有参数，也不能够直接调用，必须由表中 的一定的数据变化，而被系统激活。SQL Server 2000网络数据库教程第9章存储过程和触发器8 触发器可以引用其他数据库以外的对象，但是只能在当前数 据库中创建；触发器不能在临时表上创建，但是可以引用临 时表。9 WRITETEXT和TRUNCATE TABLE语句者B不能激活触发器。10 如果视图上定义了WITH CHECK OPTION参数，则该视图不 能定义INSTEAD OF触发器。11 触发器创建定义中不允许包含以下Transact-SQL语句：ALTER DATABASE CREATE DATABASE DISK RESIZE、DISK INT DROP DATABASE LOAD DATABASE LOAD LOG RECONFIGURE RESTORE DATABASE RESTORE LOGoSQL Server 2000网络数据库教程第9章存储过程和触发器9.2.2 触发器的嵌套 触发器的嵌套是指在一个触发器执行操作中包含了另外一个触 发器的表，从而引发第二个触发器。当然被触发器激活的触发 器还可以再激活其他触发器，但是这种潜逃不是没有境的，最 多可以嵌套至32层，当超出界限时，触发器将自动终止。触发方式可以分为两种：直接递归和间接递归。直接递归指当 触发器被激发执行的操作又使用一个触发器再次激发。间接递 归指当触发器被激发执行的操作会更改另一个触发器的表，从 而激发第二个表。SQL Server 2000网络数据库教程第9章存储过程和触发器9.2.3 删除触发器用DROP TRIGGER语句可以删除不再需要的触发器，此时原来的触发表以及表中的数据不影响。如果删除 表，则表中所有的触发器都将被自动删除。删除触发器语法格式为：DROP TRIGGERtrigger.n参数说明 trigger：要删除触发器的名称。SQL Server 2000网络数据库教程第9章存储过程和触发器:示例例9.8删除触发器USE dzswIF EXISTS(SELECT name FROM sysobjects WHERE name=tMessagel1 AND type=fTR!)DROP TRIGGER Messagelgo结束SQL Server 2000网络数据库教程第9章存储过程和触发器SQL Server 2000网络数据库教程