mysql高级进阶(存储过程、游标、触发器)
废话不多说,直接进入正题...
一、存储过程
a、概述
- 存储过程可以看成是对一系列 SQL 操作的批处理;
- 使用存储过程的好处
- 代码封装,保证了一定的安全性;
- 代码复用;
- 由于是预先编译,因此具有很高的性能。
- 创建存储过程
- 命令行中创建存储过程需要自定义分隔符,因为命令行是以 ; 为结束符,而存储过程中也包含了分号,因此会错误把这部分分号当成是结束符,造成语法错误。
- 包含 in、out 和 inout 三种参数。
- 给变量赋值都需要用 select into 语句。
- 每次只能给一个变量赋值,不支持集合的操作。
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 语句,而是被该语句检索出来的结果集。
- 在存储过程中使用游标可以对一个结果集进行移动遍历。
- 游标主要用于交互式应用,其中用户需要对数据集中的任意行进行浏览和修改。
- 使用游标的四个步骤:
- 声明游标,这个过程没有实际检索出数据;
- 打开游标;
- 取出数据;
- 关闭游标;
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;
本文来自博客园,作者:攻城狮-tester,转载请注明原文链接:https://www.cnblogs.com/zcjbky/p/15534061.html