项目六 存储过程、游标和触发器（课件）-《MySQL数据库》同步教学（电子工业版）

项目六 存储过程、游标和触发器 任务1 存储过程 任务2 游 标 任务3 触发器 任务1 存储过程 任务引入 小白正在创建一个大型数据库系统，其中需要创建完成某一特定功能的存储过程。那么，什么是存储过程？怎么创建存储过程？怎么调用存储过程？怎么修改和删除存储过程呢？ 知识准备 存储过程存储在数据库内，可由应用程序通过一个调用执行，而且允许用户声明变量、有条件执行以及其他强大的编程功能。存储过程可以使得数据库的管理、显示关于数据库及其用户信息的工作容易得多。 一、存储过程概述 存储过程包含程序流、逻辑以及对数据库的查询。它们可以接受参数、输出参数、返回单个或多个结果集以及返回值。 存储过程通常有如下优点： 1.封装性 存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的 SQL 语句，并且数据库专业人员可以随时对存储过程进行修改，而不会影响到调用它的应用程序源代码。 3.可减少网络流量 由于存储过程是在服务器端运行的，且执行速度快，因此当客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而可降低网络负载。 4.高性能 存储过程执行一次后，产生的二进制代码就驻留在缓冲区，在以后的调用中，只需要从缓冲区中执行二进制代码即可，从而提高了系统的效率和性能。 5.提高数据库的安全性和数据的完整性 使用存储过程可以完成所有数据库操作，并且可以通过编程的方式控制数据库信息访问的权限。 二、创建存储过程 MySQL存储过程的参数用在存储过程的定义，共有三种参数类型：IN,OUT,INOUT。 案例——创建一个不带参数的存储过程。 三、调用存储过程 在MySQL中使用CALL命令调用已经定义好的存储过程。 创建存储过程 案例——创建带输入参数的存储过程。 创建一个带输入参数的存储过程，从读者表中查询成人读者信息。 案例——创建带输入参数和输出参数的存储过程。 创建一个带输入参数和输出参数的存储过程，统计文学类书籍的总数。 四、查看存储过程 创建好存储过程后，用户可以通过 SHOW ATATUS 语句来查看存储过程的状态，也可以通过 SHOW CREATE 语句来查看存储过程的定义。 1. 查看存储过程的状态 在MySQL 中可以通过 SHOW ATATUS 语句来查看存储过程的状态。 案例——查看名称中含有“count”字符串的存储过程状态的信息。 2. 查看存储过程的定义 在MySQL 中可以通过 SHOW CREATE 语句来查看存储过程的定义。 案例——查看sp_reader存储过程的定义。 五、修改存储过程 在实际开发过程中，业务需求修改的情况时有发生，这样，不可避免的需要修改 MySQL 中存储过程的特征 。在MySQL 中通过使用 ALTER PROCEDURE 语句来修改存储过程的某些特征。 案例——修改读写权限为MODIFIES SQL DATA，并指明调用者可以执行。 （1）修改存储过程。 （2）查看修改后存储过程。 案例——修改读写权限为READS SQL DATA，并添加注释信息。 （1）修改存储过程。 查看sp_reader存储过程的状态 （2）查看修改后存储过程。 五、修改存储过程 在实际开发过程中，业务需求修改的情况时有发生，这样，不可避免的需要修改 MySQL 中存储过程的特征 。在MySQL 中通过使用 ALTER PROCEDURE 语句来修改存储过程的某些特征。 案例——修改读写权限为MODIFIES SQL DATA，并指明调用者可以执行。 （1）修改存储过程。 （2）查看修改后存储过程。 案例——修改读写权限为READS SQL DATA，并添加注释信息。 （1）修改存储过程。 查看修改后p_count存储过程 六、删除存储过程 存储过程被创建后，保存在数据库服务器上，直至被删除。删除一个存储过程比较简单，和删除表一样。 可以使用 DROP PROCEDURE 语句删除数据库中已创建的存储过程。 案例——删除sp_reader存储过程。 （1）修改存储过程。 （2）查看sp_reader存储过程是否存在。 任务2 游 标 任务引入 小刘老师让小白帮忙从学校的数据库中查询他们专业前十名的学生信息并打印出来，可是小白查询出来是所有学生信息按名次排名，这时小白想到用游标来提取数据。那么，怎么声明游标？怎么打开游标并从游标中提取数据呢？ 知识准备 关系数据库中的操作会对整个行集产生影响。由SELECT语句返回的行集包括所有满足该语句WHERE子句中条件的行。由语句所返回的这一完整的行集称为结果集。应用程序，特别是交互式联机应用程序，并不总能将整个结果集作为一个单元来有效地处理。这些应用程序需要一种机制，以便每次处理一行或一部分行。游标就是用来提供这种机制的结果集扩展。 一、使用游标过程 使用游标的典型过程如下： （1）声明SQL变量包含游标返回的数据。 （2）使用DECLARE CURSOR语句把SQL游标与一个SELECT语句相关联。 （3）使用OPEN语句执行SELECT语句并生成游标。 （4）使用FETCH INTO语句提取单个行，并把每列中的数据转移到指定的变量中。（5）结束游标时使用CLOSE语句。 游标的典型使用过程 二、声明游标 声明游标使用DECLARE CURSOR语句，其基本语法如下： DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR FOR select_statement 案例——声明一个名为cur_book的游标。 三、打开游标 打开游标使用OPEN语句 四、从游标中提取值 游标声明，而且被打开以后，游标位置位于第一行。可以使用FETCH语句从游标结果集中提取数据。 五、关闭游标 关闭游标使用CLOSE语句 案例——声明一个名为cur_book1的游标并提取数据。 （1）声明游标。 （2）调用存储过程。 任务3 触发器 任务引入 小白想要保证数据库系统中数据完整性，还想在多个表中添加、更新或删除行时，保留表之间所定义的关系，他咨询老师，老师告诉他可以在程序中设置一个触发器。那么，什么是触发器？怎么创建触发器？怎么修改和删除触发器呢？ 知识准备 一、触发器概述 MySQL的触发器和存储过程一样，都是嵌入到MySQL中的一段程序，是MySQL中管理数据的有力工具。不同的是执行存储过程要使用CALL语句来调用，而触发器的执行不需要使用CALL语句来调用，也不需要手工启动，而是通过对数据表的相关操作来触发、激活从而实现执行。 触发器在INSERT、UPDATE或DELETE语句对表或视图进行修改时会被自动执行。触发器可以查询其他表，并可以包含复杂的SQL语句。一个表可以有多个触发器。触发器具有如下优点： 触发器可通过数据库中的相关表实现级联更改。 触发器可以强制比用CHECK约束定义的约束更为复杂的约束。 触发器也可以评估数据修改前后的表状态，并根据其差异采取对策。 一个表中的多个同类触发器（INSERT、UPDATE或DELETE）允许采取多个不同的对策，以响应同一个修改语句。 触发器的优点如下： 触发器的执行是自动的，当对触发器相关表的数据做出相应的修改后立即执行。 触发器可以实施比 FOREIGN KEY 约束、CHECK 约束更为复杂的检查和操作。 触发器可以实现表数据的级联更改，在一定程度上保证了数据的完整性。 触发器的缺点如下： 使用触发器实现的业务逻辑在出现问题时很难进行定位，特别是涉及到多个触发器的情况下，会使后期维护变得困难。 大量使用触发器容易导致代码结构被打乱，增加了程序的复杂性， 如果需要变动的数据量较大时，触发器的执行效率会非常低。 二、创建触发器 在 MySQL中，可以使用 CREATE TRIGGER 语句创建触发器。 1．INSERT触发器 插入某一行时激活触发器。 使用 INSERT 触发器需要注意以下几点： 在 INSERT 触发器代码内，可引用一个名为 NEW（不区分大小写）的虚拟表来访问被插入的行。 在 BEFORE INSERT 触发器中，NEW 中的值也可以被更新，即允许更改被插入的值（只要具有对应的操作权限）。 对于 AUTO_INCREMENT 列，NEW 在 INSERT 执行之前包含的值是 0，在 INSERT 执行之后将包含新的自动生成值。 案例——创建一个触发器，当执行INSERT操作时，该触发器被触发。 （1）创建测试表。 （2）创建触发器。 （3）通过触发器表的插入记录操作，测试触发器。 2．UPDATE触发器 当向表中更新记录时，UPDATE触发器被执行。 使用 UPDATE 触发器需要注意以下几点： 在 UPDATE 触发器代码内，可引用一个名为 NEW（不区分大小写）的虚拟表来访问更新的值。 在 UPDATE 触发器代码内，可引用一个名为 OLD（不区分大小写）的虚拟表来访问 UPDATE 语句执行前的值。 在 BEFORE UPDATE 触发器中，NEW 中的值可能也被更新，即允许更改将要用于 UPDATE 语句中的值（只要具有对应的操作权限）。 OLD 中的值全部是只读的，不能被更新。 创建测试表 创建触发器 测试触发器 案例——创建一个触发器，当该触发器被触发更新一个表时，另一个表也更新。 （1）创建测试表。 （2）创建触发器。 （3）通过触发器表的插入记录操作，测试触发器。 创建测试表 创建触发器 测试触发器 3．DELETE触发器 在 DELETE 语句执行之前或之后响应的触发器。 使用 DELETE 触发器需要注意以下几点： 在 DELETE 触发器代码内，可以引用一个名为 OLD（不区分大小写）的虚拟表来访问被删除的行。 OLD 中的值全部是只读的，不能被更新。 三、查看触发器 1.查看触发器信息 可以通过 SHOW TRIGGERS 语句来查看触发器的基本信息。 案例——查看library数据库中的触发器状态。 2.查看触发器定义 查看指定触发器的定义语句。 案例——查看library数据库中的trigger1触发器的定义。 3.在trigger表中查看触发器信息 在 MySQL 中，所有触发器的信息都存在 information_schema 数据库的 triggers 表中，可以通过查询命令 SELECT 来查看。 案例——在trigger表中查看trigger2触发器的信息。 四、修改和删除触发器 修改触发器可以通过删除原触发器，再以相同的名称创建新的触发器。 案例——删除trigger2触发器。 项目总结 习题 1. 什么是存储过程？使用存储过程有什么好处？ 2. 存储过程中包含几种参数？各有什么特点？ 3. 什么是触发器？有什么优缺点？ 4. 简述游标的使用过程。 项目实战 实战一 创建存储过程。 创建一个带输入参数和输出参数的存储过程，统计市场部职工的总数。 创建存储过程并统计市场部的人数 实战二 声明游标。 声明一个名为cur_worker1的游标并提取数据。 （1）声明游标。 （2）调用存储过程。 $$