第10章存储过程触发器4

数据库原理与应用教程―SQLServer第10章存储过程和触发器第10章存储过程和触发器在SQLServer2008应用操作中，存储过程和触发器都扮演着相当重要的角色。存储过程可以使用户对数据库的管理工作变得更容易。存储过程是SQL语句和可选流程控制语句的预编译集合，它以一个名称存储并作为一个单元处理，能够提高系统的应用效率和执行速度。SQLServer提供了许多系统存储过程以管理SQLServer和显示有关数据库和用户的信息。第10章存储过程和触发器触发器是一种特殊类型的存储过程。当有操作影响到触发器保护的数据时，触发器就会自动触发执行。触发器是与表紧密联系在一起的，它在特定的表上定义，并与指定的数据修改事件相对应，它是一种功能强大的工具，它可以扩展SQLServer完整性约束默认值对象和规则的完整性检查逻辑，实施更为复杂的数据完整性约束。本章主要介绍存储过程的基本概念，存储过程的创建、修改、调用和删除操作；触发器的基本概念，触发器的分类，触发器的创建、修改和删除，以及触发器的应用。第10章存储过程和触发器10.1存储过程当开发一个应用程序时，为了易于修改和扩充，经常会将负责不同功能的语句集中起来而且按照用途分别独立放置，以便能够反复调用，而这些独立放置且拥有不同功能的语句，即是“过程”(Procedure)。SQLServer2008的存储过程（StoredProcedure）包含一些T-SQL语句并以特定的名称存储在数据库中。可以在存储过程中声明变量、有条件地执行以及其他各项强大的程序设计功能。第10章存储过程和触发器10.1.1存储过程概述存储过程是一种数据库对象，独立存储在数据库内。存储过程可以接受输入参数、输出参数，返回单个或多个结果集以及返回值，由应用程序通过调用执行。存储过程是SQLServer中一个非常有用的工具。SQLServer支持存储过程和系统过程。存储过程是独立存在于表之外的数据对象。可以由客户调用，也可以从另一个过程或触发器调用，参数可以被传递和返回，出错代码也可以被检验。第10章存储过程和触发器存储过程最主要的特色是当写完一个存储过程后即被翻译成可执行码存储在系统表内，当作是数据库的对象之一，一般用户只要执行存储过程，并且提供存储过程所需的参数就可以得到所要的结果而不必再去编辑T-SQL命令。一般来讲，应使用SQLServer中的存储过程而不使用存储在客户计算机本地的T-SQL程序，其优势主要表现在：第10章存储过程和触发器(1)允许模块化程序设计。只需创建一次并将其存储在数据库中，以后即可在程序中调用该过程任意次。存储过程可由在数据库编程方面有专长的人员创建，并可独立于程序源代码而单独修改。如果业务规则发生变化，可以通过修改存储过程来适应新的业务规则，而不必修改客户端的应用程序。这样所有调用该存储过程的应用程序就会遵循新的业务规则。第10章存储过程和触发器(2)允许更快速地执行。如果某操作需要大量T-SQL语句或需重复执行，存储过程将比T-SQL批处理代码的执行要快。创建存储过程时对其进行分析和优化并预先编译好放在数据库内，减少编译语句所花的时间；编译好的存储过程会进入缓存，所以对于经常执行的存储过程，除了第一次执行外，其他次执行的速度会有明显提高。而客户计算机本地的T-SQL语句每次运行时，都要从客户端重复发送，并且在SQLServer每次执行这些语句时，都要对其进行编译和优化。第10章存储过程和触发器(3)减少网络流量。一个需要数百行T-SQL语句的操作由一条执行过程代码的单独语句就可实现，而不需要在网络中发送数百行代码。(4)可作为安全机制使用。数据库用户可以通过得到权限来执行存储过程，而不必给予用户直接访问数据库对象的权限。这些对象将由存储过程来执行操作，另外，存储过程可以加密，这样用户就无法阅读存储过程中的T-SQL语句。这些安全特性将数据库结构和数据库用户隔离开来，这也进一步保证数据的完整性和可靠性。第10章存储过程和触发器10.1.2存储过程的类型1.系统存储过程存储过程在运行时生成执行方式，其后在运行时执行速度很快。SQLServer2008中的许多管理活动都是通过一种特殊的存储过程执行的，这种存储过程被称为系统存储过程。系统过程主要存储在master数据库中并以sp_为前缀，并且系统存储过程主要是从系统表中获取信息，从而为数据库系统管理员管理SQLServer提供支持。通过系统存储过程，SQLServer中的许多管理性或信息性的活动（如获取数据库和数据库对象的信息）都可以被顺利有效地完成。第10章存储过程和触发器尽管这些系统存储过程被存储在master数据库中，但是仍可以在其他数据库中对其进行调用，在调用时，不必在存储过程名前加上数据库名。而且当创建一个数据库时，一些系统存储过程会在新的数据库中被自动创建。SQLServer2008系统存储过程是为用户提供方便的，它们使用户可以很容易地从系统表中提取信息、管理数据库，并执行涉及更新系统表的其他任务。第10章存储过程和触发器如果过程以SP_开始，又在当前数据库中找不到，SQLServer2008就在master数据库中寻找。当系统存储过程的参数是保留字或对象名，且对象名由数据库或拥有者名字限定时，整个名字必须包含在单引号中。一个用户可以在所有数据库中执行一个系统存储过程的许可权，否则在任何数据库中都不能执行系统存储过程。第10章存储过程和触发器2．本地存储过程本地存储过程也就是用户自行创建并存储在用户数据库中的存储过程，一般所说的存储过程指的就是本地存储过程。用户创建的存储过程是由用户创建并能完成某一特定功能(如查询用户所需的数据信息)的存储过程。第10章存储过程和触发器3．临时存储过程临时存储过程可分为以下两种：(1)本地临时存储过程不论哪一个数据库是当前数据库，如果在创建存储过程时，其名称以“#”号开头，则该存储过程将成为一个存放在tempdb数据库中的本地临时存储过程。本地临时存储过程只有创建它的连接的用户才能够执行它，而且一旦这位用户断开与SQLServer的连接，本地临时存储过程就会自动删除，当然，这位用户也可以在连接期间用DROPPROCEDURE命令删除他所创建的本地临时存储过程。第10章存储过程和触发器(2)全局临时存储过程不论哪一个数据库是当前数据库，只要所创建的存储过程名称是以两个“#”号开头，则该存储过程将成为一个存储在tempdb数据库中的全局临时存储过程。全局临时存储过程一旦创建，以后连接到SQLServer2008的任意用户都能执行它，而且不需要特定的权限。第10章存储过程和触发器当创建全局临时存储过程的用户断开与SQLServer2008的连接时，SQLServer2008将检查是否有其他用户正在执行该全局临时存储过程，如果没有，便立即将全局临时存储过程删除；如果有，SQLServer2008会让这些正在执行中的操作继续进行，但是不允许任何用户再执行全局临时存储过程，等到所有未完成的操作执行完毕后，全局临时存储过程就会自动删除。不论创建的是本地临时存储过程还是全局临时存储过程，只要SQLServer2008停止运行，它们将不复存在。第10章存储过程和触发器4．远程存储过程在SQLServer2008中，远程存储过程是位于远程服务器上的存储过程，通常可以使用分布式查询和EXECUTE命令执行一个远程存储过程。5．扩展存储过程扩展存储过程是用户可以使用外部程序语言（例如C语言）编写的存储过程。显而易见，扩展存储过程可以弥补SQLServer2008的不足，并按需要自行扩展其功能。扩展存储过程在使用和执行上与一般的存储过程完全相同，为了区别，扩展存储过程的名称通常以XP_开头。扩展存储过程是以动态链接库(DLL)的形式存在，能让SQLServer2008动态地装载和执行。扩展存储过程一定要存储在系统数据库master中。第10章存储过程和触发器10.1.3创建存储过程在SQLServer2008中创建存储过程主要有两种方式：一种方式是在SQLServerManagementStudio中创建存储过程；另一种方式是通过在查询窗口中执行T-SQL语句创建存储过程。1．在SQLServerManagementStudio中创建存储过程在SQLServerManagementStudio中创建存储过程的步骤如下：(1)打开SQLServerManagementStudio，展开要创建存储过程的数据库，展开“可编程性”选项，可以看到存储过程列表中系统自动为数据库创建的系统存储过程。右键单击“存储过程”选项，选择“新建存储过程”命令第10章存储过程和触发器(2)出现创建存储过程的T-SQL命令，编辑相关的命令即可。(3)命令编辑成功后，进行语法检查，然后单击“！”按钮，至此一个新的存储过程建立成功。注意：用户只能在当前数据库中创建存储过程，数据库的拥有者有默认的创建权限，权限也可以转让给其他用户。第10章存储过程和触发器2．利用T-SQL语句创建存储过程SQLServer2008提供了CREATEPROCEDURE创建存储过程。语法格式如下：CREATE{PROC|PROCEDURE}procedure_name[;number][{@parameterdata_type}[VARYING][=default][[OUT[PUT]][,...n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}[,...n]][FORREPLICATION]ASsql_statement[...n]第10章存储过程和触发器在创建存储过程时，应当注意以下几点。(1)存储过程最大不能超过128MB。(2)用户定义的存储过程只能在当前数据库中创建，但是临时存储过程通常是在tempdb数据库中创建的。(3)在一条T-SQL语句中CREATEPROCEDURE不能与其他T-SQL语句一起使用。第10章存储过程和触发器(4)SQLServer允许在存储过程创建时引用一个不存在的对象，在创建的时候，系统只检查创建存储过程的语法。存储过程在执行的时候，如果缓存中没有一个有效的计划，则会编译生成一个可执行计划。只有在编译的时候，才会检查存储过程所引用的对象是否都存在。这样，如果一个创建存储过程语句值要在语法上没有错误，即使引用了不存在的对象也是可以成功执行的。但是，如果在执行的时候，存储过程引用了一个不存在的对象，这次执行操作将会失败。第10章存储过程和触发器【例10-1】在“教学库”创建无参存储过程，查询每个同学各门功课的平均成绩。USE教学库GOCREATEPROCEDUREstudent_avgASSELECT学号,avg(成绩)as'平均分'FROM选课表GROUPBY学号GO命令执行，创建存储过程成功第10章存储过程和触发器【例10-2】在教学库创建带参数的存储过程，查询某个同学的基本信息。USE教学库GOCREATEPROCEDUREGetStudent@numberchar(7)ASSELECT*FROM学生表WHERE学号=@numberGO第10章存储过程和触发器【例10-3】在“教学库”创建带参数的存储过程，修改某个同学某门课的成绩。USE教学库GOCREATEPROCEDUREUpdate_score@numberchar(7),@cnchar(4),@scoreintASUPDATE选课表SET成绩=@scoreWHERE学号=@numberand课程号=@cn第10章存储过程和触发器【例10-4】使用流程控制语句，在“教学库”数据库创建存储过程，修改某学生的成绩，如果平均成绩大于某个值，就增加10%。USE教学库CREATEPROCEDUREUpdate_score2@numberchar(3),@