Oracle中預存程序如何控制提交和復原

create table test1(id number,name varchar2(20));
create table test2(id number,name varchar2(20));
create table test3(id number,name varchar2(20));

1. t1中沒有顯示commit;

create or replace procedure t1
as
begin
  for i in 1..10000 loop
    insert into test1(id,name) values(i,'leng'||i);
  end loop;
end;

t1中沒有顯示commit;

exec t1 之後,如果不退出session的話,是不會提交的,此時如果rollback,則復原,如果commit則提交, 如果disconn的話,會自動認可;

 

2. t1中有顯示commit;

CREATE OR REPLACE procedure SCOTT.t1
as
begin
  for i in 1..10000 loop
    insert into test1(id,name) values(i,'leng'||i);
    commit;
  end loop;
end;
/

t1中有顯示commit時:

exec t1之後,會直接提交;

3.

CREATE OR REPLACE procedure SCOTT.t1
as
begin
  for i in 1..10000 loop
    insert into test1(id,name) values(i,'leng'||i);
    commit;
    if i=20 then
       exit;
    end if;
  end loop;
end;
/

迴圈中有顯示commit, exit前已經提交的就commit了.

4. procedure中既有commit也有rollback,commit之前的就提交,commit和rollback之間的就復原.

CREATE OR REPLACE procedure SCOTT.t1
as
begin
  for i in 1..10000 loop
    insert into test1(id,name) values(i,'leng'||i);
    commit;
    if i=20 then
     rollback;
     exit;
    end if;
  end loop;
end;
/

4. procedure中既有commit也有rollback,commit之前的就提交,commit和rollback之間的就復原.

CREATE OR REPLACE procedure SCOTT.t1
as
begin
  for i in 1..10000 loop
    insert into test1(id,name) values(i,'leng'||i);
    commit;
    if i=20 then
     rollback;
     exit;
    end if;
  end loop;
end;
/

5.procedure中有部分commit,commit之前的就提交,commit之後的就不提交,如果在session中rollback則復原,commit則提交,退出自動認可.

CREATE OR REPLACE procedure SCOTT.t1
as
begin
  for i in 1..10000 loop
    insert into test1(id,name) values(i,'leng'||i);
    if i<20 then
    commit;
    end if;
  end loop;
end;
/

 

6. procedure中沒有顯示commit和rollback, 如果程式出錯,則強制退出程式並復原.

CREATE OR REPLACE procedure SCOTT.t1
as
    var_name varchar2(20);
begin
  for i in 1..10000 loop
    insert into test1(id) values(i);
    if i=100 then
      select name into var_name from test1 where id=0 ;  --類比出錯
    end if;
  end loop;
end;
/

6. procedure中有顯示commit, 如果程式出錯,commit之前的就已經提交了,commit和出錯之間的強制復原.
CREATE OR REPLACE procedure SCOTT.t1
as
    var_name varchar2(20);
begin
  for i in 1..10000 loop
    insert into test1(id) values(i);
    if i<20 then
        commit;
    end if;
   
    if i=100 then
      select name into var_name from test1 where id=0 ;
    end if;
  end loop;
end;
/

result: 19

7. 嵌套出錯. 出錯前commit的就提交了,未commit的強制退出程式並復原.

create or replace procedure t1
as
begin
  for i in 1..10000 loop
    insert into test1(id,name) values(i,'leng'||i);
  end loop;
  commit;
  t2;
end;

CREATE OR REPLACE procedure SCOTT.t2
as
    var_name varchar2(20);
begin
  for i in 1..10000 loop
    insert into test2(id) values(i);
    if i<20 then
        commit;
    end if;
   
    if i=100 then
      select name into var_name from test1 where id=0; --出錯的地方.
    end if;
  end loop;
end;
/

t1:10000
t2:19

8.  t1嵌套t2, t2的commit對t1也起效.
create or replace procedure t1
as
begin
  for i in 1..10000 loop
    insert into test1(id,name) values(i,'leng'||i);
  end loop;
  t2;
end;

CREATE OR REPLACE procedure SCOTT.t2
as
    var_name varchar2(20);
begin
  for i in 1..10000 loop
    insert into test2(id) values(i);
    if i<20 then
     commit;
    end if;
   
    if i=100 then
      select name into var_name from test1 where id=0;
    end if;
  end loop;
end;
/

t1: 10000
t2:19

9.  t1嵌套t2, t2的rollback對t1也起效.
create or replace procedure t1
as
begin
  for i in 1..10000 loop
    insert into test1(id,name) values(i,'leng'||i);
  end loop;
  t2;
end;

CREATE OR REPLACE procedure SCOTT.t2
as
    var_name varchar2(20);
begin
  for i in 1..10000 loop
    insert into test2(id) values(i);
    if i<20 then
     commit;
    end if;
   
    if i=100 then
      select name into var_name from test1 where id=0;
    end if;
  end loop;
end;
/

t1: 10000
t2:19

10. t1嵌套t2,t2嵌套t3, 出錯前commit的提交,未提交的強制復原.

CREATE OR REPLACE procedure SCOTT.t1
as
begin
  for i in 1..10000 loop
    insert into test1(id,name) values(i,'leng'||i);
  end loop;
  t2;
end;
/

CREATE OR REPLACE procedure SCOTT.t2
as
begin
  for i in 1..10000 loop
    insert into test2(id,name) values(i,'leng'||i);
  end loop;
  t3;
end;
/

CREATE OR REPLACE procedure SCOTT.t3
as
    var_name varchar2(20);
begin
  for i in 1..10000 loop
    insert into test3(id) values(i);
    if i<20 then
     commit;
    end if;
   
    if i=100 then
      select name into var_name from test1 where id=0;
    end if;
  end loop;
end;
/

t1:10000
t2:10000
t3:19

11. 總結
把一個procedure中所有的程式和語句看成順序執行,不管是嵌套多少層,commit的就起效,未commit的,如果出錯則從出錯的地方強制退出程式,如果不出錯,退出session時預設提交.