通过存储过程的游标修改某个字段的全部数据

delimiter $
CREATE PROCEDURE cg(in flag INT,in initial VARCHAR(6))
BEGIN  
-- 定义变量   
declare cgid BIGINT;  
declare bdid BIGINT;
declare yearStr BIGINT;
declare serial_number INT default 1;   
declare done int;  
-- 创建游标,并存储数据  
declare class_group CURSOR for  
   SELECT cg.id AS cgid , bd.id AS bdid ,DATE_FORMAT(cg.createByTime,'%y') AS yearStr FROM t_platform_marketing_class_group  cg 
   INNER JOIN t_platform_marketing_course_sku cs ON cg.courseSkuId = cs.id 
   INNER JOIN t_platform_marketing_course_category cc ON cs.courseCategoryId = cc.id 
   INNER JOIN t_platform_marketing_course c ON cc.courseId = c.id
   INNER JOIN t_platform_marketing_basic_data bd ON c.phaseId = bd.id 
   WHERE IF(flag = 19,DATE_FORMAT(cg.createByTime,'%y')>18,DATE_FORMAT(cg.createByTime,'%y')<19) AND bd.classify = initial; 
-- 游标中的内容执行完后将done设置为1  
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;   
-- 打开游标  
open class_group;  
--  执行循环  
  posLoop:LOOP  
-- 判断是否结束循环  
        IF done=1 THEN    
      LEAVE posLoop;  
    END IF;   
-- 取游标中的值  
    FETCH class_group into cgid ,bdid ,yearStr;  
-- 执行更新操作  
        -- SELECT cgid; 
      update t_platform_marketing_class_group set serialNumber = serial_number , phaseId = bdid,yearStr = yearStr where id = cgid;
            -- 字段自增一
            SET serial_number = serial_number + 1 ;
  END LOOP posLoop;  
CLOSE class_group; 
END $

-- 调用存储过程
CALL cg(18,'X');
CALL cg(18,'C');
CALL cg(18,'G');
CALL cg(19,'X');
CALL cg(19,'C');
CALL cg(19,'G');
-- 删除存储过程
 drop procedure cg;

 

posted @ 2019-04-25 16:58  史安良  阅读(591)  评论(0编辑  收藏  举报