MYSQL游标使用

最新推荐文章于 2025-09-06 22:56:13 发布

weixin_33895604

最新推荐文章于 2025-09-06 22:56:13 发布

阅读量82

点赞数

文章标签：数据库

CREATE PROCEDURE `sp_UpdateData`()
BEGIN
DECLARE v_card_id VARCHAR(50);
/*修改会员卡的几个金额*/
DECLARE done INT DEFAULT FALSE;
DECLARE cur CURSOR FOR 
    SELECT main.card_id FROM mem_card main
    INNER JOIN mem_card_extend b ON main.card_id=b.card_id
    WHERE main.status_code=1 AND b.status_code=1
    AND main.sale_status=2
    GROUP BY main.card_id;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur;
read_loop: LOOP
FETCH cur INTO v_card_id;
IF done THEN
LEAVE read_loop;
END IF;
UPDATE mem_card_extend a SET total_fill_self_money=IFNULL((SELECT SUM(adjust_money) FROM mem_card_money_flow f WHERE (money_operate_type=2 OR money_operate_type=4) AND status_code=1 AND f.card_id=v_card_id),0) WHERE a.card_id=v_card_id;  -- 累计充值本金
UPDATE mem_card_extend a SET total_fill_give_money=IFNULL((SELECT SUM(adjust_give_money) FROM mem_card_money_flow f WHERE money_operate_type=4 AND status_code=1 AND f.card_id=v_card_id),0) WHERE a.card_id=v_card_id; -- 累计充值赠送
UPDATE mem_card_extend a SET total_adjust_self_money=IFNULL((SELECT SUM(adjust_money) FROM mem_card_money_flow f WHERE money_operate_type=3 AND status_code=1 AND f.card_id=v_card_id),0) WHERE a.card_id=v_card_id; -- 累计调整本金
UPDATE mem_card_extend a SET total_adjust_give_money=IFNULL((SELECT SUM(adjust_give_money) FROM mem_card_money_flow f WHERE money_operate_type=3 AND status_code=1 AND f.card_id=v_card_id),0) WHERE a.card_id=v_card_id; -- 累计调整赠送
UPDATE mem_card_extend a SET total_score_fill_self_money=IFNULL((SELECT SUM(adjust_money) FROM mem_card_money_flow f WHERE money_operate_type=5 AND status_code=1 AND f.card_id=v_card_id),0) WHERE a.card_id=v_card_id; -- 累计积分兑换本金
UPDATE mem_card_extend a SET total_score_fill_give_money=IFNULL((SELECT SUM(adjust_give_money) FROM mem_card_money_flow f WHERE money_operate_type=5 AND status_code=1 AND f.card_id=v_card_id ),0) WHERE a.card_id=v_card_id; -- 累计积分兑换赠送
END LOOP;
CLOSE cur;
END$$