有奖活动：PL/SQL Challenge 每日一题：2012-3-2 自治事务

newkid

最快答对的puber将获得彩蛋章一枚，其他会员如果提供有价值的分析、讨论也可获得彩蛋章一枚。

以往旧题索引：
http://www.itpub.net/thread-1499223-1-1.html

原始出处：
http://www.plsqlchallenge.com/

作者：Steven Feuerstein
难度：高

我创建了如下的日志表并填入数据:

CREATE TABLE plch_log
(
   code         NUMBER PRIMARY KEY
, text         VARCHAR2 (4000)
, created_on   DATE
, created_by   VARCHAR2 (30)
)
/

BEGIN
   INSERT INTO plch_log
        VALUES (-1422
              , 'Problem encountered'
              , SYSDATE
              , USER);

   COMMIT;
END;
/

然后我创建了这个过程来管理表的内容：
CREATE OR REPLACE PROCEDURE plch_log_action (
   action_in   IN VARCHAR2
, code_in     IN plch_log.code%TYPE DEFAULT NULL
, text_in     IN plch_log.text%TYPE DEFAULT NULL)
IS
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   CASE action_in
      WHEN 'DELETE'
      THEN
         DELETE FROM plch_log
               WHERE code = code_in;
      WHEN 'UPDATE'
      THEN
         UPDATE plch_log
            SET code = code * 10
          WHERE 1 = 2;
      WHEN 'INSERT'
      THEN
         INSERT INTO plch_log
              VALUES (code_in, text_in, SYSDATE, USER);
      ELSE
         NULL;
   END CASE;
END;
/

哪些选项在执行之后屏幕上会显示 "Action completed" ?

(A)
BEGIN
   plch_log_action ('DELETE', 1000);
   DBMS_OUTPUT.put_line ('Action completed');
END;
/


(B)
BEGIN
   plch_log_action ('DELETE', -1422);
   DBMS_OUTPUT.put_line ('Action completed');
END;
/

(C)
BEGIN
   plch_log_action ('UPDATE');
   DBMS_OUTPUT.put_line ('Action completed');
END;
/

(D)
BEGIN
   plch_log_action ('INSERT', 2000, 'Save it!');
   DBMS_OUTPUT.put_line ('Action completed');
END;
/

(E)
BEGIN
   plch_log_action ('MERGE'
                  , 2000
                  , 'Insert-Update Together');
   DBMS_OUTPUT.put_line ('Action completed');
END;
/

〇〇

e

oversoul

D,E

hudingchen

e,
If you try to exit an active autonomous transaction without committing or rolling back, Oracle raises an exception. If the exception goes unhandled, or if the transaction ends because of some other unhandled exception, the transaction is rolled back.
来自http://docs.oracle.com/cd/B19306 ... agma.htm#sthref2466
含有自治事务的程序中，第一个SQL语句会开启一个事务，所以a,b,c,d,都执行了SQL语句，只有e没有执行SQL，也就没有开启事务，不会抛出异常。

solomon_007

OO起得真早

notags

自治事务DML应该要显示地结束，否则主事务被挂起，所以是E

wanglei2924

E
自治事务内没有提交，只有E是没有事物发生的case

jimn1982

E必须的
如果在过程能处理异常就没有问题了

CREATE OR REPLACE PROCEDURE plch_log_action (
   action_in   IN VARCHAR2
, code_in     IN plch_log.code%TYPE DEFAULT NULL
, text_in     IN plch_log.text%TYPE DEFAULT NULL)
IS
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   CASE action_in
      WHEN 'DELETE'
      THEN
         DELETE FROM plch_log
               WHERE code = code_in;
      WHEN 'UPDATE'
      THEN
         UPDATE plch_log
            SET code = code * 10
          WHERE 1 = 2;
      WHEN 'INSERT'
      THEN
         INSERT INTO plch_log
              VALUES (code_in, text_in, SYSDATE, USER);
      ELSE
         NULL;
   END CASE;
   exception
     when others then
       null;
END;
/

newkid

答案E，OO起个大早没白费。
如果自治事务里面执行了DML, 哪怕这个DML没有修改任何数据，在退出之前也必须显式提交或回滚事务。

aslo_brook

测试验证，DML事务中没有commit或rollback或异常抛出。
create or replace procedure dbmonitor.plch_log_action(
action_in in varchar2,
code_in in plch_log.code%TYPE DEFAULT NULL,
text_in in plch_log.text%TYPE DEFAULT NULL)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
        CASE action_in
                WHEN 'DELETE' THEN
                DELETE FROM plch_log WHERE code = code_in;
                COMMIT;
                WHEN 'UPDATE' THEN
                UPDATE plch_log SET code = code *10 where 1 = 2;
                COMMIT;
                WHEN 'INSERT' THEN
                INSERT INTO plch_log VALUES(code_in,text_in,SYSDATE,USER);
                COMMIT;
                ELSE
                        NULL;
        END CASE;
END;
/
create or replace procedure dbmonitor.plch_log_action(
action_in in varchar2,
code_in in plch_log.code%TYPE DEFAULT NULL,
text_in in plch_log.text%TYPE DEFAULT NULL)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
        CASE action_in
                WHEN 'DELETE' THEN
                DELETE FROM plch_log WHERE code = code_in;
                ROLLBACK;
                WHEN 'UPDATE' THEN
                UPDATE plch_log SET code = code *10 where 1 = 2;
                ROLLBACK;
                WHEN 'INSERT' THEN
                INSERT INTO plch_log VALUES(code_in,text_in,SYSDATE,USER);
                ROLLBACK;
                ELSE
                        NULL;
        END CASE;
END;
/

17:08:07 SQL> BEGIN
17:08:15   2    plch_log_action('DELETE',-1422);
17:08:15   3    DBMS_OUTPUT.put_line('Action completed');
17:08:15   4  END;
17:08:15   5  /
Action completed

PL/SQL 程序順利完成.