注册 X
我已阅读并同意  服务条款
首页 > IT技术笔记 > 查看笔记

mysql 存储过程实现从一张表数据迁移到另一种表

本文档主要是描述怎么在一系列条件下将home_brand_manual_pdf的数据插入到brand_shuomingshu表中


1172279-20171220210253225-1455754677.png

过滤条件brand_shuomingshu表:status > 0 and pdfStatus > 0(即只需要将该条件下的数据迁移到brand_shuomingshu中)

通过存储过程迁移数据,通过navicat(mysql可视化根据)建立的存储过程如下(假设存储过程名字为test):


点击复制

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

 打赏        分享



评论