DELIMITER $$
DROP
PROCEDURE
IF EXISTS `transferEmailTempData`$$
CREATE
PROCEDURE
transferEmailTempData(
IN
jobId
VARCHAR
(24))
BEGIN
DECLARE
idval
VARCHAR
(24)
DEFAULT
''
;
DECLARE
taskIdval
VARCHAR
(24)
DEFAULT
''
;
DECLARE
groupIdval
VARCHAR
(24)
DEFAULT
''
;
DECLARE
emailval
VARCHAR
(50)
DEFAULT
''
;
/*标识正式表是否存在一条相同数据,即:groupId、email相同*/
DECLARE
infoId
VARCHAR
(24)
DEFAULT
''
;
/*标识事务错误*/
DECLARE
err
INT
DEFAULT
0;
/*达到一定数量就进行提交,计数器*/
DECLARE
counts
INT
DEFAULT
0;
/*标识是否回滚过*/
DECLARE
isrollback
INT
DEFAULT
0;
/*游标遍历时,作为判断是否遍历完全部记录的标记*/
DECLARE
done
INTEGER
DEFAULT
0;
/*获取临时表该任务的数据*/
DECLARE
cur
CURSOR
FOR
SELECT
id,taskId,groupId,email
FROM
`t_email_data_temp`
WHERE
taskId=jobId;
/*根据群组id、email查询是否存在相同记录*/
DECLARE
cur2
CURSOR
FOR
SELECT
id
FROM
`t_email_info` e
WHERE
e.`group_id` = groupIdval
AND
e.`email_address` = emailval;
/* 出现错误,设置为1,只要发生异常就回滚*/
DECLARE
CONTINUE
HANDLER
FOR
SQLEXCEPTION
SET
err=1;
/*声明当游标遍历完全部记录后将标志变量置成某个值*/
DECLARE
CONTINUE
HANDLER
FOR
NOT
FOUND
SET
done=1;
/*开启事务*/
START
TRANSACTION
;
/*打开游标*/
OPEN
cur;
/*使用LOOP循环遍历*/
out_loop:LOOP
/*将每一条结果对应的字段值赋值给变量*/
FETCH
cur
INTO
idval,taskIdval,groupIdval,emailval;
IF done = 1
THEN
LEAVE out_loop;
END
IF;
/*打开第二个游标*/
OPEN
cur2;
SET
done = 0;
FETCH
cur2
INTO
infoId;
/*如果正式表不存在相同groupId
and
email记录,添加到正式表*/
IF done = 1
THEN
/*插入正式表*/
INSERT
INTO
`t_email_info`
VALUES
(idval,emailval,groupIdval,0,
''
,NOW(),
'admin'
,NOW(),
'admin'
);
/*删除临时数据*/
DELETE
FROM
`t_email_data_temp`
WHERE
id = idval;
/*计数器,每1000条才提交*/
SET
counts = counts + 1;
/*发生异常,回滚*/
IF err=1
THEN
SET
isrollback=1;
ROLLBACK
;
ELSE
IF counts = 1000
THEN
COMMIT
;
/*达到1000条提交后,重置计数器*/
SET
counts=0;
END
IF;
END
IF;
ELSE
/*已经存在相同记录,则删除该记录*/
IF done=0
THEN
DELETE
FROM
`t_email_data_temp`
WHERE
id = idval;
END
IF;
END
IF;
FETCH
cur2
INTO
infoId;
CLOSE
cur2;
/*控制外部的循环,该步骤不能缺少,否则只循环一次就结束了*/
SET
done=0;
END
LOOP out_loop;
CLOSE
cur;
/*如果没有发生过回滚事件,则更新task状态*/
/*如果回滚过,不更新task状态,下次执行任务的时候,会再次将剩余没有提交的数据进行添加到正式表*/
IF isrollback=0
THEN
UPDATE
`t_email_task` t
SET
t.`if_finish` = 1
WHERE
t.`id`=jobId;
END
IF;
END
$$
DELIMITER ;