Oracle-savepoint相关

2024-05-07 14:44

文章标签 oracle 相关 savepoint

本文主要是介绍Oracle-savepoint相关，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

总结：在同一个会话下，如果savepoint的名称相同，新的savepoint则会覆盖旧的savepoint，在不同的会话下，savepoint的名称相同，不会相互影响。
为了避免savepoint名称相同，可以在建立savepoint名称时使用时间戳拼接

经过验证，在不同的session使用相同名称的savepoint，也不会互相影响。

session1

INSERT INTO your_table (column1) VALUES ('s1-value1');
SAVEPOINT sp1;INSERT INTO your_table (column1) VALUES ('s1-value2');
SAVEPOINT sp2;INSERT INTO your_table (column1) VALUES ('s1-value3');
SAVEPOINT sp3;--column1 in ('s1-value1', 's1-value2', 's1-value3')
SELECT * FROM your_table;ROLLBACK WORK TO sp2;--column1 in ('s1-value1', 's1-value2')
SELECT * FROM your_table;

session2

INSERT INTO your_table (column1) VALUES ('s2-value1');
SAVEPOINT sp1;INSERT INTO your_table (column1) VALUES ('s2-value2');
SAVEPOINT sp2;INSERT INTO your_table (column1) VALUES ('s2-value3');
SAVEPOINT sp3;--column1 in ('s2-value1', 's2-value2', 's2-value3')
SELECT * FROM your_table;ROLLBACK WORK TO sp1;--column1 in ('s2-value1')
SELECT * FROM your_table;

经过验证，在同一个session下，相同名称的savepoint，新的savepoint会覆盖旧的savepoint

INSERT INTO your_table (column1) VALUES ('value1');
SAVEPOINT sp1;INSERT INTO your_table (column1) VALUES ('value2');
SAVEPOINT sp1;INSERT INTO your_table (column1) VALUES ('value3');
SAVEPOINT sp2;--column1 in ('value1', 'value2', 'value3')
SELECT * FROM your_table;ROLLBACK WORK TO sp1;--column1 in ('value1', 'value2')
SELECT * FROM your_table;INSERT INTO your_table (column1) VALUES ('value4');
SAVEPOINT sp4;INSERT INTO your_table (column1) VALUES ('value5');
SAVEPOINT sp5;--column1 in ('value1', 'value2', 'value4', 'value5')
SELECT * FROM your_table;ROLLBACK WORK TO sp4;--column1 in ('value1', 'value2', 'value4')
SELECT * FROM your_table;--ORA-01086: 从未在此会话中创建保存点 'SP5' 或者该保存点无效
ROLLBACK WORK TO sp5;--column1 in ('value1', 'value2', 'value4')
SELECT * FROM your_table;