原文：https://my.oschina.net/u/3582142/blog/1581929
delimiter $$ /* 重新定义mysql结束符，而不再是分号是结束符 */
create procedure mergeDeclare() /* 创建存储过程 */
BEGIN
/*定义局部变量*/
DECLARE id INT DEFAULT 0;
DECLARE Done INT DEFAULT 0;
DECLARE mobile VARCHAR(20);
DECLARE mobileTemp VARCHAR(20) DEFAULT '';
DECLARE maxId INT DEFAULT 0;
DECLARE t_error INTEGER DEFAULT 0;
DECLARE rs CURSOR FOR /* 定义从mysql取数据的游标 */
/* 查出需要的数据 */
select
a.declare_id,a.user_mobile
from
declare_info a,
(select user_mobile from declare_info b where b.status=1 group by user_mobile having count(1)>1) b
where
a.user_mobile=b.user_mobile and a.status =1 and CHARACTER_LENGTH(a.user_mobile) = 11 ORDER BY a.user_mobile , a.declare_id DESC;
/* 当游标遍历完所有数据的时候，set Done的值为1 */
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done = 1;
/* 当发生sql异常的时候set t_error的值为1 */
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;
/* 开启事务 */
START TRANSACTION;
/* 打开游标 */
OPEN rs;
/* 取游标当前的值，into后面是赋值的意思，被赋值变量的数目与sql查出来的字段数相等 */
FETCH NEXT FROM rs INTO id,mobile;
/* 在此处进行循环 */
REPEAT
IF NOT Done THEN
IF mobileTemp = mobile THEN
UPDATE farmer_certification_info set declare_id = maxId where declare_id = id;
UPDATE farmer_contact_info set declare_id = maxId where declare_id = id;
UPDATE farmer_industry_info set declare_id = maxId where declare_id = id;
UPDATE farmer_info set declare_id = maxId where declare_id = id;
UPDATE farmer_job_info set declare_id = maxId where declare_id = id;
UPDATE farmer_skill_info set declare_id = maxId where declare_id = id;
UPDATE training_class_user set declare_id = maxId where declare_id = id;
UPDATE training_class_evaluate set declare_id = maxId where declare_id = id;
UPDATE declare_info set `status`= 0 where declare_id = id;
ELSE
set mobileTemp = mobile;
set maxId = id;
END IF;
END IF;
IF t_error = 1 THEN
/* sql异常回滚 */
ROLLBACK;
ELSE
/* 提交事务 */
COMMIT;
END IF;
/* 向游标取出下一个值进行赋值 */
FETCH NEXT FROM rs INTO id,mobile;
/* 遍历完Done=1，结束遍历 */
UNTIL Done END REPEAT;
/*关闭游标*/
CLOSE rs;
END $$ /* 结束符，和开始定义的一致 */
/*
drop procedure mergeDeclare /* 删除存储过程 */
*/
/*
call mergeDeclare() /* 调用存储过程 */
*/
标注：存储过程没有容器和数据概念，但可新建表借助表当作一个容器使用。