mysql高级进阶（存储过程、游标、触发器）

废话不多说，直接进入正题...

一、存储过程

a、概述

  • 存储过程可以看成是对一系列 SQL 操作的批处理；
  • 使用存储过程的好处
    1. 代码封装，保证了一定的安全性；
    2. 代码复用；
    3. 由于是预先编译，因此具有很高的性能。
  • 创建存储过程
    1. 命令行中创建存储过程需要自定义分隔符，因为命令行是以 ; 为结束符，而存储过程中也包含了分号，因此会错误把这部分分号当成是结束符，造成语法错误。
    2. 包含 in、out 和 inout 三种参数。
    3. 给变量赋值都需要用 select into 语句。
    4. 每次只能给一个变量赋值，不支持集合的操作。

 

b、创建存储过程

  • DROP PROCEDURE IF EXISTS `proc_adder`;
  • DELIMITER ;;
  • CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_adder`(IN a int, IN b int, OUT sum int)
  • BEGIN
  • DECLARE c int;
  • if a is null then set a = 0;
  • end if;
  • if b is null then set b = 0;
  • end if;
  • set sum = a + b;
  • END
  • ;;
  • DELIMITER ;

c、使用存储过程

  • set @b=5;
  • call proc_adder(2,@b,@s);
  • select @s as sum;

二、游标 

a、概述

  • 游标（cursor）是一个存储在 DBMS 服务器上的数据库查询，它不是一条 SELECT 语句，而是被该语句检索出来的结果集。
  • 在存储过程中使用游标可以对一个结果集进行移动遍历。
  • 游标主要用于交互式应用，其中用户需要对数据集中的任意行进行浏览和修改。
  • 使用游标的四个步骤：
    1. 声明游标，这个过程没有实际检索出数据；
    2. 打开游标；
    3. 取出数据；
    4. 关闭游标；

SQL语句示例：

  • DELIMITER $
  • CREATE PROCEDURE getTotal()
  • BEGIN
  • DECLARE total INT;
  • -- 创建接收游标数据的变量
  • DECLARE sid INT;
  • DECLARE sname VARCHAR(10);
  • -- 创建总数变量
  • DECLARE sage INT;
  • -- 创建结束标志变量
  • DECLARE done INT DEFAULT false;
  • -- 创建游标
  • DECLARE cur CURSOR FOR SELECT id,name,age from cursor_table where age>30;
  • -- 指定游标循环结束时的返回值
  • DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;
  • SET total = 0;
  • OPEN cur;
  • FETCH cur INTO sid, sname, sage;
  • WHILE(NOT done)
  • DO
  • SET total = total + 1;
  • FETCH cur INTO sid, sname, sage;
  • END WHILE;
  • CLOSE cur;
  • SELECT total;END $DELIMITER ;
  • -- 调用存储过程
  • call getTotal();

三、触发器

a、概述

  • 触发器是一种与表操作有关的数据库对象，当触发器所在表上出现指定事件时，将调用该对象，即表的操作事件触发表上的触发器的执行

可以使用触发器来进行审计跟踪，把修改记录到另外一张表中。

MySQL 不允许在触发器中使用 CALL 语句 ，也就是不能调用存储过程。

  • BEGIN 和 END
    • 当触发器的触发条件满足时，将会执行 BEGIN 和 END 之间的触发器执行动作。
    • 注意：
      • 在 MySQL 中，分号 ; 是语句结束的标识符，遇到分号表示该段语句已经结束，MySQL 可以开始执行了。因此，解释器遇到触发器执行动作中的分号后就开始执行，然后会报错，因为没有找到和 BEGIN 匹配的 END。
      • 这时就会用到 DELIMITER 命令（DELIMITER 是定界符，分隔符的意思）。它是一条命令，不需要语句结束标识，语法为：DELIMITER new_delemiter。new_delemiter 可以设为 1 个或多个长度的符号，默认的是分号 ;，我们可以把它修改为其他符号，如 $ - DELIMITER $ 。在这之后的语句，以分号结束，解释器不会有什么反应，只有遇到了 $，才认为是语句结束。注意，使用完之后，我们还应该记得把它给修改回来。
  • NEW 和 OLD
    • MySQL 中定义了 NEW 和 OLD 关键字，用来表示触发器的所在表中，触发了触发器的那一行数据。
    • 在 INSERT 型触发器中，NEW 用来表示将要（BEFORE）或已经（AFTER）插入的新数据；
    • 在 UPDATE 型触发器中，OLD 用来表示将要或已经被修改的原数据，NEW 用来表示将要或已经修改为的新数据；
    • 在 DELETE 型触发器中，OLD用来表示将要或已经被删除的原数据；
    • 使用方法： NEW.columnName（columnName 为相应数据表某一列名）

b、创建触发器

提示：为了理解触发器的要点，有必要先了解一下创建触发器的指令。

    • CREATE TRIGGER 指令用于创建触发器。
    • 语法：
      • CREATE TRIGGER trigger_name
      • trigger_time
      • trigger_event
      • ON table_name
      • FOR EACH ROW
      • BEGIN
      • trigger_statements
      • END;
    • 说明：
      • trigger_name：触发器名
      • trigger_time: 触发器的触发时机。取值为 BEFORE 或 AFTER。
      • trigger_event: 触发器的监听事件。取值为 INSERT、UPDATE 或 DELETE。
      • table_name: 触发器的监听目标。指定在哪张表上建立触发器。
      • FOR EACH ROW: 行级监视，Mysql 固定写法，其他 DBMS 不同。
      • trigger_statements: 触发器执行动作。是一条或多条 SQL 语句的列表，列表内的每条语句都必须用分号 ; 来结尾。
    • 示例：
      • DELIM ITER $
      • CREATE TRIGGER `trigger_insert_user`
      • AFTER INSERT ON `user`
      • FOR EACH ROW
      • BEGIN
      • INSERT INTO `user_history`(user_id, operate_type, operate_time)
      • VALUES (NEW.id, 'add a user', now());
      • END $
      • DELIMITER ;

c、查看触发器

SHOW TRIGGERS;

d、删除触发器

DROP TRIGGER IF EXISTS trigger_insert_user;

posted @ 2021-11-10 16:28  攻城狮-tester  阅读(144)  评论(0)    收藏  举报