第11章 存储过程、触发器和函数

SQLServer2005基础教程清华大学出版社第11章存储过程、触发器和函数SQLServer2005基础教程清华大学出版社教学目标教学过程SQLServer2005基础教程清华大学出版社教学目标理解存储过程的特点、类型和作用使用CREATEPROCEDURE语句创建存储过程理解存储过程的执行方式理解DML触发器的特点和创建方式理解DML触发器的工作原理使用CREATETRIGGER语句创建DML触发器理解DDL触发器的特点和创建方式理解用户定义函数的类型和特点使用CREATEFUNCTION语句创建用户定义函数SQLServer2005基础教程清华大学出版社教学过程11.1存储过程11.2触发器11.3用户定义函数SQLServer2005基础教程清华大学出版社11.1存储过程存储过程的特点和类型创建存储过程的规则使用CREATEPROCEDURE语句创建存储过程执行存储过程修改和删除存储过程存储过程的执行过程查看存储过程的信息SQLServer2005基础教程清华大学出版社存储过程的特点和类型存储过程是一个可重用的代码模块，可以高效率地完成指定的操作。在MicrosoftSQLServer2005系统中，既可以使用Transact-SQL语言编写存储过程，也可以使用CLR方式编写存储过程。使用CLR编写存储过程是MicrosoftSQLServer2005系统与.NET框架紧密集成的一种表现形式。在MicrosoftSQLServer2005系统中，提供了三种基本的存储过程类型，即用户定义的存储过程、扩展存储过程和系统存储过程。SQLServer2005基础教程清华大学出版社创建存储过程的规则在设计和创建存储过程时，应该满足一定的约束和规则。只有满足了这些约束和规则，才可以创建有效的存储过程。虽然说在CREATEPROCEDURE语句中可以包括任意数量和类型的Transact-SQL语句，但是某些特殊的语句是不能包含在存储过程定义中的。SQLServer2005基础教程清华大学出版社使用CREATEPROCEDURE语句创建存储过程CREATEPROCEDURE语句的基本语法形式如下所示：–CREATEPROCEDUREprocedure_name–parameter_namedata_type,…–WITHprocedure_option–AS–sql_statementSQLServer2005基础教程清华大学出版社执行存储过程在MicrosoftSQLServer2005系统中，可以使用EXECUTE语句执行存储过程。EXECUTE语句也可以简写为EXEC。如果将要执行的存储过程需要参数，那么应该在存储过程名称后面带上参数值。SQLServer2005基础教程清华大学出版社修改和删除存储过程在MicrosoftSQLServer2005系统中，可以使用ALTERPROCEDURE语句修改已经存在的存储过程。修改存储过程，而不是删除和重建存储过程，其目的是保持存储过程的权限不发生变化。例如，如果修改HumanResources.GetEmployeeInfo存储过程，那么与该存储过程对象相关的权限将不会发生任何变化。但是，如果删除HumanResources.GetEmployeeInfo存储过程并且重新创建同名的存储过程，那么该存储过程对象相关的权限都需要重新定义。SQLServer2005基础教程清华大学出版社存储过程的执行过程语法分析阶段是指创建存储过程时，系统检查其创建语句的语法正确性的过程。解析阶段是指某个存储过程首次执行时，查询处理器从sys.sql_modules目录视图中读取该存储过程的文本并且检查该过程引用的对象名称是否存在的过程。编译阶段是指分析存储过程和生成存储过程执行计划的过程。执行阶段是指执行驻留在过程高速缓冲存储区中的存储过程的执行计划的过程。SQLServer2005基础教程清华大学出版社查看存储过程的信息在MicrosoftSQLServer2005系统中，可以使用系统存储过程和目录视图查看有关存储过程的信息。如果希望查看存储过程的定义信息，那么可以使用sys.sql_modules目录视图、OBJECT_DEFINITION元数据函数、sp_helptext系统存储过程等。除此之外，使用sp.sql_dependencies、sp_depends等系统存储过程可以查看存储过程的依赖信息。使用sys.objects、sps.procedures、sys.parameters、sys.numbered_procedures等目录视图可以查看有关存储过程的名称、参数等信息。SQLServer2005基础教程清华大学出版社11.2触发器触发器的概念和类型DML触发器的类型创建DML触发器DML触发器的工作原理一个DML触发器示例DDL触发器SQLServer2005基础教程清华大学出版社触发器的概念和类型一般地认为，触发器是一种特殊类型的存储过程，它包括了大量的Transact-SQL语句。按照触发事件的不同，可以把MicrosoftSQLServer2005系统提供的触发器分成两大类型，即DML触发器和DDL触发器。SQLServer2005基础教程清华大学出版社DML触发器的类型按照触发器事件类型的不同，可以把MicrosoftSQLServer2005系统提供的DML触发器分成3种类型，即INSERT类型、UPDATE类型和DELETE类型。这也是DML触发器的基本类型。SQLServer2005基础教程清华大学出版社创建DML触发器DML触发器是一种特殊类型的存储过程，所以DML触发器的创建和存储过程的创建方式有很多相似的地方。可以使用CREATETRIGGER语句创建DML触发器。在CREATETRIGGER语句中，指定了定义触发器的基表或视图、触发事件的类型和触发的时间、触发器的所有指令等内容。创建DML触发器的CREATETRIGGER语句的基本语法形式如下：–CREATETRIGGERtrigger_name–ONtable_name_or_view_name–WITHENCRYPTION–{FOR|AFTER|INSTEADOF}{[DELETE][,][INSERT][,][UPDATE]}–ASsql_statementSQLServer2005基础教程清华大学出版社DML触发器的工作原理当向表中插入数据时，INSERT触发器触发执行。当INSERT触发器触发时，新的记录增加到触发器表中和inserted表中。该inserted表是一个逻辑表，保存了所插入记录的拷贝，允许用户参考INSERT语句中数据。触发器可以检查inserted表，来确定该触发器的操作是否应该执行和如何执行。在inserted表中的那些记录，总是触发器表中一行或多行记录的冗余。SQLServer2005基础教程清华大学出版社一个DML触发器示例为了更加全面地掌握开发触发器的步骤和技术，本节通过一个具体的示例，全面讲述使用Transact-SQL语言开发和创建触发器的技术。一般地，开发触发器的过程包括用户需求分析、确定触发器的逻辑结构、编写触发器代码和测试触发器。SQLServer2005基础教程清华大学出版社DDL触发器DDL触发器与DML触发器有许多类似的地方，例如可以自动触发完成规定的操作、都可以使用CREATETRIGGER语句创建等，但是也有一些不同的地方。例如，DDL触发器的触发事件主要是CREATE、ALTER、DROP以及GRANT、DENY、REVOKE等语句，并且触发的时间条件只有AFTER，没有INSTEADOF。一般地，DDL触发器主要是用于下面一些操作：–防止对数据库架构进行某些更改。–希望数据库中发生某种情况以便相应数据库架构中的更改。–记录数据库架构中的更改或事件。SQLServer2005基础教程清华大学出版社11.3用户定义函数用户定义函数的特点创建用户定义函数时的考虑使用CREATEFUNCTION语句查看用户定义函数的信息SQLServer2005基础教程清华大学出版社用户定义函数的特点在MicrosoftSQLServer系统中，使用用户定义函数可以带来许多好处，这些好处包括允许模块化设计，只需创建一次函数并且将其存储在数据库中，以后便可以在程序中调用任意次。用户定义函数可以独立于程序源代码进行修改。执行速度更快，就像存储过程一样，使用Transact-SQL编写的用户定义函数通过缓存计划并在重复执行时重用它来降低Transact-SQL代码的编译开销。SQLServer2005基础教程清华大学出版社创建用户定义函数时的考虑在MicrosoftSQLServer2005系统中，可以分别使用CREATEFUNCTION、ALTERFUNCTION、DROPFUNCTION语句来实现用户定义函数的创建、修改和删除。在创建用户定义函数时，每个完全限定用户函数名称(schema_name.function_name)必须惟一。函数的BEGINEND块中的语句不能有任何副作用。SQLServer2005基础教程清华大学出版社使用CREATEFUNCTION语句在MicrosoftSQLServer2005系统中，使用CREATEFUNCTION语句可以创建标量函数、内联表值函数、多语句表值函数。需要说明的是，如果RETURNS子句指定了一种标量数据类型，则该函数为标量值。如果RETURNS子句指定了TABLE，则该函数为表值函数。根据函数主体的定义方式，表值函数可以分为内联函数或多语句函数。内联函数可以用于获得参数化视图的功能。SQLServer2005基础教程清华大学出版社查看用户定义函数的信息MicrosoftSQLServer2005系统提供了几个可以用于查看用户定义函数的信息的系统存储过程和目录视图。使用这些工具，可以查看用户定义函数的定义、获取函数的架构和创建时间、列出指定函数所使用的对象等信息。可以使用sys.sql_modules、OBJECT_DEFINITION、sp_helptext等工具查看用户定义函数的定义，可以使用sys.objects、sys.parameters、sp_help等工具查看有关用户定义函数的信息，可以使用sys.sql_dependencies、sp_depends等工具查看用户定义函数的依赖关系。