数据库原理与应用-项目九

学习目标：了解存储过程和触发器的功能能熟练存储过程的创建和使用方法能熟练触发器的类型和使用方法积累职业素质培养团队合作精神和语言表达能力任务的提出SQLServer数据库管理系统中，存储过程具有很重要的作用，存储过程是T-SQL语句的集合，它提供了一种高效、便捷和安全的访问数据库的方法，经常被用来查询和更新数据。而触发器就其本质而言也是一种存储过程。它只是在满足一定条件时就可以触发完成预制好的各种动作，可以帮助我们更好地维护数据库中数据的完整性，实现对数据的管理。

。

存储过程的概念存储过程（StoredProcedure）是一组为了完成特定功能、可以接受和返回用户提供的参数的T-SQL语句的预编译集合，经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程在第一次执行时进行语法检查和编译，执行后他的执行计划就驻留在高速缓存中，用于后续调用。存储过程可以接受和输出参数、返回执行存储过程的状态值，还可以嵌套调用。用户可以像使用函数一样重复调用这些存储过程，实现它所定义的操作。存储过程的分类在SQLServer2008中存储过程分为三类：1.系统提供的存储过程系统存储过程一般以“sp_”为前缀的，是由SQLServer2008自己创建、管理和使用的一种特殊的存储过程，不要对其进行修改或删除。2.用户自定义的存储过程用户自定义存储过程是由用户创建并能完成某一特定功能（如查询用户所需的数据信息）而编写的存储过程。可以输入参数、向客户端返回表格或结果、消息等，也可以返回输出参数。3.扩展存储过程扩展存储过程通常是以“xp_”为前缀。扩展存储过程允许使用其他编辑语言（例如C#等）创建自己的外部存储过程，其内容并不存储在SQLServer2008中，而是以DLL形式单独存在。但可能在以后的版本中该内容会被废除，不建议使用。存储过程

使用存储过程的优点：(1)模块化的程序设计。存储过程经过了一次创建以后，可以被调用无数次。用户可以独立于应用程序而对存储过程进行修改。可以按照独特的功能模式设计不同的存储过程以供使用。(2)执行速度快。存储过程在创建时就经过了语法检查和性能优化，因此在执行时不必再重复这些步骤。存储过程在经过第一次调用之后，就驻留在内存中，不必再经过编译和优化，所以执行速度快。在有大量批处理的Transact-SQL语句要重复执行的时候，使用存储过程可以极大地提高运行效率。(3)减少网络通信量。存储过程中可以包含大量的Transact-SQL语句，但存储过程作为一个独立的单元来使用。在进行调用时，只需要使用一个语句就可以实现，所以大大减少了网络上数据的传输。(4)保证系统的安全性。可以设置用户通过存储过程对某些关键数据进行访问，但不允许用户直接使用T-SQL或企业管理器对数据进行访问。在SQLServerManagementStudio

中创建存储过程【任务9.1】在“学生管理系统”数据库中，创建存储过程实现对学生成绩进行查询。要求在查询时提供欲查询的学生姓名和课程名称，存储过程根据用户提供的信息对数据进行查询。（l）启动SQLServerManagementStudio，登录到要使用的服务器，在【对象资源管理器】窗格中，选择本地数据库实例--【数据库】--【mydb】--【可编程性】--【存储过程】选项（2）右击【存储过程】选项，在弹出的快捷菜单中选择【新建存储过程】选项。（3）出现图9.2所示的创建存储过程的查询编辑器窗格，其中已经加入了创建存储过程的代码，（4）单击菜单栏上的【查询】--【指定模板参数的值】选项，弹出图9.3所示的对话框，其中Author（作者）、CreateDate（创建时间）、Description（说明）为可选项，内容可以为空。Proceudre_Name为存储过程名，@Param1为第一个输入参数名，Datetype_For_Param1为第一个输入参数的类型，Default_Value_For_Param1为第一个输入参数的默认值。（5）设置完毕，单击【确定】按钮，返回到创建存储过程的查询编辑器窗格，如图9.5所示，此时代码已改变。（6）在“Insertstatementsforprocedurehere”下输入T-SQL代码，在本例中输入：SelectgradeNumFromgradeWheresID=(selectsIDfromstudentwheresName=@stuname)andkcID=(selectkcIDfromcoursewherekcName=@kcname)（7）单击【执行】按钮完成操作，最后的结果如图9.6所示。使用CreateProcdure语句创建存储过程１.创建存储过程格式:CREATEPROC[EDURE]存储过程名

[WITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION]ASSQL语句RECOMPILE：在执行完存储过程以后不在高速缓存里保留存储过程的备份，所以每次执行存储过程时都需要对存储过程进行重新编译和优化。ENCRYPTION：存储过程作为数据库对象将在syscomments表中留下完整的代码等信息。使用了ENCRYPTION参数以后，将对访问这些数据的入口进行加密。创建存储过程的时候要考虑以下几个因素存储过程可以参考表、视图或其他因素如果在存储过程中创建了临时表，那么该临时表只在该存储过程招待时有效在一个批命令中，CREATEPROCEDURE语句不能与其他的T-SQL语句混合使用，之间加GO命令存储过程可以嵌套调用，但最多不能超过32层，当前嵌套的数据值存储在全局变量@@NESTLEVEL中。如果一个存储过程调用另一个存储过程，那么内层的存储过程可以使用另一个存储过程所创建的全部对象，包括临时表。【任务9.2】使用存储过程解决【任务9.1】中的问题。解决【任务9.1】中的问题，可以在查询编辑器中执行下列SQL语句，运行结果如图9.7所示USEmyDB–-打开数据库--检查欲创建的存储是否存在，如果存在则删除IFEXISTS(SELECTnameFROMsysobjectsWHEREname='proc_Q_stuGrade'ANDtype='P')DROPPROCEDUREproc_Q_stuGradeGO--创建存储过程proc_Q_stuGradeCREATEPROCproc_Q_stuGrade@stuNamechar(20),@kcnamechar(20)ASif(@stuNameisnull)print'请输入学生姓名！'elseif(@kcnameisnull)print'请输入课程名称！'elseselectgradeNumfromgradewheresID=(selectsIDfromstudentwheresName=@stuName)andkcID=(selectkcIDfromcoursewherekcName=@kcname)go执行存储过程格式：[EXECUTE]]

|存储过程名|

[@参数=]|参数值@变量[OUTPUT][WITHRECOMPILE]【任务9.3】请通过调用存储过程proc_Q_stuGrade来查询“刘常福”同学的《商务网站建设》这门课的成绩。usemyDBgo--执行存储过程，查询“刘常福”同学的《商务网站建设》这门课的成绩execproc_Q_stuGrade'刘常福','商务网站建设'使用SQLServerManagementStudio

修改存储过程在SQLServerManagementStudio中，首先找到要修改的存储过程。然后用鼠标右健单击所要修改的存储过程，在弹出菜单中选择【修改】命令。在图9.9所示的修改存储过程的查询编辑器窗格中对存储过程代码进行修改。使用Alterprocedure语句修改存储过程格式：ALTERPROC[EDURE]存储过程名

[WITHRECOMPILE｜ENCRYPTION｜RECOMPILE,ENCRYPTION]ASSQL语句【任务9.4】由于【任务9.3】中的存储过程文本可以使用存储过程sp_helptext进行查看，出于对代码安全性的考虑，请将【任务9.3】中创建的存储过程进行加密处理。分析：只要将该存储过程加密即或解决这个问题。代码如下：USEmyDB--打开数据库GO--修改存储过程proc_Q_stuGradeALTERPROCproc_Q_stuGrade@stuNamechar(20),@kcnamechar(20)withencryptionASif(@stuNameisnull)print'请输入学生姓名！'elseif(@kcnameisnull)print'请输入课程名称！'elseselectgradeNumfromgradewheresID=(selectsIDfromstudentwheresName=@stuName)andkcID=(selectkcIDfromcoursewherekcName=@kcname)go重命名存储过程存储过程也可以被重新命名。新的名称必须遵守标识符规则。要重命名的存储过程必须位于当前数据库中，并且要拥有相应的权限。使用SQLServerManagementStudio重命名使用系统存储过程sp_rename进行重命名使用系统存储过程sp_rename可以重命名存储过程。其语法格式如下：sp_rename[@objname=]'object_name',[@newname=]'new_name'[,[@objtype=]'object_type']各参数说明如下：[@objname=]'object_name'：是存储过程或触发器的当前名称。[@newname=]'new_name'：是指定存储过程或触发器的新名称。[@objtype=]'object_type'：是要重命名的对象的类型。【任务9.5】请使用存储过程将【任务9.3】中创建的存储过程proc_Q_stuGrade重新命名为proc_Q_stuGrade_new。请在查询编辑器中执行下列T-SQL语句：usemyDBgoexecsp_rename'proc_Q_stuGrade','proc_Q_stuGrade_new','OBJECT'使用SQLServerManagementStudio删除（1）用鼠标右键单击待删除存储过程，在弹出菜单中选择【删除】命令，或单击要删除的存储过程，接着按下delete键，弹出如图9.11所示的【删除对象】窗口。（2）可以点击【显示依赖关系】按钮，查看当前存储过程与其他对象的依赖关系。如图9.12所示使用DropProcedure命令删除存储过程其语法格式如下：DROPPROCEDURE{procedure}[,...n]参数说明如下：procedure：是要删除的存储过程或存储过程组的名称。【任务9.6】请使用存储过程将存储过程【任务9.5】中重命名的存储过程从数据库中删除。解决【任务9.6】中的任务，可以在查询分析器中执行下列T-SQL语句：usemyDBgoDROPPROCEDUREproc_Q_stuGrade_new使用SQLServerManagementStudio

查看存储过程（1）在SQLServerManagementStudio中，首先找到要查看的存储过程。然后用鼠标右键单击所要查看的存储过程，打开弹出菜单。（2）如要查看存储过程的源代码，可在弹出菜单中选择【修改】命令，即可在查询编辑器中查看该存储过程的定义文本。（3）如要查看存储过程的相关性在弹出菜单中选择【查看依赖关系命令】即可（4）如要查看存储过程的其他内容，可在弹出菜单中选择【属性】命令，打开如图9.13所示属性窗口。查看存储过程定义使用系统存储过程sp_helptext，可以查看未加密的存储过程的文本。其语法格式如下：sp_helptext[@objname=]'name'参数说明如下：[@objname=]'name'：存储过程的名称，将显示该存储过程的定义文本。该存储过程必须在当前数据库中。【任务9.7】请使用存储过程显示存储过程proc_Q_stuGrade的定义文本。结果如图9.14所示USEmyDBgoEXECsp_helptext'proc_Q_stuGrade'查看存储过程依赖关系其语法如下：sp_depends[@objname=]'object'参数说明如下：[@objname=]'object'：被检查相关性的数据库对象。对象可以是表、视图、存储过程或触发器。【任务9.8】请使用存储过程显示存储过程proc_Q_stuGrade的相关性的信息。结果如图9.15所示解决【任务9.8】中的任务，可以在查询分析器中执行下列T-SQL语句：USEmyDBgoEXECsp_depends'proc_Q_stuGrade'查看存储过程其他相关信息其语法格式如下：sp_help[[@objname=]name]参数说明如下：[@objname=]name：是sysobjects中的存储过程的名称。当没有指定name时，sp_help列出当前数据库中所有对象的名称、所有者和对象类型。【任务9.9】请使用存储过程显示存储过程'proc_Q_stuGrade'的信息。结果如图9.16所示解决【任务9.9】中的任务，可以在查询分析器中执行下列T-SQL语句：USEmyDBgoEXECsp_help'proc_Q_stuGrade'触发器触发器是一种特殊类型的存储过程，它作用于指定的表中，当这个表中的数据发生相应的变化（执行INSERT，UPDATE，DELETE操作）时，触发器被唤醒，自动生效，以响应命令中的INSERT，UPDATE，DELETE语句。触发器（Trigger）是一个能由系统自动执行对数据库修改的语句。通常由三部分组成：（1）事件。事件是指对数据库的插入、删除和修改等操作，触发器在这些事件发生时，将开始工作。（2）条件。触发器将测试条件是否成立。如果条件成立，就执行相应的动作，否则什么也不做。（3）动作。如果触发器测试满足预设的条件，那么就由DBMS执行这些动作（即对数据库的操作）。这些动作能使触发器事件不发生，即撤消事件，如删除刚插入的元组等。这些动作也可以是一系列对数据库的操作，甚至可以是与触发事件本身无关的其它操作。触发器的种类和分类在SQLServer2008中，触发器可以分为两大类：DML触发器和DDL触发器。DML触发器：DML触发器是在数据库服务器中发生数据操作语言（DataManipulationLanguage）事件时执行的存储过程。DML触发器分为两个类，After触发器和InsteadOf触发器。DDL触发器：DDL触发器是在响应数据定义语言（DataDefinitionLanguage）事件时执行的存储过程。DDL触发器一般用于执行数据库中的管理任务，例如审核和规范数据库操作、防止数据库表结构被修改等。DML触发器的分类：AFTER触发器和INSTEADOF触发器。AFTER触发器：该类型触发器要求只有执行某一操作（INSERT、UPDATE、DELETE）之后，触发器才能被触发，且只能在表上定义。可以为针对表的同一操作定义多个触发器。对于AFTER触发器，可以定义哪一个触发器被最先触发，哪一个被最后触发，通常使用系统过程sp_settriggerorder来完成此任务。INSTEADOF触发器表示拒绝执行其所定义的操作（INSERT、UPDATE和DELETE）而仅是执行触发器本身。既可在表上定义INSTEADOF触发器，也可以在视图上定义INSTEADOF触发器，但对同一操作只能定义一个INSTEADOF触发器。触发器的工作原理在触发器执行时，将生成两个临时表（逻辑表），即inserted表和deleted表。这两个临时表由系统进行管理，存储在内存中，并且不允许用户直接对其修改。这两个表的结构总是与被触发器作用的表的结构相同，它们可以被用于触发器条件的测试。在执行INSERT语句时，插入到表中的新记录也同时插入到表inserted表中。在执行UPDATE语句时，系统首先删除原有记录，并将原有记录插入到deleted表中，而新插入的记录也同时插入到inserted表中。在执行DELETE语句时，删除表中数据的同时，也将该数据插入到deleted表中。也就是说，触发器会自动记录所要更新数据的新值和原值，根据对新值和原值的测试来决定是否执行触发器中预设的动作。当触发器工作完成时，临时性的inserted表和deleted数据表会自动消失。使用SQLServerManagementStudio

创建触发器【任务9.11】使用SQLServerManagementStudio创建一个简单的触发器，实现在学生信息表（student）中当学生姓名被修改时，需要返回一个提示信息。具体操作步骤如下：（1）启动SQLServerManagementStudio。登陆到指定的服务器上（2）在图9.17所示的【对象资源管理器】窗格中选择【数据库】选项，定位到【mydb】--【表】--【dbo.student】选项，并找到【触发器】选项（3）右击触发器选项，在弹出的快捷菜单中选择【新建触发器选项】，此时会自动弹出查询编辑器窗格。在查询编辑器窗格的编辑区里，SQLServer2008已经自动写入了一些建立触发器相关的SQL语句。修改该代码，将从“CREATE”开始到“GO”结束的代码改写为自己编写的内容。如图9.18所示（4）单击工具栏中【分析】按扭

，检查输入的T-SQL语句是否有语法错误。如果没有语法错误，则在下面的【结果】窗格中显示“命令已成功完成”。如果有语法错误，则进行修改，直到没有语法错误为止。（5）单击【执行】按扭，生成触发器。（6）关闭查询编辑器窗格，刷新【触发器】选项，可以看到刚才建立的触发器，如图9.19所示。当创建成功后，可以在查询编辑器中执行下列T-SQL语句进行测试，结果如图9.20所示：usemyDBgoupdatestudentsetsName='王亚力_新'wheresID='60402'使用CreateTrigger语句创建触发器可以使用T-SQL语句中的CREATETRIGGER命令来创建触发器，其语法格式如下：CREATETRIGGERtrigger_nameON{table|view}[WITHENCRYPTION]{{{FOR|AFTER|INSTEADOF}{[INSERT][,][UPDATE][,][DELETE]}[WITHAPPEND][NOTFORREPLICATION]AS[{IFUPDATE(column)[{AND|OR}UPDATE(column)][...n]|IF(COLUMNS_UPDATED(){bitwise_operator}updated_bitmask){comparison_operator}column_bitmask[...n]}]sql_statement[...n]}}【任务9.12】编写触发器实现系统自动统计相应学生的处分记录并进行系统提示。在查询编辑器中执行下列T-SQL语句：CREATETRIGGER[TRI_jiangchen_ins]ON[dbo].[jiangchen]FORINSERTASdeclare@countNumint,@jcRenchar(6),@sNamevarchar(20)--从inserted表中取出所要添加的奖惩记录中学生的学号select@jcRen=jcRenfrominserted--从student表中取出该学生的姓名select@sName=sNamefromstudentwheresID=@jcRen--统计jiangchen表中是该学生的奖惩记录数select@countNum=count(*)fromjiangchenwherejcRen=@jcRenandjcLeibie='处分'--对统计的结果进行判断if@countNum>0print(@sName+'同学已存在'+str(@countNum)+'条处分记录，请加强对该学生的教育！')上述T-SQL语句执行的结果如图9.21所示。在创建成功后，可以执行下语句进行测试：insertintojiangchen(jcRen,jcLeibie,jcNeirong,jcShijian,jcWenjian)values('40108','处分','旷课达72学时给予记过处分，时间自06年9月起至07年9月止。','2008-04-20','院学2008第10号文件')测试结果如图9.22所示。使用SQLServerManagementStudio修改触发器通过SQLServerManagementStudio修改触发器与创建触发器的过程非常相似，在【对象资源管理器】中选择要修改的触发器，右键单击打开快捷菜单选择【修改】命令。在打开的查询编辑器中对代码进行修改。修改完触发器后要使用【分析】按钮对语法进行检查。最后点击【执行】重新生成触发器即可。使用Altertrigger语句修改触发器可以使用T-SQL语句ALTERTRIGGER修改触发器，其语法格式为：ALTERTRIGGERtrigger_nameON(table|view)[WITHENCRYPTION]{{(FOR|AFTER|INSTEADOF){[DELETE][,][INSERT][,][UPDATE]}[NOTFORREPLICATION]ASsql_statement[...n]}|{(FOR|AFTER|INSTEADOF){[INSERT][,][UPDATE]}[NOTFORREPLICATION]AS{IFUPDATE(column)[{AND|OR}UPDATE(column)][...n]|IF(COLUMNS_UPDATED(){bitwise_operator}updated_bitmask){comparison_operator}column_bitmask[...n]}sql_statement[...n]}}【任务9.13】修改触发器TRI_student_update，加密触发器文本定义。解决【任务9.13】中的任务，可以在查询编辑器中执行下列T-SQL语句：usemyDBgoaltertriggerTRI_student_updateonstudentwithencryptionforupdateas--定义两个字符型变量

declare@sNameOldchar(20),@sNameNewchar(20)--从inserted表中取出修改后的用户姓名select@sNameNew=sNamefrominserted--从inserted表中取出修改后的用户姓名select@sNameOld=sNamefromdeletedprint'原学生姓名'+rtrim(@sNameOld)+'被改成了'+rtrim(@sNameNew)使用sp_rename命令重命名触发器可以使用存储过程sp_rename重命名触发器，其语法格式为：sp_renameoldname,newname参数说明和用例参考重命名存储过程的说明，在此不再赘述了。暂时禁用或启用触发器可以使用T-SQL语句中的ALTERTABLE命令可以实现启用或暂时禁用触发器，其语法格式如下：ALTERTABLE触发器表名称{ENABLE|DISABLE}TRIGGER{ALL|触发器名称[，……n]}【任务9.14】暂时禁止触发器TRI_student_update的使用。解决【任务9.14】中的任务，可以在查询分析器中执行下列T-SQL语句：usemyDBgoALTERTABLEstudentDISABLETRIGGERTRI_student_update使用SQLServerManagementStudio

查看触发器（1）启动SQLServerManagementStudio。登陆到指定的服务器上（2）在【对象资源管理器】窗格中选择【数据库】选项，定位到要查看触发器的数据表上，并找到【触发器】选项（3）单击【触发器】选项，在右边的【详细信息】窗格中，可以看到该数据表已经创建好的触发器信息（4）双击要查看的触发器名，SQLServerManagementStudio会自动弹出一个查询编辑器窗格，里面显示的即是该触发器的内容。使用系统存储过程sp_helptrigger

查看触发器信息可以使用系统存储过程sp_helptrigger返回基本表中指定类型的触发器信息。其语法格式如下：sp_helptrigger[@tabname=]'table'[,[@triggertype=]'type']参数说明：[@tabname=]'table'：是当前数据库中表的名称，将返回该表的触发器信息。[@triggertype=]'type'：是触发器