《《数据库技术》项目8存储过程和触发器》-精选课件（公开PPT）》由会员分享，可在线阅读，更多相关《《数据库技术》项目8存储过程和触发器》-精选课件（公开PPT）（42页珍藏版）》请在金锄头文库上搜索。
1、项目八,存储过程和触发器,2,项目要求,了解存储过程和触发器的优点学会创建和使用存储过程与触发器,3,项目准备,1.存储过程存储过程是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数来执行它，相当于其他编程语言的函数、过程和方法.2.存储过程的优点允许模块化程序设计较好的安全机制有效的降低网络流量更高的执行效率,4,3.存储过程的类型系统存储过程是由系统创建的存储过程，目的在于能够方便的从系统表中获取信息，在SQLServer中许多管理和信息活动可以通过系统存储过程执行。系统存储过程以sp_前缀，存储在master数据库中，为检索系统表的信息提供
2、快捷方式。尽管这些存储过程存储在master数据库中，但是仍可以在其他数据库中对其进行调用，调用时不必在存储过程名前加上数据库名。而且在创建一个新的数据库时，一些系统存储过程会在新数据库中自动创建。,项目准备,5,本地存储过程本地存储过程是用户自行创建并存储在用户数据库中的存储过程。临时存储过程临时存储过程是一种特殊的本地存储过程。如果在本地存储过程过程前加上一个“#”号，这种存储过程称为局部临时存储过程，只能在一个用户对话中使用。如果如果在本地存储过程过程前加上一个“#”号，这种存储过程称为全局临时存储过程，可以在所有用户会话中使用。无论是局部临时存储过程还是全局临时存储过程，只要SQLSe
3、rver一停止运行，它们将不复存在。,项目准备,6,远程存储过程远程存储程序是位于远程服务器上的存储过程。扩展存储过程扩展存储过程是用户可以使用外部程序语言编写的存储过程，扩展存储过程通常以xp_开头。扩展存储过程是以动态链接库的形式存在，能让SQL动态的装载和执行。扩展存储过程一定要放在系统数据库master中。,项目准备,7,项目准备,1触发器触发器是一种特殊类型的存储过程，其特殊性在于它不需要用户来调用，当对表进行插入、删除和修改等操作时自动执行。所以，触发器可以用开实施复杂的完整性约束，已防止对数据的不正确修改。,8,2触发器的优缺点触发器的功能很强大，使用它可以检查数据输入的正确性；
4、实现数据库中多个表的级联修改；强制更复杂的数据完整性等。但也要谨慎使用，因为每次访问表时都可能激活某个触发器，这样会给数据库带来负担，所以，尽量使用触发器解决别的方法不能执行的操作。另外，有些语句不能在触发器中执行，如CREATEDATABASE、ALTERDATABASE等。,项目准备,9,3、三种类型触发器INSERT触发器：当向表中插入数据时，将执行INSERT触发器。UPDATE触发器：当向表中插入数据时，将执行UPDATE触发器。DELETE触发器：当向表中插入数据时，将执行DELETE触发器。,项目准备,10,项目8.1存储过程,任务1：创建不带参数的存储过程任务2：创建带参数的存
5、储过程任务3：执行存储过程,11,创建不带参数的存储过程命令CREATEPROCEDUREprocedure_nameASsql_statements其中的参数含义如下：procedure_name：为所创建的存储过程的名称。sql_statements：为在存储过程中需要执行的数据库操作。,任务1：创建不带参数的存储过程,12,任务1：创建不带参数的存储过程,提出任务针对student数据库中的xs表，创建一个名为student_proc1的存储过程，该存储过程的功能是从xs表中查询所有男同学的信息。分析任务可以使用企业管理器或使用T-SQL语句创建存储过程。,13,任务1：创建不带参数的存
6、储过程,完成任务方法一：使用企业管理器创建存储过程展开【student】数据库，右击【存储过程】对象，选择【新建存储过程】命令；在【文本】编辑框输入存储过程的正文内容；在【存储过程属性】对话框中修改正文内容。如图1所示。单击【检查语法】按钮，检查语法是否正确，如正确，则单击【确定】按钮来保存创建的存储过程。此时，在右窗口中，出现该存储过程。如图2所示。,14,任务1：创建不带参数的存储过程,图1,图2,15,任务1：创建不带参数的存储过程,方法二：使用T-SQL语句创建存储过程在查询分析器中直接输入如下一端T-SQL语句：createprocstudent_proc1asselect*from
7、xswherexb=男并执行该语句，若显示“命令已成功完成”，表示存储过程创建成功且存储在数据库中。,16,任务2：创建带参数的存储过程,CREATEPROCEDUREprocedure_namepatameterdata_type=DEFAULTOUTPUT，。nWITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONASql_statements解释：procedure_name：为所创建的存储过程的名称。patameter：指定输入和输出的参数的名称，参数的名称以字符开头。data_type：指定输入参数的数据类型。DEFAULT：指定输入参数的默认值。
8、OUTPUT：表明参数是返回参数。WITHRECOMPILE：决定执行计划不保存在过程的高速缓存中，每次重新编译。WITHENCRYPTION：对含有CREATEPROCEDURE正文的syscomments项进行加密。sql_statements：为在存储过程中需要执行的数据库操作。,17,任务2：创建带参数的存储过程,提出任务针对student数据库中的xs表，创建一个名为student_proc2的存储过程，该存储过程的功能是向数据库表xs中插入一条记录，新记录的值由参数提供，并执行该存储过程。分析任务可以使用企业管理器或使用T-SQL语句创建存储过程。使用企业管理器方法同前，此次采用T
9、-SQL语句创建存储过程,18,任务2：创建带参数的存储过程,完成任务1在【查询分析器】中输入如下语句：createprocstudent_proc2(xh1char(8),xm1char(6),xbchar(2),csrqsmalldatetime,zychar(20)asinsertintoxsvalues(xh1,xm1,xb,csrq,zy)2执行该语句，则名为student_proc2的带参数的存储过程创建成功。,19,任务3：执行存储过程,执行存储过程命令EXECUTEreturn_status=procedure_name|procedure_name_varparameter=
10、value|variableOUTPUT|DEFAULT,nWITHRECOMPILE其中的参数含义如下：return_status：保存存储过程的返回状态。procedure_name：是拟调用的存储过程的名称。procedure_name_var：是局部定义变量名，代表存储过程名称。parameter：是过程参数，在CREATEPROCEDURE语句中定义。Value：是过程中参数的值。variable：是用来保存参数或返回参数的变量。WITHRECOMPILE：强制编译存储过程。,20,任务3：执行存储过程,带有输入参数的存储过程的执行方法使用参数名传送参数值在执行存储过程的语句中，通过
11、语句parameter_name=value给出参数的传递值。当存储过程中含有多个输入参数时，参数值可以以任意的顺序指定，对于允许空值和具有默认值的输入参数可以不给出参数的传递值。按位置传送参数值在执行存储过程的语句中，不参照被传递的参数和直接给出参数的传递值。当存储过程中含有多个输入参数时，传递值的顺序必须与存储过程中定义的输入参数的顺序相一致。,21,任务3：执行存储过程,提出任务1.执行不带参数的存储过程student_proc1。2.执行带参数的存储过程student_proc2。分析任务按照相关的T-SQL命令执行存储过程。,22,任务3：执行存储过程,完成任务1操作步骤：在【查询分
12、析器】窗口输入命令：“usestudentexecstudent_proc1”。执行该语句，结果如图所示。,23,任务3：执行存储过程,2操作步骤：方法一：使用参数名传送参数值在查询分析器中输入如下语句：execstudent_proc2xh1=056789,xm1=李明,xb=男,csrq=1970-5-2,zy=计算机及应用执行该语句，则在xs表的最后一行将出现该条记录。,24,任务3：执行存储过程,方法二：按位置传送参数值在查询分析器中输入如下语句：execstudent_proc2056789,李明,男,1970-5-2,计算机及应用执行该语句，则在xs表的最后一行将出现该条记录。,2
13、5,任务3：执行存储过程,执行student_proc2后的“xs”表记录,26,项目练习,练习8.1：针对xs表，创建一个存储过程student_proc3，该存储过程有一个表示性别的参数，通过传递显示相应性别学生的记录。练习8.2：针对xs表，创建一个存储过程student_proc4，用来根据传入的学生编号返回学生的出生日期。,27,项目8.2触发器,28,项目8.2触发器,29,项目8.2触发器,任务1：创建触发器任务2：删除触发器项目练习,30,任务1：创建触发器,提出任务1.创建一个触发器student_tri1，当对xs表执行增加、删除或修改操作时触发该触发器，输出字符串“该操作
14、已执行”。2.在数据库student的表xs上建立一个名为student_tri2的DELETE触发器，该触发器将实现对表xs中删除记录的操作给出警报，并取消当前的删除操作。分析任务触发器的创建可以使用企业管理器或T-SQL语句来完成。,31,任务1：创建触发器,完成任务1操作步骤：在【查询分析器】中，输入下列语句并执行createtriggerstudent_tri1onxsafterinsert,update,deleteasprint操作已执行,32,任务1：创建触发器,在【查询分析器】中，输入语句：insertintoxs(xh,xm)values(123456,lily)，执行该语句
15、后，将激活触发器student_tri1。所以在该窗口出现的提示信息为操作已执行，并且该记录亦为出现在xs表中。如图所示。,33,任务1：创建触发器,2.操作步骤：在【查询分析器】中，输入下列语句并执行该语句，用来创建触发器student_tri2。createtriggerstudent_tri2onxsinsteadofdeleteasprint禁止删除表中记录,34,任务1：创建触发器,在【查询分析器】中，输入语句：deletefromxswherexh=123456，执行该语句时，将激活触发器student_tri2。该记录将被禁止插入到xs表中。如图所示。,35,任务2：删除触发器,
16、基本语法删除触发器的命令DROPTRIGGERtrigger_name,36,任务2：删除触发器,提出任务删除触发器student_tri2。分析任务使用DROPTRIGGER命令删除触发器。,37,任务2：删除触发器,完成任务在【查询分析器】窗口，输入命令：DROPTRIGGERstudent_tri2执行该命令，则删除该触发器。,38,项目练习,练习8.3：在数据库student的表xs上建立一个名为student_tri3的UPDATE触发器，该触发器将被UPDATE操作激活，该触发器不允许用户修改xs表中的数据。练习8.4：修改student_tri3中的语句，使该触发器不允许用户修改xm一列的值，但其他列的值可以允许被修改。,39,项目总结,存储过程与触发器是数据库的一重要组成部分。存储过程可以把对数据库的复杂操作封装成一个独立的模块，相当于其他编程语言的函数、过程和方法，具有“编写一次随时调用”的特点。触发器则是自动调用执行的程序模块，可以检查数据输入和修改的正确性，保证数据的完整性。,40,项目实训,一、实训目的和要求掌握存储过程和触发器的创建掌握存
