文章积累知识,如有存在问题,请大家不啬赐教
CREATE DEFINER=`root`@`%` PROCEDURE `automatic_signIn`()
BEGIN
DECLARE done int DEFAULT FALSE;
DECLARE z_id int DEFAULT 0;
DECLARE
infos CURSOR FOR SELECT
o.id
FROM
tbl_order o
WHERE
o.order_status = 2
AND TIMESTAMPDIFF( DAY, o.sendtime, now( ) ) >= 7;
-- 声明 是否没有记录
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN infos;
out_loop :
LOOP
FETCH infos INTO z_id;
IF
done THEN
LEAVE out_loop;
END IF;
UPDATE tbl_order
SET order_status = 3 ,signtime = now()
WHERE
id = z_id;
UPDATE tbl_commission
set commission_status = 1
where order_id = z_id;
END LOOP out_loop;
CLOSE infos;
END