关于pl/sql异常的问题

Question

以下文本摘录自 Oracle 文档Oracle® Database PL/SQL 语言参考 11g 第 1 版 (11.1)：

未处理的异常也会影响 子程序。如果退出子程序 成功后，PL/SQL 将值分配给 输出参数。但是，如果您退出 带有未处理的异常，PL/SQL 不给 OUT 赋值 参数（除非它们是 NOCOPY 参数）。 此外，如果存储 子程序因未处理而失败 异常，PL/SQL不回滚 子程序完成的数据库工作。

注意粗体文本，这是真的吗？我对此很好奇，所以我编写了以下示例来测试它。

-- create a test table
CREATE TABLE e AS SELECT * FROM HR.EMPLOYEES;   

-- create p1 which will update a row in table e
CREATE OR REPLACE PROCEDURE p1
IS
    ex EXCEPTION;
    row e%ROWTYPE;
BEGIN
    select * into row from e where employee_id=100;

    row.employee_id := 100;
    row.first_name := 'yang';

    -- update
    UPDATE e set ROW = row where employee_id = 100;
    -- and raise an error
    RAISE ex;
END;


BEGIN
    -- call the upper procedure
    p1;
END;

-- test whether update success
select * from e where employee_id=100;

-- the upper query gives me
Steven

所以我的问题是：我说得对吗？

Answer 1

请参阅以下问题： Oracle 回滚吗事务发生错误？

在您的情况下，过程 P1 将成功或失败并回滚其更改。为什么文档中的语句看起来相反（p1 在过程中间失败并留下未完成的工作）？

答案就在 报价前的句子：

请记住，如果无法找到引发异常的处理程序，PL/SQL 将向主机环境返回未处理的异常错误，主机环境决定结果。例如，在 Oracle 预编译器环境中，由失败的 SQL 语句或 PL/SQL 块所做的任何数据库更改都会回滚。

这意味着当过程失败时，如果引发的异常未得到处理，未完成的工作将被回滚。但是，如果异常被捕获并且没有重新引发，则未完成的工作将保持原样。

我们可以通过在您的示例中放置 WHEN OTHERS 块（而不是重新引发异常 - 当然这是一个非常非常糟糕的主意，请参阅下面的原因）来展示此行为：

SQL> BEGIN
  2     -- call the upper procedure
  3     p1;
  4  EXCEPTION
  5     WHEN OTHERS THEN
  6        dbms_output.put_line('log error...');
  7  END;
  8  /

log error...

PL/SQL procedure successfully completed

SQL> select employee_id, first_name from e where employee_id = 100;

EMPLOYEE_ID FIRST_NAME
----------- --------------------
        100 yang

您真的< a href="http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4018285967344#671326800346790791" rel="nofollow noreferrer">永远不要 执行此操作 ：我们留下了未完成的工作，错误被记录下来，如果不重新引发它，我们就有一个潜在的严重错误。此外，默默地忽视例外情况会导致灾难。

Answer 2

“此外，如果存储子程序因未处理的异常而失败，PL/SQL 不会回滚该子程序完成的数据库工作。”

上面的引用特指存储子程序，但以下代码块是一个匿名块，而不是存储的子程序

BEGIN
    -- call the upper procedure
    p1;
END;

因此，引用不适用。顶级匿名块的失败是执行回滚的原因（就像任何其他 SQL 语句一样）
使用以下代码进行测试表明，当 SERVERERROR 触发器被触发时（即返回主机之前），值 1 和 10 的 INSERT 已经回滚（因为重新插入 1 不会失败）重复键或死锁）。

drop table test_se_auto_tbl;

create table test_se_auto_tbl (id number(2) primary key, val varchar2(20));

create or replace trigger test_se_auto_trg after servererror on schema 
begin
  for c_rec in (select id, val from test_se_auto_tbl) loop
dbms_output.put_line(c_rec.id||':'||c_rec.val);
  end loop;
  dbms_output.put_line('Trigger');
  insert into test_se_auto_tbl values (1,'test ');
end;
/

begin
  insert into test_se_auto_tbl values (1,'test ');
  insert into test_se_auto_tbl values (10,'test 10');
  insert into test_se_auto_tbl values (100,'test 100');
end;
/

select id, val from test_se_auto_tbl;

支持这一假设的另一种情况。在这种情况下，匿名 PL/SQL 块在嵌套在另一个 PL/SQL 块内的 EXECUTE IMMEDIATE 中调用。尽管异常被外部块捕获，但当 EXECUTE IMMEDIATE 运行原子语句时，插入已经回滚。

DECLARE
  v_num NUMBER;
begin
   begin
     execute immediate 
         'declare 
           v_num number(2); 
         begin 
           insert into dummy values (1);
           dbms_output.put_line(101);
           v_num := 100;
         end;';
   exception
      when others then null;
   end;
   select count(*) into v_num from dummy;
   dbms_output.put_line(v_num);
end;
/