mysql中用于数据迁移存储过程分享

复制代码代码如下:
DELIMITER $$
USE `servant_591up`$$
DROP PROCEDURE IF EXISTS `sp_move_data`$$
CREATE PROCEDURE `sp_move_data`（）
BEGIN
DECLARE v_exit INT DEFAULT 0;
DECLARE v_spid BIGINT;
DECLARE v_id BIGINT;
DECLARE i INT DEFAULT 0;
DECLARE c_table INT;
DECLARE v_UniqueKey VARCHAR（57）;
DECLARE v_TagCatalogId INT;
DECLARE v_RootCatalogId INT;
DECLARE v_UserId BIGINT;
DECLARE v_QuestionId CHAR（36）;
DECLARE v_CorrectCount INT;
DECLARE v_ErrorCount INT;
DECLARE v_LastIsCorrect INT;
DECLARE v_LastAnswerXML TEXT CHARSET utf8;
DECLARE v_TotalCostTime INT;
DECLARE v_Reviews VARCHAR（200） CHARSET utf8;
DECLARE v_AnswerResultCategory INT;
DECLARE v_LastCostTime INT;
DECLARE v_LastAnswerTime DATETIME;
DECLARE v_IsPublic INT;
DECLARE v_SUBJECT INT;
DECLARE v_TotalCount INT;
DECLARE v_AnswerMode SMALLINT（6）;
DECLARE v_ExerciseWeight FLOAT;
DECLARE c_ids CURSOR FOR SELECT UniqueKey,TagCatalogId,RootCatalogId,UserId,QuestionId,CorrectCount,ErrorCount,LastIsCorrect,LastAnswerXML,TotalCostTime,Reviews,AnswerResultCategory,LastCostTime,LastAnswerTime,IsPublic,SUBJECT,TotalCount,AnswerMode,ExerciseWeight FROM ol_answerresult_56;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_exit=1;
OPEN c_ids;
REPEAT
FETCH c_ids INTO v_UniqueKey,v_TagCatalogId,v_RootCatalogId,v_UserId,v_QuestionId,v_CorrectCount,v_ErrorCount,v_LastIsCorrect,v_LastAnswerXML,v_TotalCostTime,v_Reviews,v_AnswerResultCategory,v_LastCostTime,v_LastAnswerTime,v_IsPublic,v_SUBJECT,v_TotalCount,v_AnswerMode,v_ExerciseWeight;
IF v_exit = 0 THEN
SET @vv_id = v_id;
SELECT MOD（v_UserId,100） INTO c_table;
SET @SQL_CONTEXT =
CONCAT（"INSERT INTO new_answerresult_",
c_table,"
（UniqueKey,TagCatalogId,RootCatalogId,UserId,QuestionId,CorrectCount,ErrorCount,LastIsCorrect,LastAnswerXML,TotalCostTime,Reviews,AnswerResultCategory,LastCostTime,LastAnswerTime,IsPublic,SUBJECT,TotalCount,AnswerMode,ExerciseWeight）values（",
"""",v_UniqueKey,"""",",",
v_TagCatalogId,",",
v_RootCatalogId,",",
v_UserId,",",
"""",v_QuestionId,"""",",",
v_CorrectCount,",",
v_ErrorCount,",",
v_LastIsCorrect,",",
"""",v_LastAnswerXML,"""",",",
v_TotalCostTime,",",
"""",REPLACE（IFNULL（v_Reviews,""）,"""",""）,"""",",",
v_AnswerResultCategory,",",
v_LastCostTime,",",
"""",v_LastAnswerTime,"""",",",
v_IsPublic,",",
v_SUBJECT,",",
v_TotalCount,",",
v_AnswerMode,",",
v_ExerciseWeight,"）"）;
PREPARE STMT FROM @SQL_CONTEXT;
EXECUTE STMT ;
DEALLOCATE PREPARE STMT;
END IF;
SET i=i+1;
#100
#IF MOD（i,100）=0 THEN COMMIT;
#END IF;
UNTIL v_exit=1
END REPEAT;
CLOSE c_ids;
#COMMIT;
END$$
DELIMITER ;