mysql存储过程数据迁移_MySQL_mysql中用于数据迁移存储过程分享，复制代码 代码如下: DELIMITER $$ - phpStudy...

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 ;相关阅读:

Windows 7系统Msglive的天气预报服务

CSS网页设计：百分比进行背景图片定位

用ASP.ENT创建带事件的用户控件

在Linux系统平台上面如何设置网卡的绑定

只用4个字符区分IE6,IE7和IE8

替换SQL Server、Oracle和MySQL中查出值为NULL

IE中伪类hover的使用及BUG

修改发贴的编辑功能

巧编Autorun.inf硬盘分区变安装盘

php关于array_multisort多维数组排序的使用说明

Ubuntu Linux中特效设置和快捷键的使用

批量替换织梦DedeCMS所有文章来源和作者的方法

超级链接标记A的TARGET属性详解

Oracle 基本知识轻松学