存储过程与触发器课件

1、第8章 存储过程与触发器 将一些T-SQL语句打包成一个数据库对象(存储过程或触发器)并存储在SQL Server服务器上，等到需要时，就调用或触发这些T-SQL语句包。本章教与学的建议：教师使用4课时边讲解示例，边安排练习，学生在教师的指导下使用4课时在课内练习和检查。本章目标是通过学习，能够实施存储过程和触发器应用管理。第8章 存储过程与触发器7/31/20222本章学习任务 存储过程概述 存储过程定义与特点、类型 创建和执行存储过程 创建和执行带参数或不带参数的存储过程、创建和使用扩展存储过程 修改和删除存储过程 查看、修改、删除存储过程 创建和管理触发器 触发器概述、创建触发器、管理触

2、发器第8章 存储过程与触发器7/31/20223存储过程的概述 当开发一个应用程序时，为了易于修改和扩充方便，我们经常会将负责不同功能的语句集中起来而且按照用途分别独立放置，以便能够反复调用，而这些独立放置且拥有不同功能的语句，即是“过程”(Procedure)。SQL Server的存储过程是一组完成特定功能的T-SQL语句集，经编译后以特定的名称存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行存储过程。7/31/20224存储过程特点与类型 存储过程特点允许模块化的程序设计更快的执行速度有效降低网络流量较好的安全机制存储过程类型系统存储过程本地存储过程临

3、时存储过程远程存储过裎扩展存储过程7/31/20225不带参数的存储过程使用对象资源管理器创建存储过程使用SQL命令创建存储过程CREATE PROCEDURE procedure_nameAS sql_statementsprocedure_name为所创建的存储过程的名字；sql_statements为在存储过程中需要执行的数据库操作。7/31/20226不带参数的存储过程7/31/20227不带参数的存储过程7/31/20228带参数的存储过程 存储过程通过参数来与调用它的程序通信。在程序调用存储过程时，可以通过输入参数将数据传给存储过程，存储过程可以通过输出参数和返回值将数据返回给调用

4、它的程序。 创建带参数的存储过程的语法格式CREATE PROCEDURE procedure_nameparameter data_type =DEFAULTOUTPUT ,nWITHRECOMPILE | ENCRYPTION| RECOMPILE, ENCRYPTINAS sql_ statements 7/31/20229带参数的存储过程 使用参数名传送参数值 EXECUTE procedure_name parameter_name = value ,nprocedure_name为存储过程名；parameter_name为输入参数名；value为传递给输入参数的值。 如果存储过程中

5、有输入参数，在执行存储过程时没有给出参数，则系统会显示错误提示。7/31/202210带参数的存储过程 7/31/202211带参数的存储过程 7/31/202212带参数的存储过程 按位置传送参数值 EXECUTE proc_name value1,value2,proc_name为存储过程名；value1,value2,为传递给各输入参数的值。 具有默认值的存储过程例如，针对一个表，创建一个存储过程，执行存储过程时将向数据表插入一条记录，新记录的值由参数提供，如果未提供某个字段的值时，由参数的默认值代替。7/31/202213带参数的存储过程 7/31/202214带参数的存储过程 7/3

6、1/202215带参数的存储过程 创建具有返回值的存储过程parameter_name datatype=default OUTPUT ,nparameter_name存储过程的输出参数名，必须以符号为前缀。存储过程通过该参数返回结果。datatype指明输出参数的数据类型，它既可以是系统提供的数据类型，也可以是用户自定义的数据类型，但必须是除text和image以外的数据类型。default指定输出参数的默认值。OUTPUT指明参数为输出参数。7/31/202216带参数的存储过程 7/31/202217带参数的存储过程 执行具有返回值的存储过程EXECUTE return_status=p

7、rocedure_name | procudure_name_varparameter_name=value|variableOUTPUT ,nWITH RECOMPILEprocedure_name:需执行的存储过程的名字；parameter_name=value | variable为输入参数传递值；parameter_name=variable OUTPUT为传递给输出参数的变量，variable用来存放返回参数的值。OUTPUT指明这是一个输出传递参数，与响应的存储过程中的输出参数相匹配。7/31/202218带参数的存储过程 7/31/202219带参数的存储过程 存储过程的重编译处

8、理 存储过程的处理在创建存储过程时，SQL Server需要对存储过程中的语句进行语法检查。如果存储过程定义中存在语法错误，将返回错误，并且将不能创建该存储过程。如果语法正确，则存储过程的文本将存储在syscomments系统表中。 在建立存储过程时设定重编译选项CREATE PROCEDURE WITH RECOMPILE通过在创建时设定重编译选项，在每次执行时对存储过程进行重编译处理。7/31/202220带参数的存储过程 存储过程的重编译处理 在执行存储过程时设定重编译选项EXECUTE procedure_nameparameter_name=variable OUTPUT WITH

9、RECOMPILE通过在执行存储过程的EXECUTE语句中设定WITH RECOMPILE选项，可以在执行存储过程时重新编译该存储过程。通过系统存储过程设定重编译选项EXEC sp_recompile OBJECTsp_recompile为用于重编译存储过程的系统存储过程；OBJECT为当前数据库中的存储过程、触发器、表或视图的名称。7/31/202221带参数的存储过程 7/31/202222扩展存储过程的创建和使用 扩展存储过程是使用编程语言创建的外部程序，它与通常的存储过程不同。存储过程是一系列预编译的T-SQL语句，而扩展存储过程是对动态链接库函数的调用。使用T-SQL语句注册扩展存储

10、过程sp_addextendedproc functname= procedure, dllname= dll 使用对象资源管理器注册扩展存储过程使用扩展存储过程扩展存储过程注册到SQL Server中之后，这个扩展存储过程就可以像普通存储过程一样被使用。7/31/202223修改和删除存储过程存储过程被创建以后，它的名字存储在系统表sysobjects中；它的源代码存放在系统表syscomments中。我们既可以通过对象资源管理器查看存储过程的源代码，也可以通过SQL Server提供的系统存储过程来查看用户创建的存储过程信息。使用系统存储过程查看存储过程源代码的语句是：sp_helptex

11、t procedure_name 如果在创建存储过程时使用了WITH ENCRYPTION选项，那么就无法查看到存储过程的源代码。7/31/202224存储过程的查看、修改、删除 7/31/202225存储过程的查看、修改、删除 使用 ALTER PROCEDURE命令ALTER PROCEDURE procedure_nameparameter data_type=DEFAULTOUTPUT ,nWITHRECOMPILE | ENCRYPTION | RECOMPILE,ENCRYTIONAS Sql_statement,nprocedure_name 欲修改存储过程名称parameter

12、 输入和输出参数data_type 参数的数据类型default 指定的默认值WITH RECOMPILE 重编译选项WITH ENCRYPTION加密选项7/31/202226存储过程的查看、修改、删除 7/31/202227存储过程的查看、修改、删除 重新命名存储过程sp_rename old_procedure_name, new_procedure_name 使用DROP PROCEDURE语句DROP PROCEDURE procedure_name,nDROP命令可将一个或多个存储过程或者存储过程组从当前数据库中删除。7/31/202228创建和管理触发器 触发器是一种特殊的存储过

13、程，它在特定语言事件发生时自动执行，通常用于实现强制业务规则和数据完整性。触发器的主要作用是实现由主键和外键所不能保证的复杂的参照完整性和数据一致性。除此之外，触发器还有以下作用。 触发器可以对数据库进行级联修改。 实现比CHECK约束更为复杂的限制。 比较数据修改前后的差别。 强制表的修改要合乎业务规则。7/31/202229触发器概述触发器也是一种存储过程，一种在基表被修改时自动执行的内嵌过程，主要通过事件进行触发而被执行的，而存储过程可以通过存储过程名字而被直接调用。当对某一表进行诸如UPDATE、INSERT、DELETE这些操作时，SQL Server就会自动执行触发器所定义的SQL

14、语句。从而确保对数据的处理必须符合由这些SQL语句所定义的规则。触发器的主要作用就是其能够实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性。7/31/202230创建触发器 使用对象资源管理器创建触发器使用命令创建触发器CREATE TRIGGER trigge_nameON table | viewFOR | AFTER | INSTEAD OFINSERT,UPDATE,DELETEWITH ENCRYPTIONASIF UPDATE(column_name)andor UPDATE(column_name) sql_statesments7/31/202231创建触发器 7/3

15、1/202232创建触发器 7/31/202233管理触发器 使用系统存储过程查看触发器信息系统存储过程sp_help、sp_helptext和sp_depends分别提供有关触发器的不同信息。使用系统表查看触发器信息用户还可以通过查询系统表sysobjects得到触发器的相关信息。使用对象资源管理器查看触发器的相关信息使用sp_rename命令修改触发器的名字sp_rename oldname, newname其中，oldname为触发器原来的名称，newname为触发器的新名称。通过对象资源管理器修改触发器定义7/31/202234管理触发器 使用ALERT TRIGGER命令修改触发器A

16、LTER TRIGGER trigge_name ON table|viewFOR|AFTER|INSTEAD OFINSERT,UPDATE,DELETEWITH ENCRYPTIONASIF UPDATE(column_name)and|or UPDATE(column name)sql_statesments7/31/202235管理触发器 使用DROP TRIGGER删除触发器DROP TRIGGER trigger_name使用对象资源管理器删除触发器，右击将要删除触发器，在弹出菜单中选择“删除”，接着再单击“删除对象”对话框中的“确定”即可。删除数据表将自动删除与数据表相关的触发器

17、。当不再需要某个触发器时，可以将其删除。删除了触发器后，它所基于的表和数据不会受到影响。7/31/202236管理触发器 禁止和启用触发器ALTER TABLE table_name ENABLE | DISABLE TRIGGER ALL | trigger_name,n在有些情况下，用户希望暂停触发器的作用，但并不删除它，这时就可以通过DISABLE TRIGGER语句使触发器无效；DISABLE TRIGGER指定禁用trigger_name要使DML触发器重新有效，可使用ENABLE TRIGGER语句ENABLE TRIGGER指定启用trigger_name7/31/202237 实验目的 理解存储过程和触发器的概念。 学会使用对象资源管理器和SQL编辑器创建存储过程和触发器。 学会存储过程和触发器的管理方法。 实验准备 在Windows Server 2000以上版本的微机上安装MS SQL Server 2005 ； 创建数据库和所需要的数据表 ； 向各个数据表输入实验数据 。 实验内容和步骤