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:
