mysql 存储过程游标遍历数据

youliroam

已于 2023-08-10 17:47:04 修改

阅读量226

点赞数

分类专栏： php 文章标签： mysql 数据库

于 2023-08-10 17:43:58 首次发布

本文链接：https://blog.csdn.net/u014650004/article/details/132215104

版权

php 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

CREATE DEFINER=`root`@`%` PROCEDURE `fixData`()
BEGIN
DECLARE Vid BIGINT;
DECLARE Vamount FLOAT;
DECLARE VreAmount FLOAT;
DECLARE VinterestAmount FLOAT;
DECLARE VserviceAmount FLOAT;
declare VsyReAmount float;

DECLARE done INT DEFAULT FALSE;
-- 待还款的订单
DECLARE rs CURSOR FOR SELECT Id,Amount,ReAmount,InterestAmount,ServiceAmount,SyReAmount FROM SysRePayment where Id=1335164 and State=1;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN rs;
	read_loop: LOOP
		FETCH rs INTO Vid,Vamount,VreAmount,VinterestAmount,VserviceAmount,VsyReAmount;
		IF done THEN
			LEAVE read_loop;
		END IF;
			select Vid,Vamount,VreAmount,VinterestAmount,VserviceAmount,VsyReAmount;
			update SysRePayment set 
				ReAmount=Vamount-VserviceAmount-VinterestAmount,
				SyReAmount=Vamount-VserviceAmount-VinterestAmount,
				SyFeeAmount=VserviceAmount
			where Id=Vid;
	END LOOP;
close rs;
END