欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

mysql游标批量操作

程序员文章站 2022-06-10 20:37:45
...
BEGIN
    DECLARE _uuid VARCHAR(60);
    DECLARE _insertNum INT DEFAULT 0;
    DECLARE done int DEFAULT 0;
    #定义游标
    DECLARE rs_cursor CURSOR FOR 
       SELECT uuid,(10-IFNULL(rateNum,0)) as insertNum from product p 
           LEFT JOIN 
      (SELECT product_uuid,count(product_uuid) AS rateNum FROM rate GROUP BY product_uuid HAVING rateNum<10) as n
           ON p.uuid = n.product_uuid;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
    OPEN rs_cursor;
    cursor_loop:LOOP
				FETCH rs_cursor INTO _uuid,_insertNum;
        IF done = 1 THEN
           LEAVE cursor_loop;
        END IF;
        SELECT _uuid,_insertNum;
        INSERT INTO rate(uuid,nick,result,created,content,tid,product_uuid,deleted,flag) 
          SELECT rand_string(10),nick,result,now(),content,NULL,_uuid,0,NULL FROM rate_sample ORDER BY RAND() LIMIT _insertNum;
    END LOOP;
    CLOSE rs_cursor;
END

 select count(*) from (SELECT s.phone_num,u.weixin_id,s.total_point FROM `app_db`.`si_receive_red_envelope` s left join app_user u on s.phone_num = u.phone_num  where  ifNull(u.weixin_id,'') !='')
m left join si_red_draw_log w on m.phone_num = w.phone_num where   ifNull(w.weixin_id,'') =''  and m.total_point >=25 and m.total_point <50