Oracle PL/SQL之DDL導(dǎo)致的隱式提交

1. 如果DDL語法正確，即使執(zhí)行失敗，也會導(dǎo)致隱式提交：

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語法錯(cuò)誤，則不會導(dǎo)致隱式提交，亦不會自動回滾事務(wù)：

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

總結(jié)：

DDL偽代碼：

Begin

Parse DDL;

Commit;

Do DDL;

Exception

When others then

Null;

End

服務(wù)器租用/服務(wù)器托管中國五強(qiáng)！虛擬主機(jī)域名注冊頂級提供商！15年品質(zhì)保障！--億恩科技[ENKJ.COM]