CREATE PROCEDURE `pro_insert_tsss_routemax`()
BEGIN
-- 定义错误标识
declare t_error INTEGER DEFAULT 0;
declare t_i int DEFAULT 1;
declare t_mail_no varchar(1000);
-- 游标结束的标志
declare done int default 0;
-- 定义游标
declare cur_mail_no cursor for select a.mail_no from fs_route_back a
group by a.mail_no having count(a.id)>1 limit 0,35000;
declare CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;
-- 指定游标循环结束时的返回值
declare continue handler for not found set done = 1;
--获得时间戳
set @create_time = CAST((SELECT UNIX_TIMESTAMP(NOW()))*1000 AS DECIMAL(30));
-- 数据记录
open cur_mail_no;
fetch cur_mail_no into t_mail_no;
--记录日志表
insert into test_routemax(p0,p1,p2,p5) values(@create_time,3,"routemax",t_i);
-- while 循环
while done != 1 do
fetch cur_mail_no into t_mail_no;
if done != 1 then
START TRANSACTION;
-- 执行处理
insert into table_1
select * from table_2;
-- 如果错误则进行回滚
IF t_error = 1 THEN
ROLLBACK;
ELSE
-- 更新执行结果
update test_routemax set p5=p5+1 where p0=@create_time and p2='routemax';
COMMIT;
END IF;
end if;
end while;
-- 关闭游标
close cur_mail_no;
end