mysql 循环修改 存储过程
DELIMITER $$
create procedure my_proc()
begin
DECLARE billId int;
DECLARE moneyOrder DECIMAL(10,2);
DECLARE stop int default 0;
DECLARE My_Cursor CURSOR FOR (SELECT money_order, bill_id FROM orders WHERE order_time = '2019-07-31');#定义游标并输入结果集 结果集越少越好
DECLARE CONTINUE HANDLER FOR NOT FOUND SET stop = null;
OPEN My_Cursor;#打开游标
FETCH My_Cursor into moneyOrder,billId;#将游标当前读取行的数据顺序赋予自定义变量12
WHILE ( stop is not null) DO
update bill set bill_amount = bill_amount - moneyOrder,order_num = order_num -1 where id = billId;#更新具体内容,CONCAT中的content是数据表中原有的字段属性值
FETCH My_Cursor into moneyOrder,billId;
END WHILE;
CLOSE My_Cursor;
END $$
DELIMITER ;
call my_proc();#执行存储过程
drop procedure my_proc;#销毁该存储过程