10-89 SavePointで原子性を制御

SQLパズル

TableA    TableB
ID        ID
--        --
 1         7
 2         8
           9
          10

TableBのIDをTableAにinsertしてから、
TableBのIDをdeleteする(トランザクションの原子性はID単位で保持)

更新結果(IDが8と9のデータでエラーを発生させます)
TableA    TableB
ID        ID
--        --
 1         8
 2         9
 7
10

データ作成スクリプト

create table TableA(ID number(2));
insert into TableA values(1);
insert into TableA values(2);

create table TableB(ID number(2));
insert into TableB values(7);
insert into TableB values(8);
insert into TableB values(9);
insert into TableB values(10);
commit;

SQL

create or replace procedure test(pID TableA.ID%type) Is
begin
    savepoint a;
    insert into TableA select ID from TableB where ID = pID;
    if pID = 8 then
        raise_application_error(-20101,'例外');
    end if;

    delete from TableB where ID = pID;
    if pID = 9 then
        raise_application_error(-20101,'例外');
    end if;
exception
    when others then
        rollback to a;
end;
/

実行結果

begin
    for i in 7..10 Loop
        test(i);
    end Loop;
    commit;
end;
/

---

解説

savepointを使って、
トランザクションの原子性を制御できます

---