delimiter // CREATE DEFINER=`root`@`localhost` PROCEDURE `transforms`(_from int ,_to int ,num int ) begin declare t_error int default 0; declare _temp int default 0; declare continue handler for sqlexception set t_error=1; declare AffectRows int out Number ; start transaction; set _temp=(select count(*) from account where id=_from); if _temp=0 then set t_error=1; end if; set _temp=(select count(*) from account where id=_to); if _temp=0 then set t_error=1; end if; update account set account.account=account.account+num where id=_to; update account set account.account=account.account-num where id=_from; if t_error=1 then AffectRows:=0; rollback; else AffectRows:=sql%rowcount; commit; end if; end// delimiter ; 就是这段代码能不能带出所影响的行数delimiter // CREATE DEFINER=`root`@`localhost` PROCEDURE `transforms`(_from int ,_to int ,num int ) begin declare t_error int default 0; declare _temp int default 0; declare continue handler for sqlexception set t_error=1; start transaction; set _temp=(select count(*) from account where id=_from); if _temp=0 then set t_error=1; end if; set _temp=(select count(*) from account where id=_to); if _temp=0 then set t_error=1; end if; update account set account.account=account.account+num where id=_to; update account set account.account=account.account-num where id=_from; if t_error=1 then rollback; else commit; end if; end// delimiter ;执行这段代码之后为什么所影响的行数是零啊。。但是数据库更新了
begin declare t_error int default 0;
declare _temp int default 0;
declare continue handler for sqlexception set t_error=1;
declare AffectRows int out Number ;
start transaction;
set _temp=(select count(*) from account where id=_from);
if _temp=0 then set t_error=1; end if;
set _temp=(select count(*) from account where id=_to);
if _temp=0 then set t_error=1; end if; update account set account.account=account.account+num where id=_to;
update account set account.account=account.account-num where id=_from; if t_error=1 then
AffectRows:=0;
rollback;
else
AffectRows:=sql%rowcount;
commit; end if; end// delimiter ;
就是这段代码能不能带出所影响的行数delimiter // CREATE DEFINER=`root`@`localhost` PROCEDURE `transforms`(_from int ,_to int ,num int )
begin declare t_error int default 0;
declare _temp int default 0;
declare continue handler for sqlexception set t_error=1;
start transaction;
set _temp=(select count(*) from account where id=_from);
if _temp=0 then set t_error=1; end if;
set _temp=(select count(*) from account where id=_to);
if _temp=0 then set t_error=1; end if; update account set account.account=account.account+num where id=_to;
update account set account.account=account.account-num where id=_from; if t_error=1 then
rollback;
else
commit; end if; end// delimiter ;执行这段代码之后为什么所影响的行数是零啊。。但是数据库更新了
+------+
| a |
+------+
| 1 |
| 2 |
| 3 |
| 1 |
| 2 |
| 3 |
| 4 |
+------+
7 rows in set (0.14 sec)mysql> delimiter //
mysql> create procedure sp()
-> begin
-> update t1 set a=a+1;
-> end;
-> //
Query OK, 0 rows affected (0.55 sec)mysql> call sp();
-> //
Query OK, 7 rows affected (0.05 sec)mysql> delimiter ;
第二段,就是在我这里测试的时候,发现事务不管成功与失败,它所影响的行数是零。如“call transforms(1,2,1)”,数据库里有1和2这两条记录,但数据库给更新了
2:看6楼的例子