SQL_Server_2008基础教程_(011)

2020年2月22日第1页第11章存储过程、触发器和函数本章概述本章要点本章内容2020年2月22日第2页本章概述如何提高Transact-SQL语句的执行效率呢？如何加强数据库中数据完整性的机制呢？这些问题的解决都依赖于数据库的编程对象。典型的数据库编程对象包括视图、存储过程、触发器、函数等。视图在第10章已经讲过了，本章将讲述其他常用的数据库编程对象。存储过程是一个可重用的代码模块，可以高效率地完成指定的操作。触发器是一种特殊类型的存储过程，可以实现自动化的操作。用户定义函数是由用户根据应用程序的需要而定义的可以完成特定操作的函数。本章将全面研究存储过程、触发器、用户定义函数等特点和使用方式。2020年2月22日第3页本章要点存储过程的特点、类型和作用使用CREATEPROCEDURE语句创建存储过程存储过程的执行方式DML触发器的特点和创建方式DML触发器的工作原理使用CREATETRIGGER语句创建DML触发器DDL触发器的特点和创建方式用户定义函数的类型和特点使用CREATEFUNCTION语句创建用户定义函数2020年2月22日第4页本章内容11.1存储过程11.2触发器11.3用户定义函数11.4本章小结11.1存储过程存储过程可以提高应用程序的设计效率和增强系统的安全性。本节将全面介绍存储过程的特点、类型、创建、执行等内容。2020年2月22日第5页存储过程的特点和类型存储过程是一个可重用的代码模块，可以高效率地完成指定的操作。在MicrosoftSQLServer2008系统中，可以使用Transact-SQL语言编写存储过程，也可以使用CLR方式编写存储过程。使用CLR编写存储过程是MicrosoftSQLServer2008系统与.NET框架紧密集成的一种表现形式。2020年2月22日第6页类型在MicrosoftSQLServer2008系统中，提供了3种基本的存储过程类型，即用户定义的存储过程、扩展存储过程和系统存储过程。2020年2月22日第7页创建存储过程的规则在设计和创建存储过程时，应该满足一定的约束和规则。只有满足了这些约束和规则，才可以创建有效的存储过程。虽然说在CREATEPROCEDURE语句中可以包括任意数量和类型的Transact-SQL语句，但是某些特殊的语句是不能包含在存储过程定义中的。2020年2月22日第8页使用CREATEPROCEDURE语句在MicrosoftSQLServer2008系统中，可以使用CREATEPROCEDURE语句创建存储过程。需要强调的是，必须具有CREATEPROCEDURE权限才能创建存储过程，存储过程是架构作用域中的对象，只能在本地数据库中创建存储过程。在创建存储过程时，应该指定所有的输入参数、执行数据库操作的编程语句、返回至调用过程或批处理表明成功或失败的状态值、捕捉和处理潜在错误的错误处理语句。2020年2月22日第9页CREATEPROCEDURE语句CREATEPROCEDURE语句的基本语法形式如下所示：CREATEPROCEDUREprocedure_nameparameter_namedata_type,…WITHprocedure_optionASsql_statement2020年2月22日第10页创建一个简单的存储过程2020年2月22日第11页创建一个带参数的存储过程2020年2月22日第12页创建带有OUTPUT类型参数的存储过程2020年2月22日第13页执行存储过程在MicrosoftSQLServer2008系统中，可以使用EXECUTE语句执行存储过程。EXECUTE语句也可以简写为EXEC。如果将要执行的存储过程需要参数，那么应该在存储过程名称后面带上参数值。2020年2月22日第14页执行简单的存储过程2020年2月22日第15页为存储过程直接提供参数值2020年2月22日第16页为存储过程间接提供参数值2020年2月22日第17页使用OUTPUT参数执行存储过程2020年2月22日第18页修改和删除存储过程在MicrosoftSQLServer2008系统中，可以使用ALTERPROCEDURE语句修改已经存在的存储过程。修改存储过程，不是删除和重建存储过程，其目的是保持存储过程的权限不发生变化。例如，如果修改HumanResources.GetEmployeeInfo存储过程，那么与该存储过程对象相关的权限将不会发生任何变化。但是，如果删除HumanResources.GetEmp-loyeeInfo存储过程并且重新创建同名的存储过程，那么与该存储过程对象相关的权限都需要重新定义。2020年2月22日第19页删除存储过程2020年2月22日第20页存储过程的执行过程存储过程创建之后，在第一次执行时需要经过语法分析阶段、解析阶段、编译阶段和执行阶段。2020年2月22日第21页查看存储过程的信息在MicrosoftSQLServer2008系统中，可以使用系统存储过程和目录视图查看有关存储过程的信息。2020年2月22日第22页使用OBJECT_DEFINITION元数据函数2020年2月22日第23页11.2触发器MicrosoftSQLServer2008系统提供了两种强制业务逻辑和数据完整性的机制，即约束技术和触发器技术。第9章已经讲过了约束技术，本节将讲述触发器技术。2020年2月22日第24页触发器的概念和类型一般地认为，触发器是一种特殊类型的存储过程，它包括了大量的Transact-SQL语句。但是触发器又与存储过程不同，存储过程可以由用户直接调用执行，而触发器不能被直接调用执行，它只能自动执行。按照触发事件的不同，可以把MicrosoftSQLServer2008系统提供的触发器分成两种类型，即DML触发器和DDL触发器。2020年2月22日第25页DML触发器当数据库中发生数据操纵语言(DataManipulationLanguage，简称为DML)事件时将调用DML触发器。DML事件包括在指定表或视图中修改数据的INSERT语句、UPDATE语句或DELETE语句。在DML触发器中，可以执行查询其他表的操作，也可以包含更加复杂的Transact-SQL语句。DML触发器将触发器本身和触发事件的语句作为可以在触发器内回滚的单个事务对待。也就是说，在执行触发器操作过程中，如果检测到错误发生，则整个触发事件语句和触发器操作的事务自动回滚。2020年2月22日第26页DML触发器当数据库中发生数据操纵语言(DataManipulationLanguage，简称为DML)事件时将调用DML触发器。DML事件包括在指定表或视图中修改数据的INSERT语句、UPDATE语句或DELETE语句。在DML触发器中，可以执行查询其他表的操作，也可以包含更加复杂的Transact-SQL语句。DML触发器将触发器本身和触发事件的语句作为可以在触发器内回滚的单个事务对待。也就是说，在执行触发器操作过程中，如果检测到错误发生，则整个触发事件语句和触发器操作的事务自动回滚。2020年2月22日第27页DML触发器的类型按照触发器事件类型的不同，可以把MicrosoftSQLServer2008系统提供的DML触发器分成3种类型，即INSERT类型、UPDATE类型和DELETE类型。这也是DML触发器的基本类型。2020年2月22日第28页创建DML触发器创建DML触发器的CREATETRIGGER语句的基本语法形式如下：CREATETRIGGERtrigger_nameONtable_name_or_view_nameWITHENCRYPTION{FOR|AFTER|INSTEADOF}{[DELETE][,][INSERT][,][UPDATE]}ASsql_statement2020年2月22日第29页使用sys.triggers对象目录视图2020年2月22日第30页使用sp_helptext系统存储过程2020年2月22日第31页DML触发器的工作原理现在介绍触发器是如何工作的。通过了解触发器的工作原理，可以更好地使用触发器，写出效率更高的触发器。下面主要介绍INSERT、DELETE和UPDATE类型触发器的工作原理。向表中插入数据时，INSERT触发器触发执行。当INSERT触发器触发时，新的记录增加到触发器表中和inserted表中。inserted表是一个逻辑表，保存了所插入记录的备份，允许用户参考INSERT语句中数据。触发器可以检查inserted表，来确定该触发器的操作是否应该执行和如何执行。在inserted表中的记录，总是触发器表中一行或多行记录的冗余。2020年2月22日第32页DELETE触发器当触发一个DELETE触发器时，被删除的记录放在一个特殊的deleted表中。deleted表是一个逻辑表，用来保存已经从表中删除的记录。该deleted表允许参考原来的DELETE语句删除的已经记录在日志中的数据。2020年2月22日第33页UPDATE触发器修改一条记录就等于插入一条新记录同时删除一条旧记录。同样，UPDATE语句也可以看成是由删除一条记录的DELETE语句和增加一条记录的INSERT语句组成。当在某一个有UPDATE触发器表的上面修改一条记录时，表中原来的记录移动到deleted表中，修改过的记录插入到了inserted表中。触发器可以检查deleted表和inserted表以及被修改的表，以便确定是否修改了多个行和应该如何执行触发器的操作。2020年2月22日第34页一个DML触发器示例为了更加全面地掌握开发触发器的步骤和技术，本节通过一个具体的示例，全面讲述使用Transact-SQL语言开发和创建触发器的技术。一般地，开发触发器的过程包括用户需求分析、确定触发器的逻辑结构、编写触发器代码和测试触发器。2020年2月22日第35页创建accountData表2020年2月22日第36页创建auditAccountData表创建t_accountData_insert触发器创建t_accountData_delete触发器一组插入数据的操作审计到的插入数据的操作一组删除数据的操作审计到的删除数据的操作DDL触发器DDL触发器与DML触发器有许多类似的地方，都可以自动触发完成规定的操作或使用CREATETRIGGER语句创建等，但是也有一些不同的地方。例如，DDL触发器的触发事件主要是CREATE、ALTER、DROP以及GRANT、DENY、REVOKE等语句，并且触发的时间条件只有AFTER，没有INSTEADOF。CREATETRIGGER创建DDL触发器的CREATETRIGGER语句的基本语法形式如下：CREATETRIGGERtrigger_nameON{ALLSERVER|DATABASE}WITHENCRYPTION{FOR|AFTER}{event_type}ASsql_statement定义一个DDL触发器删除表的操作失败11.3用户定义函数在MicrosoftSQLServer2008系统中，用户定义函数是接受参数、执行操作并且将运算结果以值的形式返回的例程。这种返回值既可以是单个标量值，也可以是一个结果集。在MicrosoftSQLServer2008系统中，用户定义函数可以使用Transact-SQL语言编写，也可以使用.NET编程语言来编写。用户定义函数的特点使用Transact-SQL编写的用户定义函数通过缓存计划并在重复执行时重用它来降低Transact-SQL代码的编译开销。也就是说，每次使用用户定义函数时均无需重新解析和重新优化，从而大大缩短了执行时间。减少网络流量，基于某种无法用单一标量表达式表示的复杂约束来过滤数据的操作，可以表示为函数。然后，该函数可以在WHERE子句中调用，以减少发送至客户端的数字