如何确保所有事务都已提交或回滚?

最后,做一个 COMMIT; 或 ROLLBACK; -就这样。当然,只有在中间没有执行隐式提交的命令时,才能获得预期的行为,例如。 TRUNCATE TABLE ...

注意,如果将来添加任何DDL语句(create、alter),这些语句将被提交。

BEGIN

insert into table1 (field1, Field2) values ('value1','value2');

-- do some staff

insert into table2 (field1, Field2) values ('value1','value2');

-- do some staff

delete from table3 where id = xx;
commit;
  -- if any error occured
   WHEN OTHERS THEN  
 rollback;
END; 
/

procedure MyProcedure( ret_status out int ) is
..
..

调用 (可以是另一个过程/块或Web应用程序)

DECLARE
     v_ret_status   INTEGER;
BEGIN
     myprocedure(v_ret_status);
     IF
          v_ret_status = 0
     THEN
          COMMIT;
     ELSE
          ROLLBACK;
     END IF;
END;
/

此外,最好调用日志记录过程或 dbms_output 在异常部分显示相关错误消息。

您要求的或多或少是默认行为:

create table test1 (id integer not null);
create table test2 (id integer not null);
create table test3 (id integer not null);

create or replace procedure myProcedure
    ( p1 test1.id%type
    , p2 test2.id%type
    , p3 test3.id%type )
as
begin
    insert into test1 (id) values (p1);
    insert into test2 (id) values (p2);
    insert into test3 (id) values (p3);
end myProcedure;
/

call myProcedure(1, 2, 3);
-- completes successfully

call myProcedure(1, 2, null);
-- fails with:
-- ERROR at line 1:
-- ORA-01400: cannot insert NULL into ("WILLIAM"."TEST3"."ID")
-- ORA-06512: at "WILLIAM.MYPROCEDURE", line 9

select 'test1' as table_name, test1.id from test1 union all
select 'test2', test2.id from test2 union all
select 'test3', test3.id from test3
order by 1;

TABLE         ID
----- ----------
test1          1
test2          2
test3          3

3 rows selected.

因此,我有成功调用中的三行,失败调用中没有任何行,所有这些都没有显式提交或回滚。

您可以添加一个 commit

,只有当异常一直传播到调用方时,才会发生默认的“回滚到隐式保存点”行为,例如,如果我尝试使用以下方法处理它:

begin
    delete test1;
    delete test2;
    delete test3;

    myProcedure(1, 2, null);
exception
    when others then
        dbms_output.put_line(sqlerrm);
        dbms_output.put_line(dbms_utility.format_error_backtrace);
end;

ORA-01400: cannot insert NULL into ("WILLIAM"."TEST3"."ID")
ORA-06512: at "WILLIAM.MYPROCEDURE", line 9
ORA-06512: at line 6

PL/SQL procedure successfully completed.

select 'test1' as table_name, test1.id from test1 union all
select 'test2', test2.id from test2 union all
select 'test3', test3.id from test3
order by 1;

TABLE         ID
----- ----------
test1          1
test2          2

2 rows selected.

无论如何,要明确地处理所有这些问题,您可能需要以下内容:

create or replace procedure myProcedure
    ( p1 test1.id%type
    , p2 test2.id%type
    , p3 test3.id%type )
as
begin
    savepoint start_of_processing;

    insert into test1 (id) values (p1);
    insert into test2 (id) values (p2);
    insert into test3 (id) values (p3);

    commit;
exception
    when others then
        rollback to start_of_processing;
        -- Log using whatever logging package you have:
        logger.message('Something appears to have gone disastrously amiss');
        raise;
end myProcedure;

不过,请记住 the act of observing an exception changes it irrevocably