mysql存储过程左连接批量修改数据状态值DEMO

BEGIN  
DECLARE  a INT;

DECLARE  b INT;

DECLARE  c INT;

DECLARE s int DEFAULT 0;

#声明游标cursor_name（cursor_name是个多行结果集）  
DECLARE
    cursor_name CURSOR FOR SELECT
        price,
        count(b.user_id) AS num,
        a.id
    FROM
        xyt_advertising AS a
    LEFT JOIN xyt_browse AS b ON a.id = b.advertiser_id 
    WHERE a.status = 1
    GROUP BY
        a.id;
#声明当游标遍历完后将标志变量置成某个值
DECLARE CONTINUE HANDLER FOR NOT FOUND SET s=1;

OPEN cursor_name;

#获取游标当前指针的记录，读取一行数据并传给变量a,b  
FETCH cursor_name INTO a,b,c;

#开始循环，判断是否游标已经到达了最后作为循环条件   
WHILE s<>1 DO
   
   if b>=a then
   #INSERT test(a1,b1,c1) VALUES(a,b,c);
UPDATE xyt_advertising SET STATUS = 2 WHERE id = c;
   END if;

  fetch  cursor_name into a,b,c;  
END WHILE; 

#关闭游标     
CLOSE cursor_name;

END