这个存储过程为什么会失败 加了if @@error_count=0
这个存储过程为什么会失败
CREATE PROCEDURE `ba3`(id int)
begin
START TRANSACTION;
update ba2 a, (select max(a) as aa from ba2 where a < id) b set a.b=1 where a.a=b.aa;
delete from ba2 where a=id;
if @@error_count=0 then
commit;
else
rollback;
end;
建立表语句如下（附后 CREATE TABLE `ba2` (
`b` int(11) default NULL,
`a` int(11) NOT NULL auto_increment,
PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8; ）
错误的关键在
if @@error_count=0 then
commit;
else
rollback;
提示错误
1064－－you have an error in your SQL syntax;
check the manual that corresponds to your MYSQL server version for the right
syntax to use near “” at line 14
end if 哪儿去了？
1，用@@error_count，我没测试过，是否可行. 但“前面定义了事务 他会自动会滚的”这句话在mysql中还不成立，除非在前面设置出错处理handle
declare headle SQLERROR … ROLLBACK.
2, 一定要加上start transaction, 否则commit和rollback不起作用，除非auto_commit=0
-
SQL code
CREATE PROCEDURE `handlerdemo`()
BEGIN
DECLARE exit HANDLER FOR 1146 SET @x2 = 2;
INSERT INTO test1 VALUES (1);
set @x2=@x2+1;
select @x2;
END
