mysql存储过程

CREATE DEFINER=`root`@`%` PROCEDURE `shxc40`()
BEGIN
-- 定义变量
    DECLARE s int default 0;
    DECLARE f_id varchar(32);
    DECLARE imuserid varchar(32);
    DECLARE imusername varchar(255);
    DECLARE finishtime datetime;
    -- 定义游标，并将sql结果集赋值到游标中
    DECLARE report CURSOR FOR select id,finish_time,user_id,report_person from bas_fault_report where create_time>'2020-10-01 00:00:00' and create_time<'2020-10-18 23:59:59' and status='待验收';
 
    -- 声明当游标遍历完后将标志变量置成某个值
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET s=1;
 
    -- 打开游标
    open report;
 
        -- 将游标中的值赋值给变量，注意：变量名不要和返回的列名同名，变量顺序要和sql结果列的顺序一致
        fetch report into f_id,finishtime,imuserid,imusername;
 
        -- 当s不等于1，也就是未遍历完时，会一直循环
        while s<>1 do
             
            -- 执行业务逻辑
            update bas_fault_report set bas_fault_report.status='已完成',bas_fault_report.confirm_time=date_add(finishtime, interval 60 minute)
						where bas_fault_report.id=f_id;
						
						insert into bas_fault_repair_record values(REPLACE(uuid(),'-',''),f_id,'填写验收记录','验收',imuserid,imusername,date_add(finishtime, interval 60 minute),imuserid);
            -- 当s等于1时表明遍历以完成，退出循环
            fetch report into f_id,finishtime,imuserid,imusername;
        end while;
				 -- 获取查询影响的行数
    -- 关闭游标
    close report;
END

执行存储过程

call shxc40();