第8章存储过程与触发器.ppt

《第8章存储过程与触发器.ppt》由会员分享，可在线阅读，更多相关《第8章存储过程与触发器.ppt（39页珍藏版）》请在优知文库上搜索。

1、将一些将一些T-SQL语句打包成一个数据库对象语句打包成一个数据库对象(存储存储过程或触发器过程或触发器)并存储在并存储在SQL Server服务器上，服务器上，等到需要时，就调用或触发这些等到需要时，就调用或触发这些T-SQL语句包。语句包。本章教与学的建议：本章教与学的建议：教师使用教师使用4课时边讲解示例，边安排练习，学课时边讲解示例，边安排练习，学生在教师的指导下使用生在教师的指导下使用4课时在课内练习和检查课时在课内练习和检查。本章目标是通过学习，能够实施存储过程和触发本章目标是通过学习，能够实施存储过程和触发器应用管理。器应用管理。本章学习任务本章学习任务q 存储过程概述存储过程概

2、述 存储过程定义与特点、类型存储过程定义与特点、类型q 创建和执行存储过程创建和执行存储过程 创建和执行带参数或不带参数的存储过程、创建和执行带参数或不带参数的存储过程、创建和使用扩展存储过程创建和使用扩展存储过程q 修改和删除存储过程修改和删除存储过程 查看、修改、删除存储过程查看、修改、删除存储过程q 创建和管理触发器创建和管理触发器 触发器概述、创建触发器、管理触发器触发器概述、创建触发器、管理触发器q当开发一个应用程序时，为了易于修改和扩充当开发一个应用程序时，为了易于修改和扩充方便，我们经常会将负责不同功能的语句集中起方便，我们经常会将负责不同功能的语句集中起来而且按照用途分别独立放

3、置，以便能够反复调来而且按照用途分别独立放置，以便能够反复调用，而这些独立放置且拥有不同功能的语句，即用，而这些独立放置且拥有不同功能的语句，即是是“过程过程”(Procedure)。qSQL Server的存储过程是一组完成特定功能的的存储过程是一组完成特定功能的T-SQL语句集，经编译后以特定的名称存储在数语句集，经编译后以特定的名称存储在数据库中，用户通过指定存储过程的名字并给出参据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行存储过程。数（如果该存储过程带有参数）来执行存储过程。存储过程特点存储过程特点q允许模块化的程序设计允许模块化的程序设计q更快的执行速

4、度更快的执行速度q有效降低网络流量有效降低网络流量q较好的安全机制较好的安全机制存储过程类型存储过程类型q系统存储过程系统存储过程q本地存储过程本地存储过程q临时存储过程临时存储过程q远程存储过裎远程存储过裎q扩展存储过程扩展存储过程q使用对象资源管理器创建存储过程使用对象资源管理器创建存储过程q使用使用SQL命令创建存储过程命令创建存储过程CREATE PROCEDURE procedure_nameAS sql_statementsprocedure_name为所创建的存储过程的名字；为所创建的存储过程的名字；sql_statements为在存储过程中需要执行的数据库操作。为在存储过程中需

5、要执行的数据库操作。q 存储过程通过参数来与调用它的程序通信。在存储过程通过参数来与调用它的程序通信。在程序调用存储过程时，可以通过输入参数将数据程序调用存储过程时，可以通过输入参数将数据传给存储过程，存储过程可以通过输出参数和返传给存储过程，存储过程可以通过输出参数和返回值将数据返回给调用它的程序。回值将数据返回给调用它的程序。q 创建带参数的存储过程的语法格式创建带参数的存储过程的语法格式CREATE PROCEDURE procedure_nameparameter data_type=DEFAULTOUTPUT,nWITHRECOMPILE|ENCRYPTION|RECOMPILE,E

6、NCRYPTINAS sql_ statements q 使用参数名传送参数值使用参数名传送参数值 EXECUTE procedure_name parameter_name=value,nprocedure_name为存储过程名；为存储过程名；parameter_name为输入参数名；为输入参数名；value为传递给输入参数的值。为传递给输入参数的值。q 如果存储过程中有输入参数，在执行存储过程如果存储过程中有输入参数，在执行存储过程时没有给出参数，则系统会显示错误提示。时没有给出参数，则系统会显示错误提示。q 按位置传送参数值按位置传送参数值 EXECUTE proc_name value

7、1,value2,proc_name为存储过程名；为存储过程名；value1,value2,为传为传递给各输入参数的值。递给各输入参数的值。q 具有默认值的存储过程具有默认值的存储过程例如，针对一个表，创建一个存储过程，执行存例如，针对一个表，创建一个存储过程，执行存储过程时将向数据表插入一条记录，新记录的值储过程时将向数据表插入一条记录，新记录的值由参数提供，如果未提供某个字段的值时，由参由参数提供，如果未提供某个字段的值时，由参数的默认值代替。数的默认值代替。q 创建具有返回值的存储过程创建具有返回值的存储过程parameter_name datatype=default OUTPUT ,

8、nparameter_name存储过程的输出参数名，必存储过程的输出参数名，必须以符号为前缀。存储过程通过该参数返回结须以符号为前缀。存储过程通过该参数返回结果。果。datatype指明输出参数的数据类型，它既可以是指明输出参数的数据类型，它既可以是系统提供的数据类型，也可以是用户自定义的数系统提供的数据类型，也可以是用户自定义的数据类型，但必须是除据类型，但必须是除text和和image以外的数据类型。以外的数据类型。default指定输出参数的默认值。指定输出参数的默认值。OUTPUT指明参数为输出参数。指明参数为输出参数。q 执行具有返回值的存储过程执行具有返回值的存储过程EXECUTE

9、 return_status=procedure_name|procudure_name_varparameter_name=value|variableOUTPUT,nWITH RECOMPILEprocedure_name:需执行的存储过程的名字；需执行的存储过程的名字；parameter_name=value|variable为输入参数传为输入参数传递值；递值；parameter_name=variable OUTPUT为传递给输为传递给输出参数的变量，出参数的变量，variable用来存放返回参数的值。用来存放返回参数的值。OUTPUT指明这是一个输出传递参数，与响应的指明这是一个输出

10、传递参数，与响应的存储过程中的输出参数相匹配。存储过程中的输出参数相匹配。q 存储过程的重编译处理存储过程的重编译处理q 存储过程的处理存储过程的处理在创建存储过程时，在创建存储过程时，SQL Server需要对存储过程需要对存储过程中的语句进行语法检查。如果存储过程定义中存中的语句进行语法检查。如果存储过程定义中存在语法错误，将返回错误，并且将不能创建该存在语法错误，将返回错误，并且将不能创建该存储过程。如果语法正确，则存储过程的文本将存储过程。如果语法正确，则存储过程的文本将存储在储在syscomments系统表中。系统表中。q 在建立存储过程时设定重编译选项在建立存储过程时设定重编译选项

11、CREATE PROCEDURE WITH RECOMPILE通过在创建时设定重编译选项，在每次执行时对通过在创建时设定重编译选项，在每次执行时对存储过程进行重编译处理。存储过程进行重编译处理。q 存储过程的重编译处理存储过程的重编译处理q 在执行存储过程时设定重编译选项在执行存储过程时设定重编译选项EXECUTE procedure_nameparameter_name=variable OUTPUT WITH RECOMPILE通过在执行存储过程的通过在执行存储过程的EXECUTE语句中设定语句中设定WITH RECOMPILE选项，可以在执行存储过程时选项，可以在执行存储过程时重新编译该

12、存储过程。重新编译该存储过程。q通过系统存储过程设定重编译选项通过系统存储过程设定重编译选项EXEC sp_recompile OBJECTsp_recompile为用于重编译存储过程的系统存储过为用于重编译存储过程的系统存储过程；程；OBJECT为当前数据库中的存储过程、触发器、为当前数据库中的存储过程、触发器、表或视图的名称。表或视图的名称。q扩展存储过程是使用编程语言创建的外部程序，扩展存储过程是使用编程语言创建的外部程序，它与通常的存储过程不同。存储过程是一系列预它与通常的存储过程不同。存储过程是一系列预编译的编译的T-SQL语句，而扩展存储过程是对动态链语句，而扩展存储过程是对动态链

13、接库函数的调用。接库函数的调用。q使用使用T-SQL语句注册扩展存储过程语句注册扩展存储过程sp_addextendedproc functname=procedure,dllname=dll q使用对象资源管理器注册扩展存储过程使用对象资源管理器注册扩展存储过程q使用扩展存储过程使用扩展存储过程扩展存储过程注册到扩展存储过程注册到SQL Server中之后，这个扩中之后，这个扩展存储过程就可以像普通存储过程一样被使用。展存储过程就可以像普通存储过程一样被使用。存储过程被创建以后，它的名字存储在系统表存储过程被创建以后，它的名字存储在系统表sysobjects中；它的源代码存放在系统表中；它的

14、源代码存放在系统表syscomments中。我们既可以通过对象资源管理中。我们既可以通过对象资源管理器查看存储过程的源代码，也可以通过器查看存储过程的源代码，也可以通过SQL Server提供的系统存储过程来查看用户创建的存提供的系统存储过程来查看用户创建的存储过程信息。储过程信息。使用系统存储过程查看存储过程源代码的语句是：使用系统存储过程查看存储过程源代码的语句是：sp_helptext procedure_name如果在创建存储过程时使用了如果在创建存储过程时使用了WITH ENCRYPTION选项，那么就无法查看到存储过选项，那么就无法查看到存储过程的源代码。程的源代码。使用使用 AL

15、TER PROCEDURE命令命令ALTER PROCEDURE procedure_nameparameter data_type=DEFAULTOUTPUT,nWITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYTIONAS Sql_statement,nprocedure_name 欲修改存储过程名称欲修改存储过程名称parameter 输入和输出参数输入和输出参数data_type 参数的数据类型参数的数据类型default 指定的默认值指定的默认值WITH RECOMPILE 重编译选项重编译选项WITH ENCRYPTION加密选项加密选项q重新命名存储

16、过程重新命名存储过程sp_rename old_procedure_name,new_procedure_name q使用使用DROP PROCEDURE语句语句DROP PROCEDURE procedure_name,nDROP命令可将一个或多个存储过程或者存储过命令可将一个或多个存储过程或者存储过程组从当前数据库中删除。程组从当前数据库中删除。q触发器是一种特殊的存储过程，它在特定语言触发器是一种特殊的存储过程，它在特定语言事件发生时自动执行，通常用于实现强制业务规事件发生时自动执行，通常用于实现强制业务规则和数据完整性。则和数据完整性。q触发器的主要作用是实现由主键和外键所不能触发器的主要作用是实现由主键和外键所不能保证的复杂的参照完整性和数据一致性。除此之保证的复杂的参照完整性和数据一致性。除此之外，触发器还有以下作用。外，触发器还有以下作用。m 触发器可以对数据库进行级联修改。触发器可以对数据库进行级联修改。m 实现比实现比CHECK约束更为复杂的限制。约束更为复杂的限制。m 比较数据修改前后的差别。比较数据修改前后的差别。m 强制表的修改要合乎业务规则。强制表的修改要合乎