CUBRID学习笔记17 事务的回滚

语法:ROLLBACK [ WORK ]

下面的语句会报错

ALTER TABLE code DROP s_name;

INSERT INTO code (s_name, f_name) VALUES (‘D’,’Diamond’);

ERROR: s_name is not defined.

回滚 修复破损的伤口

ROLLBACK WORK;

再来

ALTER TABLE code drop s_name;

INSERT INTO code (f_name) VALUES (‘Diamond’);

COMMIT WORK;

部分回滚

可以设置标记,使回滚到指定的标记位置

方法1 SAVEPOINT mark;

mark:

_ a SQL identifier

_ a host variable (starting with 🙂

方法2

ROLLBACK [ WORK ] [ TO [ SAVEPOINT ] mark ] [ ; ]

mark:

_ a SQL identifier

_ a host variable (starting with 🙂

例子

首先设置两个回滚的标记 sp1 和sp2

CREATE TABLE athlete2 (name VARCHAR(40), gender CHAR(1), nation_code CHAR(3), event VARCHAR(30));

INSERT INTO athlete2(name, gender, nation_code, event)

VALUES (‘Lim Kye-Sook’, ‘W’, ‘KOR’, ‘Hockey’);

SAVEPOINT SP1;

SELECT * from athlete2;

INSERT INTO athlete2(name, gender, nation_code, event)

VALUES (‘Lim Jin-Suk’, ‘M’, ‘KOR’, ‘Handball’);

SELECT * FROM athlete2;

SAVEPOINT SP2;

RENAME TABLE athlete2 AS sportsman;

SELECT * FROM sportsman;

ROLLBACK WORK TO SP2;

上面的重命名表的操作将被回滚

SELECT * FROM athlete2;

DELETE FROM athlete2 WHERE name = ‘Lim Jin-Suk’;

SELECT * FROM athlete2;

ROLLBACK WORK TO SP2;

上面的删除也不会执行,会被回滚

SELECT * FROM athlete2;

ROLLBACK WORK TO SP1;

SELECT * FROM athlete2;

COMMIT WORK;

这个是回滚到sp1的例子