存储过程和触发器PPT学习教案

1、会计学1存储过程和触发器存储过程和触发器2021年7月31日星期六2第1页/共64页2021年7月31日星期六3第2页/共64页2021年7月31日星期六4第3页/共64页2021年7月31日星期六5存储过程与函数区别：存储过程与函数区别： 存储过程的返回值只是说明过程是否成功完成，而不像大多数函数那样可以返回其它值。存储过程要返回其它数据，包括结果集时，需使用输出参数。不能直接调用，必须用存储过程的返回值只是说明过程是否成功完成，而不像大多数函数那样可以返回其它值。存储过程要返回其它数据，包括结果集时，需使用输出参数。不能直接调用，必须用EXEC 过程名（参数）或过程名（参数）或CALL 过

2、程名（参数）的形式来调用。过程名（参数）的形式来调用。第4页/共64页2021年7月31日星期六6视图视图存储过程存储过程语句语句只能是只能是SELECT语句语句可以包含程序流、逻辑以及可以包含程序流、逻辑以及SELECT语句语句输入、返回结果输入、返回结果不能接受参数，只能返不能接受参数，只能返回结果集回结果集可以有输入输出参数，也可可以有输入输出参数，也可以有返回值以有返回值典型应用典型应用多个表格的连接查询多个表格的连接查询完成某个特定的较复杂的任完成某个特定的较复杂的任务务第5页/共64页2021年7月31日星期六78.2 创建存储过程创建存储过程第6页/共64页2021年7月31日星

3、期六8n创建存储过程创建存储过程第7页/共64页2021年7月31日星期六9第8页/共64页2021年7月31日星期六10第9页/共64页2021年7月31日星期六11第10页/共64页2021年7月31日星期六12第11页/共64页2021年7月31日星期六13第12页/共64页2021年7月31日星期六14n【例例8-1】创建存储过程，返回每个学生的总分创建存储过程，返回每个学生的总分 ：第13页/共64页2021年7月31日星期六15n【例例8-1】创建存储过程，返回每个学生的总分创建存储过程，返回每个学生的总分 ：第14页/共64页2021年7月31日星期六16判断存储过程是否存在，若

4、存在先删除它判断存储过程是否存在，若存在先删除它创建过程，返回某个学生的总成绩创建过程，返回某个学生的总成绩执行过程，返回学生的总成绩执行过程，返回学生的总成绩n【例例8-2】创建带参数的存储过程，求某名同学的总分。创建带参数的存储过程，求某名同学的总分。第15页/共64页2021年7月31日星期六17n【例例8-3】存储过程中使用存储过程中使用RETURN，返回所有学生的总分，返回所有学生的总分第16页/共64页2021年7月31日星期六18结果为：结果为： 姓名姓名 家庭地址家庭地址 张军张军 海淀区海淀区 张朔张朔 西城区西城区n【例例8-4】存储过程使用通配符存储过程使用通配符%、_、

5、和和。求。求“张张”姓学生家庭地址姓学生家庭地址第17页/共64页2021年7月31日星期六19结果为：结果为：表名表名 学生成绩学生基本信息学生成绩学生基本信息创建过程，显示创建过程，显示“sys”开头的表名开头的表名执行过程，返回以执行过程，返回以“学学”开头的表名开头的表名n【例例8-5】创建用户定义的系统存储过程，显示以创建用户定义的系统存储过程，显示以“学学” 开头的所开头的所有表。缺省情况下返回以有表。缺省情况下返回以“sys”开头的所有表。开头的所有表。第18页/共64页2021年7月31日星期六20结果为：结果为： 姓名姓名家庭地址家庭地址 张军张军 海淀区海淀区 执行过程执行

6、过程USE 学生学籍数据库学生学籍数据库n【例例8-6】使用参数默认值使用参数默认值 NULL创建存储过程。创建存储过程。第19页/共64页2021年7月31日星期六21强制重新编译。若提供参数为非典型参数强制重新编译。若提供参数为非典型参数或数据有很大的改变时使用。建议尽量少或数据有很大的改变时使用。建议尽量少用该项，因它消耗较多系统资源。用该项，因它消耗较多系统资源。第20页/共64页2021年7月31日星期六22第21页/共64页2021年7月31日星期六23第22页/共64页2021年7月31日星期六24第23页/共64页2021年7月31日星期六25第24页/共64页2021年7月3

7、1日星期六26第25页/共64页2021年7月31日星期六27第26页/共64页2021年7月31日星期六28第27页/共64页2021年7月31日星期六29第28页/共64页2021年7月31日星期六302使用 sp_depends查看存储过程所引用的对象n注：n不报告对当前数据库以外对象的引用。不报告对当前数据库以外对象的引用。8.3.2 查看存储过程查看存储过程第29页/共64页2021年7月31日星期六31第30页/共64页2021年7月31日星期六328.3.3 删除存储过程删除存储过程第31页/共64页2021年7月31日星期六338.3.4 修改和重命名存储过程修改和重命名存储过

8、程第32页/共64页2021年7月31日星期六34储过程的权限和启动属性。储过程的权限和启动属性。第33页/共64页2021年7月31日星期六35USE StudentsIF EXISTS(SELECT name FROM sysobjects WHERE name=学分学分_课程课程AND type = P)DROP PROCEDURE 学分学分_课程课程USE StudentsCREATE PROCEDURE 学分学分_课程课程AS SELECT * FROM Courses WHERE 学分学分 = 4ALTER PROCEDURE 学分学分_课程课程 - 修改该存储过程修改该存储过程A

9、S SELECT * FROM Courses WHERE 学分学分 = 3SELECT o.id, c.text - 查看修改后的存储过程创建查看修改后的存储过程创建文本文本FROM sysobjects o INNER JOIN syscomments c ON o.id = c.idWHERE o.type = P and = 学分学分_课程课程第34页/共64页2021年7月31日星期六36第35页/共64页2021年7月31日星期六378.4 创建触发器创建触发器 第36页/共64页2021年7月31日星期六388.4.1 使用企业管理器创建触发器使用企业管理器创建触发

10、器第37页/共64页2021年7月31日星期六39第38页/共64页2021年7月31日星期六40发顺序不确定，并且无法控制。发顺序不确定，并且无法控制。第39页/共64页2021年7月31日星期六41第40页/共64页2021年7月31日星期六42第41页/共64页2021年7月31日星期六43第42页/共64页2021年7月31日星期六44第43页/共64页2021年7月31日星期六45第44页/共64页2021年7月31日星期六46第45页/共64页2021年7月31日星期六47第46页/共64页2021年7月31日星期六48n当插入或更新学生成绩时，触发器检查该课程是否为考查课，若是，

11、当插入或更新学生成绩时，触发器检查该课程是否为考查课，若是，则通过的成绩只能以则通过的成绩只能以60分计，未通过的成绩以分计，未通过的成绩以40分计。分计。USE StudentsIF EXISTS (SELECT name FROM sysobjects WHERE name = 考查课成绩检查考查课成绩检查 AND type = TR)DROP TRIGGER 考查课成绩检查考查课成绩检查CREATE TRIGGER 考查课成绩检查考查课成绩检查ON SCFOR INSERT, UPDATEASDECLARE 成绩成绩 smallint, 考核方式考核方式 varchar（8）,课程名课程

12、名 varchar（20）SELECT 成绩成绩 = I.Grade, 考核方式考核方式 =Courses.考核方式考核方式, 课程名课程名 = COurses.CNameFROM SC INNER JOIN inserted I ON SC.SNo = I.SNo JOIN Courses ON Courses. CNo=I. CNoIF (考核方式考核方式= 考查考查) and (成绩成绩 60 OR 成绩成绩 40) BEGIN RAISERROR (课程课程 %s为考查课，成绩以为考查课，成绩以60或或40计！计！, 16, 1, 课程名课程名) ROLLBACK TRANSACTIONEND第47页/共64页2021年7月31日星期六49第48页/共64页2021年7月31日星期六508.5 触发器的使用与管理触发器的使用与管理第49页/共64页2021年7月31日星期六51第50页/共64页2021年7月31日星期六52第51页/共64页2021年7月31日星期六53第52页/共64页2021年7月31日星期六54第53页/共64页2021年7月31日星期六55输出结果输出结果fname-smith第54页/共64页2021年7月31日星期六56