甲骨文语句级别的回滚

文档地址：http://docs.oracle.com/cd/B19306_01/server.102/b14220/transact.htm#CNCPT117

 

Statement-Level Rollback

If at anytime during execution a SQL statement causes an error, all effectsof the statement are rolled back. The effect of the rollback is asif that statement had never been run. This operation is astatement-level rollback.

 

Errors discovered during SQL statement executioncause statement-level rollbacks. An example of such an error isattempting to insert a duplicate value in a primary key.Single SQL statements involved in adeadlock (competition for the same data) can alsocause a statement-level rollback. Errors discovered during SQLstatementparsing, such as a syntaxerror, have not yet been run, so they do not cause astatement-level rollback.

 

A SQL statementthat fails causes the loss only of any work it would have performeditself.It does not cause the loss of any work that precededit in the current transaction. If the statement is a DDLstatement, then the implicit commit that immediately preceded it isnot undone.---一个执行失败的SQL语句会导致它本身执行的任何工作的丢失。它不会导致在当前事务中在此失败语句之前（即其它语句所做的工作，这个事务是多个SQL语句）所做工作的丢失。OCP认证考试指南P196页有描述。如果一个语句是DDL语句，那么直接在此语句之前的隐式提交是不会被撤销的。--即在一个DDL之前的，在一个提交操作之后（无论是COMMIT还是隐式提交）的DML语句都会隐式提交掉。参考OCA认证考试指南047第77页。(因此在维护数据库的时候，写了很多DML语句，结果不想执行的，当在后边加了一条DDL语句，那么会导致一些不必要的麻烦。）

 

--这里的概念比较重要哦。学习oracle那么长时间，这概念差点把我弄糊涂。(*^__^*)

 

例如：

CREATE TABLE orders
(order_id NUMBER(3) PRIMARY KEY,
order_date DATE,
customer_id number(3));--这里会产生隐式提交

INSERT INTO orders VALUES (100,'10mar2007',
222);
ALTER TABLE orders MODIFY order_date NOTNULL;--这里会产生隐式提交，因此INSERT语句被提交，写到数据库。

UPDATE orders SET customer_id=333;
DELETE FROM order;
The DELETE statement results in the following error:
ERROR at line 1:
ORA00942:
table or view does notexist --这里是个事务，DELETE语句会回滚，但UPDATE语句不会回滚。

 

当然在这个事务里头，直接在最后边加上ROLLBACK语句，那么就不仅是DELETE语句，还有UPDATE语句都回滚了。那是在这个事务里头所有DML都回滚。