mysql 游标

BEGIN

DECLARE no_more_record INT DEFAULT 0;
DECLARE f_id BIGINT;
DECLARE p_m DECIMAL(18,4);
DECLARE p_account_pre DECIMAL(18,4);
DECLARE p_account DECIMAL(18,4);
DECLARE cur_record CURSOR FOR SELECT fund_id, money, account_money from fund_record where user_id=1040 ORDER BY fund_id ASC; /*首先这里对游标进行定义*/
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_record = 1; /*这个是个条件处理,针对NOT FOUND的条件,当没有记录时赋值为1*/


OPEN cur_record; /*接着使用OPEN打开游标*/
FETCH cur_record INTO f_id, p_m, p_account; /*把第一行数据写入变量中,游标也随之指向了记录的第一行*/

SET p_account_pre= p_account;

WHILE no_more_record != 1 DO

FETCH cur_record INTO f_id, p_m, p_account;
#修改账户金额
UPDATE fund_record set account_money= money+p_account_pre where fund_id=f_id;

##重置原始值
SET p_account_pre= p_m + p_account_pre;

END WHILE;
CLOSE cur_record; /*用完后记得用CLOSE把资源释放掉*/

END

mysql 游标

标签：