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

目录

目标

熟练使用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 $$

CREATEEVENT `数据库名`.`事件名`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 $$

CREATEEVENT `数据库名`.`事件名`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 $$

CREATEEVENT `数据库名`.`事件名`ON SCHEDULE EVERY 15 MINUTE STARTS '2000-11-16 23:59:00' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN

/*************************下面写业务************************/

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

END$$

DELIMITER ;

注意事项

存储过程和函数的参数可以为空，如有多个参数需要用逗号分隔。参数格式为：参数名称 数据类型，比如：param_username VARCHAR(64)；

SQL结尾处必须加分号，IF与THEN之间的判断SQL语句结尾处不能加分号；

ELSE和IF组合使用的语法为：ELSEIF，而不是ELSE IF；

为了避免参数名、变量名、表字段重复而发生冲突，建议统一参数名和变量名的前缀，参数名形如：param_username；变量名形如：var_username；

为了区分触发器、函数、存储过程、事件，建议各个名称前缀统一。函数名形如：fun_*；存储过程名形如：pro_*；触发器名形如：trigger_*；事件名形如：even_*。