MYSQL游标有SELECT的操作方式

DECLARE dtCur CURSOR FOR SELECT rate,update_dt FROM rate_t WHERE update_dt > `st` AND update_dt< DATE_ADD(`et`, INTERVAL '23:59:59' HOUR_SECOND) ORDER BY update_dt ASC;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET `nomore` = 1;

上面的通过`nomore` = 1结束游标循环的方式碰到其中有SELECT的情况不适用，游标会在第一个SELECT执行之后关闭

下面的方式可以使用select

SELECT COUNT(*) INTO @dtCount FROM rate_t WHERE update_dt > `st` AND update_dt< DATE_ADD(`et`, INTERVAL '23:59:59' HOUR_SECOND);

  SET @dtNum = 0;
  
  OPEN dtCur;
WHILE @dtNum<@dtCount DO 
SET @dtNum = @dtNum+1;
FETCH dtCur INTO `tmpClickRate`,`tmpEt`;
INSERT INTO dt_array(Dt,rate) values(`tmpEt`,`tmpClickRate`); 

INSERT INTO daily_report(Dt, Clicks) 
SELECT DATE(t.created_dt) as Dt,
              SUM(CASE t.event_id WHEN 0 THEN `clickRate` ELSE 0 END) AS Clicks 
              FROM track_t t WHERE t.created_dt BETWEEN `tmpSt` AND `tmpEt`  
              GROUP BY DATE(t.created_dt) ASC;
SET `clickRate` = `tmpClickRate`;
SET `tmpSt` = `tmpEt`;
END WHILE;
  CLOSE dtCur;