MySQL 存储过程游标的简单用法

代码：

delimiter $
create PROCEDURE PROC_COUPON_TIMING_TASK()
BEGIN
        -- 定义变量
        DECLARE key_Id INT DEFAULT 0;

        -- 遍历数据结束标志
    DECLARE done INT DEFAULT FALSE;
    -- 游标
    DECLARE coupon_data CURSOR FOR SELECT u.id FROM xsxx_store_user_coupon u 
        LEFT JOIN xsxx_store_coupon c on u.coupon_id = c.id
        WHERE NOW() >= c.end_time and u.status = 0;

    -- 将结束标志绑定到游标
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- 打开游标
    OPEN  coupon_data;     
    -- 遍历
    read_loop: LOOP
            -- 取值
            FETCH  NEXT from coupon_data INTO key_Id;
            IF done THEN
                LEAVE read_loop;
             END IF;

        -- 将到期的优惠券修改状态为-1
                UPDATE xsxx_store_user_coupon SET `status` = -1 WHERE id = key_Id;
        END LOOP;
        -- 关闭游标
        CLOSE coupon_data;
END