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

淩亂°似流年 2022-12-23 06:48 82阅读 0赞

**目录**

目标

存储过程

基本的创建语法

基本的调用语法

基本的语法案例

中断存储过程

存储过程的事务

存储过程内循环

函数

基本的创建语法

基本的调用语法

触发器

触发顺序

触发条件

触发器种类

触发的数据类型

基本的创建语法

事件

常用语法

从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 函数名称(参数列表);

--------------------

# 触发器 #

## 触发顺序 ##

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

## 触发条件 ##

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

## 触发器种类 ##

有6种，分别是：  
       1.**BEFORE INSERT** 表示对表进行**插入**数据之**前**触发；  
       2.**BEFORE UPDATE** 表示对表进行**修改**数据之**前**触发；  
       3.**BEFORE DELETE** 表示对表进行**删除**数据之**前**触发；  
       4.**AFTER INSERT** 表示对表进行**插入**数据之**后**触发；  
       5.**AFTER UPDATE** 表示对表进行**修改**数据之**后**触发；  
       6.**AFTER 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\_\***。