If you have following batch of SQL:
Insert into table A values (123, 'Student A', 'Teacher B', 'Class C')
Insert into table B values (123, 'English', 'Grade A')
Insert into table B values (123, 'Math', 'Grade B')If the third statement failed, how can you ensure the first and second statement will not be applied to the database too.也就是说:在批量执行三条插入语句,第三句因为主键冲突插入失败,现在你怎么确定前两句没有应用于数据库中?
Insert into table A values (123, 'Student A', 'Teacher B', 'Class C')
Insert into table B values (123, 'English', 'Grade A')
Insert into table B values (123, 'Math', 'Grade B')If the third statement failed, how can you ensure the first and second statement will not be applied to the database too.也就是说:在批量执行三条插入语句,第三句因为主键冲突插入失败,现在你怎么确定前两句没有应用于数据库中?
用这个看看:BEGIN
INSERT INTO A VALUES (123, 'Student A', 'Teacher B', 'Class C');
INSERT INTO B VALUES (123, 'English', 'Grade A');
INSERT INTO B VALUES (123, 'Math', 'Grade B');
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
ROLLBACK;
END;
还有就是这三条insert语句都已经执行了,在第三句时出错,现在我想问的就是通过什么方法查看前两句没有对数据库表造成影响???
"If the third statement failed, how can you ensure the first and second statement will not be applied to the database too. "其实就是说要确保事务回滚。
看样子我真的要好好学习一下事务了
BEGIN
INSERT INTO A VALUES (123, 'Student A', 'Teacher B', 'Class C');
INSERT INTO B VALUES (123, 'English', 'Grade A');
INSERT INTO B VALUES (123, 'Math', 'Grade B');
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
ROLLBACK;
END;
前面两条还插进去了?
但是如果我不把三条insert语句放在代码块中,直接分别运行三条insert,这样前面两条是插进去了.
我把自己一直卡在"怎么确定"那里了,~~
谢谢dreamhunter_lan !不过还有一个疑问:
把三条insert语句放在代码块中,和 直接分别运行三条insert, 有什么区别?