目录

目标

存储过程

基本的创建语法

基本的调用语法

基本的语法案例

中断存储过程

存储过程的事务

存储过程内循环

函数

基本的创建语法

基本的调用语法

触发器

触发顺序

触发条件

触发器种类

触发的数据类型

基本的创建语法

事件

常用语法

从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_*。