CREATE PROCEDURE `ba3`(id int)
begin
START TRANSACTION;------加上了
savepoint sp;
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;
end;
if @@error_count=0 then
commit;
else
rollback sp;
end if;
end;发现不能及时回到savepoint sp;
请看我的执行过程mysql> select * from ba2;-------------没有作操作前 原始数据
+------+-----+
| b | a |
+------+-----+
| 1 | 1 |
| 0 | 102 |
| 0 | 103 |
| 0 | 104 |
+------+-----+
4 rows in set (0.00 sec)mysql> call ba2(103);
ERROR 1054 (42S22): Unknown column 'id22' in 'where clause'
mysql> select * from ba2;------------执行完后
+------+-----+
| b | a |
+------+-----+
| 1 | 1 |
| 1 | 102 | ----------------------按照会滚 不因该这里b=1 相反应该为0才对
| 0 | 103 |
| 0 | 104 |
+------+-----+
4 rows in set (0.00 sec)
这个错误在什么地方 ,如何修改这个存储过程。?? (按照道理 应该事务整体会滚才对 ?????)
如果有想java的try catch就不会出现这个问题
begin
START TRANSACTION;------加上了
savepoint sp;
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;
end;
if @@error_count=0 then
commit;
else
rollback sp;
end if;
end;发现不能及时回到savepoint sp;
请看我的执行过程mysql> select * from ba2;-------------没有作操作前 原始数据
+------+-----+
| b | a |
+------+-----+
| 1 | 1 |
| 0 | 102 |
| 0 | 103 |
| 0 | 104 |
+------+-----+
4 rows in set (0.00 sec)mysql> call ba2(103);
ERROR 1054 (42S22): Unknown column 'id22' in 'where clause'
mysql> select * from ba2;------------执行完后
+------+-----+
| b | a |
+------+-----+
| 1 | 1 |
| 1 | 102 | ----------------------按照会滚 不因该这里b=1 相反应该为0才对
| 0 | 103 |
| 0 | 104 |
+------+-----+
4 rows in set (0.00 sec)
这个错误在什么地方 ,如何修改这个存储过程。?? (按照道理 应该事务整体会滚才对 ?????)
如果有想java的try catch就不会出现这个问题
编译不能通过 提示没偶这个写法 ?? (找了半天 没有找到准确的写法) ---现在的问题
---不知道具体如何解决参考:
DECLARE X2 INT;
DECLARE CONTINUE HANDLER FOR SQLWARNING SET X2= 1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET X2 = 2;
DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET X2= 3;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET X2 = 4;
现在的存储过程是
CREATE PROCEDURE `ba3`(id int)
begin
START TRANSACTION;------加上了
savepoint sp;
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;
end;
if @@error_count=0 then
commit;
else
rollback sp;
end if;
end;
不知道如何申明例外 (异常)
需要加declare handle SQLERROR ... ROLLBACK. 不知道具体的写法
begin
DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
START TRANSACTION;------加上了
savepoint sp;
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;
end;
if @@error_count=0 then
commit;
else
rollback to savepoint sp;
end if;
end; 无论在autocommit=0/1情况成功通过
官方手册确实是最好的但是没有找到类似
DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK; 这个如何查找实现的
有如DECLARE CONTINUE HANDLER FOR SQLWARNING SET X2= 1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET X2 = 2;
DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET X2= 3;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET X2 = 4;这些 定义SQLEXCEPTION SQLWARNING NOT FOUND等等从那里来的???
后面的数字SQLSTATE '23000' 又是来自哪里???
手册中Appendix B. Errors, Error Codes, and Common Problems
Table of ContentsB.1. Problems and Common Errors
B.1.1. How to Determine What Is Causing a Problem
B.1.2. Common Errors When Using MySQL Programs
B.1.3. Installation-Related Issues
B.1.4. Administration-Related Issues
B.1.5. Query-Related Issues
B.1.6. Optimizer-Related Issues
B.1.7. Table Definition-Related Issues
B.1.8. Known Issues in MySQL
B.2. Types of Error Values
B.3. Server Error Codes and Messages
B.4. Client Error Codes and Messages