复制代码 代码如下: 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
复制代码 代码如下: 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 ;
