使用存储过程批量迁移数据

-- --------------------------------------------------------
-- 主机:                                  xxx             
-- 服务器版本:                        5.7.20-log - Source distribution
-- 服务器操作系统:                      Linux
-- HeidiSQL 版本:                  9.5.0.5291
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

-- 导出  过程 xxx 结构
DELIMITER //
CREATE DEFINER=`ai_call_engine`@`%` PROCEDURE `update_call_detail_batch`(
	IN `startId` BIGINT,
	IN `endId` BIGINT
)
    COMMENT 'robot_call_job_id,dialog_flow_id,start_time'
BEGIN
	-- 定义变量
	DECLARE s int DEFAULT 0;
	
	DECLARE robotCallJobId BIGINT(20);
	DECLARE dialogFlowId BIGINT(20);
	DECLARE startTime TIMESTAMP;
	DECLARE callRecordId BIGINT(20);
	
	-- 定义游标，并将sql结果集赋值到游标中
	DECLARE report CURSOR FOR select robot_call_job_id,dialog_flow_id,start_time,call_record_id  from call_record where call_record_id > startId and call_record_id < endId;
	-- 声明当游标遍历完后将标志变量置成某个值
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET s=1;
	-- 打开游标
	open report;
		-- 将游标中的值赋值给变量，注意：变量名不要和返回的列名同名，变量顺序要和sql结果列的顺序一致
		fetch report into robotCallJobId,dialogFlowId,startTime,callRecordId;
		-- 当s不等于1，也就是未遍历完时，会一直循环
		while s<>1 do
			-- 执行业务逻辑
			update call_detail set robot_call_job_id = robotCallJobId,dialog_flow_id = dialogFlowId,start_time = startTime  where call_record_id=callRecordId;
			-- 将游标中的值再赋值给变量，供下次循环使用
			fetch report into robotCallJobId,dialogFlowId,startTime,callRecordId;
		-- 当s等于1时表明遍历以完成，退出循环
		end while;
	-- 关闭游标
	close report;
END//
DELIMITER ;

/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;