BEGIN
DECLARE tbname VARCHAR(150) DEFAULT ''; -- 表名称
DECLARE processFlag int(1) DEFAULT 1; -- 是否继续进行 1,不进行0
DECLARE v_sql_1 VARCHAR(800); -- 存储创建表的语句
DECLARE currentSign varchar(50); -- 当前循环的sign
DECLARE done int DEFAULT 0; -- 游标没有结束时是0,结束时候该值是1
DECLARE yesDate varchar(15) DEFAULT ''; -- 昨天的日期(yyyy_MM_dd)
DECLARE yesDateCopy varchar(15) DEFAULT ''; -- 昨天的日期(yyyy-MM-dd)
DECLARE signcursor CURSOR FOR SELECT sign from t_sys_route GROUP BY sign ; -- 执行sql语句和声明对应sql执行结果的游标
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
SET yesDate = DATE_FORMAT(date_add(NOW(),interval -1 day),'%Y_%m_%d'); -- 获得昨天的时间(yyyy_MM_dd)
SET yesDateCopy = REPLACE(yesDate,'_','-'); -- 获得昨天的时间(yyyy-MM-dd)
OPEN signcursor ;
REPEAT
FETCH signcursor into currentSign;
IF NOT done THEN
SET tbname = CONCAT('t_',currentSign,'_attribute_',yesDate); -- 当前表名赋值
-- 0 查看是否含有数据
BEGIN
set @p=CONCAT('select count(1) into @p_num from t_',currentSign,'_attribute where SUBSTR(time FROM 1 FOR 10)="'
, yesDateCopy, '"');
PREPARE p_sql FROM @p;
EXECUTE p_sql;
deallocate prepare p_sql;
select @p_num;
if @p_num=0
then set processFlag=0;
else
set processFlag=1;
end if;
END;
IF processFlag =1 then
-- 1.创建表
SET v_sql_1 = CONCAT('CREATE table if not exists ',tbname
,'(`id` VARCHAR(50) NOT NULL DEFAULT "" COMMENT "主键",'
,'`cncid` VARCHAR (50) DEFAULT NULL COMMENT "cncid",'
,'`time` VARCHAR (50) DEFAULT "" COMMENT "时间点",'
,'`value` VARCHAR (50) DEFAULT NULL COMMENT "值",'
,'`type` INT (1) DEFAULT NULL COMMENT "类型:2转速 3进给 4功率 5刀具号",'
,' PRIMARY KEY (`id`)) ENGINE = INNODB DEFAULT CHARSET = utf8;'
);
SET @sql_1 = v_sql_1;
PREPARE stmt1 FROM @sql_1; -- 预编译sql
EXECUTE stmt1; -- 执行sql
DEALLOCATE PREPARE stmt1; -- 回收分配stmt1
-- 2.插入数据
SET v_sql_1 = CONCAT('INSERT INTO ',tbname
,'(id,cncid,time,value,type) SELECT id,cncid,time,value,type FROM t_'
,currentSign,'_attribute where time like "%',yesDateCopy,'%"' ) ;
SET @sql_1 = v_sql_1;
PREPARE stmt1 FROM @sql_1; -- 预编译sql
EXECUTE stmt1; -- 执行sql
DEALLOCATE PREPARE stmt1; -- 回收分配stmt1
-- 3.删除数据
SET v_sql_1 = CONCAT('DELETE FROM t_',currentSign,'_attribute where time like "%',yesDateCopy,'%"' ) ;
SET @sql_1 = v_sql_1;
PREPARE stmt1 FROM @sql_1; -- 预编译sql
EXECUTE stmt1; -- 执行sql
DEALLOCATE PREPARE stmt1; -- 回收分配stmt1
END IF;
END IF;
UNTIL done
END REPEAT;
CLOSE signcursor ;
END
mysql 存储过程 一个表的数据定期拷贝到另外一个表中
最新推荐文章于 2022-08-31 10:11:20 发布