BEGIN
DECLARE
t_error
INTEGER
DEFAULT
0;
DECLARE
CONTINUE
HANDLER
FOR
SQLEXCEPTION
SET
t_error=1;
# 保证数据一致性 开启事务
START
TRANSACTION
;
# 获取需同步数据的时间节点(3个月前的第一天)
# 即当前日期 2018-07-10 @upmonth 日期 2018-04-01 8
SET
@upmonth= DATE_ADD(CURDATE() -
DAY
(CURDATE()) + 1, INTERVAL - 3
MONTH
);
# 迁移数据语句
SET
@sqlstr=CONCAT(‘
INSERT
INTO
fd_aseet_record_back_3_6
SELECT
*
FROM
fd_asset_record
WHERE
type
in
(3, 6)
AND
calendar_date < ?‘);
# 删除数据语句
SET
@delsqlstr=CONCAT(‘
DELETE
FROM
fd_asset_record
WHERE
type
in
(3, 6)
AND
calendar_date < ?‘);
#执行数据迁移
PREPARE
_fddatamt
FROM
@sqlstr;
EXECUTE
_fddatamt USING @upmonth;
DEALLOCATE
PREPARE
_fddatamt;
#执行迁移后的数据删除
PREPARE
_fddatadel
FROM
@delsqlstr;
EXECUTE
_fddatadel USING @upmonth;
DEALLOCATE
PREPARE
_fddatadel;
IF t_error = 1
THEN
ROLLBACK
; #语句异常-回滚
ELSE
COMMIT
; #提交事务
END
IF;
END