关于mysql存储过程的例子，和解释说明

DELIMITER $$

USE `gps_hisdata`$$       
/* 选择一个数据库 */

DROP PROCEDURE IF EXISTS `adasalarm_test`$$
/* 存储过程的名称类似：java中的函数 */

CREATE DEFINER=`root`@`localhost` PROCEDURE `adasalarm_test`()
/* 创建存储过程的名称 */

BEGIN
 /* 开始 */
 
DECLARE `@suffix` VARCHAR(15);
/*声明一个变量@suffix */
DECLARE `@sqlstr` VARCHAR(255);

DECLARE `@current` VARCHAR(100);

DECLARE `@sqlinsert` VARCHAR(100);

DECLARE `@someday` VARCHAR(100);


SET `@suffix`  =  DATE_FORMAT(NOW(),'%Y_%m_%d');
/*赋值一个变量@suffix ,(就是将当天的时间赋值给@suffix)*/
SET `@current` =  DATE_FORMAT(NOW(),'%Y-%m-%d');
SET `@someday` =  DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 2 DAY),'%Y-%m-%d');

/*下面的赋值一个变量@sqlstr ,(相对比较复杂，用到拼接字符串api:concat(str1,str2,...))*/
SET @sqlstr = CONCAT(
"CREATE TABLE adasalarm",
`@suffix`,
"(
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `alarmId` INT(11) NOT NULL,
  `alarmNo` VARCHAR(32) NOT NULL,
  `alarmSource` VARCHAR(255) DEFAULT NULL,
  `alarmStatus` INT(11) NOT NULL,
  `alarmTime` DATETIME NOT NULL,
  `alarmType` VARCHAR(255) DEFAULT NULL,
  `altitude` INT(11) NOT NULL,
  `attachmentCount` INT(11) NOT NULL,
  `deviateType` INT(11) NOT NULL,
  `fatigueDegree` INT(11) NOT NULL,
  `frontCarDistance` INT(11) NOT NULL,
  `frontCarSpeed` INT(11) NOT NULL,
  `gpsTime` DATETIME DEFAULT NULL,
  `latitude` DOUBLE NOT NULL,
  `level` INT(11) NOT NULL,
  `longitude` DOUBLE NOT NULL,
  `roadRecognitionData` INT(11) NOT NULL,
  `roadRecognitionMark` INT(11) NOT NULL,
  `sn` INT(11) NOT NULL,
  `speed` INT(11) NOT NULL,
  `terminalId` VARCHAR(255) DEFAULT NULL,
  `tirePressureAlarmDetail` VARCHAR(255) DEFAULT NULL,
  `vehicleStatus` INT(11) NOT NULL,
  `createDate` DATETIME DEFAULT NULL,
  `plateNo` VARCHAR(255) DEFAULT NULL,
  `vehicleId` BIGINT(20) NOT NULL,
  `location` VARCHAR(255) DEFAULT NULL,
  `simNo` VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (`id`,`alarmTime`),
  KEY `alarmNo` (`alarmNo`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;"
);

/* prepare :准备一个存储过程，从一个变量 */
PREPARE createDateTable FROM @sqlstr;
EXECUTE createDateTable;
/* 执行这个存储过程 */

事实上：
（1）声明变量，《后面的赋值需要用到》
（2）给前面声明的变量赋值，
（3）准备存储过程，然后执行。

SET @sqlinsert = CONCAT(
"INSERT INTO adasalarm",
`@suffix`,
" select * from adasalarm  where createDate < ","'",`@current`,"'",";"
);
PREPARE insterDatabase FROM @sqlinsert;
EXECUTE insterDatabase;


SET @sqldelete =CONCAT(
"delete from adasalarm  where createDate < ",
"'",`@someday`,"'",";"
);
PREPARE deleteTable FROM @sqldelete;
EXECUTE deleteTable;


END$$

DELIMITER ;

下面是定时任务。也就是：事件（负责存储过程《类似于java中的函数》的执行的频率）

DELIMITER $$

-- SET GLOBAL event_scheduler = ON$$     -- required for event to execute but not create    

CREATE	/*[DEFINER = { user | CURRENT_USER }]*/	EVENT `gps_hisdata`.`adasalarm_event`

ON SCHEDULE EVERY 7 DAY
  
STARTS '2020-10-11 02:59:59'
	   /*执行的频率其实效果：周日的2点59分59秒执行一次*/
	 
DO
	BEGIN

	      CALL adasalarm_use();
	    /*执行这个存储过程*/
	END$$

DELIMITER ;

本文地址：https://blog.csdn.net/deadchance/article/details/108995185