需求如下:
数据库是mysql,写个存储过程从表tb_accountinfo中取出所有计费模式(aipolicyfee)为包月的用户,从用户金额(aibalance)中扣去月租,月租当参数在页面调用此存储过程是传入。 DELIMITER $$DROP PROCEDURE IF EXISTS `bosscn`.`pr_kouyuezu` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `pr_kouyuezu`(input decimal(10,2))
BEGINDECLARE done INT DEFAULT 0;
declare bal decimal(10,2);
declare id varchar(45);
declare id1 varchar(45);
declare rtn int;Declare curDecFee Cursor
for
Select aiid From tb_accountinfo where aipolicyfee = '包月';DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;set bal = 0;Open curDecFee;repeatFetch curDecFee Into id;Select aibalance into bal From tb_accountinfo Where aiid=id;
set bal = bal - input;if bal >= 0 then
update tb_accountinfo set aibalance = bal where aiid = id;
else
set rtn = 0;
end if;UNTIL done end repeat;close curDecFee;END $$DELIMITER ;问题:表中前面包月用户扣费正常,最后一个包月用户总是多扣一次,请问为什么,有什么比较好的解决办法,谢谢!
数据库是mysql,写个存储过程从表tb_accountinfo中取出所有计费模式(aipolicyfee)为包月的用户,从用户金额(aibalance)中扣去月租,月租当参数在页面调用此存储过程是传入。 DELIMITER $$DROP PROCEDURE IF EXISTS `bosscn`.`pr_kouyuezu` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `pr_kouyuezu`(input decimal(10,2))
BEGINDECLARE done INT DEFAULT 0;
declare bal decimal(10,2);
declare id varchar(45);
declare id1 varchar(45);
declare rtn int;Declare curDecFee Cursor
for
Select aiid From tb_accountinfo where aipolicyfee = '包月';DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;set bal = 0;Open curDecFee;repeatFetch curDecFee Into id;Select aibalance into bal From tb_accountinfo Where aiid=id;
set bal = bal - input;if bal >= 0 then
update tb_accountinfo set aibalance = bal where aiid = id;
else
set rtn = 0;
end if;UNTIL done end repeat;close curDecFee;END $$DELIMITER ;问题:表中前面包月用户扣费正常,最后一个包月用户总是多扣一次,请问为什么,有什么比较好的解决办法,谢谢!
解决方案 »
- MySQL有图形化前端吗?
- 一个数据库查询问题
- MySQL的随机查询,想说爱你不容易
- 通过附加层访问mysql
- MySql中如何判断数据库是否存在的代码?
- can't return a result set in the given context
- 老土问题:mysql jdbc取出来的数据中文显示乱码
- msq 设置默认值问题
- 高分啊!!我刚在redhat linux8.0上装了mysql,问题多多啊,各位高手回答我啊!
- win2008 64bit下,mysql odbc连接数据出错?
- 现在有个表取出一个金额字段并减去例如10然后再保存到数据库中
- 关于最新版本postgresql的问题:通过postgresql-8.3.7-1-linux.bin安装的...
update tb_accountinfo set aibalance = bal where aiid = id;
else
set rtn = 0;
end if;