mysql 存储过程 -- 游标的使用（备忘）

BEGIN

    DECLARE f_leastCount INT DEFAULT 100;
    DECLARE f_ratio FLOAT DEFAULT 0.8;

    DECLARE i_channel VARCHAR(64);
    DECLARE i_appVersionName VARCHAR(64);
    DECLARE i_statDate DATE;
    DECLARE i_accumulateCount INT;
    DECLARE i_activateCount INT;
    DECLARE i_dau30Count INT;
    DECLARE i_dau7Count INT;
    DECLARE i_dauCount INT;
    DECLARE i_dauImeiCount INT;
    DECLARE i_dauImsiCount INT;
    DECLARE i_launchCount INT;
    DECLARE i_lostCount INT;
    DECLARE i_newCount INT;

    #遍历数据结束标志
  DECLARE done INT DEFAULT FALSE;

  #游标
  DECLARE cur CURSOR FOR SELECT ds.channel,ds.appVersionName,ds.statDate,ds.accumulateCount,ds.activateCount,ds.dau30Count,ds.dau7Count,ds.dauCount,ds.dauImeiCount,ds.dauImsiCount,ds.launchCount,ds.lostCount,ds.newCount FROM appstore_dau_stat ds WHERE ds.statDate = DATE_SUB(CURDATE(),INTERVAL m_daysdelay DAY);
    #将结束标志绑定到游标
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- 打开游标
    OPEN cur;
    -- 开始循环
  read_loop: LOOP
        -- 提取游标里的数据
        FETCH cur INTO i_channel, i_appVersionName, i_statDate, i_accumulateCount, i_activateCount, i_dau30Count, i_dau7Count, i_dauCount, i_dauImeiCount, i_dauImsiCount, i_launchCount, i_lostCount, i_newCount;
        -- 声明结束的时候
    IF done THEN
      LEAVE read_loop;
    END IF;
        -- 事务处理
        IF i_accumulateCount > f_leastCount THEN
            SET i_accumulateCount = ROUND(i_accumulateCount * f_ratio);
        END IF;
        IF i_activateCount > f_leastCount THEN
            SET i_activateCount = ROUND(i_activateCount * f_ratio);
        END IF;
        IF i_dau30Count > f_leastCount THEN
            SET i_dau30Count = ROUND(i_dau30Count * f_ratio);
        END IF;
        IF i_dau7Count > f_leastCount THEN
            SET i_dau7Count = ROUND(i_dau7Count * f_ratio);
        END IF;
        IF i_dauCount > f_leastCount THEN
            SET i_dauCount = ROUND(i_dauCount * f_ratio);
        END IF;
        IF i_dauImeiCount > f_leastCount THEN
            SET i_dauImeiCount = ROUND(i_dauImeiCount * f_ratio);
        END IF;
        IF i_dauImsiCount > f_leastCount THEN
            SET i_dauImsiCount = ROUND(i_dauImsiCount * f_ratio);
        END IF;
        IF i_launchCount > f_leastCount THEN
            SET i_launchCount = ROUND(i_launchCount * f_ratio);
        END IF;       
        IF i_lostCount > f_leastCount THEN
            SET i_lostCount = ROUND(i_lostCount * f_ratio);
        END IF;       
        IF i_newCount > f_leastCount THEN
            SET i_newCount = ROUND(i_newCount * f_ratio);
        END IF;

        IF NOT EXISTS (SELECT id FROM appstore_dau_stat_temp WHERE channel = i_channel AND appVersionName = i_appVersionName AND statDate = i_statDate) THEN
            INSERT INTO appstore_dau_stat_temp(channel,appVersionName,statDate,accumulateCount,activateCount,dau30Count,dau7Count,dauCount,dauImeiCount,dauImsiCount,launchCount,lostCount,newCount)
                VALUES(i_channel, i_appVersionName, i_statDate, i_accumulateCount, i_activateCount, i_dau30Count, i_dau7Count, i_dauCount, i_dauImeiCount, i_dauImsiCount, i_launchCount, i_lostCount, i_newCount);
        END IF;

  END LOOP;
    -- 关闭游标
    CLOSE  cur;

END

参数声明：IN `m_daysdelay` int

提取一下，游标的使用大体是这样：

BEGIN

  #遍历数据结束标志
  DECLARE done INT DEFAULT FALSE;

  #游标
  DECLARE cur CURSOR FOR SELECT 字段1,字段2... FROM 表1;
    #将结束标志绑定到游标
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  -- 打开游标
  OPEN cur;
  -- 开始循环
  read_loop: LOOP
        -- 提取游标里的数据
        FETCH cur INTO 变量1，变量2...; 
        -- 声明结束的时候
    IF done THEN
      LEAVE read_loop;
    END IF;
        -- 事务处理

　　　　#在这里做你想做的事

 END LOOP;

-- 关闭游标 
CLOSE cur; 

END

 

转载于:https://www.cnblogs.com/crazyapple/p/5553068.html