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();
