游标循环(无入参)
功能,存储过程根据过期状态自动更改过期状态
CREATE PROCEDURE `sys_project_overtime_status`()
BEGIN
# add by sam
DECLARE row_quote_id varchar(64);#定义变量报价ID
DECLARE row_proj_id varchar(64);#定义变量项目ID
DECLARE done INT;
-- 定义游标
DECLARE rs_cursor CURSOR FOR
#查询所有报价截止日期已过,并且未报价的报价列表
SELECT distinct
quote.quote_id,quote.proj_id
FROM project_quote quote
LEFT JOIN proj_base base ON quote.proj_id = base.proj_id
WHERE (quote.quote_status = '0' OR quote.quote_status = '1')
AND base.proj_quotation_deadline <= NOW();
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
#打开游标 进入循环
OPEN rs_cursor;
cursor_loop:LOOP
FETCH rs_cursor INTO row_quote_id,row_proj_id; -- 取数据
IF done=1 THEN
leave cursor_loop;
END IF;
-- 更新报价表
UPDATE project_quote SET quote_status='10' WHERE quote_id=row_quote_id;
END LOOP cursor_loop;
CLOSE rs_cursor;
END
loop 循环好处,相较于while,性能更高