MySQL存储过程&函数&触发器&事件大一统

此文转载自：https://blog.csdn.net/qq_39706570/article/details/109993452#commentBox

目录

目标

存储过程

• 基本的创建语法
• 基本的调用语法
• 基本的语法案例
• 中断存储过程
• 存储过程的事务
• 存储过程内循环

函数

• 基本的创建语法
• 基本的调用语法

触发器

• 触发顺序
• 触发条件
• 触发的数据类型
• 基本的创建语法

事件

• 常用语法
• 从2000-11-16 23:59:00开始，每天定时执行一次
• 从2000-11-16 23:59:00开始，每小时定时执行一次
• 从2000-11-16 23:59:00开始，每15分钟定时执行一次

注意事项

---

目标

• 熟练使用MySQL存储过程、函数、触发器、事件。

---

存储过程

基本的创建语法

DELIMITER $$

CREATE
    PROCEDURE `数据库名称`.`存储过程名称`(参数列表)
    BEGIN
	/*********************下面写业务。**********************/

	/*********************上面写业务。**********************/
    END$$
 
DELIMITER ;

基本的调用语法

call 存储过程名(参数列表);

基本的语法案例

DELIMITER $$

USE `test`$$

DROP PROCEDURE IF EXISTS `pro_find_user_by_id`$$

CREATE DEFINER=`root`@`%` PROCEDURE `pro_find_user_by_id`(param_user_id INT(10))
BEGIN
/*创建一个用户名变量*/
DECLARE var_user_name VARCHAR(64);
/*创建一个成绩变量*/
DECLARE var_achievement DOUBLE(3,2);
/*根据用户ID查询用户名和成绩，并给变量名赋值。*/
SELECT user_name,achievement INTO var_user_name,var_achievement FROM `user_info` WHERE id=param_user_id;
IF
	/*如果用户名为空*/
	var_user_name IS NULL OR  LENGTH(REPLACE(var_user_name,' ',''))=0
THEN
	/*返回信息*/
	SELECT '用户名为空。' msg,'500' resultCode;
ELSEIF
	var_achievement IS NULL OR var_achievement<60
THEN
	SELECT '不及格' msg,var_user_name userName,var_achievement achievement,'200' resultCode;
ELSE
	SELECT '及格' msg,var_user_name userName,var_achievement achievement,'200' resultCode;
END IF;
    END$$

DELIMITER ;

中断存储过程

DELIMITER $$

USE `test`$$

DROP PROCEDURE IF EXISTS `pro_judge`$$

CREATE DEFINER=`root`@`%` PROCEDURE `pro_judge`(param_a INT(10))
/*注意，我在这里自定义了一个终端标记tx。*/
tx:BEGIN
IF
	param_a>0
THEN
	SELECT CONCAT(param_a,'大于0') msg;
	/*在这里终止存储过程，不会再继续下去。*/
	LEAVE tx;
END IF;
	/*在这里终止存储过程，不会再继续下去。*/
	SELECT CONCAT(param_a,'不大于0') msg;
    END$$

DELIMITER ;

存储过程的事务

DELIMITER $$

USE `test`$$

DROP PROCEDURE IF EXISTS `pro_insert_user`$$

CREATE DEFINER=`root`@`%` PROCEDURE `pro_insert_user`()
BEGIN

DECLARE var_b INT(10);
	
	/*事务标识*/
	DECLARE t_error INTEGER DEFAULT 0;
	DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;
    START TRANSACTION;
        
        /*向用户表插入一条数据。*/
        INSERT INTO `user_info` (user_name,achievement)VALUES('zhangsan',99);
        
        /*在这里定义一个异常:设置var_b的值。*/
	    SET var_b= 1/0;
IF 
	t_error = 1 
THEN
	/*失败回滚。*/
	ROLLBACK;
ELSE
	/*成功提交。*/
        COMMIT;
END IF;

    END$$

DELIMITER ;

存储过程内循环

DELIMITER $$

USE `test`$$

DROP PROCEDURE IF EXISTS `pro_while`$$

CREATE DEFINER=`root`@`%` PROCEDURE `pro_while`(param_i INT(10))
BEGIN
	
	WHILE param_i > 0 DO 
	/*可以在这里写业务*/
	SET param_i=param_i-1;
	END WHILE;
	
	/*打印param_i最后的值*/
	SELECT param_i ;
	
    END$$

DELIMITER ;

---

函数

基本的创建语法

DELIMITER $$
 
CREATE
    FUNCTION `数据库名称`.`函数名`(param_a INT(5),param_b INT(5))
    /*定义函数返回类型，我这里定义返回INT类型。*/
    RETURNS INT(6)
    BEGIN
/*********************下面写业务。**********************/

/*********************上面写业务。**********************/
/*返回同样是INT类型*/
RETURN param_a+param_b;
    END$$
 
DELIMITER ;

基本的调用语法

SELECT 函数名称(参数列表);

---

触发器

触发顺序

       有两种，分别是：
       1.BEFORE表示对表操作前执行触发器；
       2.AFTER表示对表操作后执行触发器；

触发条件

       有三种，分别是：
       1.INSERT表示对表进行插入数据才会触发；
       2.UPDATE表示对表进行修改数据才会触发；
       3.DELETE表示对表进行删除数据才会触发。

触发的数据类型

有两种，分别是：
       1.新数据（NEW.字段名）；
       2.旧数据（OLD.字段名）。
       其中，新数据只有在触发条件是INSERT、UPDATE才有会产生；旧数据只有在UPDATE、DELETE产生。

基本的创建语法

DELIMITER $$
 
CREATE
/*AFTER和INSERT组合使用，表示数据插入以后触发该触发器。*/
    TRIGGER `数据库名`.`触发器名称` AFTER INSERT
    /*监控user_info表*/
    ON `数据库名`.`监控的表名`
    FOR EACH ROW BEGIN
    /***********************下面写业务***************************/
    
	/*表示每当有数据插入user_info表时，会向user_info_copy插入新产生的数据。*/
	INSERT INTO `user_info_copy` (user_name,achievement)VALUES(NEW.user_name,NEW.achievement);
	
    /***********************上面写业务***************************/
    END$$
 
DELIMITER ;

---

事件

常用语法

从2000-11-16 23:59:00开始，每天定时执行一次

DELIMITER $$ 

CREATE	EVENT `数据库名`.`事件名`ON SCHEDULE EVERY 1 DAY STARTS '2000-11-16 23:59:00' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN
/*************************下面写业务************************/

/*************************上面写业务************************/

	END$$
DELIMITER ;

从2000-11-16 23:59:00开始，每小时定时执行一次

DELIMITER $$ 

CREATE	EVENT `数据库名`.`事件名`ON SCHEDULE EVERY 1 HOUR STARTS '2000-11-16 23:59:00' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN
/*************************下面写业务************************/

/*************************上面写业务************************/

	END$$
DELIMITER ;

从2000-11-16 23:59:00开始，每15分钟定时执行一次

DELIMITER $$ 

CREATE	EVENT `数据库名`.`事件名`ON SCHEDULE EVERY 15 MINUTE STARTS '2000-11-16 23:59:00' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN
/*************************下面写业务************************/

/*************************上面写业务************************/

	END$$
DELIMITER ;

---

注意事项

1. 存储过程和函数的参数可以为空，如有多个参数需要用逗号分隔。参数格式为：参数名称 数据类型，比如：param_username VARCHAR(64)；
2. SQL结尾处必须加分号，IF与THEN之间的判断SQL语句结尾处不能加分号；
3. ELSE和IF组合使用的语法为：ELSEIF，而不是ELSE IF；
4. 为了避免参数名、变量名、表字段重复而发生冲突，建议统一参数名和变量名的前缀，参数名形如：param_username；变量名形如：var_username；
5. 为了区分触发器、函数、存储过程、事件，建议各个名称前缀统一。函数名形如：fun_*；存储过程名形如：pro_*；触发器名形如：trigger_*；事件名形如：even_*。