DROP PROCEDURE IF EXISTS sp_data_migration_increment_wcmchnldoc; DELIMITER $$ CREATE PROCEDURE sp_data_migration_increment_wcmchnldoc(IN prevRecId BIGINT(20), IN recIdOffset BIGINT(20), IN docIdOffset BIGINT(20), IN siteIdOffset BIGINT(20), IN channelIdOffset BIGINT(20), IN docKindOffset BIGINT(20)) BEGIN DECLARE isover INT DEFAULT 0; DECLARE migr_table_name varchar(255) DEFAULT 'wcmchnldoc'; DECLARE doc_migr_table_name varchar(255) DEFAULT 'wcmdocument'; DECLARE view_migr_table_name varchar(255) DEFAULT 'xwcmviewinfo'; DECLARE site_migr_table_name varchar(255) DEFAULT 'wcmwebsite'; DECLARE channel_migr_table_name varchar(255) DEFAULT 'wcmchannel'; DECLARE f_CHNLID int(11) DEFAULT '0'; DECLARE f_DOCID int(11) DEFAULT '0'; DECLARE f_DOCORDER int(11) DEFAULT '0'; DECLARE f_DOCSTATUS int(11) DEFAULT '0'; DECLARE f_CRUSER varchar(100) DEFAULT 'admin'; DECLARE f_CRTIME datetime DEFAULT NULL; DECLARE f_DOCPUBTIME datetime DEFAULT NULL; DECLARE f_DOCPUBURL varchar(300) DEFAULT NULL; DECLARE f_RECID int(11) DEFAULT '0'; DECLARE f_DOCORDERPRI int(11) DEFAULT '0'; DECLARE f_INVALIDTIME datetime DEFAULT NULL; DECLARE f_OPERUSER varchar(50) DEFAULT NULL; DECLARE f_OPERTIME datetime DEFAULT NULL; DECLARE f_MODAL int(11) DEFAULT '1'; DECLARE f_DOCRELTIME datetime DEFAULT NULL; DECLARE f_DOCCHANNEL int(11) DEFAULT NULL; DECLARE f_DOCFLAG int(11) DEFAULT NULL; DECLARE f_DOCKIND int(11) DEFAULT '0'; DECLARE f_SITEID int(11) DEFAULT '0'; DECLARE f_SRCSITEID int(11) DEFAULT '0'; DECLARE f_DOCFIRSTPUBTIME datetime DEFAULT NULL; DECLARE f_NODEID int(11) DEFAULT '0'; DECLARE f_CRDEPT varchar(200) DEFAULT NULL; DECLARE f_DOCOUTUPID int(11) DEFAULT '0'; DECLARE f_DOCFORM int(11) DEFAULT '0'; DECLARE f_DOCLEVEL int(11) DEFAULT NULL; DECLARE f_attachpic smallint(6) DEFAULT NULL; DECLARE f_POSCHNLID int(11) DEFAULT '0'; DECLARE f_DocType smallint(6) DEFAULT '1'; DECLARE f_ISTIMINGPUBLISH tinyint(2) DEFAULT '0'; DECLARE f_ACTIONTYPE tinyint(4) DEFAULT '0'; DECLARE f_PUBSTATUS tinyint(4) DEFAULT '0' ; DECLARE f_GDORDER tinyint(4) DEFAULT '0'; DECLARE f_DOCAUTHOR varchar(200) DEFAULT NULL ; DECLARE f_ORIGINRECID int(11) DEFAULT '0'; DECLARE f_DOCSOURCENAME varchar(200) DEFAULT NULL ; DECLARE f_TIMEDSTATUS tinyint(4) DEFAULT '0' ; DECLARE f_CANCELPUBTIME datetime DEFAULT NULL ; -- 迭代十五才有的字段 DECLARE f_ACTIONUSER varchar(100) DEFAULT NULL ; DECLARE cur CURSOR FOR SELECT CHNLID, DOCID, DOCORDER, DOCSTATUS, CRUSER, CRTIME, DOCPUBTIME, DOCPUBURL, RECID, DOCORDERPRI, INVALIDTIME, OPERUSER, OPERTIME, MODAL, DOCRELTIME, DOCCHANNEL, DOCFLAG, DOCKIND, SITEID, SRCSITEID, DOCFIRSTPUBTIME, NODEID, CRDEPT, DOCOUTUPID, DOCFORM, DOCLEVEL, attachpic, POSCHNLID, 20, 0, 0, 0, 0, null, 0, null, 0, null -- 迭代十五才有的字段 ACTIONUSER FROM wcmchnldoc where CHNLID > 0 and SITEID > 0 AND RECID > prevRecId ORDER BY RECID ASC ; DECLARE CONTINUE HANDLER FOR NOT FOUND SET isover = 1; OPEN cur; FETCH cur INTO f_CHNLID, f_DOCID, f_DOCORDER, f_DOCSTATUS, f_CRUSER, f_CRTIME, f_DOCPUBTIME, f_DOCPUBURL, f_RECID, f_DOCORDERPRI, f_INVALIDTIME, f_OPERUSER, f_OPERTIME, f_MODAL, f_DOCRELTIME, f_DOCCHANNEL, f_DOCFLAG, f_DOCKIND, f_SITEID, f_SRCSITEID, f_DOCFIRSTPUBTIME, f_NODEID, f_CRDEPT, f_DOCOUTUPID, f_DOCFORM, f_DOCLEVEL, f_attachpic, f_POSCHNLID, f_DocType, f_ISTIMINGPUBLISH, f_ACTIONTYPE, f_PUBSTATUS, f_GDORDER, f_DOCAUTHOR, f_ORIGINRECID, f_DOCSOURCENAME, f_TIMEDSTATUS, f_CANCELPUBTIME -- 迭代十五才有的字段 f_ACTIONUSER ; select concat('开始迁移 ', migr_table_name) info; WHILE isover= 0 DO # 断点续传 IF (f_RECID > prevRecId) THEN SET @RECID = f_RECID; SET f_RECID = f_RECID + recIdOffset; set f_ORIGINRECID = f_RECID; SET @doc_offset_num = ifnull((SELECT offset_num FROM data_migration_offset WHERE table_name = doc_migr_table_name AND start_id < f_DOCID and end_id >= f_DOCID), docIdOffset); SET f_DOCID = f_DOCID + @doc_offset_num; SET @channel_offset_num = ifnull((SELECT offset_num FROM data_migration_offset WHERE table_name = channel_migr_table_name AND start_id < f_CHNLID and end_id >= f_CHNLID), channelIdOffset); SET f_CHNLID = f_CHNLID + @channel_offset_num; SET @site_offset_num = ifnull((SELECT offset_num FROM data_migration_offset WHERE table_name = site_migr_table_name AND start_id < f_SITEID and end_id >= f_SITEID), siteIdOffset); SET f_SITEID = f_SITEID + @site_offset_num; SET @view_offset_num = ifnull((SELECT offset_num FROM data_migration_offset WHERE table_name = view_migr_table_name AND start_id < f_DOCKIND and end_id >= f_DOCKIND), docKindOffset); SET f_DOCKIND = f_DOCKIND + @view_offset_num; SET @docchannel_offset_num = ifnull((SELECT offset_num FROM data_migration_offset WHERE table_name = channel_migr_table_name AND start_id < f_DOCCHANNEL and end_id >= f_DOCCHANNEL), channelIdOffset); SET f_DOCCHANNEL = f_DOCCHANNEL + @docchannel_offset_num; IF (f_DocType = 1) THEN SET f_DocType = 20; END IF; insert into trs_hycloud_iip.wcmchnldoc ( CHNLID, DOCID, DOCORDER, DOCSTATUS, CRUSER, CRTIME, DOCPUBTIME, DOCPUBURL, RECID, DOCORDERPRI, INVALIDTIME, OPERUSER, OPERTIME, MODAL, DOCRELTIME, DOCCHANNEL, DOCFLAG, DOCKIND, SITEID, SRCSITEID, DOCFIRSTPUBTIME, NODEID, CRDEPT, DOCOUTUPID, DOCFORM, DOCLEVEL, attachpic, POSCHNLID, DocType, ISTIMINGPUBLISH, ACTIONTYPE, PUBSTATUS, GDORDER, DOCAUTHOR, ORIGINRECID, DOCSOURCENAME, TIMEDSTATUS, CANCELPUBTIME -- 迭代十五才有的字段 ACTIONUSER ) VALUES ( f_CHNLID, f_DOCID, f_DOCORDER, f_DOCSTATUS, f_CRUSER, f_CRTIME, f_DOCPUBTIME, f_DOCPUBURL, f_RECID, f_DOCORDERPRI, f_INVALIDTIME, f_OPERUSER, f_OPERTIME, f_MODAL, f_DOCRELTIME, f_DOCCHANNEL, f_DOCFLAG, f_DOCKIND, f_SITEID, f_SRCSITEID, f_DOCFIRSTPUBTIME, f_NODEID, f_CRDEPT, f_DOCOUTUPID, f_DOCFORM, f_DOCLEVEL, f_attachpic, f_POSCHNLID, f_DocType, f_ISTIMINGPUBLISH, f_ACTIONTYPE, f_PUBSTATUS, f_GDORDER, f_DOCAUTHOR, f_ORIGINRECID, f_DOCSOURCENAME, f_TIMEDSTATUS, f_CANCELPUBTIME -- 迭代十五才有的字段 f_ACTIONUSER ); SET @log_sql = build_migration_log_sql(migr_table_name, @RECID, now()); prepare stmt from @log_sql; EXECUTE stmt; deallocate prepare stmt; call setOffset(migr_table_name, recIdOffset, prevRecId, @RECID); END IF ; FETCH cur INTO f_CHNLID, f_DOCID, f_DOCORDER, f_DOCSTATUS, f_CRUSER, f_CRTIME, f_DOCPUBTIME, f_DOCPUBURL, f_RECID, f_DOCORDERPRI, f_INVALIDTIME, f_OPERUSER, f_OPERTIME, f_MODAL, f_DOCRELTIME, f_DOCCHANNEL, f_DOCFLAG, f_DOCKIND, f_SITEID, f_SRCSITEID, f_DOCFIRSTPUBTIME, f_NODEID, f_CRDEPT, f_DOCOUTUPID, f_DOCFORM, f_DOCLEVEL, f_attachpic, f_POSCHNLID, f_DocType, f_ISTIMINGPUBLISH, f_ACTIONTYPE, f_PUBSTATUS, f_GDORDER, f_DOCAUTHOR, f_ORIGINRECID, f_DOCSOURCENAME, f_TIMEDSTATUS, f_CANCELPUBTIME -- 迭代十五才有的字段 f_ACTIONUSER ; END WHILE; CLOSE cur; update trs_hycloud_iip.wcmchnldoc set PUBSTATUS = 1 where RECID > prevRecId and DOCSTATUS = 10; select concat('完成迁移 ', migr_table_name) info; END $$ DELIMITER ;