CREATE PROCEDURE `copy_record_out`()
BEGIN
DECLARE startYear int;
DECLARE endYear int;
DECLARE startMonth int;
DECLARE endMonth int;
DECLARE startTime dateTime;
DECLARE endTime dateTime;
DECLARE middleTime dateTime;
declare startTimeStr VARCHAR(32);
declare middleTimeStr VARCHAR(32);
select 1 into startMonth;
select str_to_date('2019-12-01 00:00:00','%Y-%m-%d %H:%i:%s') into startTime;
select str_to_date('2020-01-01 00:00:00','%Y-%m-%d %H:%i:%s') into endTime;
while startTime<endTime do
select YEAR(startTime) into startYear;
select MONTH(startTime) into startMonth;
select DATE_FORMAT(startTime,'%Y-%m-%d %H:%i:%s') into startTimeStr;
select date_add(startTime, interval 1 HOUR) into middleTime;
select DATE_FORMAT(middleTime,'%Y-%m-%d %H:%i:%s') into middleTimeStr;
set @table_name = concat("t_park_record_out_archive_",startYear,"_",startMonth);
set @statement = concat("insert into ",@table_name, "(
create_time,update_time )
select create_time, update_time from t_park_record_out
where out_time>='",startTimeStr,"' and out_time<'",middleTimeStr,"';");
prepare stmt from @statement;
execute stmt;
commit;
delete from t_park_record_out where out_time>=startTime and out_time<date_add(startTime, interval 1 HOUR);
commit;
select middleTime into startTime;
select YEAR(startTime) into startYear;
select MONTH(startTime) into startMonth;
end while;
end;
mysql编写存储过程
©著作权归作者所有,转载或内容合作请联系作者
- 文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
- 文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
- 文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
推荐阅读更多精彩内容
- 原文链接 MySQL存储过程详解 1.存储过程简介 我们常用的操作数据库语言SQL语句在执行的时候需要要先编译，然...
- 参考官方给出的：https://dev.mysql.com/doc/refman/8.0/en/stored-ro...
