这个存储过程为什么会失败
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;
elserollback;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
不好意思还是想讨论以下 我看过 写了些存储过程发现普遍都是这样的写法
-- 启动事务
start transaction;
-- 查找最大编号
select max(rep_sync_id) into iMax_rep_sync_id from rep_shadow_rs;
-- 有新数据
if iMax_rep_sync_id>=iLast_rep_sync_id then
-- 调用
call pro_rep_shadow_rs_do(iLast_rep_sync_id,iMax_rep_sync_id);
-- 更新日志
update rep_de_proc_log set rid=iLast_rep_sync_id,eid=iMax_rep_sync_id where tbl='rep_shadow_rs';
end if;
-- 运行没有异常,提交事务
commit;
-- 设置返回值为1
set rtn=1;
end; 直接前面start transaction;
后面 -- 运行没有异常,提交事务
commit想追问 是否有必要 加上
错误的关键在
if @@error_count=0 then
commit;
else
rollback;
end if; (感觉确实也没有多少必要? 前面定义了事务 他会自动会滚的)
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 if;
end;
CREATE PROCEDURE `ba3`(id int)
begin
------------------没有加那句话
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 if;
end;
问下2者之间的差别 ??? (或者是否有差别)
declare headle SQLERROR ... ROLLBACK.2, 一定要加上start transaction, 否则commit和rollback不起作用,除非auto_commit=0
我搞了20分,才发现这个问题
下面的可以?
CREATE PROCEDURE `ba3`(id int)
begin
declare headle SQLERROR ... ROLLBACK.
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 if;
end;
------
declare handle SQLERROR ... ROLLBACK.
编译不能通过 提示没偶这个写法 ???
BEGIN
DECLARE exit HANDLER FOR 1146 SET @x2 = 2;
INSERT INTO test1 VALUES (1);
set @x2=@x2+1;
select @x2;
END
LZ这是个HANDLER的例子参考下