今天来讲下MySQL存储过程中的游标和事务。游标和事务的解释,这里就不再说了。直接上例子。下面的存储过程是在我工作中写的用来同步数据的一个案例,我稍作简化,删除了大部分和业务相关的SQL,保留和突出了游标和事务的相关SQL,并且在重要的地方写上了注释,方便理解。
同步业务简单说一下:
1、取dump库中前一天的数据,放入游标
2、online库和dump库前一天的数据(第一步取出的数据)对比,online库里没有的,插入online库;online库里有的,更新online库里对应的数据。
3、更新online库已发布的既存的数据。(此处不理解可忽略,业务相关)
4、将此次同步数据的存储过程记录日志表。方便查看每次同步的耗时、更新及插入条数等。
下面就是例子了(p≧w≦q)
CREATE DEFINER=`root`@`%` PROCEDURE `SYNC_TO_ONLINE`()
BEGIN
DECLARE item_id bigint(20); # 资产编号 - 业务相关判断
DECLARE asset_state int(1); # 资产状态 - 业务相关判断
DECLARE start_time datetime DEFAULT now(); # 存储过程开始时间
DECLARE end_time datetime; # 存储过程结束时间
DECLARE insert_count int(15) DEFAULT 0; # 存储过程插入的数据条数
DECLARE update_count int(15) DEFAULT 0; # 存储过程更新的数据条数
DECLARE result tinyint(1); # 存储过程执行结果(0:失败,1:成功)
DECLARE exist int(15); # 是否存在 - 业务相关判断
DECLARE pre_asset_state int(1); # 前状态 - 业务相关判断
DECLARE Done int DEFAULT 0;
DECLARE t_error INTEGER DEFAULT 0;
DECLARE cur CURSOR FOR # 将Select查询结果放入游标
# 1.此处同步业务逻辑为:将topdb_dump.topnpl_asset_tb_detail表中昨天的数据取出来,放入cur中
SELECT
*
FROM
topdb_dump.topnpl_asset_tb_detail devtb
WHERE
DATE_FORMAT(devtb.update_time,'%Y-%m-%d') = date_format(date_sub(now(),interval 1 day),'%Y-%m-%d');
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done = 1; # 循环结束标识
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error = 1; # 事务执行失败标识
START TRANSACTION; # 开启事务
OPEN cur; # 打开游标
FETCH cur INTO # 获取游标中的值
item_id,
asset_state;
WHILE Done <> 1 DO # 开始循环
# 2.此处同步业务逻辑为:比较topnpl_dump库中昨天的数据,topdb_online.topnpl_asset_tb_detail表中不存在的插入,存在的更新,并记录插入或更新条数
SELECT
COUNT(onlinetb.id), onlinetb.asset_state INTO exist, pre_asset_state
FROM
topdb_online.topnpl_asset_tb_detail onlinetb
WHERE
onlinetb.item_id=item_id;
IF exist = 0 AND (asset_state = 5 or asset_state = 6) THEN
INSERT INTO topdb_online.topnpl_asset_tb_detail VALUES (item_id,asset_state);
SET insert_count = insert_count + 1;
ELSE
UPDATE topdb_online.topnpl_asset_tb_detail onlinetb SET onlinetb.asset_state = asset_state
WHERE onlinetb.item_id=item_id;
SET update_count = update_count + 1;
END IF;
FETCH cur INTO
item_id,
asset_state;
END WHILE; # 结束循环
CLOSE cur; # 关闭游标
# 3.此处同步业务逻辑为:更新已发布的资产数据
UPDATE
topdb_online.topnpl_asset_detail_basic basic
INNER JOIN
topdb_online.topnpl_asset_tb_detail tb ON tb.id = basic.tb_detail_id
SET
basic.startday = tb.startday,
basic.endday = tb.endday;
IF t_error = 1 THEN
# 事务回滚
ROLLBACK;
SET result = 0; # 执行结果:失败
SET end_time = now();
ELSE
# 事务提交
COMMIT;
SET result = 1; # 执行结果:成功
SET end_time = now();
END IF;
# 4.将此次同步数据的存储过程记录日志表(记录内容:事件名,开始时间,结束时间,耗时,插入个数,更新个数,变更总数,结果,日志插入者,日志插入时间)
INSERT INTO topdb_online.topnpl_event_logs
(event_name, start_time, end_time, duration, insert_count, update_count, total, result, create_id, create_time)
VALUES('SYNC_TO_ONLINE_EVENT', start_time, end_time, TIMESTAMPDIFF(SECOND, start_time, end_time), insert_count,
update_count, (insert_count + update_count), result, 0, now());
END