Oracle PL/SQL之DDL导致的隐式提交

1. 如果DDL语法正确，即使执行失败，也会导致隐式提交：

duzz$scott@orcl>create table t1 as select * from dept;

Table created.

Elapsed: 00:00:00.03

duzz$scott@orcl>update t1 set loc='xx' where deptno=10;

1 row updated.

Elapsed: 00:00:00.03

duzz$scott@orcl>drop table xx;

drop table xx

*

ERROR at line 1:

ORA-00942: table or view does not exist

Elapsed: 00:00:00.01

+++++++++++++++++++++++++++++++++++++++++++++++++++++

duzz$sys@orcl>select * from scott.t1;

DEPTNO DNAME LOC

---------- ------------------------------------------ ---------------------------------------

10 ACCOUNTING xx

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

Elapsed: 00:00:00.01

2. 如果DDL语法错误，则不会导致隐式提交，亦不会自动回滚事务：

duzz$scott@orcl>update t1 set loc='yy' where deptno=20;

1 row updated.

Elapsed: 00:00:00.00

duzz$scott@orcl>drop table col xx;

drop table col xx

*

ERROR at line 1:

ORA-00933: SQL command not properly ended

Elapsed: 00:00:00.00

+++++++++++++++++++++++++++++++++++++++++++++++++++++

duzz$sys@orcl>select * from scott.t1;

DEPTNO DNAME LOC

---------- ------------------------------------------ ---------------------------------------

10 ACCOUNTING xx

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

Elapsed: 00:00:00.00

总结：

DDL伪代码：

Begin

Parse DDL;

Commit;

Do DDL;

Exception

When others then

Null;

End;

Ref:

http://www.cublog.cn/u/25770/showart_2222436.html