MySQL一個表遷移到另一個庫_mysql 存儲過程實作從一張表資料遷移到另一種表

BEGIN

-- 需要定義接收遊标資料的變量

DECLARE idp VARCHAR(255);

DECLARE brandIdp VARCHAR(255);

DECLARE namep VARCHAR(1000);

DECLARE urlp VARCHAR(1000);

DECLARE downloadUrlp VARCHAR(1000);

DECLARE sitep VARCHAR(255);

DECLARE appIdp VARCHAR(255);

DECLARE pathp VARCHAR(255);

DECLARE siteMemop VARCHAR(255);

DECLARE sizep BIGINT(255);

DECLARE errorp VARCHAR(255);

DECLARE totalsp int(10);

DECLARE checkp int(11);

DECLARE textp int(11);

DECLARE statusp TINYINT(255);

DECLARE createUserp VARCHAR(255);

DECLARE createDatep TIMESTAMP;

DECLARE updateDatep TIMESTAMP;

DECLARE updateUserp VARCHAR(255);

-- 周遊資料結束标志

DECLARE done INT DEFAULT FALSE;

DECLARE cur CURSOR FOR SELECT

brand_shuomingshu.pdfId AS id,

home_brand.id AS brandId,

brand_shuomingshu.pdfName AS `name`,

brand_shuomingshu.url AS url,

brand_shuomingshu.downloadUrl AS downloadUrl,

brand_shuomingshu.site AS site,

brand_shuomingshu.appId AS appId,

brand_shuomingshu.pdfPath As path,

brand_shuomingshu.memo AS siteMemo,

brand_shuomingshu.pdfSize AS size,

brand_shuomingshu.pdfError AS error,

brand_shuomingshu.pdfTotals AS totals,

brand_shuomingshu.pdfCheck AS `check`,

brand_shuomingshu.pdfText AS text,

brand_shuomingshu.pdfStatus AS `status`,

brand_shuomingshu.createUser AS createUser,

brand_shuomingshu.createDate AS createDate,

brand_shuomingshu.updateUser AS updateUser,

brand_shuomingshu.updateDate AS updateDate

from brand_shuomingshu,home_brand where brand_shuomingshu.brandName = home_brand.`name` AND brand_shuomingshu.`status` >0

AND brand_shuomingshu.pdfStatus>0;

-- 将結束标志綁定到遊标

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

-- 打開遊标

OPEN cur;

-- 開始循環

read_loop: LOOP

-- 提取遊标裡的資料

FETCH cur INTO idp,brandIdp,namep,urlp,downloadUrlp,sitep,appIdp,pathp,siteMemop,sizep,errorp,totalsp,checkp,textp,statusp,createUserp,createDatep,updateUserp,updateDatep;

-- 聲明結束的時候

IF done THEN

LEAVE read_loop;

END IF;

-- 這裡做你想做的循環的事件

INSERT INTO home_brand_manual_pdf VALUES (idp,brandIdp,namep,urlp,downloadUrlp,sitep,appIdp,pathp,siteMemop,sizep,errorp,totalsp,checkp,textp,statusp,createUserp,createDatep,updateUserp,updateDatep);

END LOOP;

-- 關閉遊标

CLOSE cur;

END