先看一下存储过程的部分代码:
BEGIN
declare cur2 cursor for select goodsid,goodsrate from zqbwgoodsinfo where activeid=v_activeid;
declare exit handler for not found set done2=1;
OPEN cur2;
REPEAT
FETCH cur2 INTO v_goodsid,v_goodsrate;
select USERID into user_ID FROM ALLUSER where LOGINNAME=p_LoginName;
update PLAYERBAGGOODSINFO set NUM=Num+floor(p_Money*v_goodsrate) where USERID=user_ID and GOODSID=v_goodsid;
if found_rows()=0 then
insert into PLAYERBAGGOODSINFO (USERID, GOODSID, NUM, GIFTCERTIFICATEBUYNUM) values (user_ID,v_goodsid,p_Money*v_goodsrate,0);
end if;
UNTIL done2=1 END REPEAT;
CLOSE cur2;
END;
关键表:
mysql> select * from zqbwgoodsinfo;
+----------+-----------+-----------+--------------+
| goodsn | activeid | goodsid | goodsrate |
+----------+-----------+-----------+--------------+
| 1 | 1 | 4021 | 0.50 |
+----------+------------+-----------+-------------+
使用declare exit hanlder,执行存储过程看到的结果是:mysql> select * from PLAYERBAGGOODSINFO where userid=2000071 and goodsid=4021; /*PLAYERBAGGOODSINFO为leoxqing的物品信息,这是存储过程执行前的信息
+---------+------------+------+--------------------------------+
| UserID | GoodsID | Num | GiftCertificateBuyNum |
+----------+------------+--------+---------------------------------+
| 2000071 | 4021 | 7228 | 0 |
+-----------+------------+------+---------------------------------+
1 row in set (0.00 sec)mysql> call pay_v1('111,'600','leoxqing',@a);
Query OK, 1 row affected (0.00 sec)
mysql> select * from PLAYERBAGGOODSINFO where userid=2000071 and goodsid=4021;
+---------+---------+------+-----------------------+
| UserID | GoodsID | Num | GiftCertificateBuyNum |
+---------+---------+------+-----------------------+
| 2000071 | 4021 | 7528 | 0 |
+---------+---------+------+-----------------------+
1 row in set (0.00 sec)
执行后Num=Num+floor(600*0.5)=7528,正常;
下面是存储过程里使用declare continue hanlder的结果,
mysql> select * from PLAYERBAGGOODSINFO where userid=2000071 and goodsid=4021;/*初始表信息
+---------+---------+------+-----------------------+
| UserID | GoodsID | Num | GiftCertificateBuyNum |
+---------+---------+------+-----------------------+
| 2000071 | 4021 | 7528 | 0 |
+---------+---------+------+-----------------------+
调用sp
mysql> call pay_v1('111,'600','leoxqing',@a);
Query OK, 1 row affected (0.00 sec)
跟新的表信息
mysql> select * from PLAYERBAGGOODSINFO where userid=2000071 and goodsid=4021;/*初始表信息
+---------+---------+------+-----------------------+
| UserID | GoodsID | Num | GiftCertificateBuyNum |
+---------+---------+------+-----------------------+
| 2000071 | 4021 | 8128 | 0 |
+---------+---------+------+-----------------------+
使用declare continue hanlder声明的结果是
Num=Num+floor(600)
,下面是我从二进制(mysql-bin)里截取到的关键信息
update PLAYERBAGGOODSINFO set NUM=ifnull(Num,0)+floor( NAME_CONST('p_Money',600)* NAME_CONST('v_goodsrate',0.50)) where USERID= NAME_CONST('user_ID',2000071) and GOODSID= NAME_CONST('v_goodsid',4021)
update PLAYERBAGGOODSINFO set NUM=ifnull(Num,0)+floor( NAME_CONST('p_Money',600)* NAME_CONST('v_goodsrate',0.50)) where USERID= NAME_CONST('user_ID',2000071) and GOODSID= NAME_CONST('v_goodsid',4021)
这表明使用declare continue hanlder执行了两次,得到了错误的结果。我的原来的理解是游标遍历zqbwgoodsinfo这张表,直到表的最后一条记录后,就将done2设置为1,在repeat...end repeat中通过done2=1标志位就退出循环,何以最后一条记录要执行2次呢?
BEGIN
declare cur2 cursor for select goodsid,goodsrate from zqbwgoodsinfo where activeid=v_activeid;
declare exit handler for not found set done2=1;
OPEN cur2;
REPEAT
FETCH cur2 INTO v_goodsid,v_goodsrate;
select USERID into user_ID FROM ALLUSER where LOGINNAME=p_LoginName;
update PLAYERBAGGOODSINFO set NUM=Num+floor(p_Money*v_goodsrate) where USERID=user_ID and GOODSID=v_goodsid;
if found_rows()=0 then
insert into PLAYERBAGGOODSINFO (USERID, GOODSID, NUM, GIFTCERTIFICATEBUYNUM) values (user_ID,v_goodsid,p_Money*v_goodsrate,0);
end if;
UNTIL done2=1 END REPEAT;
CLOSE cur2;
END;
关键表:
mysql> select * from zqbwgoodsinfo;
+----------+-----------+-----------+--------------+
| goodsn | activeid | goodsid | goodsrate |
+----------+-----------+-----------+--------------+
| 1 | 1 | 4021 | 0.50 |
+----------+------------+-----------+-------------+
使用declare exit hanlder,执行存储过程看到的结果是:mysql> select * from PLAYERBAGGOODSINFO where userid=2000071 and goodsid=4021; /*PLAYERBAGGOODSINFO为leoxqing的物品信息,这是存储过程执行前的信息
+---------+------------+------+--------------------------------+
| UserID | GoodsID | Num | GiftCertificateBuyNum |
+----------+------------+--------+---------------------------------+
| 2000071 | 4021 | 7228 | 0 |
+-----------+------------+------+---------------------------------+
1 row in set (0.00 sec)mysql> call pay_v1('111,'600','leoxqing',@a);
Query OK, 1 row affected (0.00 sec)
mysql> select * from PLAYERBAGGOODSINFO where userid=2000071 and goodsid=4021;
+---------+---------+------+-----------------------+
| UserID | GoodsID | Num | GiftCertificateBuyNum |
+---------+---------+------+-----------------------+
| 2000071 | 4021 | 7528 | 0 |
+---------+---------+------+-----------------------+
1 row in set (0.00 sec)
执行后Num=Num+floor(600*0.5)=7528,正常;
下面是存储过程里使用declare continue hanlder的结果,
mysql> select * from PLAYERBAGGOODSINFO where userid=2000071 and goodsid=4021;/*初始表信息
+---------+---------+------+-----------------------+
| UserID | GoodsID | Num | GiftCertificateBuyNum |
+---------+---------+------+-----------------------+
| 2000071 | 4021 | 7528 | 0 |
+---------+---------+------+-----------------------+
调用sp
mysql> call pay_v1('111,'600','leoxqing',@a);
Query OK, 1 row affected (0.00 sec)
跟新的表信息
mysql> select * from PLAYERBAGGOODSINFO where userid=2000071 and goodsid=4021;/*初始表信息
+---------+---------+------+-----------------------+
| UserID | GoodsID | Num | GiftCertificateBuyNum |
+---------+---------+------+-----------------------+
| 2000071 | 4021 | 8128 | 0 |
+---------+---------+------+-----------------------+
使用declare continue hanlder声明的结果是
Num=Num+floor(600)
,下面是我从二进制(mysql-bin)里截取到的关键信息
update PLAYERBAGGOODSINFO set NUM=ifnull(Num,0)+floor( NAME_CONST('p_Money',600)* NAME_CONST('v_goodsrate',0.50)) where USERID= NAME_CONST('user_ID',2000071) and GOODSID= NAME_CONST('v_goodsid',4021)
update PLAYERBAGGOODSINFO set NUM=ifnull(Num,0)+floor( NAME_CONST('p_Money',600)* NAME_CONST('v_goodsrate',0.50)) where USERID= NAME_CONST('user_ID',2000071) and GOODSID= NAME_CONST('v_goodsid',4021)
这表明使用declare continue hanlder执行了两次,得到了错误的结果。我的原来的理解是游标遍历zqbwgoodsinfo这张表,直到表的最后一条记录后,就将done2设置为1,在repeat...end repeat中通过done2=1标志位就退出循环,何以最后一条记录要执行2次呢?
REPEAT
FETCH cur2 INTO v_goodsid,v_goodsrate;
select USERID into user_ID FROM ALLUSER where LOGINNAME=p_LoginName;
update PLAYERBAGGOODSINFO set NUM=Num+floor(p_Money*v_goodsrate) where USERID=user_ID and GOODSID=v_goodsid;
UNTIL done2=1 END REPEAT;
这个循环里面会执行两次呢 ,因为当游标遍历到表的最后一行后,游标就已经取不到数据了,此时即使再进入循环中,v_goodsid,v_goodsrate这两个变量应该是空值呀,这个时候即使执行了update这条语句了,这个里面的Num值也应该不会变化的呀!
想信你写出后,你也就明白了。open cur2
REPEAT
FETCH cur2 INTO v_goodsid,v_goodsrate;
select v_goodsid
UNTIL done2=1 END REPEAT;第1步: open cur2, 结果 done2=0
第2步: repeat
FETCH cur2 INTO v_goodsid,v_goodsrate;
结果集中第一条记录值赋给变量, done2=0
第3步:......
repeat
select 1;
until done=1 end repeat;select 1 会不会被执行?
repeat
select 1;
until done=1 end repeat; select 1 会不会被执行?
这个select 1 是会执行的,谢谢acmain!
mysql> select * from zqbwgoodsinfo;
+--------+----------+---------+-----------+
| goodsn | activeid | goodsid | goodsrate |
+--------+----------+---------+-----------+
| 1 | 1 | 4021 | 0.50 |
| 2 | 2 | 4021 | 0.20 |
+--------+----------+---------+-----------+
2 rows in set (0.00 sec)mysql> delimiter $$
mysql> create procedure test()
-> BEGIN
-> declare v_goodsrate numeric(6,2);
-> declare done int default 0;
-> declare cur1 cursor for select goodsrate from zqbwgoodsinfo where activeid=1;
-> declare continue handler for not found set done=1;
-> open cur1;
-> while done<>1 do
-> fetch cur1 into v_goodsrate;
-> select v_goodsrate;
-> end while;
-> close cur1;
-> END $$
Query OK, 0 rows affected (0.00 sec)mysql> call test();
+-------------+
| v_goodsrate |
+-------------+
| 0.50 |
+-------------+
1 row in set (0.00 sec)+-------------+
| v_goodsrate |
+-------------+
| 0.50 |
+-------------+
1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)在这里activeid=1时候v_goodsrate在循环中执行了2次。
REPEAT
FETCH cur2 INTO v_goodsid,v_goodsrate;
select v_goodsid;
UNTIL done2=1 END REPEAT;
第1步: open cur2, 结果 done2=0
第2步: repeat
FETCH cur2 INTO v_goodsid,v_goodsrate;
结果集中第一条记录值赋给变量, done2=0
第3步:...
按照acmain的思路走下去的话
第3步:FETCH cur2 INTO v_goodsid,v_goodsrate;
结果集中第二条记录值赋给变量, done2=0
第4步:FETCH cur2 INTO v_goodsid,v_goodsrate;
结果集中无数据,done2=1
这个时候 v_goodsid,v_goodsrate里面的数据应该就为空了(这个地方是我的我猜想),但此时还在循环中,因此,这个时候select v_goodsid应该是有个NULL出来!
第5步:end repeat识别到done2=1标志退出来!、
不知道我这样分析对不对?
大胆猜测,小心求证,
想办法自己设计个测试方案来验证一下。比如,建个表t, 然后插入两条记录,再写个存储过程,一试就知道了open cur
set i=0
repeat fetch cur into xx;
select i,done,xx;
set i=i+1;
until done =1 end repeat.
计算机软件中的很多问题其实都不是问题,自己多想一步什么都明白了。
mysql> call test();
+-------------+------+
| v_goodsrate | done |
+-------------+------+
| 0.50 | 0 |
+-------------+------+
1 row in set (0.00 sec)+-------------+------+
| v_goodsrate | done |
+-------------+------+
| 0.50 | 1 |
+-------------+------+
1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)
这个结果,这个地方和我之前所想象的v_goodsrate为NULL不同,这里是游标倒数第二次执行时候v_goodsrate执行的结果。等于说游标遍历溢出的时候将done设置为1,但是fetch出来的变量还是之前的变量值!
很简单!最后一次 fetch 之前 v_goodsrate =0.50
然后 fetch 出错,触发你的 handler ,设置 done=1 ,然后你定义是的continue 继续下一句
select v_goodsrate , done ;
当然是 | 0.50 | 1 | ,你并没有去改变 v_goodsrate
然后再执行until done =1 end repeat.