CUBRID學習筆記17 事務的回滾，cubrid學習筆記

CUBRID學習筆記17 事務的回滾，cubrid學習筆記

語法: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的例子