存储过程用户自定义函数与触发器省公共课一等奖全国赛课获奖课件.pptx

第第13章章 存放过程、用户自定义存放过程、用户自定义函数与触发器函数与触发器计算中心计算中心第1页本章内容本章内容13.1 13.1 存放过程概述存放过程概述13.2 13.2 经过企业管理器创建、修改和删除存经过企业管理器创建、修改和删除存放过程放过程13.3 13.3 存放过程执行与参数传递存放过程执行与参数传递13.4 13.4 用户自定义函数用户自定义函数13.5 13.5 触发器及其作用、效果演示触发器及其作用、效果演示第2页13.1 13.1 存放过程概述存放过程概述l存放过程（存放过程（Stored Procedure）l是什么？是什么？l l简单说：简单说：简单说：简单说：存放过程是将惯用或很复杂工作，预存放过程是将惯用或很复杂工作，预先用先用T-SQL语句写好并用一个指定名称存放起语句写好并用一个指定名称存放起来语句集合。来语句集合。l l书本定义：书本定义：书本定义：书本定义：是是SQL Server服务器上一组服务器上一组预编译预编译预编译预编译T-SQL语句，用于完成某项任务，它能够接收语句，用于完成某项任务，它能够接收参数、返回状态值和参数值，而且能够嵌套调参数、返回状态值和参数值，而且能够嵌套调用。用。l l举例举例举例举例第3页13.1 13.1 存放过程概述存放过程概述l为何要使用存放过程？为何要使用存放过程？l存放过程在创建时即在服务器上进行编译，所存放过程在创建时即在服务器上进行编译，所以执行起来比以执行起来比SQLSQL语句语句快快快快，且能降低网络通信，且能降低网络通信负担。负担。l能够在单个存放过程中执行一系列能够在单个存放过程中执行一系列SQLSQL语句，语句，完完完完成复杂操作成复杂操作成复杂操作成复杂操作。l存放过程存放过程能够重复使用能够重复使用能够重复使用能够重复使用，降低数据库开发人员，降低数据库开发人员工作量工作量 。l l安全性高安全性高安全性高安全性高，可设定只有一些用户才含有对指定，可设定只有一些用户才含有对指定存放过程使用权。存放过程使用权。第4页13.1 13.1 存放过程概述存放过程概述l存放过程类型存放过程类型l系统存放过程系统存放过程比如：比如：EXEC sp_helpdbl l用户定义存放过程：用户定义存放过程：由用户创建并能完成某由用户创建并能完成某一特定功效存放过程。一特定功效存放过程。l暂时存放过程暂时存放过程l扩展存放过程扩展存放过程比如：比如：比如：比如：EXEC xp_cmdshell dir d:第5页13.1 13.1 存放过程概述存放过程概述l存放过程功效存放过程功效(1)接收输入参数并以输出参数形式为调用过程接收输入参数并以输出参数形式为调用过程或批处理返回多个值。或批处理返回多个值。(2)包含执行数据库操作编程语句，包含调用其包含执行数据库操作编程语句，包含调用其它过程。它过程。(3)为调用过程或批处理返回一个状态值，以表为调用过程或批处理返回一个状态值，以表示成功或失败示成功或失败(及失败原因及失败原因)。l存放过程特点存放过程特点第6页13.2 13.2 创建、修改和删除存放过程创建、修改和删除存放过程l创建存放过程指导标准创建存放过程指导标准l防止出现存放过程拥有者和底层对象拥有者不一样防止出现存放过程拥有者和底层对象拥有者不一样情况，提议由情况，提议由dbo用户用户拥有数据库中全部对象拥有数据库中全部对象l每个存放过程完成单个任务每个存放过程完成单个任务l命名当地存放过程时候，防止使用命名当地存放过程时候，防止使用“sp_”前缀前缀l尽可能少使用暂时存放过程，以防止频繁连接尽可能少使用暂时存放过程，以防止频繁连接 tempdb 里系统表里系统表l不要直接从不要直接从 syscomments 系统表里删除项系统表里删除项第7页13.2 13.2 创建、修改和删除存放过程创建、修改和删除存放过程l创建存放过程创建存放过程(1)开启企业管理器，登录到要使用服务器。开启企业管理器，登录到要使用服务器。(2)选择要创建存放过程数据库，在左窗格中单击选择要创建存放过程数据库，在左窗格中单击“存放存放过程过程”文件夹，此时在右窗格中显式该数据库全部存文件夹，此时在右窗格中显式该数据库全部存放过程，如图放过程，如图13-1所表示。所表示。图图13-1 13-1 企业管理器中显示存放过程信息企业管理器中显示存放过程信息第8页13.2 13.2 创建、修改和删除存放过程创建、修改和删除存放过程(3)右击右击“存放过程存放过程”文件夹，在弹出菜单中选择文件夹，在弹出菜单中选择【新建存新建存放过程放过程】选项，打开创建存放过程对话框，以下列图。选项，打开创建存放过程对话框，以下列图。图图13-2 13-2 创建存放过程对话框创建存放过程对话框第9页13.2 13.2 创建、修改和删除存放过程创建、修改和删除存放过程(4)在在“文本文本”编辑框中输入存放过程正文。编辑框中输入存放过程正文。(5)单击单击“检验语法检验语法”按钮，检验语法是否正确。按钮，检验语法是否正确。(6)单击单击“确定确定”按钮，保留存放过程。按钮，保留存放过程。图图13-3 13-3 输入存放过程内容输入存放过程内容输入内输入内容区域容区域提醒：提醒：提醒：提醒：新创建存放过新创建存放过程名字包含程名字包含在在CREATE CREATE PROCEDUREPROCEDURE语句语句中，不在保留中，不在保留时输入。时输入。第10页13.2 13.2 创建、修改和删除存放过程创建、修改和删除存放过程l修改存放过程修改存放过程(1)在企业管理器中展开服务器组，再展开服务器。在企业管理器中展开服务器组，再展开服务器。(2)展开展开“数据库数据库”文件夹，再展开要修改存放过程数据库。文件夹，再展开要修改存放过程数据库。(3)在要修改存放过程上右击，并在弹出快捷菜单中选择在要修改存放过程上右击，并在弹出快捷菜单中选择【属性属性属性属性】项，或项，或双击双击双击双击该存放过程，弹出该存放过程，弹出“存放过程属性存放过程属性”对话框。对话框。图图13-4 控制台目录控制台目录第11页13.2 13.2 创建、修改和删除存放过程创建、修改和删除存放过程l删除存放过程删除存放过程l类似于删除表操作，在存放过程显示列表中选择要删类似于删除表操作，在存放过程显示列表中选择要删除存放过程（能够用除存放过程（能够用ctrl或或shift选多个）。选多个）。l右键单击选中存放过程，在弹出快捷菜单中选择右键单击选中存放过程，在弹出快捷菜单中选择【删删除除】项，打开项，打开“除去对象除去对象”对话框，以下列图，单击对话框，以下列图，单击【全部除去全部除去】按钮，完成删除。按钮，完成删除。图图13-5“13-5“除去对象除去对象”对话框对话框显示与该存显示与该存放过程相关放过程相关对象对象第12页13.2 13.2 创建、修改和删除存放过程创建、修改和删除存放过程l创建存放过程时，需要确定存放过程三个创建存放过程时，需要确定存放过程三个组成部分：组成部分：l l参数参数参数参数，全部输入参数以及传给调用者输出参数。，全部输入参数以及传给调用者输出参数。l l过程体过程体过程体过程体，被执行针对数据库操作语句，包含调，被执行针对数据库操作语句，包含调用其它存放过程语句；用其它存放过程语句；l l返回状态返回状态返回状态返回状态，返回给调用者状态值，以指明调用，返回给调用者状态值，以指明调用是成功还是失败。是成功还是失败。第13页13.2 13.2 创建、修改和删除存放过程创建、修改和删除存放过程l不含参数存放过程不含参数存放过程例例例例13-113-1：查询学生成绩。查询学生成绩。CREATE PROCEDURE 查询学生成绩查询学生成绩 ASSELECT 学生表学生表.姓名姓名,课程表课程表.课程名课程名,选课表选课表.成绩成绩FROM 选课表选课表 INNER JOIN 学生表学生表 ON 选课表选课表.学号学号=学生表学生表.学号学号 INNER JOIN 课程表课程表 ON 选课表选课表.课程号课程号=课程表课程表.课程号课程号执行：执行：EXEC 查询学生成绩查询学生成绩第14页13.2 13.2 创建、修改和删除存放过程创建、修改和删除存放过程例例例例13-213-2：查找查找1100之间完全平方数。之间完全平方数。CREATE PROCEDURE 查找完全平方数查找完全平方数 ASdeclare n intset n=1while n*n=85set 评价评价=优异优异elsebeginif 平均分平均分=60set 评价评价=普通普通elseset 评价评价=差差endinsert into 学生考试评价学生考试评价(学号学号,平均成绩平均成绩,考试评价考试评价)values(学号学号,平均分平均分,评价评价)或经过或经过CASE语句语句完成，见备完成，见备注注第25页13.3 13.3 存放过程执行与参数传递存放过程执行与参数传递l返回存放过程状态返回存放过程状态l用用RETURN语句定义返回值语句定义返回值l存放过程能够返回整型状态值，表示过程是否存放过程能够返回整型状态值，表示过程是否成功执行，或者过程失败原因。成功执行，或者过程失败原因。l假如存放过程没有显式设置返回代码值，则假如存放过程没有显式设置返回代码值，则SQL Server返回代码为返回代码为 0，表示成功执行；若返，表示成功执行；若返回回-1-99之间整数，表示没有成功执行。也能够之间整数，表示没有成功执行。也能够使用使用RETURN语句，用大于语句，用大于0或小于或小于-99整数来整数来定义自己返回状态值，以表示不一样执行结果。定义自己返回状态值，以表示不一样执行结果。第26页13.3 13.3 存放过程执行与参数传递存放过程执行与参数传递例例例例13-613-6：创建存放过程，输入课程号，返回课程名称。在存放创建存放过程，输入课程号，返回课程名称。在存放过程中，用值过程中，用值15表示用户没有提供参数；值表示用户没有提供参数；值-101表示没有表示没有输入课程号；值输入课程号；值0表示过程运行没有犯错。表示过程运行没有犯错。CREATE PROCEDURE dbo.查询课程名查询课程名 课程号课程号 as varchar(10)=nullASif 课程号课程号=nullreturn 15if not exists(select*from 课程表课程表 where 课程号课程号=课程号课程号)return-101select 课程名课程名 from 课程表课程表 where 课程号课程号=课程号课程号第27页13.3 13.3 存放过程执行与参数传递存放过程执行与参数传递l捕捉返回状态值捕捉返回状态值l在执行过程时，要正确接收返回状态值，必须使用语句：在执行过程时，要正确接收返回状态值，必须使用语句：EXECUTE status_var=procedure_name EXECUTE status_var=procedure_name 参数参数参数参数 例例例例13-6存放过程查询课程名执行时使用以下语句：存放过程查询课程名执行时使用以下语句：DECLARE return_status intEXEC return_status=查询课程名查询课程名 c01IF return_status=15 SELECT 语法错误，未输入参数语法错误，未输入参数!ELSE IF return_status=-101 SELECT 没有找到该课程号没有找到该课程号.l执行时，对不一样输入值返回不一样状态值。执行时，对不一样输入值返回不一样状态值。第28页13.4 13.4 用户自定义函数用户自定义函数l用户自定义函数概述用户自定义函数概述l内置函数不能满足用户（应用程序）需求，创内置函数不能满足用户（应用程序）需求，创建自己定义函数建自己定义函数l参数：零个、一个或参数：零个、一个或 多个多个返回值：一个返回值：一个l单个数值单个数值l一个表一个表l l依据函数返回值形式不一样依据函数返回值形式不一样依据函数返回值形式不一样依据函数返回值形式不一样将用户定义函数分将用户定义函数分为为3种类型种类型第29页13.4 13.4 用户自定义函数用户自定义函数(1)标量函数标量函数标量函数标量函数标量函数标量函数返回一个确定类型标量值返回一个确定类型标量值，其函数值类型为，其函数值类型为SQL Server系统数据类型（除系统数据类型（除text、ntext、image、cursor、timestamp、table类型外）。函数体语句定义在类型外）。函数体语句定义在BEGINEND语句内。语句内。(2)内嵌表值函数内嵌表值函数内嵌表值函数内嵌表值函数内嵌表值函数返回内嵌表值函数返回函数值为一个表函数值为一个表。内嵌表值函数函数体。内嵌表值函数函数体不使用不使用BEGINEND语句语句，其返回表是，其返回表是RETURN子句中子句中SELECT命令查命令查询结果集，其功效询结果集，其功效相当于一个参数化视图相当于一个参数化视图。(3)多语句表值函数多语句表值函数多语句表值函数多语句表值函数多语句表值函数能够看作标量函数和内嵌表值函数结合体。其多语句表值函数能够看作标量函数和内嵌表值函数结合体。其函函数值也是一个表数值也是一个表，但函数体用，但函数体用BEGINEND语句定义，返回值表语句定义，返回值表中数据由函数体中语句插入。中数据由函数体中语句插入。第30页13.4 13.4 用户自定义函数用户自定义函数l创建用户自定义函数创建用户自定义函数图图13-6 13-6 创建用户自定义函数对话框创建用户自定义函数对话框第31页13.4 13.4 用户自定义函数用户自定义函数l例例13-7：创建标量函数：创建标量函数DatetoQuarter，将，将输入日期数据转换为该日期对应季度值。如输入日期数据转换为该日期对应季度值。如输入输入-8-5，返回，返回3Q，表示，表示3季度。季度。lCREATE FUNCTION DatetoQuarter(dqdate datetime)lRETURNS char(6)lASlBEGIN RETURN(datename(q,dqdate)+Q+datename(yyyy,dqdate)lEND第32页例例例例13-813-8：创建创建标量函数标量函数标量函数标量函数NumToStr，输入阿拉伯数字，输入阿拉伯数字09，输出对，输出对应汉字大写。应汉字大写。CREATE FUNCTION NumToStr(num as int)RETURNS char(2)AS BEGIN declare ChineseCap as char(2)set ChineseCap=(case num when 0 then 零零when 1 then 壹壹when 2 then 贰贰when 3 then 叁叁when 4 then 肆肆when 5 then 伍伍when 6 then 陆陆when 7 then 柒柒when 8 then 捌捌when 9 then 玖玖 end)return ChineseCapEND第33页13.4 13.4 用户自定义函数用户自定义函数例例例例13-913-9：经过自定义函数依据输入课程号，返回对经过自定义函数依据输入课程号，返回对应课程名。应课程名。CREATE FUNCTION F课程名课程名(courseID char(10)RETURNS char(20)AS BEGIN declare courseName char(20)select courseName=课程名课程名 from 课程表课程表 where 课程号课程号=courseIDreturn courseNameEND第34页13.4 13.4 用户自定义函数用户自定义函数例例例例13-1013-10：创建一个创建一个内嵌表值函数内嵌表值函数内嵌表值函数内嵌表值函数stuinfo，输入学，输入学生学号，返回学生姓名及各科成绩。生学号，返回学生姓名及各科成绩。CREATE FUNCTION stuinfo(xh varchar(10)RETURNS table AS return(select 姓名姓名,课程号课程号,成绩成绩from 学生表学生表 inner join 选课表选课表 on 学生表学生表.学号学号=选课表选课表.学号学号where 选课表选课表.学号学号=xh)第35页例例例例13-1113-11：创建创建多语句表值函数多语句表值函数多语句表值函数多语句表值函数Stu_Info，依据输入学号、课程号，依据输入学号、课程号，返回对应姓名、课程名和成绩。返回对应姓名、课程名和成绩。CREATE FUNCTION Stu_Info(学号学号 varchar(10),课程号课程号 varchar(10)RETURNS stu_info table(stuName varchar(8),courseName varchar(20),score smallint)AS BEGIN declare stuName as varchar(8),courseName as varchar(20)declare score as smallintselect stuName=姓名姓名 from 学生表学生表 where 学号学号=学号学号select courseName=课程名课程名 from 课程表课程表 where 课程号课程号=课程号课程号select score=成绩成绩 from 选课表选课表 where 学号学号=学号学号 and 课程号课程号=课程号课程号insert into stu_infovalues(stuName,courseName,score)returnEND第36页13.4 13.4 用户自定义函数用户自定义函数l修改和删除用户自定义函数修改和删除用户自定义函数l用企业管理器修改用户定义函数，选择要修改用企业管理器修改用户定义函数，选择要修改函数，函数，双击或单击右键双击或单击右键双击或单击右键双击或单击右键，从快捷菜单中选择，从快捷菜单中选择“属性属性”选项，打开图选项，打开图13-6所表示所表示“用户定义函用户定义函数属性数属性”对话框。在该对话框中能够修改用户对话框。在该对话框中能够修改用户定义函数函数体、参数等。定义函数函数体、参数等。l从快捷菜单中选择从快捷菜单中选择“删除删除”选项，打开选项，打开“除去除去对象对象”对话框，则可删除用户自定义函数。对话框，则可删除用户自定义函数。第37页13.4 13.4 用户自定义函数用户自定义函数l用户自定义函数使用用户自定义函数使用l当调用标量值函数时，必须加上当调用标量值函数时，必须加上“全部者全部者”，通常是通常是dbo（但不是绝对，能够在企业管理器（但不是绝对，能够在企业管理器中中“用户定义函数用户定义函数”中查看全部者）中查看全部者）l当调用表值函数时，能够只使用函数名。当调用表值函数时，能够只使用函数名。第38页13.4 13.4 用户自定义函数用户自定义函数例例例例13-1213-12：调用例调用例13-7函数，返回当前日期对应季度值。函数，返回当前日期对应季度值。select dbo.DatetoQuarter(getdate()例例例例13-1313-13：调用例调用例13-9函数，返回函数，返回c02对应课程名。对应课程名。select dbo.F课程名课程名(c02)例例例例13-1413-14：调用例调用例13-10函数，返回学号为函数，返回学号为s001学生姓名和学生姓名和各科成绩。各科成绩。select*from stuinfo(s001)例例例例13-1513-15：调用例：调用例13-11，返回学号为，返回学号为 s031课程号为课程号为c01学生学生姓名、课程名和成绩。姓名、课程名和成绩。select*from stu_info(s031,c01)第39页例例例例13-1613-16：使用例使用例13-8函数，完成函数，完成099数字大写转换。数字大写转换。declare num as intdeclare sNum as varchar(2),ChCap as varchar(10)set num=15if num99 print 对不起，我有点弱，只能转换对不起，我有点弱，只能转换099数字！数字！elsebegin if num10set ChCap=dbo.NumToStr(num)elsebegin set sNum=cast(num as varchar(2)set ChCap=dbo.NumTostr(left(sNum,1)+拾拾+dbo.NumToStr(right(sNum,1)endendprint ChCap设置设置num值值为为30，查看结，查看结果。该程序不果。该程序不足怎样完善？足怎样完善？第40页13.5 13.5 触发器及其作用、效果演示触发器及其作用、效果演示l触发器是一个特殊类型存放过程。触发器是一个特殊类型存放过程。l l触发器主要是经过事件进行触发而被执行，而存触发器主要是经过事件进行触发而被执行，而存触发器主要是经过事件进行触发而被执行，而存触发器主要是经过事件进行触发而被执行，而存放过程能够经过过程名字直接调用。放过程能够经过过程名字直接调用。放过程能够经过过程名字直接调用。放过程能够经过过程名字直接调用。当对某一表当对某一表进行进行UPDATEUPDATE、INSERTINSERT、DELETEDELETE操作时，操作时，SQL Server就会自动执行触发器所定义就会自动执行触发器所定义SQL语句，语句，从而确保对数据处理必须符合由这些从而确保对数据处理必须符合由这些SQL语句所语句所定义规则。定义规则。l触发器主要作用就是能够实现由主键和外键所不触发器主要作用就是能够实现由主键和外键所不能确保参考完整性和数据一致性。能确保参考完整性和数据一致性。第41页13.5 13.5 触发器及其作用、效果演示触发器及其作用、效果演示例例例例13-1713-17：为为“课程表课程表”创建触发器创建触发器TRIGGER_课程课程表，当执行删除时激活，判断删除课程号是否已表，当执行删除时激活，判断删除课程号是否已被选，已选课程拒绝删除。被选，已选课程拒绝删除。CREATE TRIGGER TRIGGER_课程表课程表 ON dbo.课程表课程表 FOR DELETE ASif exists(select*from deleted inner join 选课表选课表 on deleted.课程号课程号=选课表选课表.课程号课程号)beginprint 在选课表中有该门课程，不能删除在选课表中有该门课程，不能删除rollback tranreturnend第42页小结与提问小结与提问l存放过程、用户自定义函数和触发器存放过程、用户自定义函数和触发器第43页