存储过程用户自定义函数与触发器PPT课件

.,1,第十三章存储过程、用户自定义函数与触发器,计算中心,.,2,本章内容,13.1存储过程概述13.2通过企业管理器创建、修改和删除存储过程13.3存储过程的执行与参数传递13.4用户自定义函数13.5触发器及其作用、效果演示,.,3,13.1存储过程概述,存储过程（StoredProcedure）是什么？简单的说：存储过程是将常用的或很复杂的工作，预先用T-SQL语句写好并用一个指定的名称存储起来的语句集合。课本的定义：是SQLServer服务器上一组预编译的T-SQL语句，用于完成某项任务，它可以接受参数、返回状态值和参数值，并且可以嵌套调用。举例,.,4,13.1存储过程概述,为什么要使用存储过程？存储过程在创建时即在服务器上进行编译，所以执行起来比SQL语句快，且能减少网络通信的负担。可以在单个存储过程中执行一系列SQL语句，完成复杂的操作。存储过程可以重复使用，减少数据库开发人员的工作量。安全性高，可设定只有某些用户才具有对指定存储过程的使用权。,.,5,13.1存储过程概述,存储过程的类型系统存储过程例如：EXECsp_helpdb用户定义存储过程：由用户创建并能完成某一特定功能的存储过程。临时存储过程扩展存储过程例如：EXECxp_cmdshelldird:,.,6,13.1存储过程概述,存储过程的功能(1)接收输入参数并以输出参数的形式为调用过程或批处理返回多个值。(2)包含执行数据库操作的编程语句，包括调用其他过程。(3)为调用过程或批处理返回一个状态值，以表示成功或失败(及失败原因)。存储过程特点,.,7,13.2创建、修改和删除存储过程,创建存储过程的指导原则避免出现存储过程的拥有者和底层对象的拥有者不同的情况，建议由dbo用户拥有数据库中所有对象每个存储过程完成单个任务命名本地存储过程的时候，避免使用“sp_”前缀尽量少使用临时存储过程，以避免频繁连接tempdb里的系统表不要直接从syscomments系统表里删除项,.,8,13.2创建、修改和删除存储过程,创建存储过程(1)启动企业管理器，登录到要使用的服务器。(2)选择要创建存储过程的数据库，在左窗格中单击“存储过程”文件夹，此时在右窗格中显式该数据库的所有存储过程，如图13-1所示。,.,9,13.2创建、修改和删除存储过程,(3)右击“存储过程”文件夹，在弹出菜单中选择【新建存储过程】选项，打开创建存储过程对话框，如下图。,图13-2创建存储过程对话框,.,10,13.2创建、修改和删除存储过程,(4)在“文本”编辑框中输入存储过程正文。(5)单击“检查语法”按钮，检查语法是否正确。(6)单击“确定”按钮，保存存储过程。,提示：新创建存储过程名字包含在CREATEPROCEDURE语句中，不在保存时输入。,.,11,13.2创建、修改和删除存储过程,修改存储过程(1)在企业管理器中展开服务器组，再展开服务器。(2)展开“数据库”文件夹，再展开要修改存储过程的数据库。(3)在要修改的存储过程上右击，并在弹出的快捷菜单中选择【属性】项，或双击该存储过程，弹出“存储过程属性”对话框。,图13-4控制台目录,.,12,13.2创建、修改和删除存储过程,删除存储过程类似于删除表操作，在存储过程显示列表中选择要删除的存储过程（可以用ctrl或shift选多个）。右键单击选中的存储过程，在弹出的快捷菜单中选择【删除】项，打开“除去对象”对话框，如下图，单击【全部除去】按钮，完成删除。,图13-5“除去对象”对话框,显示与该存储过程相关的对象,.,13,13.2创建、修改和删除存储过程,创建存储过程时，需要确定存储过程的三个组成部分：参数，所有的输入参数以及传给调用者的输出参数。过程体，被执行的针对数据库的操作语句，包括调用其它存储过程的语句；返回状态，返回给调用者的状态值，以指明调用是成功还是失败。,.,14,13.3存储过程的执行与参数传递,参数存储过程和调用者之间需要通过参数来交换数据，可以按输入的参数执行，也可由参数输出执行结果。例如：查询学号为s2008001的c01课程的成绩。输入参数：学号（s2008001）和课程号（c01）输出参数：成绩SQLServer支持这两类参数。,.,15,13.3存储过程的执行与参数传递,输入参数输入参数允许调用程序为存储过程传送数据值。定义存储过程的输入参数必须在CREATEPROCEDURE语句中声明一个或多个变量及数据类型。例13-1：创建带参数的存储过程，输入学生学号，返回学生姓名、性别等个人信息。CREATEPROCEDUREdbo.查询指定学生信息学号varchar(10)ASselect*from学生表where学号=学号提示：定义参数的数据类型需和表内字段类型一致。,.,16,13.3存储过程的执行与参数传递,存储过程的执行语法格式：EXECUTEreturn_status=procedure_name;number|procedure_name_varparameter=value|variableOUTPUT|DEFAULT,.nWITHRECOMPILE,.,17,13.3存储过程的执行与参数传递,执行带输入参数的存储过程在查询分析器中执行例13-1：exec查询指定学生信息s2008001exec执行（execute）查询指定学生信息存储过程名s2008001输入参数运行结果：提示：需要根据输入参数的数据类型加定界符。,.,18,13.3存储过程的执行与参数传递,执行时，参数可以由位置标识，也可以由名字标识。例如，定义一个具有3个参数的存储过程：CREATEPROCmyprocval1int,val2int,val3intAS.参数以位置传递：EXECmyproc10,20,15参数以名字传递，每个值由对应的参数名引导：EXECmyprocval2=20,val1=10,val3=15按名字传递参数比按位置传递参数具有更大的灵活性。但是，按位置传递参数却具有更快的速度。,.,19,13.3存储过程的执行与参数传递,输出参数输出参数允许存储过程将数据值传回调用程序。用OUTPUT关键字指出能返回到调用它的存储过程。例13-2：创建存储过程，查询指定学生的某门课程成绩。CREATEPROCEDUREdbo.查询成绩学号varchar(10),课程号varchar(10),scoreintoutputASselectscore=成绩from选课表where学号=学号and课程号=课程号,.,20,13.3存储过程的执行与参数传递,执行带输出参数的存储过程在查询分析器中执行例13-2：declare学生成绩intexec查询成绩s2008001,c01,学生成绩outputselect学生成绩运行结果：提示：输出参数（返回值）需要提前声明，数据类型应同输出参数的数据类型相匹配。EXEC语句需要关键字OUTPUT以允许参数值返回变量。,.,21,13.3存储过程的执行与参数传递,返回存储过程的状态用RETURN语句定义返回值存储过程可以返回整型状态值，表示过程是否成功执行，或者过程失败的原因。如果存储过程没有显式设置返回代码的值，则SQLServer返回代码为0，表示成功执行；若返回-1-99之间的整数，表示没有成功执行。也可以使用RETURN语句，用大于0或小于-99的整数来定义自己的返回状态值，以表示不同的执行结果。,.,22,13.3存储过程的执行与参数传递,例13-3：创建存储过程，输入课程号，返回课程名称。在存储过程中，用值15表示用户没有提供参数；值-101表示没有输入课程号；值0表示过程运行没有出错。CREATEPROCEDUREdbo.查询课程名课程号asvarchar(10)=nullASif课程号=nullreturn15ifnotexists(select*from课程表where课程号=课程号)return-101select课程名from课程表where课程号=课程号,.,23,13.3存储过程的执行与参数传递,捕获返回状态值在执行过程时，要正确接收返回的状态值，必须使用语句：EXECUTEstatus_var=procedure_name参数例13-3的存储过程查询课程名执行时使用以下语句：DECLAREreturn_statusintEXECreturn_status=查询课程名c01IFreturn_status=15SELECT语法错误，未输入参数!ELSEIFreturn_status=-101SELECT没有找到该课程号.执行时，对不同的输入值返回不同的状态值。,.,24,13.4用户自定义函数,用户自定义函数概述根据函数返回值形式的不同将用户定义函数分为3种类型。(1)标量函数标量函数返回一个确定类型的标量值，其函数值类型为SQLServer的系统数据类型（除text、ntext、image、cursor、timestamp、table类型外）。函数体语句定义在BEGINEND语句内。(2)内嵌表值函数内嵌表值函数返回的函数值为一个表。内嵌表值函数的函数体不使用BEGINEND语句，其返回的表是RETURN子句中的SELECT命令查询的结果集，其功能相当于一个参数化的视图。(3)多语句表值函数多语句表值函数可以看作标量函数和内嵌表值函数的结合体。其函数值也是一个表，但函数体也用BEGINEND语句定义，返回值的表中的数据由函数体中的语句插入。,.,25,13.4用户自定义函数,创建用户自定义函数,图13-6创建用户自定义函数对话框,.,26,13.4用户自定义函数,例13-4：创建一个用户定义函数DatetoQuarter，将输入的日期数据转换为该日期对应的季度值。如输入2006-8-5，返回3Q2006，表示2006年3季度。CREATEFUNCTIONDatetoQuarter(dqdatedatetime)RETURNSchar(6)ASBEGINRETURN(datename(q,dqdate)+Q+datename(yyyy,dqdate)END,.,27,13.4用户自定义函数,例13-5：通过自定义函数根据输入课程号，返回对应的课程名。CREATEFUNCTIONF课程名(courseIDchar(10)RETURNSchar(20)ASBEGINdeclarecourseNamechar(20)selectcourseName=课程名from课程表where课程号=courseIDreturncourseNameEND,.,28,13.4用户自定义函数,例13-6：创建一个表值函数stuinfo，输入学生学号，返回学生姓名及各科成绩。CREATEFUNCTIONstuinfo(xhvarchar(10)RETURNStableASreturn(select姓名,课程号,成绩from学生表innerjoin选课表on学生表.学号=选课表.学号where选课表.学号=xh),.,29,13.4用户自定义函数,修改和删除用户自定义函数用企业管理器修改用户定义函数，选择要修改函数，双击或单击右键，从快捷菜单中选择“属性”选项，打开图13-6所示的“用户定义函数属性”对话框。在该对话框中可以修改用户定义函数的函数体、参数等。从快捷菜单中选择“删除”选项，打开“除去对象”对话框，则可删除用户自定义函数。,.,30,13.4用户自定义函数,用户自定义函数的使用当调用标量值函数时，必须加上“所有者”，通常是dbo（但不是绝对，可以在企业管理器中的“用户定义函数”中查看所有者）当调用表值函数时，可以只使用函数名。例13-7：调用例13-4函数，返回当前日期对应的季度值。selectdbo.DatetoQuarter(getdate()例13-8：调用例13-6函数，返回学号为s2008001的学生姓名和各科成绩。select*fromstuinfo(s2008001),.,31,13.5触发器及其作用、效果演示,触发器是一种特殊类型的存储过程。触发器主要是通过事件进行触发而被执行的，而存储过程可以通过过程名字直接调用。当对某一表进行UPDATE、INSERT、DELETE操作时，SQLServer就会自动执行触发器所定义的SQL语句，从而确保对数据的处理必须符合由这些SQL语句所定义的规则。触发器的主要作用就是能够实现由主键和外键所不能保证的参照完整性和