《存储过程与触发器》PPT课件.ppt

第8章 存储过程与触发器,2019/5/19,2 / 39,将一些T-SQL语句打包成一个数据库对象(存储过程或触发器)并存储在SQL Server服务器上，等到需要时，就调用或触发这些T-SQL语句包。 本章教与学的建议： 教师使用4课时边讲解示例，边安排练习，学生在教师的指导下使用4课时在课内练习和检查。本章目标是通过学习，能够实施存储过程和触发器应用管理。,第8章 存储过程与触发器,2019/5/19,3 / 39,本章学习任务 存储过程概述 存储过程定义与特点、类型 创建和执行存储过程 创建和执行带参数或不带参数的存储过程、创建和使用扩展存储过程 修改和删除存储过程 查看、修改、删除存储过程 创建和管理触发器 触发器概述、创建触发器、管理触发器,第8章 存储过程与触发器,2019/5/19,4 / 39,存储过程的概述,当开发一个应用程序时，为了易于修改和扩充方便，我们经常会将负责不同功能的语句集中起来而且按照用途分别独立放置，以便能够反复调用，而这些独立放置且拥有不同功能的语句，即是“过程”(Procedure)。 SQL Server的存储过程是一组完成特定功能的T-SQL语句集，经编译后以特定的名称存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行存储过程。,2019/5/19,5 / 39,存储过程特点与类型,存储过程特点 允许模块化的程序设计 更快的执行速度 有效降低网络流量 较好的安全机制,存储过程类型 系统存储过程 本地存储过程 临时存储过程 远程存储过裎 扩展存储过程,2019/5/19,6 / 39,不带参数的存储过程,使用对象资源管理器创建存储过程 使用SQL命令创建存储过程 CREATE PROCEDURE procedure_name AS sql_statements procedure_name 为所创建的存储过程的名字； sql_statements 为在存储过程中需要执行的数据库操作。,2019/5/19,7 / 39,不带参数的存储过程,2019/5/19,8 / 39,不带参数的存储过程,2019/5/19,9 / 39,带参数的存储过程,存储过程通过参数来与调用它的程序通信。在程序调用存储过程时，可以通过输入参数将数据传给存储过程，存储过程可以通过输出参数和返回值将数据返回给调用它的程序。 创建带参数的存储过程的语法格式 CREATE PROCEDURE procedure_name parameter data_type =DEFAULTOUTPUT ,n WITHRECOMPILE | ENCRYPTION| RECOMPILE, ENCRYPTIN AS sql_ statements,2019/5/19,10 / 39,带参数的存储过程,使用参数名传送参数值 EXECUTE procedure_name parameter_name = value ,n procedure_name为存储过程名； parameter_name为输入参数名； value为传递给输入参数的值。 如果存储过程中有输入参数，在执行存储过程时没有给出参数，则系统会显示错误提示。,2019/5/19,11 / 39,带参数的存储过程,2019/5/19,12 / 39,带参数的存储过程,2019/5/19,13 / 39,带参数的存储过程,按位置传送参数值 EXECUTE proc_name value1,value2, proc_name为存储过程名；value1,value2,为传递给各输入参数的值。 具有默认值的存储过程 例如，针对一个表，创建一个存储过程，执行存储过程时将向数据表插入一条记录，新记录的值由参数提供，如果未提供某个字段的值时，由参数的默认值代替。,2019/5/19,14 / 39,带参数的存储过程,2019/5/19,15 / 39,带参数的存储过程,2019/5/19,16 / 39,带参数的存储过程,创建具有返回值的存储过程 parameter_name datatype=default OUTPUT ,n parameter_name存储过程的输出参数名，必须以符号为前缀。存储过程通过该参数返回结果。 datatype指明输出参数的数据类型，它既可以是系统提供的数据类型，也可以是用户自定义的数据类型，但必须是除text和image以外的数据类型。 default指定输出参数的默认值。 OUTPUT指明参数为输出参数。,2019/5/19,17 / 39,带参数的存储过程,2019/5/19,18 / 39,带参数的存储过程,执行具有返回值的存储过程 EXECUTE return_status= procedure_name | procudure_name_var parameter_name=value|variableOUTPUT ,nWITH RECOMPILE procedure_name:需执行的存储过程的名字； parameter_name=value | variable为输入参数传递值； parameter_name=variable OUTPUT为传递给输出参数的变量，variable用来存放返回参数的值。OUTPUT指明这是一个输出传递参数，与响应的存储过程中的输出参数相匹配。,2019/5/19,19 / 39,带参数的存储过程,2019/5/19,20 / 39,带参数的存储过程,存储过程的重编译处理 存储过程的处理 在创建存储过程时，SQL Server需要对存储过程中的语句进行语法检查。如果存储过程定义中存在语法错误，将返回错误，并且将不能创建该存储过程。如果语法正确，则存储过程的文本将存储在syscomments系统表中。 在建立存储过程时设定重编译选项 CREATE PROCEDURE WITH RECOMPILE 通过在创建时设定重编译选项，在每次执行时对存储过程进行重编译处理。,2019/5/19,21 / 39,带参数的存储过程,存储过程的重编译处理 在执行存储过程时设定重编译选项 EXECUTE procedure_name parameter_name=variable OUTPUT WITH RECOMPILE 通过在执行存储过程的EXECUTE语句中设定WITH RECOMPILE选项，可以在执行存储过程时重新编译该存储过程。 通过系统存储过程设定重编译选项 EXEC sp_recompile OBJECT sp_recompile为用于重编译存储过程的系统存储过程； OBJECT为当前数据库中的存储过程、触发器、表或视图的名称。,2019/5/19,22 / 39,带参数的存储过程,2019/5/19,23 / 39,扩展存储过程的创建和使用,扩展存储过程是使用编程语言创建的外部程序，它与通常的存储过程不同。存储过程是一系列预编译的T-SQL语句，而扩展存储过程是对动态链接库函数的调用。 使用T-SQL语句注册扩展存储过程 sp_addextendedproc functname= procedure, dllname= dll 使用对象资源管理器注册扩展存储过程 使用扩展存储过程 扩展存储过程注册到SQL Server中之后，这个扩展存储过程就可以像普通存储过程一样被使用。,2019/5/19,24 / 39,修改和删除存储过程,存储过程被创建以后，它的名字存储在系统表sysobjects中；它的源代码存放在系统表syscomments中。我们既可以通过对象资源管理器查看存储过程的源代码，也可以通过SQL Server提供的系统存储过程来查看用户创建的存储过程信息。 使用系统存储过程查看存储过程源代码的语句是： sp_helptext procedure_name 如果在创建存储过程时使用了WITH ENCRYPTION选项，那么就无法查看到存储过程的源代码。,2019/5/19,25 / 39,存储过程的查看、修改、删除,2019/5/19,26 / 39,存储过程的查看、修改、删除,使用 ALTER PROCEDURE命令 ALTER PROCEDURE procedure_name parameter data_type=DEFAULTOUTPUT ,n WITHRECOMPILE | ENCRYPTION | RECOMPILE,ENCRYTION AS Sql_statement,n procedure_name 欲修改存储过程名称 parameter 输入和输出参数 data_type 参数的数据类型 default 指定的默认值 WITH RECOMPILE 重编译选项 WITH ENCRYPTION加密选项,2019/5/19,27 / 39,存储过程的查看、修改、删除,2019/5/19,28 / 39,存储过程的查看、修改、删除,重新命名存储过程 sp_rename old_procedure_name, new_procedure_name 使用DROP PROCEDURE语句 DROP PROCEDURE procedure_name,n DROP命令可将一个或多个存储过程或者存储过程组从当前数据库中删除。,2019/5/19,29 / 39,创建和管理触发器,触发器是一种特殊的存储过程，它在特定语言事件发生时自动执行，通常用于实现强制业务规则和数据完整性。 触发器的主要作用是实现由主键和外键所不能保证的复杂的参照完整性和数据一致性。除此之外，触发器还有以下作用。 触发器可以对数据库进行级联修改。 实现比CHECK约束更为复杂的限制。 比较数据修改前后的差别。 强制表的修改要合乎业务规则。,2019/5/19,30 / 39,触发器概述,触发器也是一种存储过程，一种在基表被修改时自动执行的内嵌过程，主要通过事件进行触发而被执行的，而存储过程可以通过存储过程名字而被直接调用。当对某一表进行诸如UPDATE、INSERT、DELETE这些操作时，SQL Server就会自动执行触发器所定义的SQL语句。从而确保对数据的处理必须符合由这些SQL语句所定义的规则。触发器的主要作用就是其能够实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性。,2019/5/19,31 / 39,创建触发器,使用对象资源管理器创建触发器 使用命令创建触发器 CREATE TRIGGER trigge_name ON table | view FOR | AFTER | INSTEAD OFINSERT,UPDATE,DELETE WITH ENCRYPTION AS IF UPDATE(column_name) andor UPDATE(column_name) sql_statesments,2019/5/19,32 / 39,创建触发器,2019/5/19,33 / 39,创建触发器,2019/5/19,34 / 39,管理触发器,使用系统存储过程查看触发器信息 系统存储过程sp_help、sp_helptext和sp_depends分别提供有关触发器的不同信息。 使用系统表查看触发器信息 用户还可以通过查询系统表sysobjects得到触发器的相关信息。 使用对象资源管理器查看触发器的相关信息 使用sp_rename命令修改触发器的名字 sp_rename oldname, newname 其中，oldname为触发器原来的名称，newname为触发器的新名称。 通过对象资源管理器修改触发器定义,2019/5/19,35 / 39,管理触发器,使用ALERT TRIGGER命令修改触发器 ALTER TRIGGER trigge_name ON table|view FOR|AFTER|INSTEAD OF INSERT,UPDATE,DELETEWITH ENCRYPTION AS IF UPDATE(column_name) and|or UPDATE(column name) sql_statesments,2019/5/19,36 / 39,管理触发器,使用DROP TRIGGER删除触发器 DROP TRIGGER trigger_name 使用对象资源管理器删除触发器，右击将要删除触发器，在弹出菜单中选择“删除”，接着再单击“删除对象”对话框中的“确定”即可。 删除数据表将自动删除与数据表相关的触发器。 当不再需要某个触发器时，可以将其删除。 删除了触发器后，它所基于的表和数据不会受到影响。,2019/5/19,37 / 39,管理触发器,禁止和启用触发器 ALTER TABLE table_name ENABLE | DISABLE TRIGGER ALL | trigger_name,n 在有些情况下，用户希望暂停触发器的作用，但并不删除它，这时就可以通过DISABLE TRIGGER语句使触发器无效； DISABLE TRIGGER指定禁用trigger_name 要使DML触发器重新有效，可使用ENABLE TRIGGER语句 ENABLE TRIGGER指定启用trigger_name,2019/5/19,38 / 39, 实验目的 理解存储过程和触发器的概念。 学会使用对象资源管理器和SQL编辑器创建存储过程和触发器。 学会存储过程和触发器的管理方法。 实验准备 在Windows Server 2000以上版本的微机上安装MS SQL Server 2005 ； 创建数据库和所需要的数据表 ；