Mysql 游标使用

BEGIN
        #shopsId 商家ID
        #accountDay 10位日期 2018-01-09
        -- 定义一个或者多个 变量来接收 游标查询的列值
        DECLARE receiptContentId INT;   

        -- 遍历数据结束标志
        DECLARE done INT DEFAULT FALSE;
        -- 游标内容
        DECLARE cursor_receipt_content CURSOR FOR select id from t_shopping_receipt_content where shops_id=shopsId 
            and grasping_time >= STR_TO_DATE(CONCAT(accountDay,' 00:00:00'), '%Y-%m-%d %H:%i:%s')
            and grasping_time <= STR_TO_DATE(CONCAT(accountDay,' 23:59:59'), '%Y-%m-%d %H:%i:%s');
    　　 -- 将结束标志绑定到游标
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;  
        
        -- 打开游标
        OPEN cursor_receipt_content;  
    　　 r_loop: LOOP  
                    -- 提取游标里的数据，这里只有一个，多个用","逗号隔开
                    FETCH cursor_receipt_content INTO receiptContentId;
                    IF done THEN  
                            LEAVE r_loop; 
                    END IF;  
                    -- 这里做你想做的循环的事件
                    DELETE FROM t_receipt_detail_content   WHERE receipt_content_id = receiptContentId;
                    DELETE FROM t_shopping_receipt_content WHERE id = receiptContentId;
    　　END LOOP r_loop;
        -- 关闭游标
    　　CLOSE cursor_receipt_content; 
    RETURN 0;
END

 

转载于:https://www.cnblogs.com/eason-d/p/8275596.html