第09章 存储过程和触发器-MySQL.pptx

《第09章 存储过程和触发器-MySQL.pptx》由会员分享，可在线阅读，更多相关《第09章 存储过程和触发器-MySQL.pptx（126页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、课程主讲人：第09章 存储过程和触发器-MySQLMySQL数据库设计与应用主讲：张成叔微信（QQ）：7153265 微信公众号：张成叔第 9 章 索引、视图和事务一起努力 共同提高工作情境和任务n用户在使用“高校成绩管理系统”时，经常要查询学生信息，由于该查询在程序中使用频率非常高，开发人员可以通过编程的方式，实现重复使用且高性能n在实际中，要删除某个学生信息时，必须将成绩表中的成绩也要删除，以确保数据的完整性与一致性，可以通过触发器来实现n创建和管理存储过程n创建和管理触发器知识和技能目标n理解编程访问数据库的概念和途径n理解存储过程的概念和工作过程n掌握存储过程的创建和管理n掌握触发器的

2、创建和管理n灵活应用存储过程和触发器解决实际问题本章重点和难点n通过编程来访问数据库n使用存储过程和触发器解决实际问题知识技能结构图 MySQL编程基础2 2 存储过程1 13 3 触发器内容结构图n在实际应用开发中，需要通过编程来访问数据库u提高访问数据库的效率u保证数据库的安全uMySQL数据库也具有编程的功能u和普通程序设计语言有相似之处n通过编程来访问数据库的主要途径u存储过程u触发器MySQL编程基础nMySQL编程u可以使用常量、变量、函数u有自己的流程控制语句来实现分支和循环MySQL编程基础n常量是表示一个特定数据值的符号u在程序运行过程中始终保持不变u它的使用格式由它所表示的

3、值的数据类型决定n常用的常量u字符串常量u数值常量u时间日期常量u布尔值u空值MySQL编程基础常量n不同常量分析u字符串常量必须用单引号或双引号括起来u数值常量包括整数常量和使用小数点的浮点数常量u日期时间常量也需要用单引号将表示日期时间的字符串括起来u布尔值只有TRUE和FALSE两个可能值uNULL值适用于各种数据类型，通常表示“没有值”、“无数据”等意义MySQL编程基础常量n变量用于临时存放数据u它的值在程序运行的过程中可以被改变n变量分类u用户变量u局部变量u会话变量u全局变量u会话变量和全局变量统称为系统变量MySQL编程基础变量n变量的定义有两种方式u方式一：使用SET或SEL

4、ECT直接赋值，变量名以开头u方式二：以DECLARE关键字声明变量n使用SET或SELECT直接赋值u格式：SET a=1;u可以在一个会话的任何地方声明u作用域是整个会话u称为：用户变量MySQL编程基础变量n以DECLARE关键字声明变量MySQL编程基础变量n只能在存储过程中使用，称为存储过程变量nDEFAULT子句给变量指定了一个默认值n只能在BEGIN-END语句中声明，而且在BEGIN后的第一行声明分析分析n变量的定义有两种方式的区别u在调用存储过程时，以DECLARE声明的变量都会被初始化为nullu会话变量（即开头的变量）则不会被再初始化 在一个会话内，只须初始化一次 之后在

5、会话内都是使用上一次计算的结果 就相当于在是这个会话内的全局变量MySQL编程基础变量n用户变量u用户变量在客户端链接到数据库实例整个过程中都是有效的uMySQL中用户变量不用事前定义u在用的时候直接用“变量名”即可u必须使用“”开头MySQL编程基础变量n两种赋值符号u使用SET时可以用“=”或“:=”u使用SELECT时必须用“:=”MySQL编程基础变量分析分析示例示例MySQL编程基础变量示例示例n局部变量u只在当前“BEGIN-END”代码块中有效u其作用域仅限于该语句块u在该语句块执行完毕后，局部变量就消失了nDECLARE语句专门用于定义局部变量u可以使用default来说明默认

6、值MySQL编程基础变量n全局变量和会话变量u都称为系统变量n全局变量u在MySQL启动时由服务器自动将它们初始化为默认值u默认值可以通过更改my.ini这个文件来更改n会话变量u在每次建立一个新的连接的时候，由MySQL来初始化uMySQL会将当前所有全局变量的值复制一份，来做为会话变量MySQL编程基础变量n全局变量和会话变量的区别u对全局变量的修改会影响到整个服务器u对会话变量的修改，只会影响到当前的会话，即当前的数据库连接MySQL编程基础变量n在MySQL中，语句结束标志u默认：以分号为结束标志u程序段“BEGIN-END”中可能包含多个SQL语句 如果：每个SQL语句都以分号结尾

7、结果：服务器处理程序遇到第1个分号就会认为程序结束 造成：程序无法正常执行n修改结束标志u使用DELIMITER命令将SQL语句的结束标志修改为其他符号MySQL编程基础修改语句结束标志n该命令不需要在用“;”结束n“$”u用户定义的结束符u通常这个符号可以是一些特殊的符号u例如：2个“#”、2个“/”等分析分析MySQL编程基础修改语句结束标志 语法语法n避免使用反斜杠(“”)字符，是MySQL的转义字符n要恢复默认的“;”为结束符u需要再次使用该语句修改uDELIMITER ;分析分析MySQL编程基础修改语句结束标志 语法语法一起努力 共同提高nMySQL中编程的主要流程u顺序u分支u循

8、环n流程控制语句uIF语句uCASE语句uWHILE语句流程控制语句n条件表达式u关系运算符和逻辑运算符组成的表达式u它的值决定IF分支的执行路线分析分析流程控制语句IF语句 语法语法n语句块u条件表达式成立时执行语句块1，不成立是执行语句块2u如果语句块的语句多于一条，使用“BEGIN-END”开始和结束分析分析流程控制语句IF语句 语法语法n条件表达式u关系运算符和逻辑运算符组成的表达式u它的值决定IF分支的执行路线分析分析流程控制语句CASE语句 语法语法n条件表达式结果u要与条件表达式的数据类型相同u条件表达式与条件表达式结果相同时执行对应的THEN后的语句分析分析流程控制语句CASE

9、语句 语法语法nELSEu与前面所列出的条件表达式结果都不相匹配时u执行ELSE后的语句分析分析流程控制语句CASE语句 语法语法n执行过程u计算条件表达式的值，依次与WHEN子句的条件表达式结果进行比较u一旦匹配成功，则会返回THEN后指定的结果u如果都不匹配，则会返回ELSE后的执行结果分析分析流程控制语句CASE语句 语法语法一起努力 共同提高nMySQL支持循环语句uWHILE，使用最广泛uREPEATuLOOPnWHILE语句u设置重复执行SQL语句或语句块的条件u当指定的条件为真时，重复执行循环语句流程控制语句循环语句n条件表达式u先判断条件表达式是否为真u为真则执行程序段中的语句

10、u然后再次进行判断，为真则继续循环，不为真则结束循环分析分析流程控制语句WHELE语句 语法语法n开始标号和结束标号u是WHILE语句的标注u除非开始标号存在，否则不能单独出现结束标号u如果两者都出现，它们的名字必须是相同的u多数场合是省略标号分析分析流程控制语句WHELE语句 语法语法一起努力 共同提高知识技能结构图第1章 创建和管理数据库 MySQL编程基础1 1 存储过程 触发器2 23 3目 录内容结构图InternetSQL代码代码黑客黑客编译并执行编译并执行SQL代码代码返回数据返回数据返回数据返回数据截取、篡改截取、篡改SQL代码代码盗取数据盗取数据使用存储过程增加程序的安全性使

11、用存储过程增加程序的安全性为什么使用存储过程n预先存储好的SQL程序u数据操作语句、变量、逻辑控制语句等n保存在MySQL中n通过名称和参数执行，也可返回结果什么是存储过程-单个单个 SELECT 语句语句SELECT 语句块语句块SELECT语句与逻辑控制语语句与逻辑控制语句句n提高系统性能u存储过程执行一次后，其执行代码就驻留在高速缓冲存储器中u再次执行时，只需从高缓中调用已编译好的代码执行，提高了系统性能n实现了模块化设计思想u存储过程创建好以后，可以多次被用户调用，而不必重新编写SQL语句u如果业务规则发生改变，只需要修改存储过程，客户端程序不需要修改n确保数据库安全u用户使用存储过程

12、完成数据库操作，不需要授权其直接访问数据库对象u实现：用户和数据库隔离，保证数据的完整性和安全性存储过程的优点n存储过程名u存储过程的名称u默认在当前数据库中创建u在其它数据库中创建存储过程，必须在名称前面加上数据库的名称u格式为：db_name.sp_name分析分析创建存储过程 语法语法n存储过程名的命名u必须遵守标识符的命名规则u建议加proc前缀以区别于其他数据库对象u如：proc_GetResult分析分析创建存储过程 语法语法n参数u存储过程的参数，指明参数类型、参数名称、参数的数据类型u可以有0个、1个或多个参数，多个参数中间用逗号分隔u参数类型：输入参数IN、输出参数OUT和输

13、入输出参数INPUTu当没有参数时，存储过程名称后面的括号不能省略分析分析创建存储过程 语法语法n存储过程体u存储过程的主体部分u里面包含了在存储过程调用时必须执行的语句u以BEGIN开始，以END结束u当存储过程体中只有一个SQL语句时可以省略BEGIN-END标识分析分析创建存储过程 语法语法n存储过程名u准备调用的存储过程的名称u如果要调用其它数据库的存储过程，需要在前面加上该数据库的名称n参数：参数个数必须总是等于存储过程定义时的参数个数n括号：存储过程名后必须有一对括号，其中为参数列表分析分析调用存储过程 语法语法 查看当前数据库的存储过程查看当前数据库的存储过程查看存储过程 查看存

14、储过程的具体代码查看存储过程的具体代码操作操作演示演示：查看存储过程和及其具体代码：查看存储过程和及其具体代码n存储过程名：指要删除的存储过程名称nIF EXISTS子句：如果存储过程不存在，防止发生错误n删除之前u必须确认该存储过程没有任何依赖关系u否则会导致其他与之关联的存储过程无法运行分析分析调用存储过程 语法语法一起努力 共同提高【演示示例9-1】创建执行和管理无参存储过程【演示示例9-1】创建执行和管理无参存储过程n数据库SchoolDB中n查询“胡保蜜”同学的成绩u显示学号、姓名、课程名称和考试成绩u按照课程编号升序排序需求需求【演示示例9-1】创建执行和管理无参存储过程n涉及3张

15、表的连接查询n创建前工作u删除已经存在的同名的存储过程u修改语句结束符n创建无参数存储过程n使用CALL语句执行创建好的存储过程n通过“SHOW CREATE PROCEDURE”语句查看存储过程 关键步骤关键步骤【演示示例9-1】创建执行和管理无参存储过程代码分析代码分析【演示示例9-1】创建执行和管理无参存储过程分析结果分析结果操作操作演示演示：创建执行和管理无参存储过程：创建执行和管理无参存储过程【演示示例9-1】创建执行和管理无参存储过程n在新建之前使用“DROP”语句删除即将要创建的存储过程n修改语句结束符号u创建存储过程前，使用“DELIMITER /”命令修改语句结束符号为“/”

16、u存储过程完毕，使用“DELIMITER;”命令将句结束符号为默认的“;”n执行时，无参存储过程名称的后面也必须带上一对括号小结小结【演示示例9-1】创建执行和管理无参存储过程一起努力 共同提高【技能训练9-1】创建和管理无参存储过程【技能训练9-1】创建、执行和管理无参数存储过程n理解存储过程的机制、价值和应用场合n创建无参数存储过程n执行和管理无参数存储过程技能目标技能目标需求分析需求分析nSchoolDB数据库中，创建存储过程u实现查询学习了C语言程序设计课程所有学生的成绩【技能训练9-1】创建和管理无参存储过程n在数据库SchoolDB中，创建无参数存储过程uproc_GetSubje

17、ctResultu查询“C语言程序设计”课程的所有成绩u显示学号、姓名、课程名称和考试成绩，按照考试成绩升序排序n使用CALL语句执行创建好的存储过程n在创建存储过程之前使用删除存储过程命令u删除数据库中已经存在的同名的存储过程需求需求【技能训练9-1】创建和管理无参存储过程参考结果参考结果【技能训练9-1】创建和管理无参存储过程n先要打开SchoolDB数据库u切换为当前数据库u因为存储过程是具体数据库的对象n查询涉及3张表u先将Result表和Student表连接，连接条件是学号相等u再继续和Subject表连接，连接条件是课程编号相等u需要按照考试成绩升序排序关键步骤关键步骤【技能训练9

18、-1】创建和管理无参存储过程一起努力 共同提高【演示示例9-2】创建执行管理带输入参数存储过程【演示示例9-2】创建执行和管理带输入参数的存储过程n在“演示示例9-1”的基础上，查询任意一位同学的成绩n查询学生的姓名由输入参数决定，执行时由用户指定u查询输入参数指定的同学的成绩u显示学号、姓名、课程名称和考试成绩u按照课程编号升序排序需求需求【演示示例9-2】创建执行管理带输入参数存储过程n涉及3张表的连接查询n创建前工作u删除已经存在的同名的存储过程u修改语句结束符n创建带输入参数的存储过程n使用CALL语句执行创建好的存储过程 关键步骤关键步骤【演示示例9-2】创建执行管理带输入参数存储过

19、程代码分析代码分析【演示示例9-2】创建执行管理带输入参数存储过程分析结果分析结果操作操作演示演示：创建执行和管理带输入参数的存储过程：创建执行和管理带输入参数的存储过程【演示示例9-2】创建执行管理带输入参数存储过程n在新建之前使用“DROP”语句删除即将要创建的存储过程n输入参数的定义u定义的输入参数是一个变量u变量的类型要和后面运算需要的类型匹配u学生姓名的类型要和数据表中学生姓名的类型“VARCHAR(50)”相同n在执行时，指定参数值u如：“王子洋”、“胡保蜜”u可以多测试几个数据小结小结【演示示例9-2】创建执行管理带输入参数存储过程一起努力 共同提高【技能训练9-2】创建和管理带

20、参存储过程【技能训练9-2】创建、执行和管理带输入参数的存储过程n理解带输入参数存储过程的机制、价值和应用场合n创建带输入参数的存储过程n执行和管理带输入参数的存储过程技能目标技能目标需求分析需求分析nSchoolDB数据库中，创建带输入参数的存储过程u通过输入参数查询学习了某一门课程所有学生的成绩【技能训练9-2】创建和管理带参存储过程n在数据库SchoolDB中，创建带输入参数的存储过程uproc_GetInSubjectResultu参数为课程名称u根据参数查询学习了该课程学生的所有成绩u显示学号、姓名、课程名称和考试成绩，按照考试成绩升序排序n使用CALL语句执行该参数存储过程u先执行

21、输入参数为“C语言程序设计”，再执行输入参数为“大学英语”n在创建存储过程之前删除数据库中已经存在的同名的存储过程需求需求【技能训练9-2】创建和管理带参存储过程参考结果参考结果【技能训练9-2】创建和管理带参存储过程n先要打开SchoolDB数据库，切换为当前数据库n执行带参数的存储过程u要指定具体的输入参数，否则无法执行u输入参数的类型和个数要和创建是定义的参数相同n为了验证存储过程的正确性u执行2次，使用不同的参数测试u第1次：参数为“C语言程序设计”u第2次：参数为“大学英语”关键步骤关键步骤【技能训练9-2】创建和管理带参存储过程一起努力 共同提高【演示示例9-3】带输入和输出参数的

22、存储过程【演示示例9-3】创建执行和管理带输入和输出参数的存储过程n在“演示示例9-2”的基础上u查询任意一位同学的成绩u返回该同学的总分，为后续应用服务n查询学生的姓名由输入参数决定，执行时由用户指定u查询输入参数指定的同学的成绩u显示学号、姓名、课程名称和考试成绩u按照课程编号升序排序需求需求【演示示例9-3】带输入和输出参数的存储过程n涉及3张表的连接查询n创建前工作u删除已经存在的同名的存储过程u修改语句结束符n创建带输入和输出参数的存储过程n使用CALL语句执行创建好的存储过程n输出输入参数对应同学的总分 关键步骤关键步骤【演示示例9-3】带输入和输出参数的存储过程代码分析代码分析【

23、演示示例9-3】带输入和输出参数的存储过程分析结果分析结果操作操作演示演示：创建执行和管理带输入和输出参数的存储过程：创建执行和管理带输入和输出参数的存储过程【演示示例9-3】带输入和输出参数的存储过程n在新建之前使用“DROP”语句删除即将要创建的存储过程n输入参数为变量，变量的类型要和后面运算需要的类型匹配n输出参数u变量u变量的类型根据后续应用的需求确定u要与存储过程中统计的量相匹配n在执行时，指定输入参数值和接收输出参数值的变量小结小结【演示示例9-3】带输入和输出参数的存储过程一起努力 共同提高【技能训练9-3】创建和管理带参存储过程【技能训练9-3】创建和管理带输入输出参数的存储过

24、程n理解带输入参数和输出参数存储过程的机制、价值和应用场合n创建带输入参数和输出参数的存储过程n执行和管理带输入参数和输出参数的存储过程技能目标技能目标需求分析需求分析nSchoolDB数据库中，创建带输入参数和输出参数的存储过程u实现查询任意一门课程所有学生的成绩u并带回该课程的平均分【技能训练9-3】创建和管理带参存储过程n在数据库SchoolDB中，创建带输入参数和输出参数的存储过程u名称：proc_InOutResultu输入参数：课程名称u输出参数：该课程平均分u根据输入参数查询学习了该课程学生的所有成绩u显示学号、姓名、课程名称和考试成绩u按照考试成绩升序排序需求需求【技能训练9-

25、3】创建和管理带参存储过程n在数据库SchoolDB中，创建带输入参数和输出参数的存储过程n使用CALL语句执行创建好的存储过程u先执行：输入参数为“C语言程序设计”、输出参数为“avg1”u再执行：输入参数为“大学英语”、输出参数为“avg2”n在创建存储过程之前删除数据库中已经存在的同名的存储过程u确保存储过程创建成功需求需求【技能训练9-3】创建和管理带参存储过程参考结果参考结果【技能训练9-3】创建和管理带参存储过程n先要打开SchoolDB数据库，切换为当前数据库n参照“演示示例9-3”u存储过程内使用2个查询语句，分别输出和求平均分n执行带输入和输出参数的存储过程u参数的顺序要和创

26、建时定义的顺序系统u比如：前面是输入参数，后面是输出参数n输入参数和输出参数的类型和个数要和创建是定义的参数相同关键步骤关键步骤【技能训练9-3】创建和管理带参存储过程一起努力 共同提高知识技能结构图第1章 创建和管理数据库 MySQL编程基础 存储过程 触发器1 12 23 3目 录内容结构图n触发器是一种特殊的存储过程u是一组SQL语句的集合u作为表的一部分被创建u当预定义的事件（如：向表中插入数据）发生时自动执行，无需用户调用n触发器与表的关系紧密u常用于保护表中的数据或实现数据的完整性u例如：当某个用户订购了某种商品时，相应商品的库存数量要做变更u通过触发器可以自动执行触发器n触发器只

27、能在永久表上创建u不能对临时表创建触发器u也不可以在视图上创建分析分析创建触发器 语法语法n触发器名u要创建的触发器的名称u在当前数据库中必须具有唯一的名称u在不同的数据库中可以同名分析分析创建触发器 语法语法n触发时间u触发器触发的时机u表示触发器是在激活它的语句之前或之后触发n两个选项：AFTER、BEFOREuAFTER选项：在激活触发器的语句执行之后uBEFORE选项：要验证新数据是否满足使用的限制分析分析创建触发器 语法语法n触发事件u指明在表上执行哪种操作时会激活触发器u将新行插入到数据表时激活触发器，如：INSERT语句等u从表中删除某一行时激活触发器，如：DELETE语句等u更

28、改某一行时激活触发器，如：UPDATE语句分析分析创建触发器 语法语法n表名u建立触发器的表名u对同一个表相同触发时间的相同触发事件，只能定义一个触发器n例如：对某个表的不同字段的AFTER更新触发器u在MySQL中只能定义成一个触发器u在触发器中通过判断更新的字段进行对应的处理分析分析创建触发器 语法语法nFOR EACH ROWu触发器的执行间隔u对于受触发器事件影响的每一行都要激活触发器的动作分析分析创建触发器 语法语法n触发器动作u包含触发器激活时将要执行的语句u如果要执行多个语句，使用BEGIN-END复合语句结构n在触发器的SQL语句中u使用OLD和NEW来引用触发器中发生变化的记

29、录内容分析分析创建触发器 语法语法n在触发器的SQL语句中：使用OLD和NEWuOLD关联被删除或被更新前的记录uNEW关联被插入的记录或被更新后的记录u通过OLD.col_name、NEW.col_name关联对应记录中的某个字段的值分析分析创建触发器 语法语法n在触发器的SQL语句中：使用OLD和NEWu对于INSERT语句：只有NEW合法u对于DELETE语句：只有OLD合法u对于UPDATE语句：可以与NEW或OLD同时使用n在触发器的SQL语句中，建议不使用SELECT语句u阻止从触发器返回结果分析分析创建触发器 语法语法一起努力 共同提高【演示示例9-4】创建和验证触发器【演示示例

30、9-4】 创建和验证触发器n在SchoolDB数据库中，创建一个触发器n当删除学生信息表Student中的记录是，成绩表Result中对应的该学生的成绩也应该做相应的删除需求需求【演示示例9-4】创建和验证触发器n成绩表Result中的学号引用了学生信息表Student中的学号u根据主外键约束，删除Student表中的数据，必须先删除子表Result中的数据，否则主表数据删除失败n设计一个触发器。当删除学生信息表Student中的记录时，成绩表Result中对应的该学生的成绩做相应的删除n验证触发器u即删除一条Student表中的数据，如姓名为“胡仲友”（学号为：G1463342）的同学，将会

31、自动删除Result中学号为“G1463342”的成绩信息 关键步骤关键步骤【演示示例9-4】创建和验证触发器代码分析代码分析【演示示例9-4】创建和验证触发器代码分析代码分析【演示示例9-4】创建和验证触发器代码分析代码分析【演示示例9-4】创建和验证触发器结果结果分析分析【演示示例9-4】创建和验证触发器结果结果分析分析【演示示例9-4】创建和验证触发器操作操作演示演示：创建触发器并验证触发器的效果：创建触发器并验证触发器的效果n创建触发器时使用了“BEFORE”u因为Student表为主表，Result表为子表u要删除主表的数据，必须先删除子表的数据u如果使用“AFTER”创建触发器，创

32、建本身也是成功的u但执行“DELETE FROM Student WHERE studentNo=G1463342;”语句是报错，执行失败u也就意味着触发器无效，不起作用小结小结【演示示例9-4】创建和验证触发器n验证触发器的步骤u先确保创建触发器成功u使用SELECT语句查询删除之前的数据，即删除学生胡俊文前，2张表中都有他的信息u执行删除操作，如果删除成功，证明触发器已经有效u在使用SELECT语句查询删除之后的数据，结果是删除学生胡俊文后，2张表中都没有他的信息了u再次证明触发器已经有效小结小结【演示示例9-4】创建和验证触发器一起努力 共同提高【技能训练9-4】创建和验证触发器【技能训

33、练9-4】 创建和验证触发器n理解触发器的机制、价值和应用场合n会创建一个触发器n会设计删除和查询语句，验证触发器是否有效技能目标技能目标需求分析需求分析nSchoolDB数据库中，创建并验证触发器u删除课程表Subject中某门课程u验证触发器的有效性【技能训练9-4】创建和验证触发器n在数据库SchoolDB中，创建一个触发器u触发器名称：tr_sub_deleteu删除课程表Subject中删除一门课程的信息u能自动删除成绩表Result中与该课程相关联的所有成绩信息n验证触发器u在Subject中删除一门课程（“软件测试”课程编号为“13”）的信息u查询是否已经删除了成绩表Result

34、中该课程的所有信息n在创建之前需要先删除已经存在的同名触发器需求需求【技能训练9-4】创建和验证触发器参考结果参考结果【技能训练9-4】创建和验证触发器n先要打开SchoolDB数据库，切换为当前数据库n创建触发器时必须使用了“BEFORE”uSubject为主表，Result为子表n验证时“DELETE”语句一定要带“WHERE”子句u否则课程表的数据会被全部删除u且成绩表中数据也会被全部删除u因为触发器已经有效关键步骤关键步骤【技能训练9-4】创建和验证触发器一起努力 共同提高n查看触发器u使用SHOW TRIGGERS语句可以查看触发器的基本信息管理触发器 语法语法n删除触发器u使用DROP TRIGGER语句可以删除当前数据库中的触发器u在“演示示例9-4”中已经使用了该语句管理触发器 语法语法知识技能结构图一起努力 共同提高